How can I pass a parameter to a stored procedure(the parameter is used in a select query in the procedure). Then run the procedure and notify VB that the procedure completed so VB can do something else.
Printable View
How can I pass a parameter to a stored procedure(the parameter is used in a select query in the procedure). Then run the procedure and notify VB that the procedure completed so VB can do something else.
This is a synchronous call to a stored procedure, assuming your parameter is in a textbox (or put a variable where text1.text is) and VB will wait until the stored procedure has completed, and the results will be stored in recordset (RS).
If you want your code to do other things while waiting for completion of the stored procedure, let me know and I will post some code that does an asynchronous call to the db.
Code:dim rs as adodb.recordset
dim cn as adodb.connection
set cn = new connection
cn.open <connectionstringtodb>
set rs = cn.execute("sp_StoredProcName '" & text1.text & "'",,adcmdtext)
Yes, I do need my code to continue doing other things. I appreciate your knowledge and help.
OK
This is the code from a standard form and a single command button. It seems to be more trouble than it's worth (at least for small projects, but what the hell...)
Hope you find that usefulCode:Option Explicit
Dim WithEvents cn As ADODB.Connection
Dim WithEvents rs As ADODB.Recordset
Dim boolConnectCompleted As Boolean
Private Sub cn_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
boolConnectCompleted = True
End Sub
Private Sub cn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
MsgBox pRecordset.Fields(0).Value
End Sub
Private Sub Command1_Click()
cn.Open "Provider=SQLOLEDB.1;Data Source=TOM;UID=sa;Initial Catalog=Northwind", , , adAsyncConnect
Do Until boolConnectCompleted = True
DoEvents
Loop
cn.Execute "Select * from Customers", , adCmdText
End Sub
Private Sub Form_Load()
Set cn = New Connection
boolConnectCompleted = False
End Sub
Tom