Crear Stored Procedure desde Visual Basic 6
Quisiera que me ayuden por favor, quiero saber si saben o si s epuede crear un store procedure de sql server 2000 pero desde el codigo de visual basic 6, si lo saben les agradeceria que me den una mano con el codigo de vb...
1 respuesta
Respuesta de dayvitt
1
Bien,
Aqui tienes un ejemplo. Debes usar un objeto ADO (en concreto ADODB.Command.)
En el ejemplo se hace una conexión, se crea parámetros para el store procedure y finalmente lo ejecuta con .execute.
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
'--------------------------------------------------------------------
'--- Open the connection to the pubs database
'--------------------------------------------------------------------
Set objConn = New ADODB.Connection
objConn.Provider = "SQLOLEDB"
objConn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=pubs;Data Source=papanotebook;User ID=sa;Password="
objConn.Open
'--------------------------------------------------------------------
'--- Set the command object up to run the stored procedure
'--------------------------------------------------------------------
Set objCmd = New ADODB.Command
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "prUpdateAuthor"
Set objCmd.ActiveConnection = objConn
'--------------------------------------------------------------------
'--- Create and add the parameters
'--------------------------------------------------------------------
ObjCmd. Parameters. Append objCmd. CreateParameter("@chvFirstName", adVarChar,
adParamInput, 20, "John")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chvLastName", adVarChar,
adParamInput, 40, "White")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chrPhone", adChar, adParamInput,
12, "408 496-7223")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chvAddress", adVarChar,
adParamInput, 40, "10932 Bigge Rd.")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chvCity", adVarChar,
adParamInput, 20, "Menlo Park")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chrState", adChar, adParamInput,
2, "CA")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chrZip", adChar, adParamInput,
5, "94025")
ObjCmd. Parameters. Append objCmd. CreateParameter("@bitContract", adBoolean,
adParamInput,, 1)
ObjCmd. Parameters. Append objCmd. CreateParameter("@chrID", adChar, adParamInput,
1, "172-32-1176")
'--------------------------------------------------------------------
'--- Execute the stored procedure
'--------------------------------------------------------------------
ObjCmd. Execute
ObjConn. Close
Set objCmd = Nothing
Set objConn = Nothing
'--------------------------------------------------------------------
Aqui tienes un ejemplo. Debes usar un objeto ADO (en concreto ADODB.Command.)
En el ejemplo se hace una conexión, se crea parámetros para el store procedure y finalmente lo ejecuta con .execute.
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
'--------------------------------------------------------------------
'--- Open the connection to the pubs database
'--------------------------------------------------------------------
Set objConn = New ADODB.Connection
objConn.Provider = "SQLOLEDB"
objConn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=pubs;Data Source=papanotebook;User ID=sa;Password="
objConn.Open
'--------------------------------------------------------------------
'--- Set the command object up to run the stored procedure
'--------------------------------------------------------------------
Set objCmd = New ADODB.Command
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "prUpdateAuthor"
Set objCmd.ActiveConnection = objConn
'--------------------------------------------------------------------
'--- Create and add the parameters
'--------------------------------------------------------------------
ObjCmd. Parameters. Append objCmd. CreateParameter("@chvFirstName", adVarChar,
adParamInput, 20, "John")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chvLastName", adVarChar,
adParamInput, 40, "White")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chrPhone", adChar, adParamInput,
12, "408 496-7223")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chvAddress", adVarChar,
adParamInput, 40, "10932 Bigge Rd.")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chvCity", adVarChar,
adParamInput, 20, "Menlo Park")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chrState", adChar, adParamInput,
2, "CA")
ObjCmd. Parameters. Append objCmd. CreateParameter("@chrZip", adChar, adParamInput,
5, "94025")
ObjCmd. Parameters. Append objCmd. CreateParameter("@bitContract", adBoolean,
adParamInput,, 1)
ObjCmd. Parameters. Append objCmd. CreateParameter("@chrID", adChar, adParamInput,
1, "172-32-1176")
'--------------------------------------------------------------------
'--- Execute the stored procedure
'--------------------------------------------------------------------
ObjCmd. Execute
ObjConn. Close
Set objCmd = Nothing
Set objConn = Nothing
'--------------------------------------------------------------------
Gracias por la respuesta, voy a validarlo bien el codigo en mi proyecto... GRACIAS una ultima pregunta si quisiera ejecutarlo con sql 2005 seria lo mismo...
