October 31, 2002 at 11:38 pm
I'm a novice in VB and SQL Server and I'm trying to call a SQL Stored Procedure from VB. I've tried using:
Call sql(c1, "Exec StoredProcedureName")
Do I even need a cursor if the Stored Procedure can execute on its own without any parameters? Thanks.
November 1, 2002 at 4:24 am
Not sure what you are doing with the cursor. May be logical in the procedure, but if it is to iterate thru a recordset for scrolling purposes it is better to move the cursor out of the SP and into the logic of the VB app thru a server side or client side cursor. Now as for calling in VB there are multiple ways. The quickest is to use you SQL connectiion object and do like so (this example is not going into a recordset just call SP)
SQLConn.Execute ("ip_StoredProc @param=1, @param2='janie'")
But if you want to implement a clean interface to the SP instead use this sort of fomat
' Our SQL SOnnection object
Public SQLConn As New ADODB.Connection
' This would all be in a function or sub depending on what you need to do.
Dim App As String, Msg As String, Hid As Integer
Dim Pin As String, Prov As String ' Items value storage.
' Verify our SQL connection, if closed then open else continue.
If SQLConn.State = adStateClosed Then SQLConn.Open "Provider=SQLOLEDB.1;Initial Catalog=DatabaseName;APP=ApplicationName;Data Source=ServerNameOrIP", "useraccount", "password"
' Create out command object
Dim SQLCmd As New ADODB.Command
With SQLCmd
' Set our command objects base information.
.ActiveConnection = SQLConn
.CommandText = "ip_MyProc"
.CommandType = adCmdStoredProc
' Parameters are appended to the command object directly
' CreateParameter(NameForReferenceDoesntMatter, ExactDataType, InputOutput, Size, Value)
' Last item is data to submit, also must apply each parameter in order as in the procedure.
.Parameters.Append .CreateParameter("PIN", adVarChar, adParamInput, 50, Pin)
.Parameters.Append .CreateParameter("APP", adVarChar, adParamInput, 50, App)
.Parameters.Append .CreateParameter("MSSG", adVarChar, adParamInput, 8000, Msg)
.Parameters.Append .CreateParameter("HID", adBoolean, adParamInput, , Hid)
.Parameters.Append .CreateParameter("PROV", adChar, adParamInput, 4, Prov)
' Execute our Query but I do not expect a return value.
.Execute , , adExecuteNoRecords
End With
All of the information about each collection, object and method can be ound by going out to http://msdn.microsoft.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply