Parameterized SQL UPDATE issues...

[Stewart Ross]

Hi Pat. Curioser and curioser... The Select working tells us that the Where does not appear to be the problem at all. To me, it suggests that the Update is not in fact updatable - because of an error occurring at time of interpretation perhaps.

In your code all your date parameters are defined as VarChar strings and not dates - is this correct? I ask because although Access is very good at converting types on the fly, it could be possible that the string values are not matching the format expected for dates in SQL and causing errors. A bit of a long-shot, but one to consider.

-Stewart

[patjones]

Hi Pat. Curioser and curioser... The Select working tells us that the Where does not appear to be the problem at all. To me, it suggests that the Update is not in fact updatable - because of an error occurring at time of interpretation perhaps.

In your code all your date parameters are defined as VarChar strings and not dates - is this correct? I ask because although Access is very good at converting types on the fly, it could be possible that the string values are not matching the format expected for dates in SQL and causing errors. A bit of a long-shot, but one to consider.

-Stewart

It's a good point, but assuming that's the case why would the INSERT work? This is a real tough one. I've even gone so far as to paste the UPDATE string into Word and print it out in 16-point type size hoping that something will jump out at me!

Pat

[Delerna]

Select instead of update works!
Insert works!
I think that we may have proved that the problem is not in the code. Its time to look elsewhere.


More suggestions. You may have checked these already?
When you create the connection object you supply a user and password.
1) Is the user you are using permitted to make updates in the tables permissions?
2) Is the tables key fields defined sufficiently to uniquely identify records. SQL server and ADO can be a bit iffy wth updates as well as deletes if records cannot be uniquely defined.
3) One more for the code. Have all the settings required for successful updates with ADO objects to occur been set correctly? I don't keep them in my head (getting too old.....grin), I usually search my own notes or google.
For example with the recordset object (not what you are using but illustrates the point),the default cursor type is forward only. I dont think that cursor type supports updating a record.

[patjones]

Select instead of update works!
Insert works!
I think that we may have proved that the problem is not in the code. Its time to look elsewhere.


More suggestions. You may have checked these already?
When you create the connection object you supply a user and password.
1) Is the user you are using permitted to make updates in the tables permissions?
2) Is the tables key fields defined sufficiently to uniquely identify records. SQL server and ADO can be a bit iffy wth updates as well as deletes if records cannot be uniquely defined.
3) One more for the code. Have all the settings required for successful updates with ADO objects to occur been set correctly? I don't keep them in my head (getting too old.....grin), I usually search my own notes or google.
For example with the recordset object (not what you are using but illustrates the point),the default cursor type is forward only. I dont think that cursor type supports updating a record.

Good Morning Delerna:

I'm starting to feel as though the problem is elsewhere also. In regard to your suggestions:

1) There really aren't any permissions set right now. It's completely open access.

2) This is something I've considered, and it still may be a problem. "tblLeave" is connected via one-to-many relationships to three other tables; in particular fldERN, fldLeaveType and fldLeaveStatus are all primary keys in other tables, and act as foreign keys in tblLeave. Now, I realize that if I try to update one of these fields to a value that doesn't exist in the corresponding primary key field in one of the other tables, the UPDATE wouldn't work; but I would also expect Access to throw an error in that instance.

3) I'm aware of the CursorType issue, but like you say - I'm not using a recordset here. I'm simply executing the ADO command, then clearing it and leaving the subroutine.

These are all pertinent ideas, but I keep coming back to this...if for instance this was a key issue, as in point #2, why would this work:

strSaveLeave = "UPDATE tblLeave SET fldLeaveType = @LeaveType, fldLeaveStatus = @LeaveStatus, fldDateLastWorked = @DateLastWorked, fldDateLeaveStart = @DateLeaveStart, fldDateLastPaid = @DateLastPaid, fldDateLeaveEnd = @DateLeaveEnd, fldDateWarningLetter = @DateWarningLetter, fldDateAWOLEmail = @DateAWOLEmail, fldDateReturnEmail = @DateReturnEmail, fldDateReturned = @DateReturned, fldLeaveNotes = @LeaveNotes WHERE fldLeaveNum = " & currLeave.LeaveNum & " AND fldERN = '" & currLeave.ERN & "'"

Because, as I determined earlier, it functions fine with there WHERE clause written out as a string. Very strange, no?

Pat

[patjones]

Hi:

It seems that I have found a way around the problem, rather than solving it. I tried:

 
UPDATE tblLeave SET fldLeaveType = ?, fldLeaveStatus = ?, fldDateLastWorked = ?, fldDateLeaveStart = ?, fldDateLastPaid = ?, fldDateLeaveEnd = ?, fldDateWarningLetter = ?, fldDateAWOLEmail = ?, fldDateReturnEmail = ?, fldDateReturned = ?, fldLeaveNotes = ? WHERE tblLeave.fldLeaveNum = ? AND tblLeave.fldERN = ?

By putting the .CreateParamete r statements in the same order that I need to use the respective parameters in the UPDATE, it worked fine. I'm still not sure why using the named parameters didn't work. However, the "?" formalism seems to be functioning so far.

Thanks for everyone's input.

Pat

[Delerna]

Cool you got it working. The solution was expressed in an earlier post by stewart.

Another way around the problem would have been to use a parameterised stored procedure on sql server and let the stored proc decide whether to insert or update via an if exists framework.

All your vb code would then need to do would be to build a query string and have the connection execute it. The string would have the form.

strSQL="exec NameOfStoredPro c value1,value2,v alue3......."

This simplifies your vb code, removes all those horrible add parameter lines and lets more of the work be done at the server rather than back and forth between the client and the server.