Executing SQL Server Stored Procedures from Visual Basic 6.0

[vmaruv]

Hi,

Can someone help me to Execute SQL Server Stored Procedures from Visual Basic 6.0 ? I wish to give the parameters to the stored procedure (eg to backup a database, I want to give the backup location, device etc from VB code) and then execute it and return the results back to VB. Please can someone help ?

This must be somewhere already in this forum but I'm not able to find.

Thanks in Advance.

 

[fredericofonseca]

just search for "stored procedures" and you will find plenty of examples.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Sheco]

Are you using ADO to connect to your SQL Server?

 

[vmaruv]

Actually, the requirement is as follows

backup or recover a SQL Server database. Before that the users should be cautioned and later the backup should start. I have written stored procedures making use of xp_cmdshell to caution the users by using netsend command and also other stored procedures for backing up or restoring the database (depending on the button clicked).

Now,the user will type in the message in a text box on my VB form and click on send button. this message will be sent as a parameter to the stored procedure and the SP should execute. It returns all the users who are logged in and to whome the message is sent.

I can use ADO if not please suggest any other option if it makes it simpler. I have absolutely no idea and since a long time just searching for how to do it on the net.
This is the first time I am using databases from VB6 (especially the Stored procs). I don't have much knowledge on using ADO also...

Please help !!

 

[Sheco]

So you need to call a stored procedure with a single input parameter of data type string?
... and capture the returned records?

set rs = cn.execute("sp_procname 'Log out now fools!'")

 

[vmaruv]

the input parameter (which is a string) should be taken from the text box provided on the form. So is it a good thing to use ADO for these kind of things ?

 

[VBrit]

Look up stored procedures VB6 ADO on google. You could use the SQL-DMO, which provides a COM interface to some of the management functionality.

 

[vmaruv]

I'm trying since a long time (couple of hours infact) but am unable to get a good site which gives me an example how to connect to the database and execute a parameterised stored procedure that returns some output.

Please can someone suggest if you know any ? or can you give me a sample code to do this ?

Your help is greatly appreciated.

 

[fredericofonseca]

As I said this forum has plenty of examples.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[VBrit]

something like this (parameters may not be in correct order but intellisense will let you know)

dim conn as adodb.connection
dim cmd as adodb.command
dim prm as adodb.parameter
dim rs as adodb.recordset

set conn = new adodb.connection
conn.connectionstring = "driver=SQL Server;database=mydatabase;server=myserver;uid=myuser;pwd=mypassword"
(or goto

http://www.connectionstrings.com)

conn.open
set cmd = new adodb.command
set cmd.activeconnection = conn
cmd.commandtype = adstoredproc
cmd.commandtext = "usp_my_stored_proc"
cmd.parameters.append ("myparameter",adVarchar,10, "parameterinputvalue")

set rs = cmd.execute

'now your rs contains the output of your sp and the parameter has been passed into it.

Hope this helps

VBrit

 

[vmaruv]

Thank you very much..I will try this out and get back with the results...

Thanks again.

 

[vmaruv]

I've tried the above code. I am having problems passing the paramters to the SP. I am getting an error at the line

cmd.parameters.append ("myparameter",adVarchar,10, "parameterinputvalue")

Can someone help me how to use this statement to append a parameter to the stored procedure.

It should be something like exec usp_notify_users 'Hello'

The SP that i am executing takes a string as a parameter (@notification).
Can i do someting like
Set rs = cmd.Execute("usp_notify_users, 'Hello'")

It still gives me an error that the parameter is not passed.

 

[fredericofonseca]

What examples have you tried from the forums, and how do they compare in terms of the definition of the parameter?

Also if you are asking for help on a particular bit of code you have done then you should post the full code, not just a a line that on it's own may not give in indication of the error (it does on this caase, but you should be able to find the error if you compare it to some of the examples on this forum.)


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[vmaruv]

Thanks a lot for your reply.

Actually,I have not tried any examples from the forum as such. I have tried the code given my Vbrit as an answer to my query above.

The problem is I am not able to pass parameters to the stored procedure from my vb code. but When I try to execute other SPs whcih do not take any parameters, it works fine. Below is the code I am trying to execute.

Dim conn As adodb.connection
Dim cmd As adodb.Command
Dim prm As adodb.Parameter
Dim rs As adodb.Recordset

Set conn = New adodb.connection
conn.connectionstring = "driver=SQL Server;database=master;server=hyiserv;uid=sa;pwd=test"
conn.open
Set cmd = New adodb.Command
Set cmd.activeconnection = conn
cmd.CommandType = adCmdStoredProc

cmd.commandtext = "usp_notify_users"
cmd.CreateParameter("@myparameter", adVarChar, 10, adParamInput)
cmd.Parameters("@myparameter") = "Hello"
Set rs = cmd.Execute

---------
I have also tried the cmd.execute as follows
Set rs = cmd.Execute("usp_notify_users 'Hello'") but it says syntax error.

I'm a newbie to database programming. Your help is really appreciated. Also, if you could give me the reference to any threads in this forum that are doing similar thing it would be great !!

Thanks again.

 

[vmaruv]

Can someone help me with this ??

 

[johnwm]

To get better answers read faq222-2244 carefully, especially paragraphs 8,9, 10,13,14,15,16.

That research should take you to faq222-2067

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[fredericofonseca]

I've been trying to help you but you have so far refused to follow my guidance. Can you give me a good reason why should I keep doing it?


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[vmaruv]

I'm sorry. My intent was not to trouble or irritate you. I had a deadline in front of me and was not able to make it work. So thought you could give me some help. Anyways..I'm sorry again to everyone and Thanks a ton for helping me.

Regards.