how to call a SQL Server stored procedure in VB using ADO

Could someone please provide a snippet of code on how I would call a stored procedure in SQL Server using VB and the ADO command object. A bried comment of each step would be very, very helpful.

Not sure how to tie everything together.

Thanks in advance.

LVL 13
samic400Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Qaiser_Mehmood_MughalConnect With a Mentor Commented:
Here is one example.

 Dim objConn, objCmd, objParam
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=macdb;uid=test;pwd=test"
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.CommandText = "sp_sel_no_authors"
objCmd.CommandType = adCmdStoredProc
objcmd.ActiveConnection = objConn
Set objParam = objCmd.CreateParameter ("@count_authors",adInteger,adParamOutput,4)
objCmd.Parameters.Append objParam
ObjCmd.Execute


you can also take the full details about this issue from below link.

http://www.macronimous.com/resources/stored_procedures_for_ASP_and_VB_Programmers.asp
0
 
indianguru2Commented:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Ashish PatelConnect With a Mentor Commented:
Look at the code snippet
'ConnectionObject is the connection object to database
 
    Set commnd = New ADODB.Command
    commnd.ActiveConnection = ConnectionObject
    commnd.CommandType = 4
    commnd.CommandTimeout = 180
    commnd.CommandText = "Insertvalues"
    
'@EMPID is the SP parameter name
'adInteger is the Type of @EMPID (Int)
'adParamInput is to say that this is input parameter
'4 is the length for the type
'var_EmpID is the varaible in my vb code which contains value to be passed
 
    commnd.Parameters.Append commnd.CreateParameter("@EmpID", adInteger, adParamInput, 4, var_EmpID)
    commnd.Parameters.Append commnd.CreateParameter("@Name", adVarChar, adParamInput, 50, var_Name)
    commnd.Parameters.Append commnd.CreateParameter("@FirstName", adVarChar, adParamInput, 100, var_FirstName)
    
    commnd.Execute

Open in new window

0
All Courses

From novice to tech pro — start learning today.