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 .CreateParameter 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 parameterization process...I was under the impression that what you are suggesting here wasn't necessary. It seems that one of the advantages to parameterization 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 parameterisation 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.fldLeaveNum = @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.fldLeaveNum = @LeaveNum AND trim(tblLeave.fldERN) = @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.fldLeaveNum = @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.fldLeaveNum = @LeaveNum AND trim(tblLeave.fldERN) = @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 .CreateParameter 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.fldLeaveNum = @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.fldLeaveNum = @LeaveNum AND trim(tblLeave.fldERN) = @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

[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 .CreateParameter 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 NameOfStoredProc value1,value2,value3......."

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.