Error Calling SQL Stored Procedure

[bullfrog83]

I'm trying to execute a sql server stored proc in vba. This is the sp:

CREATE PROCEDURE dbo.spRemoveIncmplClassTakenCourses
(
@TakenDiscId int,
@TakenClassIds varchar(255)
)
AS
BEGIN
	SET NOCOUNT ON
                    
	UPDATE    Cr
	SET              TakenCourseId = NULL
	FROM         StdDegReqCourse AS Cr INNER JOIN
						  StdDegReqClass AS Cl ON Cr.StdDegReqClassId = Cl.StdDegReqClassId
	WHERE     (Cr.TakenCourseId IS NOT NULL) AND (Cl.StdDegReqDiscId = @TakenDiscId) AND 
						  (Cr.StdDegReqClassId NOT IN (@TakenClassIds))

And this is my vba:

Dim cn As New ADODB.Connection
Dim lngTakenDiscId As Long
Dim strTakenClassIds As String

cn.Open CurrentProject.Connection

'lngTakenDiscId and strTakenClassIds are set

cn.Execute "spRemoveIncmplClassTakenCourses @TakenDiscId = " & lngTakenDiscId & ", @TakenClassIds = " & strTakenClassIds

When it gets to the cn.Execute line I get this:

"Error -2147217900: Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

I have no idea what this means. I have no 3rd parameter in my stored proc. How do I fix this?

[Christopher Nigro]

I'm trying to execute a sql server stored proc in vba. This is the sp:

CREATE PROCEDURE dbo.spRemoveIncmplClassTakenCourses
(
@TakenDiscId int,
@TakenClassIds varchar(255)
)
AS
BEGIN
	SET NOCOUNT ON
                    
	UPDATE    Cr
	SET              TakenCourseId = NULL
	FROM         StdDegReqCourse AS Cr INNER JOIN
						  StdDegReqClass AS Cl ON Cr.StdDegReqClassId = Cl.StdDegReqClassId
	WHERE     (Cr.TakenCourseId IS NOT NULL) AND (Cl.StdDegReqDiscId = @TakenDiscId) AND 
						  (Cr.StdDegReqClassId NOT IN (@TakenClassIds))

And this is my vba:

[CODE]
Dim cn As New ADODB.Connectio n
Dim lngTakenDiscId As Long
Dim strTakenClassId s As String

cn.Open CurrentProject. Connection

'lngTakenDiscId and strTakenClassId s are set

cn.Execute "spRemoveIncmpl ClassTakenCours es @TakenDiscId = " & lngTakenDiscId & ", @TakenClassIds = " & strTakenClassId s
[CODE]

When it gets to the cn.Execute line I get this:

"Error -2147217900: Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

I have no idea what this means. I have no 3rd parameter in my stored proc. How do I fix this?

Try:

cn.Execute "spRemoveIncmplClassTakenCourses @TakenDiscId = " & lngTakenDiscId & ", @TakenClassIds = '" & strTakenClassIds & "'"

[bullfrog83]

Try:

cn.Execute "spRemoveIncmplClassTakenCourses @TakenDiscId = " & lngTakenDiscId & ", @TakenClassIds = '" & strTakenClassIds & "'"

That doesn't work because then it passes the values like so: '226,227,228' when it should be '226','227','22 8'. Even though I dimmed my variable as a string so I can make it comma-separated, the values are all integers.

Now, to correct I added the opening and closing apostrophes when I'm stringing the values together in this part of my code:

strTakenClassId s = strTakenClassId s & "'" & lngTakenClassId & "',"

But then I get the original error message back.

[Christopher Nigro]

If it needs to be:
'226','227','22 8'

Then you need to make it look like:
'''226'',''227' ',''228'''

Those are all single quotes...

[Jim Doherty]

In your working method you are passing a command string to the server rather than the other way in ADO of create/append parameter lines (another way of doing it)

in your instance take a look at the following command string passed and notice the EXEC element difference in the string as compared to yours.

This is just a simple paste of a command string passing parameters where fGetFileName(Ac countName) is a functional representation of a users name lets say 'John Doe' for want of a better much under-used individual.

The EXEC portion is expected on the server in order to execute it once there

oConn.Execute "EXEC sp_grantdbacces s '" & AccountName & "', '" & fGetFileName(Ac countName) & "'"

[Christopher Nigro]

In your working method you are passing a command string to the server rather than the other way in ADO of create/append parameter lines (another way of doing it)

