|
| |
|
|
jfrankland
Posts: 89
Joined: 5/5/2004
From: Port Saint Lucie, Florida
Status: offline
 |
Syntax error converting datetime from character string. - 9/7/2006 12:54:23
Hello:
I have an ASP form that pulls data from a SQL server 2000 table. This form is used to update the record by changing the fields on the form and then posting it back to the SQL server using an update string. If I pull a record that has a <Null> value in a datetime field and I attempt to save or post the changes to the form I get:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.
/gis/Devstat/AppEditStep3Post.asp, line 63
I trimmed down the SQL string to find exactly where the problem is. Apparently if I attempt to write back or update the record with the pulled <Null> value from a field called Date_Stamped that is where the hickup is. Does anyone have any ideas on how to correct this? If I take the string from the reponse.write directly into the SQL Query Analyzer is works fine. Does the forms text box cause the problem? Below is my code and response.write string:
rstemp = 0
Set conntemp1 = Server.CreateObject("ADODB.Connection")
conntemp1.Open Application("GMTEST_ConnectionString")
Dim modLegal_Description
'<><><><><><><><><><><>FIRST STRING TO UPDATE THE DATABASE<><><><><><><><><><><>
Update1String = "UPDATE jafvEditProjectData SET "
Update1String = Update1String & "Project_Name = " & "('" & Request.form("Project_Name") & "'),"
Update1String = Update1String & "Date_Received = " & "('" & Request.form("Date_Received") & "'),"
Update1String = Update1String & "Project_Desc = " & "('" & Request.form("Project_Desc") & "'),"
Update1String = Update1String & "Approval_Order_No = " & "('" & Request.form("Approval_Order_No") & "'),"
Update1String = Update1String & "Residential_Units = " & "('" & Request.form("Residential_Units") & "'),"
Update1String = Update1String & "Square_Footage = " & "('" & Request.form("Square_Footage") & "'),"
Update1String = Update1String & "Date_Stamped = " & "('" & Request.form("Date_Stamped") & "'),"
'Update1String = Update1String & "Date_Transmitted = " & "('" & Request.form("Date_Transmitted") & "'),"
'Update1String = Update1String & "Date_Approved = " & "('" & Request.form("Date_Approved") & "'),"
'Update1String = Update1String & "Date_Terminated = " & "('" & Request.form("Date_Terminated") & "'),"
Update1String = Update1String & "Aerial_Photo = " & "('" & Request.form("Aerial_Photo") & "'),"
Update1String = Update1String & "Location_Sketch = " & "('" & Request.form("Location_Sketch") & "'),"
Update1String = Update1String & "Boundary = " & "('" & Request.form("Boundary") & "'),"
Update1String = Update1String & "Outfall = " & "('" & Request.form("Outfall") & "'),"
Update1String = Update1String & "Floodplain = " & "('" & Request.form("Floodplain") & "'),"
Update1String = Update1String & "Adjacent_Driveways = " & "('" & Request.form("Adjacent_Driveways") & "'),"
Update1String = Update1String & "Open_Spaces = " & "('" & Request.form("Open_Spaces") & "'),"
Update1String = Update1String & "Lighting = " & "('" & Request.form("Lighting") & "'),"
Update1String = Update1String & "Transportation_Impact = " & "('" & Request.form("Transportation_Impact") & "'),"
Update1String = Update1String & "Vegetation_Removal = " & "('" & Request.form("Vegetation_Removal") & "'),"
Update1String = Update1String & "Turtle_Protection = " & "('" & Request.form("Turtle_Protection") & "'),"
Update1String = Update1String & "Impact = " & "('" & Request.form("Impact") & "'),"
Update1String = Update1String & "Drainage_Boundaries = " & "('" & Request.form("Drainage_Boundaries") & "'),"
Update1String = Update1String & "Permit_Data = " & "('" & Request.form("Permit_Data") & "'),"
Update1String = Update1String & "Location_Dimension_Setbacks = " & "('" & Request.form("Location_Dimension_Setbacks") & "'),"
Update1String = Update1String & "Max_Buildable = " & "('" & Request.form("Max_Buildable") & "'),"
Update1String = Update1String & "Easements = " & "('" & Request.form("Easements") & "'),"
Update1String = Update1String & "Parking = " & "('" & Request.form("Parking") & "'),"
Update1String = Update1String & "Water_Facilities = " & "('" & Request.form("Water_Facilities") & "'),"
Update1String = Update1String & "Drainage_Ids = " & "('" & Request.form("Drainage_Ids") & "'),"
Update1String = Update1String & "Streets_Locations = " & "('" & Request.form("Streets_Locations") & "'),"
Update1String = Update1String & "Landscaping = " & "('" & Request.form("Landscaping") & "'),"
Update1String = Update1String & "Phasing = " & "('" & Request.form("Phasing") & "'),"
Update1String = Update1String & "Comments = " & "('" & Request.form("Comments") & "'),"
modLegal_Description = Request.form("Legal_Description")
modLegal_Description = Replace(modLegal_Description,"'","''")
Update1String = Update1String & "Legal_Description = " & "('" & modLegal_Description & "'),"
Update1String = Update1String & "Acreage = " & "('" & Request.form("Acreage") & "'),"
Update1String = Update1String & "UID = " & "('" & Request.form("UID") & "'),"
Update1String = Update1String & "Time_Stamp = " & "('" & Request.form("Updated") & "')"
'.....fill in all fields until the last field here
Update1String = Update1String & " WHERE Project_Id = '" & Request.Form("Project_Id") & "'"
'Response.write(Update1String)
set rstemp=conntemp1.execute(Update1String)
Here's the response.write from the above string:
UPDATE jafvEditProjectData SET Project_Name = ('William K. Bessemer '),Date_Received = ('1/13/2006'),Project_Desc = ('2003 Forest Model #f32102 class "A" homes of merit'),Approval_Order_No = (' Res 06-050 '),Residential_Units = (' '),Square_Footage = (' '),Date_Stamped = (' '),Aerial_Photo = (''),Location_Sketch = (''),Boundary = (''),Outfall = (''),Floodplain = (''),Adjacent_Driveways = (''),Open_Spaces = (''),Lighting = (''),Transportation_Impact = (''),Vegetation_Removal = (''),Turtle_Protection = (''),Impact = (''),Drainage_Boundaries = (''),Permit_Data = (''),Location_Dimension_Setbacks = (''),Max_Buildable = (''),Easements = (''),Parking = (''),Water_Facilities = (''),Drainage_Ids = (''),Streets_Locations = (''),Landscaping = (''),Phasing = (''),Comments = (' '),Legal_Description = (' '),Acreage = (' '),UID = ('riddlea'),Time_Stamp = ('9/7/2006 12:41:09 PM') WHERE Project_Id = '70'
|
|
|
|
rdouglass
Posts: 9206
From: Biddeford, ME USA
Status: offline
|
RE: Syntax error converting datetime from character str... - 9/7/2006 14:02:04
quote:
the record with the pulled <Null> value from a field called Date_Stamped
Are you sure you're pulling a Null from the field and are you sure that string worked in QA?
A form's text field will *not* provide a Null value but rather an empty string. They are not the same. If you're sending an empty string to a Date?Time field, that will cause problems as you already know.
I usually will test for a blank. If blank, send a Null and not an empty string as in:
...
If trim(request.form("myDateFormField")&"") = "" THEN
myDateValToUse = "Null"
ELSE
myDateValToUse = "'" & trim(request.form("myDateFormField")&"") & "'"
and then in your SQL, use it this way:
mySQL = "UPDATE myTable SET myDateField=" & myDateValToUse & ", myTextField = .....
....
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jfrankland
Posts: 89
Joined: 5/5/2004
From: Port Saint Lucie, Florida
Status: offline
|
RE: Syntax error converting datetime from character str... - 9/7/2006 15:55:23
Let me give that a whirl and I'll let you know. THANKS AGAIN!
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
