OleDb Exception : Data type mismatch in criteria expression

Question

I am using VB 2008 & MS Access Database to create a WinForm Application.

I have a primary key column set to AutoNumber in Database

I am able to save 1st Record correctly but while triying to Save second one this error occars. Reopening the form with same new data again saves it correctly but same thing again that you can not add another new one.

Here the Code

Private

Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

 

'Try

 

If Not cboFirmName.SelectedIndex = -1 AndAlso Not cboFirmName.SelectedValue = -1 AndAlso _

 

Not cboPartyName.SelectedIndex = -1 AndAlso Not cboPartyName.SelectedValue = -1 AndAlso _

IsNumeric(txtAmount.Text)

AndAlso Not txtAmount.Text < 100000 Then

 

'bsFirmInfo.CancelEdit()

 

'bsCreditors.CancelEdit()

taFormNo404Entries.Connection.Open()

 

Me.Validate()

 

Me.bsFormNo404Entries.EndEdit()

 

Dim AddedRows() As DataRow = dsRTGS.FormNo404Entries.Select("", "", DataViewRowState.Added)

 

Dim ChangedRows() As DataRow = dsRTGS.FormNo404Entries.Select("", "", DataViewRowState.ModifiedCurrent)

 

'Me.taFormNo404Entries.Update(Me.dsRTGS.FormNo404Entries)

 

Me.taFormNo404Entries.Update(AddedRows)

 

Me.taFormNo404Entries.Update(ChangedRows) 'EXCEPTION TROWING FOR THIS LINE OF CODE

 

Dim iNewId As Integer = taFormNo404Entries.LastNewId.ToString

 

Me.taFormNo404Entries.Fill(Me.dsRTGS.FormNo404Entries)

bsFormNo404Entries.Position = bsFormNo404Entries.Find(

"SrNo", iNewId)

Beep()

MDI.lblStatusLabel1.Text =

"RECORD ADDED / UPDATED"

TurnControlsToEnableDesable(

True)

 

Else

MDI.lblStatusLabel1.Text =

"PLEASE CHECK ALL FIELDS"

 

End If

 

'Catch EX As DataException

 

' MessageBox.Show(EX.Message, EX.GetType.ToString)

 

'Catch EX As Exception

 

' MessageBox.Show(EX.Message, EX.GetType.ToString)

 

'Finally

taFormNo404Entries.Connection.Close()

 

' End Try

 

End Sub

Answer 1

Hi Sandeep,

What error are you getting in the exception? Is it related to the primary key?

It maybe possible that you are providing a value for the autonumber column.

Answer 2

Hi Henry

The Error is - "Data type mismatch in criteria expression" occars while saving the record.

Also i am not providing any custom value to AutoNumber column infact i have kept the DataBound TextBox to ReadOnly also i told you that i am able to save 1st Entry.

SANDEEP

Answer 3

Here is the link for My Project

http://cid-ff10e426c5c28b48.office.live.com/self.aspx/.Public/Projects.rar

Change the Connection string in app.config file to the location of access file in you Computer & start Debugging

Goto Menu Entry --> Goods Outward

& try to add new data entry your 1st Entry will be saved without any error however with the second one you will get Cocurrency Error.

SANDEEP

Answer 4

I also have this issues in C# code:

My connectionString is :

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FileName]

;Mode=Share Deny Write;Extended Properties=\"HDR=YES\";Jet OLEDB:Engine Type=37";

using (OleDbConnection conn = new OleDbConnection(strCon.Replace("[FileName]", filename)))
            {
                OleDbCommand commond = conn.CreateCommand();
                conn.Open();
                foreach(CaseInfo cinfo in CaseList)
                {
                    commond.CommandText = "update [" + SheetName+ "$] set ExistBug='" + cinfo.BugInfo + "' where Test_ID = '" + cinfo.CaseId + "'";
                    commond.ExecuteNonQuery();
                }
                conn.Close();

            }

Who can give a help !