Parameterized SQL UPDATE issues...

[patjones]

Hi all:

I've got one of those problems that I just can't get around, no matter what! In my code, I've got a SQL UPDATE like:

 
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 tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = @ERN"

My parameters are created like this:

 
.Parameters.Append .CreateParameter("ERN", adBSTR, adParamInput, 7, currLeave.ERN)
.Parameters.Append .CreateParameter("LeaveNum", adInteger, adParamInput, , currLeave.LeaveNum)
.Parameters.Append .CreateParameter("LeaveType", adInteger, adParamInput, , currLeave.LeaveType)

...and so on. In this table, fldERN is a foreign key that connects to it's primary key counterpart in another table - however for the purpose of this UPDATE, I'm only working with this table.

Now, when I run this code, it doesn't give me any errors. It's simply that nothing happens. I open up an existing record in the form, make a few changes, hit my save button...and the changes don't show up in the record. What's interesting though is that when I construct the WHERE clause using strings and comment out the .CreateParamete r lines for ERN and LeaveNum like this:

 
WHERE tblLeave.fldERN = '" & currLeave.ERN & "' AND tblLeave.fldLeaveNum = " & currLeave.LeaveNum

...it works fine! I just don't know why the statement won't work when I use parameters in the WHERE clause. Any insight? Thanks so much...

Pat

[Stewart Ross]

Hi. The parameter for the ERN field appears to be a string, but in your WHERE clause (line 13 in first code block above) you are not referring to it within quotes. It will be interpreted as if it were a number unless you put it in quotes - which you did do in the other WHERE that works fine (last code posted)!

I am not sure which back-end application you are using (with all the @s involved in the SQL), nor do I know for certain that you are using Access VBA and not VB, say, to build the string. Assuming you are building the SQL string in Access VBA or VB please try replacing your WHERE line with

WHERE tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = '" & @ERN & "'"

The reason I am hedging a bit here is that string joins in other applications may use a different operator ("+", say, instead of the "&"). As far as I know the use of the single quote for strings is correct in the ANSI SQL standard, however.

-Stewart

[patjones]

Hi. The parameter for the ERN field appears to be a string, but in your WHERE clause (line 13 in first code block above) you are not referring to it within quotes. It will be interpreted as if it were a number unless you put it in quotes - which you did do in the other WHERE that works fine (last code posted)!

I am not sure which back-end application you are using (with all the @s involved in the SQL), nor do I know for certain that you are using Access VBA and not VB, say, to build the string. Assuming you are building the SQL string in Access VBA or VB please try replacing your WHERE line with

WHERE tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = '" & @ERN & "'"

The reason I am hedging a bit here is that string joins in other applications may use a different operator ("+", say, instead of the "&"). As far as I know the use of the single quote for strings is correct in the ANSI SQL standard, however.

-Stewart

Hi Stewart:

Thanks for your suggestion (and yes I am using Access/VBA), but unfortunately that doesn't even compile. It responds with a syntax error.

Although I'm fairly new to the parameterizatio n process...I was under the impression that what you are suggesting here wasn't necessary. It seems that one of the advantages to parameterizatio n is that one doesn't have to do all the string building that is necessary when using straight text box or field names. At least, that is what I've seen in my travels around various forums regarding the issue...

Pat

[Stewart Ross]

Hi. Regardless of parameterisatio n you will still need to delimit a string within the SQL in quotes. Why? Because otherwise the string would be interpreted like this after parameter substitution (using a dummy value for illustration)

WHERE fldERN = ABCDEF12345

instead of

WHERE fldERN = 'ABCDEF12345'

and this in turn will set the SQL interpreter looking for a field named ABCDEF12345 or whatever to find its value - and it will not succeed.

The fact that it worked for you in the other WHERE clause within the single quotes but without the parameter is a sure sign of what is wrong.

As for the compile error I cannot guess at that one, but I am sure that if you can resolve this you will resolve the issue at hand.

-Stewart

[Stewart Ross]

Ahh, your parameter has to be part of the string it would appear, at least from the way it is built in your example - hence the compile error??. Modifying:

WHERE tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = '@ERN'"

-Stewart

[patjones]

Ahh, your parameter has to be part of the string it would appear, at least from the way it is built in your example - hence the compile error??. Modifying:

WHERE tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = '@ERN'"

-Stewart

That compiles fine but gives me no results, and I can understand why. Doing it this way, my WHERE clause reads:

 
WHERE tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = 
'@ERN'

This means that my actual value in fldERN would have to be "@ERN" which doesn't make any sense.

