使用存储过程在DB2数据库中插入数据 [英] Insert data in DB2 database using store procedure
问题描述
如何使用Vb6的存储过程在Db2数据库中插入数据
对于i = 1到lvwDatalist.ListItems.Count
如果修剪(lvwDatalist.ListItems(i).Text)=然后退出
使用oCmd
.Parameters.Refresh
.ActiveConnection = cnAP
.CommandText =({CALL DB2ADMIN.INSERT_MISYS_ACTSTAT(?,?,?,?,?,?,?,?,?)})
。 CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter(p_ACTTIT,adVarChar,adParamInputOutput,5000)
.Parameters.Append .CreateParameter (p_BRANCD,adVarChar,adParamInputOutput,200)
.Parameters.Append .CreateParameter(p_CUSTID,adVarChar,adParamInputOutput,50)
.Parameters.Append .CreateParameter (p_ACSUFIX,adVarChar,adParamInputOutput,100)
.Parameters.A ppend .CreateParameter(p_ACTNUM,adVarChar,adParamInputOutput,25)
.Parameters.Append .CreateParameter(p_BLOCKED,adVarChar,adParamInputOutput,25)
.Parameters。附加.CreateParameter(p_INACTIVE,adVarChar,adParamInputOutput,25)
.Parameters.Append .CreateParameter(p_CLOSED,adVarChar,adParamInputOutput,25)
.Parameters。附加.CreateParameter(p_CURRBAL,adDecimal,adParamInputOutput,5)
oCmd.Parameters(p_ACTTIT)。Value = Trim(lvwDatalist.ListItems(i)。文本)
oCmd.Parameters(p_BRANCD)。Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(1))
oCmd.Parameters(p_CUSTID ).Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(2))
oCmd.Parameters(p_ACSUFIX)。Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(3) )
oCmd.Parameters (p_ACTNUM)。Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(4))
oCmd.Parameters(p_BLOCKED)。Value = fixQuotes(lvwDatalist.ListItems(i)。 SubItems(5))
oCmd.Parameters(p_INACTIVE)。Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(6))
oCmd.Parameters( p_CLOSED)。Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(7))
''OCmd.Parameters(p_CURRBAL)。Value = IIf(fixQuotes(lvwDatalist.ListItems( i).SubItems(8))=,0,lvwDatalist.ListItems(i).SubItems(8))
oCmd.Parameters(p_CURRBAL)。Value = Val(lvwDatalist.ListItems (i).SubItems(8))
。执行
结束
我正在使用此代码并且它给我错误---
运行时错误2147217904(80040e10)
没有给出一个或多个必需参数的值。 />
这是我的SP中的DB2
How to insert data in Db2 database using store procedure with Vb6
For i = 1 To lvwDatalist.ListItems.Count
If Trim(lvwDatalist.ListItems(i).Text) = "" Then Exit For
With oCmd
.Parameters.Refresh
.ActiveConnection = cnAP
.CommandText = ("{CALL DB2ADMIN.INSERT_MISYS_ACTSTAT(?,?,?,?,?,?,?,?,?)}")
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("p_ACTTIT", adVarChar, adParamInputOutput, 5000)
.Parameters.Append .CreateParameter("p_BRANCD", adVarChar, adParamInputOutput, 200)
.Parameters.Append .CreateParameter("p_CUSTID", adVarChar, adParamInputOutput, 50)
.Parameters.Append .CreateParameter("p_ACSUFIX", adVarChar, adParamInputOutput, 100)
.Parameters.Append .CreateParameter("p_ACTNUM", adVarChar, adParamInputOutput, 25)
.Parameters.Append .CreateParameter("p_BLOCKED", adVarChar, adParamInputOutput, 25)
.Parameters.Append .CreateParameter("p_INACTIVE", adVarChar, adParamInputOutput, 25)
.Parameters.Append .CreateParameter("p_CLOSED", adVarChar, adParamInputOutput, 25)
.Parameters.Append .CreateParameter("p_CURRBAL", adDecimal, adParamInputOutput, 5)
oCmd.Parameters("p_ACTTIT").Value = Trim(lvwDatalist.ListItems(i).Text)
oCmd.Parameters("p_BRANCD").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(1))
oCmd.Parameters("p_CUSTID").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(2))
oCmd.Parameters("p_ACSUFIX").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(3))
oCmd.Parameters("p_ACTNUM").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(4))
oCmd.Parameters("p_BLOCKED").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(5))
oCmd.Parameters("p_INACTIVE").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(6))
oCmd.Parameters("p_CLOSED").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(7))
''oCmd.Parameters("p_CURRBAL").Value = IIf(fixQuotes(lvwDatalist.ListItems(i).SubItems(8)) = "", 0, lvwDatalist.ListItems(i).SubItems(8))
oCmd.Parameters("p_CURRBAL").Value = Val(lvwDatalist.ListItems(i).SubItems(8))
.Execute
End With
I am using this code and its give me error ---
Runtime Error 2147217904(80040e10)
No value given for one or more required parameters.
AND THIS IS MY SP IN DB2
CREATE PROCEDURE INSERT_MISYS_ACTSTAT (IN P_ACTTIT VARCHAR(5000),
IN P_BRANCD VARCHAR(50),
IN P_CUSTID VARCHAR(100),
IN P_ACSUFIX VARCHAR(3),
IN P_ACTNUM VARCHAR(25),
IN P_BLOCKED VARCHAR(1),
IN P_INACTIVE VARCHAR(1),
IN P_CLOSED VARCHAR(1),
IN P_CURRBAL DECIMAL(8,0) DEFAULT 0)
P1: BEGIN
INSERT INTO DB2ADMIN.MISYS_ACTSTAT (
ACTTIT,
BRANCD,
CUSTID,
ACSUFIX,
ACTNUM,
BLOCKED,
INACTIVE,
CLOSED,
CURRBAL)
VALUES (P_ACTTIT,
P_BRANCD,
P_CUSTID,
P_ACSUFIX,
P_ACTNUM,
P_BLOCKED,
P_INACTIVE,
P_CLOSED,
P_CURRBAL
);
END P1
推荐答案
检查你的表 DB2ADMIN.MISYS_ACTSTAT
,可能需要列。
Check your table DB2ADMIN.MISYS_ACTSTAT
, there might be required columns in that.
Private Sub cmdTransfer_DB2_Click(Index as Integer)
On错误GoTo Err_Des
Dim Cmd为新ADODB.Command
Dim Param为新ADODB.Parameter
Dim res作为整数
ProBar.Visible = True
ProBar.Min = 0
ProBar.Max = lvwDatalist .ListItems.Count
ProBar.Value = 0
lblCounter.Caption = 0
Me.MousePointer = vbHourglass
Dim i As Integer
For i = 1 to lvwDatalist.ListItems.Count
If Trim( lvwDatalist.ListItems(i).Text)=然后退出
with oCmd
.Parameters.Refresh
.Act iveConnection = cnAP
设置Cmd.ActiveConnection = cnAP
.CommandType = adCmdStoredProc
Cmd.CommandText ={CALL DB2admin.INSERT_MISYS_ACTSTAT(?, ?,?,?,?,?,?,?,?)}
设置Param = Cmd.CreateParameter(p_ACTTIT,adVarChar,adParamInput,500 ,修剪(lvwDatalist.ListItems(i).Text))
Cmd.Parameters.Append Param
设置Param = oCmd.CreateParameter( p_BRANCD,adVarChar,adParamInput,200,fixQuotes(lvwDatalist.ListItems(i).SubItems(1)))
Cmd.Parameters.Append Param
设置Param = oCmd.CreateParameter(p_CUSTID,adVarChar,adParamInput,100,fixQuotes(lvwDatalist.ListItems(i).SubItems(2)))
Cmd.Parameters.Append Param
设置Param = oCmd.CreateParameter(p_ACSUFIX,adVarChar,adParamIn put,50,fixQuotes(lvwDatalist.ListItems(i).SubItems(3)))
Cmd.Parameters.Append Param
Set Param = oCmd.CreateParameter(p_ACTNUM,adVarChar,adParamInput,50,fixQuotes(lvwDatalist.ListItems(i).SubItems(4)))
Cmd.Parameters.Append Param
设置Param = oCmd.CreateParameter(p_BLOCKED,adVarChar,adParamInput,10,fixQuotes(lvwDatalist.ListItems(i).SubItems(5)))
Cmd.Parameters.Append Param
设置Param = oCmd.CreateParameter(p_INACTIVE,adVarChar,adParamInput,10,fixQuotes(lvwDatalist.ListItems(i).SubItems (6)))
Cmd.Parameters.Append Param
设置Param = oCmd.CreateParameter(p_CLOSED,adVarChar,adParamInput, 10,fixQuotes(lvwDatalist.ListItems(i).SubItems(7)))
Cmd.Parameters.Append Para m
设置Param = oCmd.CreateParameter(p_CURRBAL,adBigInt,adParamInput,8,Val(lvwDatalist.ListItems(i).SubItems(8)))
Cmd.Parameters.Append Param
Cmd.Execute
结束
lblCounter.Caption = Val(lblCounter.Caption)+ 1
lnCounter = Val(lnCounter)+ 1
ProBar.Value = lnCounter
lblCounter.Refresh
Cmd.Parameters.Refresh
下一页我是
lvwDatalist .ListItems.Clear
lnCounter = 0
ProBar.Visible = False
Me.MousePointer = vbNormal
MsgBox (完成!)
Err_Des:
MsgBox(Err.Description)
Err.Clear
< br $>
End Sub
Private Sub cmdTransfer_DB2_Click(Index As Integer)
On Error GoTo Err_Des
Dim Cmd As New ADODB.Command
Dim Param As New ADODB.Parameter
Dim res As Integer
ProBar.Visible = True
ProBar.Min = 0
ProBar.Max = lvwDatalist.ListItems.Count
ProBar.Value = 0
lblCounter.Caption = 0
Me.MousePointer = vbHourglass
Dim i As Integer
For i = 1 To lvwDatalist.ListItems.Count
If Trim(lvwDatalist.ListItems(i).Text) = "" Then Exit For
With oCmd
.Parameters.Refresh
.ActiveConnection = cnAP
Set Cmd.ActiveConnection = cnAP
.CommandType = adCmdStoredProc
Cmd.CommandText = "{CALL DB2admin.INSERT_MISYS_ACTSTAT(?,?,?,?,?,?,?,?,?)}"
Set Param = Cmd.CreateParameter("p_ACTTIT", adVarChar, adParamInput, 500, Trim(lvwDatalist.ListItems(i).Text))
Cmd.Parameters.Append Param
Set Param = oCmd.CreateParameter("p_BRANCD", adVarChar, adParamInput, 200, fixQuotes(lvwDatalist.ListItems(i).SubItems(1)))
Cmd.Parameters.Append Param
Set Param = oCmd.CreateParameter("p_CUSTID", adVarChar, adParamInput, 100, fixQuotes(lvwDatalist.ListItems(i).SubItems(2)))
Cmd.Parameters.Append Param
Set Param = oCmd.CreateParameter("p_ACSUFIX", adVarChar, adParamInput, 50, fixQuotes(lvwDatalist.ListItems(i).SubItems(3)))
Cmd.Parameters.Append Param
Set Param = oCmd.CreateParameter("p_ACTNUM", adVarChar, adParamInput, 50, fixQuotes(lvwDatalist.ListItems(i).SubItems(4)))
Cmd.Parameters.Append Param
Set Param = oCmd.CreateParameter("p_BLOCKED", adVarChar, adParamInput, 10, fixQuotes(lvwDatalist.ListItems(i).SubItems(5)))
Cmd.Parameters.Append Param
Set Param = oCmd.CreateParameter("p_INACTIVE", adVarChar, adParamInput, 10, fixQuotes(lvwDatalist.ListItems(i).SubItems(6)))
Cmd.Parameters.Append Param
Set Param = oCmd.CreateParameter("p_CLOSED", adVarChar, adParamInput, 10, fixQuotes(lvwDatalist.ListItems(i).SubItems(7)))
Cmd.Parameters.Append Param
Set Param = oCmd.CreateParameter("p_CURRBAL", adBigInt, adParamInput, 8, Val(lvwDatalist.ListItems(i).SubItems(8)))
Cmd.Parameters.Append Param
Cmd.Execute
End With
lblCounter.Caption = Val(lblCounter.Caption) + 1
lnCounter = Val(lnCounter) + 1
ProBar.Value = lnCounter
lblCounter.Refresh
Cmd.Parameters.Refresh
Next i
lvwDatalist.ListItems.Clear
lnCounter = 0
ProBar.Visible = False
Me.MousePointer = vbNormal
MsgBox ("Done!")
Err_Des:
MsgBox (Err.Description)
Err.Clear
End Sub
这篇关于使用存储过程在DB2数据库中插入数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!