Hi, I probably do something stupid, but I cannot figure out how to set the date as deault when
inserting a record in a table. I have created a Default in the database, giving it a name and the Value = GetDate() and bind it to the desired column.
When I insert a record without filling in a value in this datefield, the table shows me "01/01/1900" in stead of Today.
What am I doing wrong ?
Help is appreciated, Ger.
How are you doing the insert?|||
Here is part of the code :
Dim ConnStr As String = "workstation id=ONTWIKKEL;packet size=4096;integrated security=SSPI;data source='ONTWIKKEL\WEBAPPS';persist security info=False;initial catalog=UPOdyssee"
Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConnStr)
Dim mSQL As String
Dim a(99)
a(1) = Request.Form("HtxtVestiging")
a(2) = Request.Form("HtxtInschrijfdatum")
a(3) = Request.Form("HtxtNaamVoorletters")
a(4) = Request.Form("HtxtRoepnaam")
a(5) = Request.Form("HtxtLoginnaam")
a(6) = Request.Form("HtxtPaswoord")
a(7) = Request.Form("HtxtAdres")
a(8) = Request.Form("HtxtPostcode")
a(9) = Request.Form("HtxtWoonplaats")
a(10) = Request.Form("HtxtTelnr")
a(11) = Request.Form("HtxtEmail")
a(12) = Request.Form("HtxtMobiel")
a(13) = Request.Form("HtxtBereikbaarVia")
a(14) = Request.Form("HtxtGeboortedatum")
a(15) = DDL_Geslacht.SelectedValue
a(16) = Request.Form("HtaWatVoorWerk")
a(17) = DDL_VastTijdelijk.SelectedValue
a(18) = Request.Form("HtxtVanafWelkePeriode")
a(19) = Request.Form("HtxtTotWelkePeriode")
a(20) = DDL_Beschikbaarheid.SelectedValue
a(21) = Request.Form("HtxtVanUren")
a(22) = Request.Form("HtxtTotUren")
a(23) = DDL_Vervoer.SelectedValue
a(24) = DDL_Reisbereidheid.SelectedValue
a(25) = Request.Form("HtxtMaxReistijd")
a(26) = Request.Form("HtxtMaxKM")
a(27) = Request.Form("HtxtInkomenBruto")
a(28) = Request.Form("HtxtInkomenNetto")
a(29) = DDL_Uitkering.SelectedValue
a(30) = Request.Form("HtxtUitkering")
a(31) = Request.Form("HtxtFiscaalnr")
a(32) = DDL_Identificatiebewijs.SelectedValue
a(33) = Request.Form("HtxtSoortLegitimatiebewijs")
a(34) = Request.Form("HtxtLegitimatiebewijsnr")
a(35) = Request.Form("HtxtGeldigTot")
a(36) = DDL_Rijbewijs.SelectedValue
a(37) = Request.Form("HtxtSoortRijbewijs")
a(38) = Request.Form("HtxtRijbewijsnr")
a(39) = DDL_MilitaireDienst.SelectedValue
a(40) = Request.Form("HtxtFunctieMD")
a(41) = Request.Form("HtxtJaarMD")
a(42) = Request.Form("HtxtPaspoortnr")
a(43) = Request.Form("HtxtEinddatumPaspoort")
a(44) = Request.Form("HtxtVergunningnr")
a(45) = Request.Form("HtxtEinddatumVergunning")
a(46) = Request.Form("HtxtBankGiroNr")
a(47) = DDL_Ziektekosten.SelectedValue
a(48) = Request.Form("HtxtPolisnr")
a(49) = DDL_HoeAanOnsGekomen.SelectedValue
a(50) = Request.Form("HtxtOpl1")
a(51) = Request.Form("HtxtOpl2")
a(52) = Request.Form("HtxtOpl3")
a(53) = Request.Form("HtxtOpl4")
a(54) = Request.Form("HtxtOpl5")
a(55) = Request.Form("HtxtOpl6")
a(56) = Request.Form("HtxtOpl7")
a(57) = Request.Form("HtxtRichting1")
a(58) = Request.Form("HtxtRichting2")
a(59) = Request.Form("HtxtRichting3")
a(60) = Request.Form("HtxtRichting4")
a(61) = Request.Form("HtxtRichting5")
a(62) = Request.Form("HtxtRichting6")
a(63) = Request.Form("HtxtRichting7")
a(64) = Request.Form("HtxtDipl1")
a(65) = Request.Form("HtxtDipl2")
a(66) = Request.Form("HtxtDipl3")
a(67) = Request.Form("HtxtDipl4")
a(68) = Request.Form("HtxtDipl5")
a(69) = Request.Form("HtxtDipl6")
a(70) = Request.Form("HtxtDipl7")
a(71) = Request.Form("HtxtJaar1")
a(72) = Request.Form("HtxtJaar2")
a(73) = Request.Form("HtxtJaar3")
a(74) = Request.Form("HtxtJaar4")
a(75) = Request.Form("HtxtJaar5")
a(76) = Request.Form("HtxtJaar6")
a(77) = Request.Form("HtxtJaar7")
a(78) = DDL_NogStudie.SelectedValue
a(79) = Request.Form("HtxtWelkeStudie")
a(80) = Request.Form("HtxtWGVanTot1")
a(81) = Request.Form("HtxtWGVanTot2")
a(82) = Request.Form("HtxtWGVanTot3")
a(83) = Request.Form("HtxtWGVanTot4")
a(84) = Request.Form("HtxtWGVanTot5")
a(85) = Request.Form("HtxtWGVanTot6")
a(86) = Request.Form("HtxtFunctie1")
a(87) = Request.Form("HtxtFunctie2")
a(88) = Request.Form("HtxtFunctie3")
a(89) = Request.Form("HtxtFunctie4")
a(90) = Request.Form("HtxtFunctie5")
a(91) = Request.Form("HtxtFunctie6")
a(92) = Request.Form("HtxtVertrek1")
a(93) = Request.Form("HtxtVertrek2")
a(94) = Request.Form("HtxtVertrek3")
a(95) = Request.Form("HtxtVertrek4")
a(96) = Request.Form("HtxtVertrek5")
a(97) = Request.Form("HtxtVertrek6")
a(98) = Request.Form("HtaBijzOpm")
a(99) = DDL_Functie.SelectedIndex + 1 ' i.v.m. de zerobased van de ddl en de 1-based van de tabel
mSQL = "INSERT into Medewerkers (Vestiging,Inschrijfdatum, Naam, Roepnaam, Loginnaam, Paswoord, "
mSQL = mSQL & "Adres, Postcode, Woonplaats, Telefoonnummer, Email, Mobiel, BereikTel, Geboortedatum, Geslacht, WatVoorWerk, VastTijdelijk, VanafPeriode, TotPeriode, Beschikbaarheid, "
mSQL = mSQL & "VanUren, TotUren, Vervoer, Reisbereidheid, MaxReistijd, MaxKM, InkomenBruto, InkomenNetto, Uitkering, SoortUitkering, Fiscaalnr, "
mSQL = mSQL & "Identificatiebewijs, SoortLegibewijs, Legitimatiebewijs, GeldigTot, Rijbewijs, "
mSQL = mSQL & "Soortrijbewijs, Rijbewijsnr, MilitaireDienst, FunctieMD, JaarMD, Paspoortnr, EinddatumPaspoort, Vergunningnr, EinddatumVergunning, BankGironr, "
mSQL = mSQL & "Ziektekosten, Polisnr, HoeAanOnsGekomen, Opl1, Opl2, Opl3, Opl4, Opl5, Opl6, "
mSQL = mSQL & "Opl7, Richting1, Richting2, Richting3, Richting4, Richting5, Richting6, Richting7, Dipl1, Dipl2, Dipl3, Dipl4, Dipl5, Dipl6, Dipl7, "
mSQL = mSQL & "Jaar1, Jaar2, Jaar3, Jaar4, Jaar5, Jaar6, Jaar7, NogStudie, WelkeStudie, WGVanTot1, WGVanTot2, WGVanTot3, WGVanTot4, WGVanTot5, "
mSQL = mSQL & "WGVanTot6, Functie1, Functie2, Functie3, Functie4, Functie5, Functie6, Vertrek1, Vertrek2, Vertrek3, Vertrek4, "
mSQL = mSQL & "Vertrek5, Vertrek6, BijzOpm, FunctieID) "
mSQL = mSQL & "VALUES ('" & a(1) & "','" &a(2) & "','" & a(3) & "','" & a(4) & "','" & a(5) & "','" & a(6) & "',"
mSQL = mSQL & "'" & a(7) & "','" & a(8) & "','" & a(9) & "','" & a(10) & "','" & a(11) & "','" & a(12) & "',"
mSQL = mSQL & "'" & a(13) & "','" & a(14) & "','" & a(15) & "','" & a(16) & "','" & a(17) & "','" & a(18) & "',"
mSQL = mSQL & "'" & a(19) & "','" & a(20) & "','" & a(21) & "','" & a(22) & "','" & a(23) & "','" & a(24) & "',"
mSQL = mSQL & "'" & a(25) & "','" & a(26) & "','" & a(27) & "','" & a(28) & "','" & a(29) & "','" & a(30) & "',"
mSQL = mSQL & "'" & a(31) & "','" & a(32) & "','" & a(33) & "','" & a(34) & "','" & a(35) & "','" & a(36) & "',"
mSQL = mSQL & "'" & a(37) & "','" & a(38) & "','" & a(39) & "','" & a(40) & "','" & a(41) & "','" & a(42) & "',"
mSQL = mSQL & "'" & a(43) & "','" & a(44) & "','" & a(45) & "','" & a(46) & "','" & a(47) & "','" & a(48) & "',"
mSQL = mSQL & "'" & a(49) & "','" & a(50) & "','" & a(51) & "','" & a(52) & "','" & a(53) & "','" & a(54) & "',"
mSQL = mSQL & "'" & a(55) & "','" & a(56) & "','" & a(57) & "','" & a(58) & "','" & a(59) & "','" & a(60) & "',"
mSQL = mSQL & "'" & a(61) & "','" & a(62) & "','" & a(63) & "','" & a(64) & "','" & a(65) & "','" & a(66) & "',"
mSQL = mSQL & "'" & a(67) & "','" & a(68) & "','" & a(69) & "','" & a(70) & "','" & a(71) & "','" & a(72) & "',"
mSQL = mSQL & "'" & a(73) & "','" & a(74) & "','" & a(75) & "','" & a(76) & "','" & a(77) & "','" & a(78) & "',"
mSQL = mSQL & "'" & a(79) & "','" & a(80) & "','" & a(81) & "','" & a(82) & "','" & a(83) & "','" & a(84) & "',"
mSQL = mSQL & "'" & a(85) & "','" & a(86) & "','" & a(87) & "','" & a(88) & "','" & a(89) & "','" & a(90) & "',"
mSQL = mSQL & "'" & a(91) & "','" & a(92) & "','" & a(93) & "','" & a(94) & "','" & a(95) & "','" & a(96) & "',"
mSQL = mSQL & "'" & a(97) & "','" & a(98) & "','" & a(99) & "')"
Dim Command As SqlClient.SqlCommand = New SqlClient.SqlCommand(mSQL)
Dim x As Integer
Conn.Open()
Command.Connection = Conn
x = Command.ExecuteNonQuery()
Conn.Close()
Command = Nothing
Inschrijdatum is empty, so I expect that the field in the table will be TODAY and not 1/1/1900.
Ger.
|||Two problems:
First, Inschrijdatum isn't empty, it's a zero length string.
Secondly, the only way you are going to get SQL Server to insert the default is one of three ways.
a) Don't mention the column in the insert at all.
b) Tell it to use the DEFAULT like INSERT ... VALUES (...,DEFAULT,...), note there is no quotes around DEFAULT, it is not a string.
c) Explicitly tell it the default value like INSERT ... VALUES (...,GetDate(),...)
That said, replace:
"','" &a(2) & "','"
with:
"'," & IIF(a(2)<>"","'" & a(2) & "'","DEFAULT") & ",'"
|||Oh, I guess I should mention this code is susceptible to SQL injection attacks.
For example, type this in your field labeled "HtaBijzOpm": "','') TRUNCATE Medewerkers --" and submit your form, and you've just deleted all the records from your Medewerkers table.
|||
Motley wrote:
Oh, I guess I should mention this code is susceptible to SQL injection attacks.
For example, type this in your field labeled "HtaBijzOpm": "','') TRUNCATE Medewerkers --" and submit your form, and you've just deleted all the records from your Medewerkers table.
Thanks Motley for replying I will reconstruct the inserts according your advice.
How can I avoid this SQL injection attack ?
Ger.
No comments:
Post a Comment