Stored Procedures and VB6

[smitty]

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.

[Clunietp]

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.


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)

[smitty]

Yes, I do need my code to continue doing other things. I appreciate your knowledge and help.

[Clunietp]

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...)


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


Hope you find that useful

Tom