473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Error Calling SQL Stored Procedure

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

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.spRemoveIncmplClassTakenCourses
  2. (
  3. @TakenDiscId int,
  4. @TakenClassIds varchar(255)
  5. )
  6. AS
  7. BEGIN
  8.     SET NOCOUNT ON
  9.  
  10.     UPDATE    Cr
  11.     SET              TakenCourseId = NULL
  12.     FROM         StdDegReqCourse AS Cr INNER JOIN
  13.                           StdDegReqClass AS Cl ON Cr.StdDegReqClassId = Cl.StdDegReqClassId
  14.     WHERE     (Cr.TakenCourseId IS NOT NULL) AND (Cl.StdDegReqDiscId = @TakenDiscId) AND 
  15.                           (Cr.StdDegReqClassId NOT IN (@TakenClassIds))
  16.  
And this is my vba:

Expand|Select|Wrap|Line Numbers
  1. Dim cn As New ADODB.Connection
  2. Dim lngTakenDiscId As Long
  3. Dim strTakenClassIds As String
  4.  
  5. cn.Open CurrentProject.Connection
  6.  
  7. 'lngTakenDiscId and strTakenClassIds are set
  8.  
  9. cn.Execute "spRemoveIncmplClassTakenCourses @TakenDiscId = " & lngTakenDiscId & ", @TakenClassIds = " & strTakenClassIds
  10.  
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?
Jul 8 '10 #1
15 3227
@bullfrog83
Try:

Expand|Select|Wrap|Line Numbers
  1. cn.Execute "spRemoveIncmplClassTakenCourses @TakenDiscId = " & lngTakenDiscId & ", @TakenClassIds = '" & strTakenClassIds & "'"
  2.  
  3.  
Jul 8 '10 #2
bullfrog83
124 100+
@Christopher Nigro
That doesn't work because then it passes the values like so: '226,227,228' when it should be '226','227','228'. 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:

strTakenClassIds = strTakenClassIds & "'" & lngTakenClassId & "',"

But then I get the original error message back.
Jul 8 '10 #3
If it needs to be:
'226','227','228'

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

Those are all single quotes...
Jul 8 '10 #4
Jim Doherty
897 Expert 512MB
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(AccountName) 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_grantdbaccess '" & AccountName & "', '" & fGetFileName(AccountName) & "'"
Jul 8 '10 #5
@Jim Doherty
Ah, yes, I missed that also. Would have come up once he overcame the first hurdle...
Jul 8 '10 #6
bullfrog83
124 100+
@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 spRemoveIncmplClassTakenCourses " & lngTakenDiscId & ", " & strTakenClassIds

But then I get an error telling me that there's too many arguments specified.
Jul 8 '10 #7
Jim Doherty
897 Expert 512MB
@bullfrog83
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

Expand|Select|Wrap|Line Numbers
  1. Dim cmd As ADODB.Command
  2.     Set cmd = New ADODB.Command
  3.  
  4.     cmd.ActiveConnection = CurrentProject.Connection
  5.     cmd.CommandText = "dbo.spRemoveIncmplClassTakenCourses"
  6.     cmd.CommandType = adCmdStoredProc
  7.     Dim par As ADODB.Parameter
  8.     Set par = cmd.CreateParameter("@TakenDiscId", adinteger, adParamInput)
  9.     cmd.Parameters.Append par
  10.     Set par = cmd.CreateParameter("@TakenClassIds", adVarchar, adParamInput, 255)
  11.     cmd.Parameters.Append par
  12.  
  13.     cmd.Parameters("@TakenDiscId") = Forms!frmYourFormName!YourControlName
  14.     cmd.Parameters("@TakenClassIds") = Forms!frmYourFormName!YourControlName
  15.     cmd.Execute
Jul 8 '10 #8
Jim Doherty
897 Expert 512MB
@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,213' THIS is the parameter value sent to the server as the string and it is this value that is assigned to the string variable strTakenClassIds 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 spRemoveIncmplClassTakenCourses @TakenDiscId = '" & lngTakenDiscId & "', @TakenClassIds = '" & strTakenClassIds & "'"

The above is in direct response to you initial post

Below is the stored procedure I created for this purpose

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.spRemoveIncmplClassTakenCourses 
  2. ( @TakenDiscId int,@TakenClassIds varchar(7000)) 
  3. AS 
  4. DECLARE @procname varchar(50)
  5. SELECT @procname = object_name(@@procid)
  6.  
  7. BEGIN TRANSACTION trn_update_courseid
  8. BEGIN
  9.  
  10. UPDATE StdDegReqCourse
  11. SET TakenCourseId = NULL
  12. FROM StdDegReqCourse Cr, StdDegReqClass C1
  13. WHERE Cr.StdDegReqClassId = C1.StdDegReqClassId
  14. AND Cr.TakenCourseId IS NOT NULL
  15. AND (C1.StdDegReqDiscId = @TakenDiscId)
  16. AND Cr.StdDegReqClassId NOT IN (@TakenClassIds)
  17. IF @@error != 0
  18.     BEGIN
  19.     ROLLBACK TRANSACTION trn_update_courseid
  20.     RAISERROR('Error UPDATING table StdDegRegCourse.  Error occurred in procedure %s.  Rolling back transaction...', 16, 1, @procname)
  21.     RETURN
  22.     END
  23. ELSE
  24.     BEGIN
  25.     COMMIT  TRANSACTION trn_update_courseid
  26.     END
  27. END
  28. GO
