Updating Fields

[GWPhoenix]

I am updating fields in an Access Database, some of which may contain null values.

I get an error stating "COUNT field incorrect" on the following statement:

oRS.Open strSQL, objConn

I have attempted to use

objConn Execute strSQL

I have worked on a similar program before (in SQL Server) with some success. Note I have dates and URLs as data fields and believe I have the SQL Statement coding correct there.

The code is as follows:

Dim strSQL, id, nofa, Summary, Deadline, URL1

id = Request("id")
nofa = Request("nofa")
etc.



Response.Write strSQL & &quot;<P>&quot;

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

If nofa <> &quot;&quot; Then
	strSQL = strSQL & &quot; nofa = &quot; & nofa
	sqlCount = True
End if

If Summary <> &quot;&quot; Then
	If sqlCount Then
		strSQL = strSQL & &quot;, Summary = &quot; & Summary
	Else
		strSQL = strSQL & &quot; Summary = &quot; & Summary
	End If
	sqlCount= True
End If

If Deadline <> &quot;&quot; Then
	If sqlCount Then
		strSQL = strSQL & &quot;, Deadline = #&quot; & Deadline & &quot;#&quot;
	Else
		strSQL = strSQL & &quot; Deadline = #&quot; & Deadline  & &quot;#&quot;
	End If
	sqlCount= True
End If


If URL1 <> &quot;&quot; Then
	If sqlCount Then
		strSQL = strSQL & &quot;, (URL1 = &quot; & URL1 & &quot;)&quot;
	Else
		strSQL = strSQL & &quot; (URL1 = &quot; & URL1 &&quot;)&quot;
	End If
	sqlCount= True
End If

strSQL = strSQL & &quot; WHERE id = '&quot; & id & &quot;';&quot;


Dim objConn
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
objConn.ConnectionString = &quot;NOFA9&quot;
objConn.Open

Dim oRS
set oRS = Server.CreateObject (&quot;ADODB.recordset&quot;)
oRS.Open strSQL, objConn

Response.Write &quot;<H1>updated</H1>&quot;

'For testing
Response.Write strSQL & &quot;<P>&quot;

 

[yimitz]

You need to specify the correct cursor and lock types for updating. By omitting them you open the database as read only. Try using this:

oRS.Open strSQL, objConn, 2, 2

the first 2 sets the cursor as Dynamic, the second sets the lock type to pessimistic.

 

[GWPhoenix]

Thanks, but that didn't work. You did, however, make me realize that I had not included my adovbs file, which also didn't help.

Still trying other stuff...

 

[yimitz]

Is your connection string a DSN?

If not you need to write it like this:

objConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\path\to\DB.mdb"

If you're using a DSN you don't need to create a connection object the DSN does that for you.

Try inserting this right after you open your connection.

It should give you error info if there is a problem with the connection string.

If objConn.Errors.Count >0 Then
Set objErr = Server.CreateObject("ADODB.Error&quot
For Each objErr In objConn.Errors
If objErr.Number <> 0 Then
Response.Write &quot;An Error Occured while connecting to the database! <br>&quot;
Response.Write &quot;Error Number: &quot; & objErr.Number & &quot;<p>&quot;
Response.Write &quot;Error Description: &quot; & objErr.Description & &quot;<p>&quot;
Response.Write &quot;Source: &quot; & objErr.Source & &quot;<p>&quot;
blnCriticalError = True
End If
Next
Set objErr = Nothing
If blnCriticalError Then
Response.End
End If
End If

 

[GWPhoenix]

ymitz --

Thanks for your help, but nothing has changed. I have a DSN, which is NOFA9 (see original code) and it works fine, mostly on my &quot;Read Only&quot; Functions.

Any other ideas?

 

[yimitz]

When you put your form values to variables try naming the with a syntax of strNofa and strSummary, strDeadline, etc. ASP may be trying to populate these values because they are the names of the variable as well as the name of the field. Also, try breaking it down to get just one column updated at a time. When one works move to the next in your SQL statment.