Updating Data

[GWPhoenix]

I am trying to update fields in a SQL Table which already has all the States and some data in it. I keep getting an error of "Incorrect Syntax near ','. What am I missing or not doing right to return my values to a "Read Only" page here?

The form data from the previous page is:

<form name = 'updatedata' action = 'update.asp' method = 'post'>


And the code for update.asp is as follows:

Dim numGuar, numPool, numTrans, strState

numGuar = Request.Form("Guar")
numPool = Request.Form("Pool")
numTrans= Request.Form("Trans")
strState = Request.Form("State")

If Request.Form(&quot;sent&quot;) <> &quot;&quot; then

Dim strSQL
	
strSQL = &quot;UPDATE Table SET&quot;
strSQL = strSQL & &quot; Guar = &quot; & numGuar & &quot;,&quot;
strSQL = strSQL & &quot; Pool = &quot; & numPool & &quot;,&quot;
strSQL = strSQL & &quot; Trans = &quot; & numTrans  
strSQL = strSQL & &quot; WHERE State = '&quot; & strState & &quot;';&quot;


set cxn=server.CreateObject(&quot;ADODB.Connection&quot;)
cxn.Open strConn, UserID, PWD		'defined in cxn.asp
set rst=server.CreateObject(&quot;ADODB.Recordset&quot;)

	cxn.Execute strSQL
		
Response.Redirect (&quot;readonly.asp&quot;)

rst.Close
set rst = nothing
cxn.Close
set cxn = Nothing

End if

 

[lobstah]

sounds like maybe one of these variables: numGuar or numPool may be empty.

try doing a 'response.write strSQL' before you execute the update statement. it's a good tshooting tool anyway.

 

[GWPhoenix]

Yes! numPool and numTrans are both empty! Does this make a difference?

If so, what should I do for these fields, some of which will remain empty?

 

[lobstah]

Yes, it does make a difference, because it leaves a gaping hole in your sql statement.

Are all your fields numeric that you're updating? Not sure if you can set them to Null or not. You could test that. Or set them to 0 if the form fields are empty? Or if they are text fields, then you need to surround the variables with 's and then it might accept a blank, but I'm not sure about that either... Easy enough to test, though...

 

[GWPhoenix]

When I set the values to &quot;0&quot;, it works fine! My problem is that some of the fields may have to remain Null.

All the fields that I am updating are numeric and in the Database design, that is permitted. I don't know if I can (per my client's instructions) just set everything to zero.

If I have to leave fields Null, how would I update around the Null fields?

 

[lobstah]

Dim sqlCount
strSQL = &quot;UPDATE Table SET&quot;

If Guar <> &quot;&quot; Then
strSQL = strSQL & &quot; Guar = &quot; & numGuar
sqlCount = True
End If
If Pool <> &quot;&quot; Then
If sqlCount Then
strSQL = strSQL & &quot;, Pool = &quot; & numPool &quot;
Else
strSQL = strSQL & &quot; Pool = &quot; & numPool
End If
sqlCount = True
End If
If Trans <> &quot;&quot; Then
If sqlCount Then
strSQL = strSQL & &quot;, Trans = &quot; & numTrans
Else
strSQL = strSQL & &quot; Trans = &quot; & numTrans
End If
strSQL = strSQL & &quot; WHERE State = '&quot; & strState & &quot;';&quot;


- that should do it!

 

[lobstah]

oh, and somewhere you might want to check that there is at least something that is being updated...... didn't take that into account in what I just posted.

 

[GWPhoenix]

lobstah --

Thanks so much. I had actually originally obtained some code something like what you are suggesting here, but yours makes a lot more sense.

I will be trying this.

:-D