Several methods of accessing stored procedures in VB

Stored Procedures | access

what is the benefit of using SQL stored procedures The SQL stored procedure executes much faster than the SQL command text. When an SQL statement is contained in a stored procedure, the server does not have to parse and compile it every time it executes. Calling a stored procedure can be considered a three-tier structure. This makes your program easy to maintain. If the program needs to make some changes, you can use the power of Transact-SQL in the stored procedure as long as you change the stored procedure. An SQL stored procedure can contain multiple SQL statements. You can use variables and conditions. This means that you can use stored procedures to create very complex queries that update the database in a very complex way. Finally, this is perhaps the most important, and parameters can be used in stored procedures. You can send and return parameters. You can also get a return value (from the SQL returns statement). Environment: winxp+vb6+sp6+sqlserver2000

Database: Test table: Users

CREATE TABLE [dbo]. [Users] ([ID] [int] IDENTITY (1, 1) not NULL, [Truename] [char] (a) COLLATE chinese_prc_ci_as NULL, [regname ] [Char] (a) COLLATE chinese_prc_ci_as null, [PWD] [char] (a) COLLATE chinese_prc_ci_as null, [sex] [char] (a) COLLATE chinese_prc_ci_as null, [email] [text] COLLATE chinese_prc_ci_as null, [Jifen] [decimal] (18, 2) NULL) on [PRIMARY] textimage_on [Primary]go

ALTER TABLE [dbo]. [Users] With NOCHECK ADD CONSTRAINT [pk_users] PRIMARY KEY CLUSTERED ([id]) in [PRIMARY] Go

Stored procedure select_userscreate PROCEDURE select_users @regname char (), @numrows int Outputas select * from Users

SELECT @numrows = @ @ROWCOUNT

If @numrows = 0 return 0 else return 1GO

Stored procedure insert_userscreate PROCEDURE insert_users @truename char (@regname char (), @pwd char (), @sex char (), @email char (@jifen decimal (19,2) Asinsert into users (Truename,regname,pwd,sex,email,jifen) values (@truename, @regname, @ PWD, @sex, @email, @jifen) go

In the VB environment, add the DataGrid control, 4 buttons, 6 text box code easy to understand.

' References Microsoft Active data Object 2.X libraryoption Explicitdim mconn as ADODB. ConnectionDim rs1 as ADODB. RecordsetDim rs2 as ADODB. RecordsetDim Rs3 as ADODB. RecordsetDim RS4 as ADODB. Recordset

Dim cmd as Adodb.commanddim param as ADODB. Parameter

' Here use the first method to add data using stored procedures private Sub Command1_Click ()         Set cmd = New ADODB. command    Set rs1 = New ADODB. recordset    cmd. ActiveConnection = mconn    cmd. CommandText = "insert_users"     cmd. CommandType = adcmdstoredproc        Set param = cmd. CreateParameter ("Truename", Adchar, adParamInput, Trim (txttruename). Text)     cmd. Parameters.Append param    Set param = cmd. CreateParameter ("Regname", Adchar, adParamInput, Trim (txtregname). Text)     cmd. Parameters.Append param    Set param = cmd. CreateParameter ("pwd", Adchar, adParamInput, Trim (TXTPWD). Text)     cmd. Parameters.Append param    Set param = cmd. CreateParameter ("Sex", Adchar, adParamInput, Trim (txtsex). Text)     cmd. Parameters.Append param    Set param = cmd. CreateParameter ("Email",Adchar, adParamInput, Trim (txtemail. Text) cmd. Parameters.Append param ' The following types need to be aware that if adsingle is not used, an error with an invalid precision     Set param = cmd will occur. CreateParameter ("Jifen", Adsingle, adParamInput, Val (Txtjifen). Text)     cmd. Parameters.Append param    Set rs1 = cmd. execute        Set cmd = nothing    set rs1 = nothing    End Sub

' Here the second method uses stored procedures to add data private Sub Command2_Click ()     Set rs2 = New ADODB. recordset    Set cmd = New ADODB. command    cmd. ActiveConnection = mconn    cmd. CommandText = "insert_users"     cmd. CommandType = adcmdstoredproc        cmd. Parameters ("@truename") = Trim (txttruename. Text)     cmd. Parameters ("@regname") = Trim (txtregname. Text)     cmd. Parameters ("@pwd") = Trim (txtpwd. Text)     cmd. Parameters ("@sex") = Trim (txtsex. Text)     cmd. Parameters ("@email") = Trim (txtemail. Text)     cmd. Parameters ("@jifen") = Val (Txtjifen. Text)      Set rs2 = cmd. execute        Set cmd = nothing    set rs1 = NothingEnd Sub

' Here's a third way to use a Connection object to insert data private Sub Command4_click ()  dim strSQL as String strsql = "insert_users " & Trim (Txttruename. Text) & "', '" & Trim (Txtregname. Text) & "', '" & Trim (txtpwd. Text) & "', '" & Trim (Txtsex. Text) & "', '" & Trim (Txtemail. Text) & "', '" & Val (Txtjifen. Text) & "'"  set rs3 = New ADODB. Recordset set Rs3 = Mconn.execute (strSQL)   set rs3 = NothingEnd Sub

' Use stored procedures to display data ' to handle multiple parameters, input parameters, output parameters, and a direct return value  private Sub command3_click ()     Set RS4 = New ADODB. recordset    Set cmd = New ADODB. command    cmd. ActiveConnection = mconn    cmd. CommandText = "select_users"     cmd. CommandType = adcmdstoredproc        ' return value     Set param = cmd. CreateParameter ("RetVal", Adinteger, adParamReturnValue, 4)     cmd. Parameters.Append param    ' input parameters     Set param = cmd. CreateParameter ("Regname", Adchar, adParamInput, Trim (txtregname). Text)     cmd. Parameters.Append param    ' output parameter     Set param = cmd. CreateParameter ("NumRows", Adinteger, adparamoutput)     cmd. Parameters.Append param            Set rs4 = cmd. Execute ()     If cmd. Parameters ("RetVal"). Value = 1 then        MsgBox cmd. Parameters ("NumRows"). value    else        MsgBox "no record"     end if         MsgBox RS4. recordcount    Set DataGrid1.DataSource = rs4    Datagrid1.refresh

End Sub

' Connection database private Sub Form_Load () Set mconn = New Connection mconn.connectionstring = ' provider=sqloledb.1; Persist Security Info=false; User id=sa;initial catalog=test;data Source=yang "mconn.cursorlocation = adUseClient" set to client Mconn.openend Sub ' Close data connection Connect private Sub Form_Unload (Cancel as Integer) mconn.close Set mconn = NothingEnd Sub