can not Update database

Question

0.00/5 (No votes)

See more:

When click on update command button the error is

"Conversion from string "Syntax error in UPDATE statement" to type 'Integer' is not valid."

VB

        BtnSave.Enabled = True
        BtnAdd.Enabled = True
        BtnUpdate.Enabled = True
        BtnDelete.Enabled = True
        BtnPrevious.Enabled = True
        BtnNext.Enabled = True
        BtnFirst.Enabled = True
        BtnLast.Enabled = True

        Try

            Dim cmd As New OleDb.OleDbCommand()
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= E:\VB Project\Tansportation\Transport2003.mdb"



            con.Open()

            
            cmd.Connection = con

            cmd.CommandText = "UPDATE TblBillStatmentEntry SET Date='" & TxtDate.Text & "',FreightSlipNo='" & TxtFreightSlipNo.Text & "',FromID='" & ComboBox1.Text & "',To='" & TxtTo.Text & "',RatesPerTon='" & TxtRatePerTon.Text & "',RatesPerTrip='" & TxtRatePerTrip.Text & "',WorkEffectiveDate='" & TxtWEF.Text & "' ,MaterialID='" & ComboBox2.Text & "', TruckNoID='" & ComboBox3.Text & "',TonCategory='" & TxtTon.Text & "',ConditionListID='" & ComboBox4.Text & "',Weight='" & TxtExtra.Text & "',Trips='" & TxtTrips.Text & "',ActualWeight='" & TxtActualWeight.Text & "',BillWeight='" & TxtBillWeight.Text & "',Amount='" & TxtAmount.Text & "' WHERE ID='" & TxtID.Text & "' "


           
cmd.ExecuteNonQuery()
            If con.State = ConnectionState.Open Then
                con.Close()
            End If

            MsgBox("Data Updated")
        Catch ex As Exception
            MsgBox("Cant load", ex.Message)

        End Try

       
    End Sub

Posted 15-Apr-15 21:56pm

Manish Joshi

Updated 15-Apr-15 22:15pm

Afzaal Ahmad Zeeshan

v2

Add a Solution

Comments

Afzaal Ahmad Zeeshan 16-Apr-15 4:14am

Time to learn basics of English language before learning programming langauge. Conversion from string "Syntax error in UPDATE statement" to type 'Integer' is not valid. It tells that your object is a string, and cannot convert it to Integer because it includes non-digit characters.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2015-04-15T22:47:00

The immediate problem is as Afzaal said: the value you give for one or more columns is not convertible to an integer, which the column expects.

But you have bigger problems than that!

You can't assume that users will enter valid data - so rather than assuming they will and sending it to SQL regardless and letting it sort it out, you really should check and convert each numeric value, each date based value, and report individually on the problems so the user can fix them.
Since you have a lot of inputs, just telling him "Cant load" isn't at all helpful - and users will spend a lot more time trying to work out which data is wrong than you would spend adding the code to check it!
It's not complex:

VB

Dim textBox As New TextBox()
Dim value As Integer
If Not Integer.TryParse(textBox.Text, value) Then
        ' Report problem
        ...
End If

Secondly, don't do SQL like that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead, and pass the checked and converted values directly as the right datatype.

Thirdly, if this really is VB.NET, then don't use MsgBox - for starters, you should be using the .NET MessageBox.Show instead, and secondly VB code runs on the server, not the client - so the message will show at the server, and the client will not get any response...