|
jfrankland -> 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'
|
|
|
|
