Unable to insert data using Parameters.AddWithValue

Question

0.00/5 (No votes)

See more:

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:

VB

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:

VB

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()

Posted 17-Mar-14 22:15pm

Luiey Ichigo

Add a Solution

2 solutions

Solution 1

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

VB

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

Posted 17-Mar-14 22:22pm

Aravindba

Updated 17-Mar-14 22:58pm

v3

Comments

Luiey Ichigo 18-Mar-14 4:29am

It goes for exception "Number of query values and destination fields are not the same."

Please look at my new code above see what I lack..thanks

Aravindba 18-Mar-14 4:32am

pls try to use break point and check how many fields you specify and how many values u pass in parameter.The error is appear bcz u specify no of fields not match with no of values pass in parameter

Luiey Ichigo 18-Mar-14 4:41am

I have many data for each strSQL..so I use 6 to see why this problem occur. But still same. I never do this before. Does the strSQL that I put is correct where the values should put question mark for each data that I want to insert?

Aravindba 18-Mar-14 4:56am

r u insert some values in this functions ? MDBBeginTrans() or MDBCommitTrans(),what purpose u use this 2 function ? just comment this 2 functon and try .and which line u get error ? and which time it show error,i mean first loop it work and second time it will show error(if i as 1) ?
this line intRec = command.ExecuteNonQuery() or any other place ?
or command.Dispose() Add this line and try,see my solution updated

Luiey Ichigo 18-Mar-14 5:22am

It error at command.ExecuteNonQuery at first loop before it dispose..yes I do insert statement to insert data store in arraylist to be filled into Ms Access database. The MDBBeginTrans(),MDBCommitTrans() is use to proceed to commit trans. Safe procedure so when the intRec = 0 i will rollback the insert transaction.

Aravindba 18-Mar-14 5:48am

ok for testing purpose u comment MDBBeginTrans(),MDBCommitTrans() and try to execute ,use breakpoint and get value in intRec and post here

Luiey Ichigo 18-Mar-14 21:14pm

Exception occur when ExecuteNonQuery. Syntax error "INSERT INTO" statement it said.

Public Function MDBInsertFromSQL() As Boolean
Dim strSQL As String
Dim cmd As New OleDbCommand
Dim intRec, icnt As Integer
Try
Dim connect As New OleDb.OleDbConnection
connect.ConnectionString = strcLocalProp.m_strLocalDBConnStr
For i = 0 To (arrDirInfo.Count - 1)
strSQL = "INSERT INTO tblMallDir(dirno,dirname,dirdetails,category,level,lotno) VALUES (@dirno,@dirname,@dirdetails,@category,@level,@lotno);"
Dim command As New System.Data.OleDb.OleDbCommand(strSQL, connect)
command.Connection = connect
connect.Open()
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)
command.ExecuteNonQuery() 'Exception here
command.Dispose()
connect.Close()
Next
Catch ex As Exception
strErrMsg = "Oops! Something is wrong with inserting data from server to local database"
MessageBox.Show(strErrMsg & vbCrLf & "Err: " & ex.Message)
MDBRollbackTrans()
Return False
Finally
If (Not IsNothing(cmd)) Then
cmd.Dispose()
cmd = Nothing
MDBCloseConnection()
End If
End Try
End Function

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Luiey Ichigo · Accepted Answer · 2014-03-18T18:06:00

I have remake my new code to this:

VB

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