in your instance take a look at the following command string passed and notice the EXEC element difference in the string as compared to yours.

This is just a simple paste of a command string passing parameters where fGetFileName(Ac countName) is a functional representation of a users name lets say 'John Doe' for want of a better much under-used individual.

The EXEC portion is expected on the server in order to execute it once there

oConn.Execute "EXEC sp_grantdbacces s '" & AccountName & "', '" & fGetFileName(Ac countName) & "'"

Ah, yes, I missed that also. Would have come up once he overcame the first hurdle...

[bullfrog83]

In your working method you are passing a command string to the server rather than the other way in ADO of create/append parameter lines (another way of doing it)

in your instance take a look at the following command string passed and notice the EXEC element difference in the string as compared to yours.

This is just a simple paste of a command string passing parameters where fGetFileName(Ac countName) is a functional representation of a users name lets say 'John Doe' for want of a better much under-used individual.

The EXEC portion is expected on the server in order to execute it once there

oConn.Execute "EXEC sp_grantdbacces s '" & AccountName & "', '" & fGetFileName(Ac countName) & "'"

I added EXEC but I still get the error msg.

Taking Christopher's advice, if I add quotes around each integer value then I get an conversion failed when converting varchar value '226,227,228' to data type int.

I also tried removing my specifying the parameter name:

cn.Execute "exec spRemoveIncmplC lassTakenCourse s " & lngTakenDiscId & ", " & strTakenClassId s

But then I get an error telling me that there's too many arguments specified.

[Jim Doherty]

I added EXEC but I still get the error msg.

Taking Christopher's advice, if I add quotes around each integer value then I get an conversion failed when converting varchar value '226,227,228' to data type int.

I also tried removing my specifying the parameter name:

cn.Execute "exec spRemoveIncmplC lassTakenCourse s " & lngTakenDiscId & ", " & strTakenClassId s

But then I get an error telling me that there's too many arguments specified.

Dont give up on the string building aspect of this it will be a simple issue of that concatenation not appearing combining/concatenating correctly where the process thinks it has another parameter 'because' of it.

My problem is i cannot see the singles or doubles on here too well and at moment I go for a meal as an interim have a look at doing it this way where the values are provided from some screen controls if you can. I will endavour to monitor this later

Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command

    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "dbo.spRemoveIncmplClassTakenCourses"
    cmd.CommandType = adCmdStoredProc
    Dim par As ADODB.Parameter
    Set par = cmd.CreateParameter("@TakenDiscId", adinteger, adParamInput)
    cmd.Parameters.Append par
    Set par = cmd.CreateParameter("@TakenClassIds", adVarchar, adParamInput, 255)
    cmd.Parameters.Append par

    cmd.Parameters("@TakenDiscId") = Forms!frmYourFormName!YourControlName
    cmd.Parameters("@TakenClassIds") = Forms!frmYourFormName!YourControlName
    cmd.Execute

[Jim Doherty]

I added EXEC but I still get the error msg.

Taking Christopher's advice, if I add quotes around each integer value then I get an conversion failed when converting varchar value '226,227,228' to data type int.

I also tried removing my specifying the parameter name:

cn.Execute "exec spRemoveIncmplC lassTakenCourse s " & lngTakenDiscId & ", " & strTakenClassId s

But then I get an error telling me that there's too many arguments specified.

Ok Yesterday I was in rather a hurry due to an impending appointment. I had. However since then and this morning I have had a closer look at what it is you are actually doing so lets break it down...

Your intial post is saying "I have a procedure and am passing an integer parameter to a procedure and also a varchar string parameter (sized at 255? you can increase that for future proofing it really doesn't matter. I suspect you might be taking your values from an access textbox or something. If you are not however and are actually looping and concatenating a string in code you might well exceed the 255 size limit you have currently set.

Parameters passed in to a stored procedure can be very much more than 255 nearly the 8k page limit with varchar (8000) in fact. It is commonly used for passing in comma separated values but beware it is does have an overall limit doing things this way)

You might also want to look at the principle of 'output' parameters as I do believe you would benefit from the result of your stored procedure processing by returning a 'value' in itself to tour frontend procedure. That way you can control program flow a better ie: if the procedure does not error the return will be zero thus giving a logic for redirection and so on.

By not including some type of error handling you are simply hoping the process works on the server and hoping 'some' kind of message comes your way.....which might not come in a understandable and meaningful way to the end user.

In fairness the current Update code will either succeed or fail and you might think to not be too concerned about it however, it is something you should consider as a better practice as your skill level increases and so on.

