无法使用Parameters.AddWithValue插入数据 [英] Unable to insert data using Parameters.AddWithValue
本文介绍了无法使用Parameters.AddWithValue插入数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我的代码有问题,我希望将数据从arraylist插入Ms Access数据库MDB,但连接关闭异常。下面我列出我以前的代码和我的新代码,我从互联网上获得教程,但似乎不行。
旧代码:
< span class =code-keyword> Dim strSQL As String
< span class =code-keyword> Dim cmd As 新 OleDbCommand
Dim intRec As 整数
如果(不 IsNothing(MDBcnDB))然后
如果 不(MDBcnDB.State = ConnectionState.Open)然后
MDBOpenConnection()
结束 如果
佛r i = 0 到(arrDirInfo.Count - 1 )
strSQL = INSERT into tblMallDir(dirno,dirname,dirdetails,category ,level,lotno,website,phoneno,dirinitial,imgpath,bMultilot)& _
VALUES('& arrDirInfo.Item(i).strShopNo& ','& arrDirInfo.Item(i).strShopName& ','& arrDirInfo.Item(i).strShopDetails& ',& _
'& arrDirInfo.Item(i).strCategory& ',' & arrDirInfo.Item(i).strLevel& ',' & arrDirInfo.Item(i).strLotNo& ','& arrDirInfo.Item (i).strWebsite& ','& arrDirInfo.Item(i).strPhoneNo& _
','& arrDirInfo.Item(i).strInit& ','& arrDirInfo.Item(i).strImgPath& ',& arrDirInfo.Item(i).bMultiLot& );
MDBBeginTrans()
cmd.CommandText = strSQL
cmd.CommandType = CommandType.Text
如果(cmd.ExecuteNonQuery> 0 ) 然后
MDBCommitTrans()
其他
MDBRollbackTrans()
结束 如果
下一步
结束 如果
cmd.Dispose()
cmd = Nothing
MDBCloseConnection()
新代码:
Dim connect As 新 OleDb.OleDbConnect ion
connect.ConnectionString = strcLocalProp.m_strLocalDBConnStr
对于 i = 0 To (arrDirInfo.Count - 1 )
strSQL = INSERT into tblMallDir VALUES(?,?,?,?,?,?);
MDBBeginTrans()
< span class =code-keyword> Dim 命令作为 新 System.Data.OleDb .OleDbCommand(strSQL,connect)
command.Parameters.AddWithValue( @ dirno, arrDirInfo.Item(i).strShopNo)
command.Parameters.AddWithValue( @ dirname,arrDirInfo.Item(i).strShopName)
command.Parameters.AddWithValue( @ dirdetails,arrDirInf o.Item(i).strShopDetails)
command.Parameters.AddWithValue( @ category,arrDirInfo.Item(i).strCategory)
command.Parameters.AddWithValue( @ level,arrDirInfo.Item(i).strLevel)
command.Parameters.AddWithValue( @ lotno ,arrDirInfo.Item(i).strLotNo)
connect.Open()
intRec = command.ExecuteNonQuery()
MDBCommitTrans()
下一步
connect.Close()
解决方案
hai
尝试将connect.open移到For Loop.bcz之外,第一次打开它,如果第二次你尝试打开连接,但连接已经打开,所以尝试移出con.open和con.close表单对于循环。或尝试在MDBCommitTrans之后关闭con()
试试这样
Dim connect 作为 新 OleDb。 OleDbConnection
connect.ConnectionString = strcLocalProp.m_strLocalDBConnStr
connect.Open()
对于 i = 0 (arrDirInfo.Count - 1 )
strSQL = < span class =code-string> INSERT into tblMallDir VALUES(?,?,?,?,?,?);
MDBBeginTrans()
Dim 命令作为 新 System.Data.OleDb.OleDbCommand(strSQL,connect)
command.Parameters.AddWithValue( @dirno,arrDirInfo.Item(i).strShopNo)
command.Parameters.AddWithValue( @ dirname,arrDirInfo.Item(i).strShopName)
command.Parameters.AddWithValue( @ dirdetails,arrDirInfo.Item(i)。 strShopDetails)
command.Parameters.AddWithValue( @ category,arrDirInfo.Item(i ).strCategory)
command.Parameters.AddWithValue( @ level,arrDirInfo.Item (i).strLevel)
command.Parameters.AddWithValue( @ lotno,arrDirInfo .Item(i).strLotNo)
intRec = command.ExecuteNonQuery()
command.Dispose()' 添加此行并尝试
MDBCommitTrans()
下一步
connect.Close()
' 或
Dim connect 作为 新 OleDb.OleDbConnection
connect.ConnectionString = strcLocalProp.m_strLocalDBConnStr
对于 i = 0 到(arrDirInfo.Count - 1 )
connect.Open()
strSQL = INSERT into tblMallDir VALUES(?,?,?,?,?,?);
MDBBeginTrans()
Dim 命令 As New System.Data.OleDb.OleDbCommand(strSQL,connect)
command.Parameters.AddWithValue( @dirno,arrDirInfo.Item(i).strShopNo)
comman d.Parameters.AddWithValue( @ dirname,arrDirInfo.Item(i).strShopName)
command.Parameters.AddWithValue( @ dirdetails,arrDirInfo.Item(i).strShopDetails)
command.Parameters.AddWithValue( @ category,arrDirInfo.Item(i)。 strCategory)
command.Parameters.AddWithValue( @ level,arrDirInfo.Item(i ).strLevel)
command.Parameters.AddWithValue( @ lotno,arrDirInfo.Item (i).strLotNo)
intRec = command.ExecuteNonQuery()
command.Dispose()' 添加此行并尝试
MDBCommitTrans()
connect.Close()
下一步
我已经将我的新代码翻译成:
公开 功能 InsertToDBFromArraylist() As Boolean
Dim strSQL As String
Dim cmd 作为 新 OleDbCommand
Dim intRec,icnt As 整数
尝试
如果(cnDB。 State = ConnectionState.Closed)然后
OpenConnection()
结束 如果
对于 x = 0 To (arrDirInfo.Count - 1 )
strSQL = INSERT INTO [tblMallDir]([dirno],[dirname],[dirdetails],[category],[level],[lotno],[website],[phoneno],[ imgpath],[dirinitial])& _
VALUES('& arrDirInfo.Item(x).strShopNo& ','& arrDirInfo.Item(x).strShopName& ','& arrDirInfo.Item(x).strShopDetails& _
','& arrDirInfo.Item(x).strCategory& ','& arrDirInfo.Item(x).strLevel& ','& arrDirInfo.Item(x).strLotNo& ','& arrDirInfo.Item(x).strWebsite& ','& arrDirInfo.Item(x).strPhoneNo& _
','& arrDirInfo.Item(x).strInit& ','& arrDirInfo.Item(x).strImgPath& ');
cmd.CommandText = strSQL
cmd.CommandType = CommandType.Text
cmd.Connection = cnDB
cmd.Transaction = trDB
intRec = cmd.ExecuteNonQuery
icnt = icnt + intRec
下一步
如果 icnt = arrDirInfo.Count 那么
MessageBox.Show( 总数据插入:& icnt& & arrDirInfo.Count)
结束 如果
Catch ex As 例外
strErrMsg = 哎呀!将数据从arraylist插入本地数据库时出现问题
MessageBox.Show(strErrMsg& vbCrLf& Err:& ex.Message)
RollbackTrans()
返回 错误
最后
如果(不 IsNothing(cmd))然后
cmd.Dispose()
cmd = Nothing
CloseConnection()
结束 如果
结束 尝试
结束 功能
Hi all,
I have a problem with my code where I want to insert data from arraylist to Ms Access database MDB but exception of connection close. Below I list my previous code and my new code where I get the tutorial from internet but seems not ok.
Old Code:
Dim strSQL As String
Dim cmd As New OleDbCommand
Dim intRec As Integer
If (Not IsNothing(MDBcnDB)) Then
If Not (MDBcnDB.State = ConnectionState.Open) Then
MDBOpenConnection()
End If
For i = 0 To (arrDirInfo.Count - 1)
strSQL = "INSERT into tblMallDir(dirno,dirname,dirdetails,category,level,lotno,website,phoneno,dirinitial,imgpath,bMultilot) " & _
"VALUES ('" & arrDirInfo.Item(i).strShopNo & "','" & arrDirInfo.Item(i).strShopName & "','" & arrDirInfo.Item(i).strShopDetails & "'," & _
"'" & arrDirInfo.Item(i).strCategory & "','" & arrDirInfo.Item(i).strLevel & "','" & arrDirInfo.Item(i).strLotNo & "','" & arrDirInfo.Item(i).strWebsite & "','" & arrDirInfo.Item(i).strPhoneNo & _
"','" & arrDirInfo.Item(i).strInit & "','" & arrDirInfo.Item(i).strImgPath & "'," & arrDirInfo.Item(i).bMultiLot & ");"
MDBBeginTrans()
cmd.CommandText = strSQL
cmd.CommandType = CommandType.Text
If (cmd.ExecuteNonQuery > 0) Then
MDBCommitTrans()
Else
MDBRollbackTrans()
End If
Next
End If
cmd.Dispose()
cmd = Nothing
MDBCloseConnection()
New Code:
Dim connect As New OleDb.OleDbConnection
connect.ConnectionString = strcLocalProp.m_strLocalDBConnStr
For i = 0 To (arrDirInfo.Count - 1)
strSQL = "INSERT into tblMallDir VALUES (?,?,?,?,?,?);"
MDBBeginTrans()
Dim command As New System.Data.OleDb.OleDbCommand(strSQL, connect)
command.Parameters.AddWithValue("@dirno", arrDirInfo.Item(i).strShopNo)
command.Parameters.AddWithValue("@dirname", arrDirInfo.Item(i).strShopName)
command.Parameters.AddWithValue("@dirdetails", arrDirInfo.Item(i).strShopDetails)
command.Parameters.AddWithValue("@category", arrDirInfo.Item(i).strCategory)
command.Parameters.AddWithValue("@level", arrDirInfo.Item(i).strLevel)
command.Parameters.AddWithValue("@lotno", arrDirInfo.Item(i).strLotNo)
connect.Open()
intRec = command.ExecuteNonQuery()
MDBCommitTrans()
Next
connect.Close()
解决方案
hai
Try to move connect.open outside of For Loop.bcz first time it will open and if goes second time u try to open connection,but connection already open,so try to move out of con.open and con.close form For Loop. or try to close con after MDBCommitTrans()
Try like this
Dim connect As New OleDb.OleDbConnection connect.ConnectionString = strcLocalProp.m_strLocalDBConnStr connect.Open() For i = 0 To (arrDirInfo.Count - 1) strSQL = "INSERT into tblMallDir VALUES (?,?,?,?,?,?);" MDBBeginTrans() Dim command As New System.Data.OleDb.OleDbCommand(strSQL, connect) command.Parameters.AddWithValue("@dirno", arrDirInfo.Item(i).strShopNo) command.Parameters.AddWithValue("@dirname", arrDirInfo.Item(i).strShopName) command.Parameters.AddWithValue("@dirdetails", arrDirInfo.Item(i).strShopDetails) command.Parameters.AddWithValue("@category", arrDirInfo.Item(i).strCategory) command.Parameters.AddWithValue("@level", arrDirInfo.Item(i).strLevel) command.Parameters.AddWithValue("@lotno", arrDirInfo.Item(i).strLotNo) intRec = command.ExecuteNonQuery() command.Dispose()'Add this line and try MDBCommitTrans() Next connect.Close() 'OR Dim connect As New OleDb.OleDbConnection connect.ConnectionString = strcLocalProp.m_strLocalDBConnStr For i = 0 To (arrDirInfo.Count - 1) connect.Open() strSQL = "INSERT into tblMallDir VALUES (?,?,?,?,?,?);" MDBBeginTrans() Dim command As New System.Data.OleDb.OleDbCommand(strSQL, connect) command.Parameters.AddWithValue("@dirno", arrDirInfo.Item(i).strShopNo) command.Parameters.AddWithValue("@dirname", arrDirInfo.Item(i).strShopName) command.Parameters.AddWithValue("@dirdetails", arrDirInfo.Item(i).strShopDetails) command.Parameters.AddWithValue("@category", arrDirInfo.Item(i).strCategory) command.Parameters.AddWithValue("@level", arrDirInfo.Item(i).strLevel) command.Parameters.AddWithValue("@lotno", arrDirInfo.Item(i).strLotNo) intRec = command.ExecuteNonQuery() command.Dispose()'Add this line and try MDBCommitTrans() connect.Close() Next
I have remake my new code to this:
Public Function InsertToDBFromArraylist() As Boolean Dim strSQL As String Dim cmd As New OleDbCommand Dim intRec, icnt As Integer Try If (cnDB.State = ConnectionState.Closed) Then OpenConnection() End If For x = 0 To (arrDirInfo.Count - 1) strSQL = "INSERT INTO [tblMallDir] ([dirno],[dirname],[dirdetails],[category],[level],[lotno],[website],[phoneno],[imgpath],[dirinitial]) " & _ " VALUES ('" & arrDirInfo.Item(x).strShopNo & "','" & arrDirInfo.Item(x).strShopName & "','" & arrDirInfo.Item(x).strShopDetails & _ "','" & arrDirInfo.Item(x).strCategory & "','" & arrDirInfo.Item(x).strLevel & "','" & arrDirInfo.Item(x).strLotNo & "','" & arrDirInfo.Item(x).strWebsite & "','" & arrDirInfo.Item(x).strPhoneNo & _ "','" & arrDirInfo.Item(x).strInit & "','" & arrDirInfo.Item(x).strImgPath & "');" cmd.CommandText = strSQL cmd.CommandType = CommandType.Text cmd.Connection = cnDB cmd.Transaction = trDB intRec = cmd.ExecuteNonQuery icnt = icnt + intRec Next If icnt = arrDirInfo.Count Then MessageBox.Show("Total data inserts : " & icnt & " of " & arrDirInfo.Count) End If Catch ex As Exception strErrMsg = "Oops! Something is wrong with inserting data from arraylist to local database" MessageBox.Show(strErrMsg & vbCrLf & "Err: " & ex.Message) RollbackTrans() Return False Finally If (Not IsNothing(cmd)) Then cmd.Dispose() cmd = Nothing CloseConnection() End If End Try End Function
这篇关于无法使用Parameters.AddWithValue插入数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文