Solved

stored procedure vb 6 with parameters

Posted on 2004-04-26
9
195 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
9 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 250 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

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!

Question has a verified solution.

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

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

685 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