The way I see this happening is this:- using your current code You are it seems looping integers and merely string them together separating each value with a comma. That is all you need to do ie..... 210,211,212,213 you THEN wrap the complete concatenation both sides with a single quote so it becomes '210,211,212,21 3' THIS is the parameter value sent to the server as the string and it is this value that is assigned to the string variable strTakenClassId s At this point you THEN need to wrap that string variable in its own single quotes as part of the command string sent to the server in the manner you originally intended.

So..... look at the following command line and see how it differs to yours. I have replicated your scenario on my server and tested it using an ADP form button updating records on the server via a stored procedure I created for the purpose. As an aside if you wish to pursue the example method given in my last post or indeed output parameters if you are struggling let me know

cn.Execute "EXEC spRemoveIncmplC lassTakenCourse s @TakenDiscId = '" & lngTakenDiscId & "', @TakenClassIds = '" & strTakenClassId s & "'"

The above is in direct response to you initial post

Below is the stored procedure I created for this purpose

CREATE PROCEDURE dbo.spRemoveIncmplClassTakenCourses 
( @TakenDiscId int,@TakenClassIds varchar(7000)) 
AS 
DECLARE @procname varchar(50)
SELECT @procname = object_name(@@procid)

BEGIN TRANSACTION trn_update_courseid
BEGIN

UPDATE StdDegReqCourse
SET TakenCourseId = NULL
FROM StdDegReqCourse Cr, StdDegReqClass C1
WHERE Cr.StdDegReqClassId = C1.StdDegReqClassId
AND Cr.TakenCourseId IS NOT NULL
AND (C1.StdDegReqDiscId = @TakenDiscId)
AND Cr.StdDegReqClassId NOT IN (@TakenClassIds)
IF @@error != 0
	BEGIN
	ROLLBACK TRANSACTION trn_update_courseid
	RAISERROR('Error UPDATING table StdDegRegCourse.  Error occurred in procedure %s.  Rolling back transaction...', 16, 1, @procname)
	RETURN
	END
ELSE
	BEGIN
	COMMIT  TRANSACTION trn_update_courseid
	END
END
GO

[bullfrog83]

Ok Yesterday I was in rather a hurry due to an impending appointment. I had. However since then and this morning I have had a closer look at what it is you are actually doing so lets break it down...

Your intial post is saying "I have a procedure and am passing an integer parameter to a procedure and also a varchar string parameter (sized at 255? you can increase that for future proofing it really doesn't matter. I suspect you might be taking your values from an access textbox or something. If you are not however and are actually looping and concatenating a string in code you might well exceed the 255 size limit you have currently set.

Parameters passed in to a stored procedure can be very much more than 255 nearly the 8k page limit with varchar (8000) in fact. It is commonly used for passing in comma separated values but beware it is does have an overall limit doing things this way)

You might also want to look at the principle of 'output' parameters as I do believe you would benefit from the result of your stored procedure processing by returning a 'value' in itself to tour frontend procedure. That way you can control program flow a better ie: if the procedure does not error the return will be zero thus giving a logic for redirection and so on.

By not including some type of error handling you are simply hoping the process works on the server and hoping 'some' kind of message comes your way.....which might not come in a understandable and meaningful way to the end user.

In fairness the current Update code will either succeed or fail and you might think to not be too concerned about it however, it is something you should consider as a better practice as your skill level increases and so on.

The way I see this happening is this:- using your current code You are it seems looping integers and merely string them together separating each value with a comma. That is all you need to do ie..... 210,211,212,213 you THEN wrap the complete concatenation both sides with a single quote so it becomes '210,211,212,21 3' THIS is the parameter value sent to the server as the string and it is this value that is assigned to the string variable strTakenClassId s At this point you THEN need to wrap that string variable in its own single quotes as part of the command string sent to the server in the manner you originally intended.

So..... look at the following command line and see how it differs to yours. I have replicated your scenario on my server and tested it using an ADP form button updating records on the server via a stored procedure I created for the purpose. As an aside if you wish to pursue the example method given in my last post or indeed output parameters if you are struggling let me know

cn.Execute "EXEC spRemoveIncmplC lassTakenCourse s @TakenDiscId = '" & lngTakenDiscId & "', @TakenClassIds = '" & strTakenClassId s & "'"

The above is in direct response to you initial post

Below is the stored procedure I created for this purpose

