0

Good day to everybody. I wrote some code in vb.net to use a form to add a row into an Excel file (xls). Two textboxes are in the form. The two columns in the excel file are formatted as text. If I input any number into the textboxes everything works ok. If I input numbers and characters or just characters I got the "Data Type Mismatch in Criteria Expression" error on ExecuteNonQuery. I already tried many different ways to go around of it but without any positive result. I need to fix it because I'll add some other textboxes to input text (text and numbers, not just numbers). Please anybody can help me? Here is my code. Thanks in advance for any suggestion.

    Public Class Edit1

    Public con As New OleDb.OleDbConnection
    Public dbProvider As String
    Public dbSource As String
    Public ds As New DataSet
    Public da As OleDb.OleDbDataAdapter
    Public sql As String

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        dbProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
        dbSource = "Data Source = c:\test1.xls"
        con.ConnectionString = dbProvider & dbSource
        con.Open()
        sql = "SELECT * FROM [dbdata$]"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "table1")
        con.Close()
    End Sub

    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click

        Dim cmd As New OleDbCommand
        Dim str1 As String
        Dim str2 As String
        cmd.CommandType = CommandType.Text
        str1 = TextBox1.Text
        str2 = TextBox2.Text
        cmd.CommandText = "INSERT INTO [dbdata$] (item1,item2) VALUES (str1,str2);"
        cmd.Parameters.AddWithValue("@item1", str1)
        cmd.Parameters.AddWithValue("@item2", str2)
        'cmd.Parameters.Add("@item1", TextBox1.Text) 'this generate the same ExecuteNonQuery mismatch error
        'cmd.Parameters.Add("@item2", TextBox2.Text) 'this generate the same ExecuteNonQuery mismatch error
        'cmd.Parameters("@item1").Value = TextBox1.Text 'this generate the same ExecuteNonQuery mismatch error
        'cmd.Parameters("@item2").Value = TextBox2.Text 'this generate the same ExecuteNonQuery mismatch error
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()


    End Sub
End Class

UPDATE - Okay guys, I worked on this issue. What I did is clean up the Excel worksheet, deleting all formatted and filled cells, leaving just the 1st row with column names. Now it works, but there's still a small problem. It looks like ExecuteNonQuery checks the last-filled cell to identify the cell format to be used on the next one to be filled. So, since the first row has "item1" and "item2" (without quotes) as column names, the code writes numbers on column 1 with a single quote (i.e., if the textbox is 23, in the cell will be '23.

But if I manually write at least one number (in the 2nd row) then the code works fine. Any idea to avoid this behavior in order to write in the cell regardless of what type of data I write in the textbox? Thanks in advance for your help.

Brad Larson
  • 170,088
  • 45
  • 397
  • 571
LukeLuke
  • 71
  • 1
  • 8

1 Answers1

0

I ran into a similar problem recently. It ended up being the formatting of the cells. I kept getting type mismatch even though I changed the entire column setting. The only way I got it to finally work was to copy everything to another sheet in the workbook with everything formatted to text. I then deleted the first sheet and renamed the new one to the old name. It worked fine after that.

Other wise, just insert new columns beside the old one, format it to text, and then copy and PASTE_SPECIAL, then choose values only. That will get rid of the arrows in the upper left part of each cell.

Either fix works fine for me for adding, search, updating, etc.

Hope this helps,

Beosma
  • 1
  • 2