How to solve sql statement in VB.net using ms access

Question

     If InvoiceNumberTextBox.Text <> "" And ClientNoTextBox.Text <> "" And FirstNameTextBox.Text <> "" And LastNameTextBox.Text <> "" And ProductNameTextBox.Text <> "" And ProductCodeTextBox.Text <> "" And ProductUnitTextBox.Text <> "" And QuantityTextBox.Text <> "" And ProductPriceTextBox.Text <> "" And TotalPriceTextBox.Text <> "" And Label1.Text <> "" And DateToDeliverDateTimePicker.Text <> "" And AddressTextBox.Text <> "" And ContactNoTextBox.Text <> "" Then
        Dim DT As String
        DT = Label1.Text
        cmdinsert.CommandText = "INSERT INTO Orders ([InvoiceNumber], [ClientNo], [FirstName], [LastName], [ProductName], [ProductCode], [Unit], [Quantity], [Price], [TotalPrice], [DateToday], [DateToDeliver], [Address], [ContactNo]) VALUES ('" & InvoiceNumberTextBox.Text & "', '" & ClientNoTextBox.Text & "', '" & FirstNameTextBox.Text & "', '" & LastNameTextBox.Text & "', '" & ProductNameTextBox.Text & "', '" & ProductCodeTextBox.Text & "', '" & ProductUnitTextBox.Text & "', '" & QuantityTextBox.Text & "', '" & ProductPriceTextBox.Text & "', '" & TotalPriceTextBox.Text & "', '" & Label1.Text & "', '" & DateToDeliverDateTimePicker.Text & "', '" & AddressTextBox.Text & "', '" & ContactNoTextBox.Text & "')"
        cmdinsert.CommandType = CommandType.Text
        cmdinsert.Connection = cnn
        cmdinsert.ExecuteNonQuery()
        MsgBox("Added")
        ProductNameTextBox.Clear()
        ProductPriceTextBox.Clear()
        FirstNameTextBox.Clear()
        ContactNoTextBox.Clear()
        TotalPriceTextBox.Clear()
        ProductUnitTextBox.Clear()
        LastNameTextBox.Clear()
        AddressTextBox.Clear()
        ClientNoTextBox.Clear()
        ProductCodeTextBox.Clear()
        QuantityTextBox.Clear()
        InvoiceNumberTextBox.Clear()
        If QuantityTextBox.Text <> "" Then
            cmdupdate.CommandText = "update Products set Quantity = '" & Label4.Text & "' where ProductCode = '" & ProductCodeTextBox.Text & "'"
            cmdupdate.CommandType = CommandType.Text
            cmd.Connection = cnn
            cmdupdate.ExecuteNonQuery()


        Else
            MsgBox("PLEASE CHECK YOUR FORM")

        End If
        cmdupdate.Dispose()
        Me.ProductsTableAdapter.Fill(Me.DatabaseCasanovaDataSet.Products)
        ProductsDataGridView.Refresh()
        ProductsDataGridView.Update()
    End If
        cmdinsert.Dispose()

Debug say is Mismatch expression but i check 3x on my database order by in orders table Anyone to check this if im wrong?

The Label1.text is a Date

Orders table list correctly

InvoiceNumber ClientNo FirstName LastName ProductName ProductCode Unit Quantity Price TotalPrice DateToday DateToDeliver Address ContactNo

Steve · Accepted Answer · 2014-03-09 15:17:12Z

1

You are passing 14 strings to your table. If any of your field in the table is not of type Text you risk the Data Type Mismatch. Really you should change your code to use a parameterized query and for each parameter pass the correct datatype.

For example, if the DateToday fields wants a datetime, you pass a datetime, not a string

cmdinsert.CommandText = "INSERT INTO Orders ([InvoiceNumber], [ClientNo], [FirstName], " & _
"[LastName], [ProductName], [ProductCode], [Unit], [Quantity], [Price], [TotalPrice], " & _
"[DateToday], [DateToDeliver], [Address], [ContactNo]) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
cmdInsert.Parameters.AddWithValue("@p1", InvoiceNumberTextBox.Text)
......
cmdInsert.Parameters.AddWithValue("@p11", Convert.ToDateTime(Label1.Text))
.....
cmdinsert.Connection = cnn
cmdinsert.ExecuteNonQuery()

In the example above the 11th parameter correspond to the DateToday field. As you can see, I have forced it to be a datetime thus the correct value is passed to the database. The same thing should be done for numerics (integer/decimal) but also for strings because a parameter avoids the problem of single quotes in textbox values.

edited Mar 9, 2014 at 15:17

answered Mar 9, 2014 at 15:08

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

KrissyNoriega Over a year ago

in the values is always ?. on paramaters on with that? what is @p1?

KekuSemau Over a year ago

Yes, you leave the ? unchanged, and add the parameters in the right order. Their names (like @p1) are virtually irrelevant and can be used just to keep the orientation up.

KrissyNoriega Over a year ago

Always miss datamatch :(

Steve Over a year ago

Sorry to be late to answer your comment. Did you solve the type mismatch?

Steve Over a year ago

As explained by @KekuSemau in OleDb parameters are not recognized by their name, but only by their position in the parameters collection. You specify the position of a parameter inserting a ? as placeholder. Then in the same exact order you add the parameters to the command collection. Of course the collection wants every element to be named and thus I have given a simple progressive name to the elements in the collection

Collectives™ on Stack Overflow

How to solve sql statement in VB.net using ms access

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related