Call Stored Procedure from VB 6

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Call Stored Procedure from VB 6

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-27 : 14:02:23

If I call this procederu from my VB application first time when I open application, everything is fine. But, If I call procedure again I have message that parameter is missing and there is not argument.
I do not understand why, because I do not have setup parameter in procedure. Why first execution is good but next is bad?
I tried to add WITH RECOMPILE in procedure, but I had same problem.
Please help!!!

CREATE PROCEDURE Del_PriceStorage
AS
SET NOCOUNT ON
DELETE Table1

Ilimax

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-27 : 14:34:45

Where's the calling code in VB?

Post that.

Brett

8-)

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-27 : 15:26:39

mydsn = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=XX;Mode=ReadWrite;Initial Catalog=XX"
myCon.Open (mydsn)
If Err.Number <> 0 Then
MsgBox ("Error connecting with database" & vbCrLf & Err.Description)
End
End If
myCmd.ActiveConnection = myCon

myCmd.CommandText = "Del_PriceStorage"
myCmd.CommandType = adCmdStoredProc
myCmd.Execute

So, first execution is right.Just second execution fail and return error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 15:39:44

What is the exact error message?

Tara

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-27 : 16:04:12

Run-time error:-2147217900
"Precedure Del_PriceStorage has no parameters and arguments were supplied"

There is also one thing interesting;
In my application there are some other sql statements that I run with this my "myCmd" Command. After executing this procedure I am not able to run any of those SQL statemnts too which are executed by "myCmd.Execute"

Do I miss anything in stored procedure.

Thanks,

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-02-27 : 16:14:51

If you're re-using the same myCmd object, you need to either destroy (set to nothing) and re-instantiate it, or at least make sure that if a previous use did any AddParameter commands that you remove those when calling this procedure that has no parameters.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-27 : 16:48:53

AjanMark,

Yes, you are right! My second procedure with parameters killed my "myCmd" Command. Now, it's working fine.Thank you.
I am little bit confused with this "WITH COMPILE". I added that to my procedure. What do you think?(Procedure will be run from only one computer)

CREATE PROCEDURE Del_PriceStorage
WITH RECOMPILE
AS
SET NOCOUNT ON
DELETE Table1

Thanks,

Ilimax

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 17:09:33

Did you look up what WITH RECOMPILE does in SQL Server Books Online? You usually don't need to specify this though.

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-02-27 : 17:35:43

I've never used the WITH RECOMPILE option on my stored procedures, so my first thought is that you probably won't need it either. Especially if your procedure is anywhere near as simple as your example. As Tara suggests, BOL (Books Online) is a great resource to learn more.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-27 : 21:34:00

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources