Solved

stored procedure vb 6 with parameters

Posted on 2004-04-26
9
189 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now