string commandText = string.Format(@/"UPDATE nf_ourself SET namemoniker=:namemoniker,descriptionhtml=:descriptionhtml, descriptiontext=:descriptiontext,photopath=:photopath,xuhao=:xuhao WHERE nameid=’{0}’/", usPersonnel.NameId); OracleParameter[] parameters = { new OracleParameter(/":namemoniker/", usPersonnel.NameMoniker), new OracleParameter(/":descriptionhtml/", usPersonnel.HtmlContent), new OracleParameter(/":descriptiontext/", usPersonnel.TextContent), new OracleParameter(/":photopath/", usPersonnel.PhotoPath), new OracleParameter(/":xuhao/", usPersonnel.XuHao) }; return Client.ExecuteNonQuery(CommandType.Text, commandText, parameters); 注意主键如果不做提交就不能放到OracleParameter[]而是另外做变量传入。 |
查询查询可以有两种方法,一种是直接在ACCESS查询对象建立参数查询,一种是在SQL语句中用PARAMETERS建立参数查询。下面是个示例:
表名:tb
字段:iNnae(文本)、qty(数字)
在ACCESS查询对象中建立一个名为 tb_in的参数查询:
INSERT INTO tb ( iName, qty )
VALUES ([@1], [@2]);
新建一个工程,上面二个textbox,一个command
Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim sql As String
Dim str As String
Dim StrConnect As String
StrConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & App.Path & "/db1.mdb;Persist " _
& "Security Info=False;Jet OLEDB:Database Password="
Set cn = New ADODB.Connection
cn.Open StrConnect
'执行 tb_in 插入数据
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "tb_in"
cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("@1", adChar, adParamInput, 20, txtCode.Text)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@2", adBigInt, adParamInput, 4, txtName.Text)
cmd.Parameters.Append param
Set rs = New ADODB.Recordset
Set rs = cmd.Execute
Set cmd = Nothing
Set rs = Nothing
'用PARAMETERS指定参数,查询上述代码插入的数据
str = "PARAMETERS @1 text,@2 int;"
sql = str & "SELECT * FROM tb WHERE iName=@1 And qty=@2"
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = sql
cmd.CommandType = adCmdText
Set param = cmd.CreateParameter("@1", adChar, adParamInput, 20, txtCode.Text)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@2", adBigInt, adParamInput, 4, txtName.Text)
cmd.Parameters.Append param
Set rs = New ADODB.Recordset
Set rs = cmd.Execute
'Debug.Print rs.Fields(0) & " / " & rs.Fields(1)
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing
End If