Jul 9 '10 #9
bullfrog83
124 100+
@Jim Doherty
I followed your advice and did the following:

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

Expand|Select|Wrap|Line Numbers
  1. strTakenClassIds = strTakenClassIds & lngTakenClassId & ","
  2.  
This produces 256,249,255,257. Then, I wrap strTakenClassIds with a single quote on both sides:

Expand|Select|Wrap|Line Numbers
  1.             lngTakenClassesLen = Len(strTakenClassIds) - 1
  2.             strTakenClassIds = "'" & Left(strTakenClassIds, lngTakenClassesLen) & "'"
  3.  
This produces '256,249,255,257'. 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,257'':

Expand|Select|Wrap|Line Numbers
  1. cn.Execute "exec spRemoveIncmplClassTakenCourses '" & lngTakenDiscId & "', '" & strTakenClassIds & "'"
  2.  
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?
Jul 9 '10 #10
Jim Doherty
897 Expert 512MB
@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,431,245,231' defined by your application string variable strTakenClassIds 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.StdDegReqClassId 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.StdDegReqClassId AS VARCHAR) NOT IN (@TakenClassIds)

Regards

Jim
Jul 9 '10 #11
bullfrog83
124 100+
@Jim Doherty
That worked! And so simple! Thanks very much!!!
Jul 12 '10 #12
bullfrog83
124 100+
@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 StdDegReqClassIds 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:

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.spClearIncmplClassTakenCourses
  2. (
  3. @TakenDiscId int,
  4. @TakenClassIds varchar(7000)
  5. )
  6. AS
  7. DECLARE @procname varchar(50)
  8. SELECT @procname = object_name(@@procid)
  9.  
  10. BEGIN TRANSACTION trn_update_takencourseid
  11. BEGIN
  12.  
  13.     SET @TakenClassIds = replace(@TakenClassIds,'''','')
  14.  
  15. 'rest of sproc
  16.  
But that hasn't worked. Any ideas?
Jul 12 '10 #13
Jim Doherty
897 Expert 512MB
@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,417' 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.
Jul 13 '10 #14
bullfrog83
124 100+
@Jim Doherty
Well, it's working now again, fortunately. I don't know what happened. It was working, then wasn't, now it is again.
Jul 13 '10 #15
Jim Doherty
897 Expert 512MB
@bullfrog83
I rather thought it was an issue unrelated to the SPROC and string but hey ho it happens... so long as you are on track. Don't forget to utilise that profiler sometime when you have a minute free, it does give you a good idea of what server processes are doing and so on.
Jul 13 '10 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: dw | last post by:
Hello all, I'm having a dickens of a time calling a stored procedure on a connection. Every time I do, it generates an error "Arguments are of the wrong type, are out of acceptable range, or are in...
1
by: Chetan | last post by:
Hi We have Oracle 8.1.7.4 and using .net framework 1.1 with Microsoft client. While executting the stored procedure we are getting an error message. Here is our code. here is the code that...
0
by: JN | last post by:
Hello, I'm having problem calling stored procedures from Visual FoxPro database. I got the following exception error: "System.Data.OleDb.OleDbException: Unrecognized command verb" It seems...
6
by: Scott McNair | last post by:
Hi all, I'm having problems calling a stored procedure from within my code. If I execute the SP manually from a Query Analyzer window it runs without a hitch, but it seems it doesn't even fire...
4
by: Jack | last post by:
Hi, I am trying to run an example code from a book. However I am getting the following error message: Number: -2147217900 Description: Syntax error or access violation Source: Microsoft OLE...
14
by: krishna1412 | last post by:
Currently i am working in a project of report generation in MS ACCESS. The tables are in sql server 2000. I have to write stored proc in ms access. Illustration: I am having a stored proc...
4
by: eighthman11 | last post by:
I'm calling a stored procedure on a sql server from an access application. I just need the stored procedure to run I do not need any data returned from the stored procedure to my Access...
0
by: Loop123 | last post by:
Hello @all, i have a problem....i wrote a stored procedure which ends with the command "complete successfully", but when i will call the stored procedure i got the message: SQL0444N Routine...
3
by: yinzara | last post by:
I have the following trigger that calls a DB2 stored procedure: DROP TRIGGER GGWU.TRI_A_MULTI_PROP@ CREATE TRIGGER GGWU.TRI_A_MULTI_PROP AFTER INSERT ON GGWU.MULTIPLIER_PROPERTY REFERENCING ...
6
Soniad
by: Soniad | last post by:
Hello, I am excecuting a stored procedure in my ASP page , it has one out parameter (@confirm) . after executing the procedure i want to retreive this out parameter and assign it to variable...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.