CREATE PROCEDURE dbo.spRemoveIncmplClassTakenCourses 
( @TakenDiscId int,@TakenClassIds varchar(7000)) 
AS 
DECLARE @procname varchar(50)
SELECT @procname = object_name(@@procid)

BEGIN TRANSACTION trn_update_courseid
BEGIN

UPDATE StdDegReqCourse
SET TakenCourseId = NULL
FROM StdDegReqCourse Cr, StdDegReqClass C1
WHERE Cr.StdDegReqClassId = C1.StdDegReqClassId
AND Cr.TakenCourseId IS NOT NULL
AND (C1.StdDegReqDiscId = @TakenDiscId)
AND Cr.StdDegReqClassId NOT IN (@TakenClassIds)
IF @@error != 0
	BEGIN
	ROLLBACK TRANSACTION trn_update_courseid
	RAISERROR('Error UPDATING table StdDegRegCourse.  Error occurred in procedure %s.  Rolling back transaction...', 16, 1, @procname)
	RETURN
	END
ELSE
	BEGIN
	COMMIT  TRANSACTION trn_update_courseid
	END
END
GO

I followed your advice and did the following:

First I loop and string my integers together separated by a comma:

strTakenClassIds = strTakenClassIds & lngTakenClassId & ","

This produces 256,249,255,257 . Then, I wrap strTakenClassId s with a single quote on both sides:

            lngTakenClassesLen = Len(strTakenClassIds) - 1
            strTakenClassIds = "'" & Left(strTakenClassIds, lngTakenClassesLen) & "'"

This produces '256,249,255,25 7'. I then changed my command line to match yours so that another set of single quotes are wrapped around it so it's ''256,249,255,2 57'':

cn.Execute "exec spRemoveIncmplClassTakenCourses '" & lngTakenDiscId & "', '" & strTakenClassIds & "'"

Upon executing this line I get the error: Incorrect syntax near '256'. I also changed my sp to match what you provided with the error handling. Is there something I misunderstood that I'm getting this error?

[Jim Doherty]

I followed your advice and did the following:

First I loop and string my integers together separated by a comma:

strTakenClassIds = strTakenClassIds & lngTakenClassId & ","

This produces 256,249,255,257 . Then, I wrap strTakenClassId s with a single quote on both sides:

            lngTakenClassesLen = Len(strTakenClassIds) - 1
            strTakenClassIds = "'" & Left(strTakenClassIds, lngTakenClassesLen) & "'"

This produces '256,249,255,25 7'. I then changed my command line to match yours so that another set of single quotes are wrapped around it so it's ''256,249,255,2 57'':

cn.Execute "exec spRemoveIncmplClassTakenCourses '" & lngTakenDiscId & "', '" & strTakenClassIds & "'"

Upon executing this line I get the error: Incorrect syntax near '256'. I also changed my sp to match what you provided with the error handling. Is there something I misunderstood that I'm getting this error?

Hello bullfrog83

The command line being sent to the server from your application is correct. YOu are sending a 'string' value to the server comprising of concatenated integers ie '211,214,231,43 1,245,231' defined by your application string variable strTakenClassId s which is received as input parameter @TakenClassIds datatype varchar when it hits the server.

The only issue here is on the comparison that the parameter once received makes against the column it is comparing against which as we now know is an INT datatype.

The comparison cannot not be made between the two different datatypes as it is and it is necessary to CAST as a VARCHAR your Cr.StdDegReqCla ssId column in order for the comparison to work as expected.

In the stored procedure that I posted previously look at line #16 and change it to the following. The update will run successfully

AND CAST(Cr.StdDegR eqClassId AS VARCHAR) NOT IN (@TakenClassIds )

Regards

Jim

[bullfrog83]

Hello bullfrog83

The command line being sent to the server from your application is correct. YOu are sending a 'string' value to the server comprising of concatenated integers ie '211,214,231,43 1,245,231' defined by your application string variable strTakenClassId s which is received as input parameter @TakenClassIds datatype varchar when it hits the server.

The only issue here is on the comparison that the parameter once received makes against the column it is comparing against which as we now know is an INT datatype.

The comparison cannot not be made between the two different datatypes as it is and it is necessary to CAST as a VARCHAR your Cr.StdDegReqCla ssId column in order for the comparison to work as expected.

In the stored procedure that I posted previously look at line #16 and change it to the following. The update will run successfully

AND CAST(Cr.StdDegR eqClassId AS VARCHAR) NOT IN (@TakenClassIds )

Regards

Jim