I have done this previously using just

 
WHERE tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = 
@ERN

and all the forums I've looked in have shown it this way. So I still don't see the necessity of putting it in quotes...

Pat

[Stewart Ross]

OK. My experience with parameter queries has been through DAO rather than ADO recordsets. Anyway, I have trawled the (completely inadequate) documentation on the use of parameter queries with ADO. I agree with you now that you should not have to enclose the parameter in quotes. The parameters are explictly typed (rather than typeless), which is one difference.

What I am puzzled by in your code is the @ syntax; the references in the documentation to parameters indicate the use of question marks as placeholders when using the command object, and in my test code below the parameter of a field keyw is set this way. It is not an update query, just a select, but it does indeed return a recordset based on a where clause matched on the string value as expected.

I saw a reference to the @ form of parameter with the Inputparameters property, but the documentation still refers to the ? markers within the code itself. Perhaps if you posted more of your code (the bits where the parameters are passed and so on) the mystery can finally be solved?

I also found the locals window of the debugger very useful for checking the actual settings of the command object and the parameter object in testing the simple example below.

	Dim RS As New ADODB.Recordset
	Dim cmd As New ADODB.Command
	Dim param As New ADODB.Parameter
	Dim conn As New ADODB.Connection
	cmd.CommandText = "select * from keywords where keyw like ?"
	cmd.CommandType = adCmdText
	Set param = cmd.CreateParameter("keyw", adChar, adParamInput, 255, "coin")
	cmd.Parameters.Append param
	Set conn = CurrentProject.Connection
	cmd.ActiveConnection = conn
	Set RS = cmd.Execute
	Do While Not RS.EOF
		Debug.Print RS!Keyw
		RS.MoveNext
	Loop
	RS.Close

-Stewart

[patjones]

Thanks so much Stewart. From what I can see, the "@" formalism is when you use named parameters, such as I'm doing. The "?" formalism seems to be when you define a list of parameters, and then use those parameters in the query in the order you defined them in the list.

I don't know that it will help, but this is the whole subroutine I'm using for saving the data in this particular form. The booEdit that I send in is just a boolean telling the subroutine whether this is a brand new record, or an edit to an existing record. You can see that it picks out different SQL statements on the basis of what booEdit is. The first branch of the If-statement logic (the case for a new record) works just fine. It's the second branch which causes the trouble we've been discussing all day:

 Public Sub SaveLeave(booEdit As Boolean) 
 
On Error GoTo Err_SaveLeave
 
Dim strSaveLeave As String
Dim commSaveLeave As ADODB.Command
 
'Set the SQL string for updating the leave table...
 
If Not booEdit Then
 
	strSaveLeave = "INSERT INTO tblLeave (fldERN, fldLeaveNum, fldLeaveType, fldLeaveStatus, fldDateLastWorked, fldDateLeaveStart, fldDateLastPaid, fldDateLeaveEnd, fldDateWarningLetter, fldDateAWOLEmail, fldDateReturnEmail, fldDateReturned, fldLeaveNotes) VALUES (@ERN, @LeaveNum, @LeaveType, @LeaveStatus, @DateLastWorked, @DateLeaveStart, @DateLastPaid, @DateLeaveEnd, @DateWarningLetter, @DateAWOLEmail, @DateReturnEmail, @DateReturned, @LeaveNotes)"
 
Else
 
	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 tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = @ERN"
 
End If
 
'Set command information for leave
 
Set commSaveLeave = New ADODB.Command
 
With commSaveLeave
 
	.CommandType = adCmdText
	.CommandText = strSaveLeave
	.ActiveConnection = conn.ConnectionString
 
	.Parameters.Append .CreateParameter("ERN", adVarChar, adParamInput, 7, currLeave.ERN)
	.Parameters.Append .CreateParameter("LeaveNum", adInteger, adParamInput, , currLeave.LeaveNum)
	.Parameters.Append .CreateParameter("LeaveType", adInteger, adParamInput, , currLeave.LeaveType)
	.Parameters.Append .CreateParameter("LeaveStatus", adBSTR, adParamInput, 1, currLeave.LeaveStatus)
	.Parameters.Append .CreateParameter("DateLastWorked", adVarChar, adParamInput, 8, currLeave.DateLastWorked)
	.Parameters.Append .CreateParameter("DateLeaveStart", adVarChar, adParamInput, 8, currLeave.DateLeaveStart)
	.Parameters.Append .CreateParameter("DateLastPaid", adVarChar, adParamInput, 8, currLeave.DateLastPaid)
	.Parameters.Append .CreateParameter("DateLeaveEnd", adVarChar, adParamInput, 8, currLeave.DateLeaveEnd)
	.Parameters.Append .CreateParameter("DateWarningLetter", adVarChar, adParamInput, 8, currLeave.DateWarningLetter)
	.Parameters.Append .CreateParameter("DateAWOLEmail", adVarChar, adParamInput, 8, currLeave.DateAWOLEmail)
	.Parameters.Append .CreateParameter("DateReturnEmail", adVarChar, adParamInput, 8, currLeave.DateReturnEmail)
	.Parameters.Append .CreateParameter("DateReturned", adVarChar, adParamInput, 8, currLeave.DateReturned)
	.Parameters.Append .CreateParameter("LeaveNotes", adVarChar, adParamInput, 250, currLeave.LeaveNotes)
 
