Click here to Skip to main content
15,914,111 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i am trying to update ms access database through vb.net but show datatype mismatch criteria expression........i tried primary key as autonumber... and number and text then all so error i changed & using + then also error why... "where billno=" primarykey as number .....

VB
Try
    Dim cmd As New OleDbCommand
    cmd.CommandText = "update Bill_Mst set Patient_no=" + ComboBox1.Text + ",dt=" + dtp1.Text + ",ward_charges=" + TextBox3.Text + ",No_of_day=" + TextBox4.Text + ",ward_amount=" + TextBox5.Text + ",Doctor_charges=" + TextBox6.Text + ",Xray_charges=" + TextBox7.Text + ",Net_amount=" + TextBox8.Text + " WHERE billNo=" + TextBox1.Text + ""
    cmd.Connection = c1
    c1.Open()
    cmd.ExecuteNonQuery()
    MsgBox("Record Update")
    Dim da As New OleDbDataAdapter("select * from Bill_Mst", c1)
    Dim dt As New DataTable
    da.Fill(dt)
    DataGridView1.DataSource = dt
    DataGridView1.Refresh()

Catch ex As Exception
    MsgBox(ex.Message)
End Try
Posted
Updated 25-Feb-12 11:07am
v2
Comments
Philippe Mori 25-Feb-12 17:12pm    
At least one thing is wrong with this coding. It is that it is not safe as it is open to SQL injection. You should never directly use user input in a query.

You should use OleDbParameter (OleDbParameter Class).

1 solution

Actually you are making the exact same mistakes I've seen someone make earlier today. Here is the question (and answers) I am referring to: how search record in c#[^]
Here is a (partial) copy of my answer:

[...] your code is flawed on many levels.
Take your query string, you simply build it and paste some user input in it. Well, suppose this user types something like ; --drop table student_info... Or worse! Even a simple and innocent input, like "D'Artagnan" will break your query. This is called SQL injection[^] and is very dangerous (and unfortunately very common).
So you need to parameterize your query. Luckily this is not hard. Consider the following code:
VB
Dim cmd As New SqlCommand("SELECT * FROM student_info WHERE no = @Number", DBConn)
cmd.Parameters.AddWithValue("@Number", CInt(TextBox12.Text))
da = New SqlDataAdapter(cmd)
'...
Notice how your code becomes better readable? Your query is now protected from SQL injection and "D'Artagnan" will not break it! What's more, SQL servers performance will increase when you use parameterized queries. It's a win win win situation!
However, this doesn't fix everything. Your TextBox12.Text is user input and thus must be validated. Perhaps the user didn't enter a valid Integer value.
So consider the following code, using Integer.TryParse[^]:
VB
Dim userInput As Integer
If Integer.TryParse(TextBox12.Text, userInput) Then
   cmd.Parameters.AddWithValue("@Number", userInput)
Else
   ' User did not type a valid numeric value.
   ' Possibly show them a MessageBox, whatever.
End If
So that brings us to the next point. Letting your users know if anything went wrong. You now catch an Exception and simply return like nothing happened. The user will wonder why they don't see their records... If you do NOT handle an Exception let it bubble up to the UI and at least show the user something has gone wrong there. Actually I have written an article on proper use of Try Catch blocks: Using Try... Catch..., Finally![^].
Another topic that article discusses is the Using block[^]. It's about cleaning up resources, which I don't see you do.
After you're done with your SqlCommand, your SqlConnection or your SqlDataAdapter you should properly dispose of them. The rule here is that when an Object Implements IDisposable[^] you should call Dispose[^] once you're done.

So now look at the completely revised code:
VB
' I am assuming DBConn is a class field and might be used elsewhere.
' I assume ds is a class field.
Try
   ds.Clear()
   ' Put your SqlCommand in a Using block.
   Using cmd As New SqlCommand("SELECT * FROM student_info WHERE no = @Number")
      ' Validate the users input.
      Dim userInput As Integer
      If Integer.TryParse(TextBox12.Text, userInput) Then
         ' The users input was valid.
         cmd.Parameters.AddWithValue("@Number", userInput)
         ' put your SqlDataAdapter in a Using block.
         ' Not sure if a SqlDataAdapter automatically opens a connection, so...
         DBConn.Open()
         Using da As New SqlDataAdapter(cmd)
            da.Fill(ds, "student_info")
            Bind()
         End Using
      Else
         ' The users input was invalid.
         MessageBox.Show("Please enter a valid numeric value")
      End If
   End Using
Catch ex As Exception
   ' Perhaps log the Exception.
   MessageBox.Show(String.Format("An exception has occurred:{0}{1}", Environment.Newline, ex.Message))
Finally
   ' Closing a closed connection is not a problem.
   DBConn.Close
   ' Don't dispose the DBConn yet if it is used elsewhere.
End Try
This code could be a lot more elegant. But this is a good start :)

Perhaps if you apply these practices your error will disappear or at least you will be better able to locate it.
I think you can apply this example to your own application. You can simply replace Sql in my example with OleDb and it will still work. Variable names differ, techniques are the same.
Good luck! :)
 
Share this answer
 
Comments
Abhinav S 25-Feb-12 23:32pm    
Very detailed answer. 5 for sure.
Sander Rossel 26-Feb-12 2:48am    
Thanks :)
[no name] 26-Feb-12 1:43am    
Thanks .......here another error syntax error in string in query expression "26-feb-12"where code=8'.
Sander Rossel 26-Feb-12 2:50am    
I see you have already posted this as a new question and you have done nothing with my advice... Parameterize your query and you'll find what wrong!
[no name] 28-Feb-12 15:54pm    
naerling its working thanks...........

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900