That worked! And so simple! Thanks very much!!!

[bullfrog83]

Hello bullfrog83

The command line being sent to the server from your application is correct. YOu are sending a 'string' value to the server comprising of concatenated integers ie '211,214,231,43 1,245,231' defined by your application string variable strTakenClassId s which is received as input parameter @TakenClassIds datatype varchar when it hits the server.

The only issue here is on the comparison that the parameter once received makes against the column it is comparing against which as we now know is an INT datatype.

The comparison cannot not be made between the two different datatypes as it is and it is necessary to CAST as a VARCHAR your Cr.StdDegReqCla ssId column in order for the comparison to work as expected.

In the stored procedure that I posted previously look at line #16 and change it to the following. The update will run successfully

AND CAST(Cr.StdDegR eqClassId AS VARCHAR) NOT IN (@TakenClassIds )

Regards

Jim

Hi Jim,

I thought this was solved but upon more testing I discovered it's not. Although it's executing the sproc, the sproc isn't handling the @TakenClassIds properly. For instance, let's say this string consists of 213,214,215. If you execute the sproc as a select statment, you can see in the results pane that records with those StdDegReqClassI ds are still showing up when they shouldn't because of the NOT IN. I think it has something to do with the fact that the string is still surrounded by quotes. I tried to remove the quotations by re-setting the @TakenClassIds like so:

CREATE PROCEDURE dbo.spClearIncmplClassTakenCourses
(
@TakenDiscId int,
@TakenClassIds varchar(7000)
)
AS
DECLARE @procname varchar(50)
SELECT @procname = object_name(@@procid)

BEGIN TRANSACTION trn_update_takencourseid
BEGIN

	SET @TakenClassIds = replace(@TakenClassIds,'''','')

'rest of sproc

But that hasn't worked. Any ideas?

[Jim Doherty]

Hi Jim,

I thought this was solved but upon more testing I discovered it's not. Although it's executing the sproc, the sproc isn't handling the @TakenClassIds properly. For instance, let's say this string consists of 213,214,215. If you execute the sproc as a select statment, you can see in the results pane that records with those StdDegReqClassI ds are still showing up when they shouldn't because of the NOT IN. I think it has something to do with the fact that the string is still surrounded by quotes. I tried to remove the quotations by re-setting the @TakenClassIds like so:

CREATE PROCEDURE dbo.spClearIncmplClassTakenCourses
(
@TakenDiscId int,
@TakenClassIds varchar(7000)
)
AS
DECLARE @procname varchar(50)
SELECT @procname = object_name(@@procid)

BEGIN TRANSACTION trn_update_takencourseid
BEGIN

	SET @TakenClassIds = replace(@TakenClassIds,'''','')

'rest of sproc

But that hasn't worked. Any ideas?

The sproc I sent works. Without going through what you have your end it is rather difficult to understand why this is not working for you.

1) The server expects to receive input variables containing data sufficient to execute obviously.

2) The string variable being passed to the sproc demands a wrap of single quotes ie '213,423,416,41 7' The conversion against the INT column in your data then provides for a comparison as per my last post.

3) If you are passing in parameter values as I would have expected then the server should be parsing it to valid SQL correctly. I have run this sproc my end my with no failure.

4) I cannot see where the need exists per se to 'replace' anything in the sproc as is.

5) I do not know if you have used or are familiar with SQL Profiler but it exists to show to you the SQL statements/command lines that the server is executing at any one time and shows by including the real value of variables passed in as part of that command line in any TRACE profile you create. I suggest you raise a trace and look see what your data or command line reveals to you.

[bullfrog83]

The sproc I sent works. Without going through what you have your end it is rather difficult to understand why this is not working for you.

1) The server expects to receive input variables containing data sufficient to execute obviously.

2) The string variable being passed to the sproc demands a wrap of single quotes ie '213,423,416,41 7' The conversion against the INT column in your data then provides for a comparison as per my last post.

3) If you are passing in parameter values as I would have expected then the server should be parsing it to valid SQL correctly. I have run this sproc my end my with no failure.

4) I cannot see where the need exists per se to 'replace' anything in the sproc as is.

5) I do not know if you have used or are familiar with SQL Profiler but it exists to show to you the SQL statements/command lines that the server is executing at any one time and shows by including the real value of variables passed in as part of that command line in any TRACE profile you create. I suggest you raise a trace and look see what your data or command line reveals to you.

Well, it's working now again, fortunately. I don't know what happened. It was working, then wasn't, now it is again.