Recent Posts

Thursday, March 13, 2008

To execute stored Procedure from vb 6.0

This article provides the code and detail information on how to insert data in the sql server database from visual basic 6.0.
The code provided below helps to execute stored procedure written in sql server database.

'To update the data in the database

Dim cmd as ADODB.Command

Dim res As Integer

Set cmd = New ADODB.Command
cmd.ActiveConnection = con ' use your active connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "update_empdetails" ' This is the name of stored Procedure

cmd.Parameters.Append cmd.CreateParameter("empid", adVarChar, adParamInput, 6,txt_empid.Text)
cmd.Parameters.Append cmd.CreateParameter("firstname", adVarChar, adParamInput, 30, txt_firstname.Text)
cmd.Parameters.Append cmd.CreateParameter("title", adVarChar, adParamInput, 30, txt_title.Text)
cmd.Parameters.Append cmd.CreateParameter("address", adVarChar, adParamInput, 100, txt_address.Text)
cmd.Parameters.Append cmd.CreateParameter("result", adInteger, adParamOutput)
cmd.Execute

res = cmd("result")

If (res = 1) Then
MsgBox "Updated Successfully"
End If

Set cmd.ActiveConnection = Nothing

' To retrive data from database

Private Sub cmd_get_Click()

str_empid = txt_empid.Text

Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "empdetails"

cmd.Parameters.Append cmd.CreateParameter("empid", adVarChar, adParamInput, 6, str_empid)

Set rs = cmd.Execute
' if you want use rs.recordcount you can use below code
' or rs.Open cmd, con, adOpenStatic

If Not rs.EOF Then
txt_firstname = rs.Fields(0)
txt_title = rs.Fields(1)
txt_address = rs.Fields(2)
End If

Set cmd.ActiveConnection = Nothing

End Sub

Related Posts by Categories




No comments:

Post a Comment