Several methods of accessing the stored procedures in VB
Time: 2004-8-10 Author: Unknown [obtain this document the absence of records, deeply sorry, this document is reproduced in full]
What is the benefit of using the SQL stored procedure?
■ The SQL stored procedure is much better than SQL command text. When a SQL statement is included in the stored procedure, the server does not have to analyze and compile it every time it executes it. ■ Call the stored procedure, it can be considered a three-layer structure. This makes your program easy to maintain. If the program needs to do some changes, you only need to change the stored procedure ■ You can use Transact-SQL's powerful features during storage. A SQL stored procedure can contain multiple SQL statements. You can use variables and conditions. This means you can build a very complex query with a stored procedure to update the database in a very complex manner. ■ Finally, this may be the most important, and parameters can be used during the stored procedure. You can transfer and return parameters. You can also get a return value (from the SQL RETURN statement). Environment: WinXP VB6 SP6 SQLSERVER2000
Database: Test table: Users
Create Table [DBO]. [Users] ([Id] [INT] Identity (1, 1) NOT NULL, [TRUENAME] [CHAR] (10) Collate Chinese_PRC_CI_AS NULL, [RegName] [char] (10) Collate Chinese_PRC_CI_AS NULL , [Pwd] [10) collate chinese_prc_ci_as null, [sex] [char] (10) 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]) ON [primary] GO
Stored Procedure Select_Users Create Procedure SELECT_USERS @Regname Char (20), @numrows Int Output As SELECT * FROM USERS
SELECT @numrows = @@ RowCount
IF @numrows = 0 RETURN 0 else Return 1 Go
Storage Procedure Insert_Users Create Procedure Insert_Users @truengname Char (20), @Regname Char (20), @ sex char (20), @ Email Char (20), @ Jifen Decimal (19, 2) AS INSERT INTO USERS (TrueName, Regname, PWD, SEX, Email, Jifen) Values (@ Truename, @ regname, @ pwd, @ sex, @ email, @ jifen) Go
In the VB environment, add a DataGrid control, 4 buttons, and 6 text box code easy to understand.
'Reference microsoft active data object 2.X library Option Explicit Dim mConn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim rs2 As ADODB.Recordset Dim rs3 As ADODB.Recordset Dim rs4 As ADODB.RecordsetDim cmd As ADODB.Command Dim param As AdoDb.Parameter
'Here to add data 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 stored procedure using the first method = cmd.CreateParameter ( "truename", adChar, adParamInput, 20, Trim (txttruename.Text)) cmd.Parameters.Append param Set param = cmd.CreateParameter ( "regname", adChar, adParamInput, 20, Trim (txtregname.Text )) Cmd.parameters.append param set param = cmd.createParameter ("PWD", Adchar, Adparaminput, 20, TRIM (TXTPWD.TEXT)) cmd.parameters.Append param set param = cmd.createParameter ("sex", adchar , ADPARAMINPUT, 20, TRIM (TXTSEX.TEXT)) cmd.parameters.append param set param = cmd.createParameter ("email", Adchar, Adparaminput, 20, TXTEMAIL.TEXT) CMD.Parameters.Append PARAM ' Types Need to note that if you do not use Adsingle, a precision invalid error set param = cmd.createParameter ("JIFEN", Adsingle, AdParaminput, 50, Val (txtjifen.text) cmd.parameters .Append param set = cmd.execute set cmd = nothing set = Nothing end Sub
'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 stored procedure herein by the second method. Parameters ("@ trumename") = trim (txttrumename.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 = cmd.execute Set cmd = nothing set = nothing end sub 'This uses the connection object to insert the data private submmand4_click () DIM strsql as string strsql = "INSERT_USERS'" & TRIM (TXTTRUENAME.TEXT) & ", ' "& TRIM (TXTREGNAME.TEXT) &" ',' "" & TXTPWD.TEXT) & "'" & TRIM (TXTSEX.TEXT) & "" "& TRIM (TXTEMAIL.TEXT) &" , '"& Val (txtjifen.text) &"' "set = new adoDb.recordset set = mconn.execute (strsql) set = nothing end sub
'Using Storage Procedure Display Data' To handle multiple parameters, input parameters, output parameters, and a direct return value private submmand3_click () set = new adoDb.recordset set cmd = new adod.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 parameter Set param = cmd.CreateParameter ( "regname", adChar , adParamInput, 20, 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 rs 4.RecordCount set dataGrid1.DataSource = rs4 dataGrid1.refreshend Sub
'Connect to the 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 the client mconn.open end sub' Off Data Connection Private Sub Form_unload (Cancel As Integer) Mconn.close Set Mconn = Nothing End Sub
China .Net Club reproduced this article. Let us make progress together and share human technical resources. [www.chinaaspx.com]