End With
 
commSaveLeave.Execute
 
Exit_SaveLeave:
	Exit Sub
 
Err_SaveLeave:
	HandleSystemError Err.Number, , "Error " & Err.Number & vbCrLf & Err.Description & vbCrLf & "Subroutine: SaveLeave. Module: basGlobals."
	Resume Exit_SaveLeave
 
End Sub

Is it possible that this problem could be arising due to the fact that many of the fields I'm trying to update (fldERN, fldLeaveType, fldLeaveStatus) are connected to other tables via INNER JOIN relationships in Access? I'm so at a loss.

Pat

[Stewart Ross]

Hi Pat. I sure see how frustrating this one is for you. As the first branch of the IF, the INSERT, works correctly the use of the parameters is correct and relationships will not be impeding anything - the table is clearly updatable or the first part would not work.

Problem is that without being able to see what the final result of the code is (because the parameters are substituted within the execute statement) it is hard to debug.

What may help is to copy your code into another procedure, and adjust it for debug purposes by replacing the UPDATE with a SELECT statement you can use to debug, like the example below:

Dim RS as New ADODB.Recordset
...
strSaveLeave = "SELECT * from tblLeave WHERE tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = @ERN"
...
set RS = commSaveLeave.Execute
Do while not RS.Eof
Debug.Print RS!fldERN, RS!fldLeaveNum
Rs.Movenext
loop
RS.close

If you have an empty recordset you know that the WHERE is not matching any records. if you wanted to test what the parameters were you could set a breakpoint at the command execute and look at the parameters to check what is set.

You could even include them in the SELECT for debug purposes:

strSaveLeave = "SELECT @LeaveNum as prmLeaveNum, @ERN as prmERN from tblLeave"

Good luck with your checking. If I come up with anything else in the meantime I'll post back here to let you know.

-Stewart

[patjones]

Hi Pat. I sure see how frustrating this one is for you. As the first branch of the IF, the INSERT, works correctly the use of the parameters is correct and relationships will not be impeding anything - the table is clearly updatable or the first part would not work.

Problem is that without being able to see what the final result of the code is (because the parameters are substituted within the execute statement) it is hard to debug.

What may help is to copy your code into another procedure, and adjust it for debug purposes by replacing the UPDATE with a SELECT statement you can use to debug, like the example below:

Dim RS as New ADODB.Recordset
...
strSaveLeave = "SELECT * from tblLeave WHERE tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = @ERN"
...
set RS = commSaveLeave.Execute
Do while not RS.Eof
Debug.Print RS!fldERN, RS!fldLeaveNum
Rs.Movenext
loop
RS.close

If you have an empty recordset you know that the WHERE is not matching any records. if you wanted to test what the parameters were you could set a breakpoint at the command execute and look at the parameters to check what is set.

You could even include them in the SELECT for debug purposes:

strSaveLeave = "SELECT @LeaveNum as prmLeaveNum, @ERN as prmERN from tblLeave"

Good luck with your checking. If I come up with anything else in the meantime I'll post back here to let you know.

-Stewart

Hi:

I will try building a SELECT query and see if it picks out the correct records. In the meantime, what I can say is that I did do this:

For j = 0 to commSaveLeave.Parameters.Count-1
	 Debug.Print commSaveLeave.Parameters.Item(j)
Next

When I opened my Immediate Window to see the results of this, I found that the parameters were all assigned correctly.

You are quite correct that, what makes this frustrating, is that the INSERT statement works well. The UPDATE statement is nearly identical except for the WHERE clause, so I have to suspect that the issue lies there. Again, I'm going to try out the SELECT query you suggested.

Thanks for all your help...

Pat

[Delerna]

