• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

stored procedure vb 6 with parameters

I have setup a stored procedure in SQL server 2000 with 2 parameters
I am trying to set this as the record source for an ododc
datasource connected to a dbgrid

code is
strsql = "AdjustmentBatchPrimaryRS "
strsql = strsql & RstControl!Current_Year_Period
strsql = strsql & "," & vFirstService & ";1"
MsgBox strsql - returns  - adjustmentbatch 93,111;1
datPrimaryRS.CommandType = adCmdStoredProc
datPrimaryRS.RecordSource = strsql

retrieve error
ODBC SQL Server Driver - syntax error or access violation

I have set the execute permissions in SQL server for my NT login
also when a run  stored procedure without parameters - no problem.
also this syntax works when use it as part of con.execute statement

  • 4
  • 2
1 Solution
Hi zwan,

You appear to have a ; rather than a , between the last two parameters, this could be part of the problem. Also you may want to use the execute keyword:

Execute adjustmentbatch 93,111,1

Tim Cottee
Brainbench MVP for Visual Basic
zwanAuthor Commented:
thanks tim
I have tried this with no success
the field after ; is not a parmeter but has default value of 1 and is automatically set to 1  when entering a stored  procedure on the property page when setting up record source without code.
Iam new to interfacing with SQL SERVER backend and I am not sure what relates to.
I have to ask the question can you use a stored procdure with parameters for the record source of adodc recordsource

I can execeute this code from the adodb.Connection using the execute syntax without any problems i.e.
strsql = "AdjustmentBatchPrimaryRS "
strsql = strsql & 93
strsql = strsql & "," & 111
MsgBox strsql
Con.Execute strsql
as I said the problem only appears when setting up the data source for the grid

Most likely then that you are getting a closed recordset returned? Try adding

Create Procedure ......


'The rest of your sp code


This will suppress the text messages you see when you run in query analyzer and return the actual data. Sometimes this causes a problem because you are getting the first closed recordset which actually represents a message like (xx records affected) instead of the recordset containing your data. This is one of the (two) ways to resolve this issue.

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

zwanAuthor Commented:
thanks tim
I will add this to the procedure, however  
I have solved the problem by trail and error -
when i changed
datPrimaryRS.CommandType  = adCmdStoredProc
datPrimaryRS.CommandType  = adCmdText
ran successfully

therefore just running the stored procedure as a piece of standard SQL

Of course, didn't see that. The reason is that if you specify adStoredProc you need to add parameters to the command in order for them to be processed properly. You can only specify the sp name with this command type and need to have the parameters value/direction etc specified seperately.

As I am the only participant you don't have many options !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now