Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

stored procedure vb 6 with parameters

Posted on 2004-04-26
9
Medium Priority
?
203 Views
Last Modified: 2012-05-05
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
datPrimaryRS.Refresh

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

PS
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


0
Comment
Question by:zwan
  • 4
  • 2
6 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 10916913
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
http://www.brainbench.com
0
 

Author Comment

by:zwan
ID: 10917397
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
 
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 10917640
zwan,

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

Create Procedure ......

SET NOCOUNT ON

'The rest of your sp code

SET NOCOUNT OFF

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.

Tim.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:zwan
ID: 10918222
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
to
datPrimaryRS.CommandType  = adCmdText
ran successfully

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

Accepted Solution

by:
TimCottee earned 1000 total points
ID: 10918256
zwan,

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.

Tim.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 12637180
As I am the only participant you don't have many options !
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question