Been perusing the posts here and I agree that it dosn't make sense that the insert works but the update dosn't.
Since Nothing happens for the update, I'm going to suggest something silly that I've been caught on before.

The update has a where condition and one of the parameters is a varchar.

WHERE tblLeave.fldLea veNum = @LeaveNum AND tblLeave.fldERN = @ERN

So the problem would't be something like fldERN being a Char(10) type would it?
Where "Happy " = "Happy" will not match.

I usually trim the field if thats the case
WHERE tblLeave.fldLea veNum = @LeaveNum AND trim(tblLeave.f ldERN) = @ERN

Just suggesting....I hope it helps!

[patjones]

Been perusing the posts here and I agree that it dosn't make sense that the insert works but the update dosn't.
Since Nothing happens for the update, I'm going to suggest something silly that I've been caught on before.

The update has a where condition and one of the parameters is a varchar.

WHERE tblLeave.fldLea veNum = @LeaveNum AND tblLeave.fldERN = @ERN

So the problem would't be something like fldERN being a Char(10) type would it?
Where "Happy " = "Happy" will not match.

I usually trim the field if thats the case
WHERE tblLeave.fldLea veNum = @LeaveNum AND trim(tblLeave.f ldERN) = @ERN

Just suggesting....I hope it helps!

Hi Delerna:

You are talking about something which I've wondered about, which is, how sensitive is this whole process to the type that is set in the .CreateParamete r statement? I have pretty much used adVarChar for everything, including currLeave.ERN. But the fact is, the ERN is always a seven digit text string (not a number, because it often begins with one or more zeroes). That's why I set the parameter size = 7 for the ERN parameter.

The table (i.e. fldERN) is, I believe, designed in accordance with that understanding, but I have to go back and check that to be sure. It's been a while since I put the back end together!

Thanks for the suggestion, and I'll let you know how it works out!

Pat

[Delerna]

Yep, the difference between varchar and char is significant in where clauses. As I said, it has caught me out before. The difference between int and bigint isn't so critical so long as the numbers at the time aren't bigger than int.
ie 64 as an int is the same as 64 as a bigint. Other types i'm not sure about.

[patjones]

Hi Pat. I sure see how frustrating this one is for you. As the first branch of the IF, the INSERT, works correctly the use of the parameters is correct and relationships will not be impeding anything - the table is clearly updatable or the first part would not work.

Problem is that without being able to see what the final result of the code is (because the parameters are substituted within the execute statement) it is hard to debug.

What may help is to copy your code into another procedure, and adjust it for debug purposes by replacing the UPDATE with a SELECT statement you can use to debug, like the example below:

Dim RS as New ADODB.Recordset
...
strSaveLeave = "SELECT * from tblLeave WHERE tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = @ERN"
...
set RS = commSaveLeave.Execute
Do while not RS.Eof
Debug.Print RS!fldERN, RS!fldLeaveNum
Rs.Movenext
loop
RS.close

If you have an empty recordset you know that the WHERE is not matching any records. if you wanted to test what the parameters were you could set a breakpoint at the command execute and look at the parameters to check what is set.

You could even include them in the SELECT for debug purposes:

strSaveLeave = "SELECT @LeaveNum as prmLeaveNum, @ERN as prmERN from tblLeave"

Good luck with your checking. If I come up with anything else in the meantime I'll post back here to let you know.

-Stewart

Good Morning:

I tried replacing my UPDATE with a SELECT, as you suggested Stewart, and it functions perfectly fine with that WHERE clause! What is it about this UPDATE statement? Yikes.

Pat

[patjones]

Been perusing the posts here and I agree that it dosn't make sense that the insert works but the update dosn't.
Since Nothing happens for the update, I'm going to suggest something silly that I've been caught on before.

The update has a where condition and one of the parameters is a varchar.

WHERE tblLeave.fldLea veNum = @LeaveNum AND tblLeave.fldERN = @ERN

So the problem would't be something like fldERN being a Char(10) type would it?
Where "Happy " = "Happy" will not match.

I usually trim the field if thats the case
WHERE tblLeave.fldLea veNum = @LeaveNum AND trim(tblLeave.f ldERN) = @ERN

Just suggesting....I hope it helps!

Hi Delerna:

I tried replacing adVarChar with adChar, but the result is the same. I'm going to keep it at adChar anyway, because I believe that's exactly what it should be. What about my LeaveNum parameter? That is always going to be an integer that never exceeds 4 or 5. Is adInteger proper for that? It's sort of a moot point, because I discovered already that the WHERE clause functions fine in the context of a SELECT statement (see my above post), but I would like to know anyway. Thanks!

Pat