Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dupati1
Posting Yak Master
123 Posts |
 Posted - 2003-11-07 : 16:31:11
|
| Hi all,I am using SQL Server 2000 DB and ASP.I have a form which takes in patientName, BirthDate, releasedDate, verifiedDate.When i try to insert the values to the database, i am facing strange problem. I am using the below SQL query:strSQL = "INSERT INTO myTable (PatientName,BirthDate, ReleaseDate, VerifiedDate) VALUES "strSQL = strSQL & " (' "&request("patName")&" ' , '"&request("dob")&"' , ' "&request("releaseDate")&" ' , ' "&request("verifiedDate")&" ')"The query works fine but when the form is filled with only patientname and birthdate. i mean no values of releasedate and verifieddate. In the database the patientname and birthdate get inserted properly but the default value '01/01/1900' is being inserted into both the releasedate and verifieddate fields whenever they are unfilled on the form.I dont know why??Any suggestions.ThanksVJ |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-11-07 : 16:47:45
|
| You are sending an empty string '' which is being converted into '01/01/1900' in a datetime datatype field.You will need to perform logic on those date fields and convert an empty string into what you wish to store (ie.(NULL). |
 |
|
|
Granick
Starting Member
46 Posts |
Posted - 2003-11-07 : 16:52:34
|
| The reason for the 1/1/1900 is that you are passing empty strings to the INSERT statement, and SQL Server changes this into 1/1/1900, the earliest date it can handle. I have run into this from ASP pages more times than I can count.Does your table allow NULLs for these fields that are having the 1/1/1900 inserted? If so, then you can simply build the second line of your insert and have it insert Null instead.Would look something like this:strSQL = strSQL & " (' "&request("patName")&" ' , '"&request("dob")&"' , ' "&request("releaseDate")&" ', "If request("verifiedDate") <> "" Then strSQL = strSQL & "'" & request("verifiedDate") & " ')"Else strSQL = strSQL & "Null" & " )"End IfYou would have to do someting similar to this for the other date(s) as well. And this will only work if your table allows nulls. If it doesn't allow Nulls, then you really should have default values for these fields.Hope this helps.Shannon |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-11-07 : 17:03:24
|
| use a stored procedure and explicitly assign the parameters. You should never insert data by building INSERT statements in ASP; always set up a stored proc which takes the needed parameters and insert that way.Within your stored proc, add all validation and/or translating of those parameters as necesssary.- Jeff |
|
|
|
dupati1
Posting Yak Master
123 Posts |
Posted - 2003-11-07 : 21:21:26
|
| Thanks Guys,I inserted null values.VJ |
|
|
|
|
|
|
|
|
SQL INSERT - strangr problem