Number of query values and destination fields are not the same.

[vivekanandaan]

Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""d:\sprrg\\scm\scm\sprrg.mdb"";")
        Dim adapter As New OleDbDataAdapter("SELECT * FROM mr_head", connection)
        Dim dbInsert As New OleDb.OleDbCommand
        Dim table As New DataTable
        adapter.Fill(table)
        'job_code,mr_no,mr_date,item_code,qty,mr_status,mr_approved_date

        'Dim row As DataRow = table.Rows(0)
        connection.Open()
        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "job_code"
        dbInsert.Parameters.Item("job_code").Value = jobcode

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "mr_no"
        dbInsert.Parameters.Item("mr_no").Value = mr_no

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "mr_date"
        dbInsert.Parameters.Item("mr_date").Value = mr_date

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "item_code"
        dbInsert.Parameters.Item("item_code").Value = itemcode

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "qty"
        dbInsert.Parameters.Item("qty").Value = qty

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "status"
        dbInsert.Parameters.Item("status").Value = status

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "mr_approved_date"
        dbInsert.Parameters.Item("mr_approved_date").Value = auth_date


       dbInsert.CommandText = "insert into table1 values (job_code,mr_no,mr_date,itemcode,qty,status,auth_date);"

        dbInsert.CommandType = CommandType.Text
        dbInsert.Connection = connection
        dbInsert.ExecuteNonQuery()
        MsgBox("Saved")
        connection.Close()

When i execute this query i am getting the error as

Number of query values and destination fields are not the same.

Kindly help...

[hericles]

Your include a parameter called mr_approved_date but in your SQL you refer to auth_date. This probably has something to do with it

[vivekanandaan]

I tried after changing the same... But still problem exists ...

[hericles]

In that case can you confirm the number of columns in the database table matches the number of columns you are trying to insert? If they aren't the same but some columns have defaults set you can specifically name the columns in your SQL statement.

dbInsert.CommandText = "insert into table1 (col1_name, col2_name, col3_name...etc) values (job_code,mr_no,mr_date,itemcode,qty,status,auth_date);"

[vivekanandaan]

JOB_CODE TEXT
MR_NO TEXT
MR_DATE DATE
ITEM_CODE TEXT
QTY INTEGER
MR_STATUS TEXT
MR_APPROVED_DATE DATE

This is my table structure.

[vivekanandaan]

Now i am getting Data type mismatch in criteria expression.

Below is my new code ...

Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""d:\sprrg\scm\scm\sprrg.mdb"";")
        Dim adapter As New OleDbDataAdapter("SELECT * FROM table1", connection)
        Dim dbInsert As New OleDb.OleDbCommand
        Dim table As New DataTable
        adapter.Fill(table)
        'job_code,mr_no,mr_date,item_code,qty,mr_status,mr_approved_date

        'Dim row As DataRow = table.Rows(0)
        connection.Open()


        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "job_code"
        dbInsert.Parameters.Item("job_code").Value = jobcode

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "mr_no"
        dbInsert.Parameters.Item("mr_no").Value = mr_no

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "mr_date"
        dbInsert.Parameters.Item("mr_date").Value = mr_date

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "item_code"
        dbInsert.Parameters.Item("item_code").Value = itemcode

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "qty"
        dbInsert.Parameters.Item("qty").Value = qty

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "status"
        dbInsert.Parameters.Item("status").Value = status

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "mr_approved_date"
        dbInsert.Parameters.Item("mr_approved_date").Value = auth_date



        dbInsert.CommandText = "insert into mr_head (job_code,mr_no,mr_date,item_code,qty,mr_status,mr_approved_date) values (job_code,mr_no,mr_date,itemcode,qty,status,auth_date);"

        'dbInsert.CommandText = "insert into table1 (jobcode,mrno,mr_date,itemcode,qty,status,auth_date) values (job_code,mr_no,mr_date,itemcode,qty,status,auth_date);"

        dbInsert.CommandType = CommandType.Text
        dbInsert.Connection = connection
        dbInsert.ExecuteNonQuery()
        MsgBox("Saved")
        connection.Close()

Kindly advice ...

[hericles]

Make sure your value types match the column types in the database table. For example is mr_no a string or an int?

[vivekanandaan]

Actually it was a String. I changed to Integer also, even then same problem exists.

Below is my table structure :

JOB_CODE TEXT
MR_NO TEXT
MR_DATE DATE
ITEM_CODE TEXT
QTY INTEGER
MR_STATUS TEXT
MR_APPROVED_DATE DATE