Update statement

[bonhomme]

Hi everyone,

I am a Visual Foxpro programmer trying to migrate to VB.NET I find it really nice way to program but I want to update a table with this statement :

Dim sqlUpdate As String = " Update Customer " & " Set Name = ' " & Name & " ', Address = ' " & Address & "'," & " Where PhoneNo = '" & Key & "'"

I received and UPDATE statement error

 

[JuggaloBrotha]

It's because you've got a space between the single quote and the actual value. IE instead of Name = ' " & use Name = '" & same thing for the other end too, instead of & " ' use & "'

 

[bonhomme]

thank you brother but I still get the same message this is my full Query

Dim sqlUpdate As String = " Update Customer " & " Set Name = '" & name & "', Address = '" & address & "'," & " Where PhoneNo = '" & Key & "'"

 

[JuggaloBrotha]

How about posting the error message, I'm not going to be able to help ya otherwise.

 

[bonhomme]

I got a "Syntax error on UPDATE Statement"

 

[bonhomme]

Take a look on the Sub

Public Shared Sub UpdateData(ByVal aCustomer As Customer)
        Dim name As String
        Dim address As String
        Dim phoneNo As String
        Dim Key As String

        Name = aCustomer.GetName()
        Address = aCustomer.GetAddress()
        PhoneNo = aCustomer.GetPhoneNo()
        Key = phoneNo
        conHanDler.Open()
        Dim sqlUpdate As String = " Update Customer " & " Set Name = '" & name & "',  Address = '" & address & "'," & " Where PhoneNo = '" & Key & "'"
        'Dim sqlUpdate As String = "Update Customer" & "Set Name =  " & name & " & " WHERE PhoneNo = '" & Key & "'"
        Dim New_DataAdapter As New OleDbDataAdapter()
        'Check if customer exists


        Dim Dscustomer As New DataSet


        'Define the Sql Statement using the phone key
        Dim sqlQue As String = " Select Name, Address,PhoneNo " & "from Customer where PhoneNo = '" & Key & "'"
        Dim adapCustomer As New OleDbDataAdapter(sqlQue, conHanDler)
        adapCustomer.Fill(Dscustomer, "Customer")
        If Dscustomer.Tables("Customer").Rows.Count > 0 Then
            Dim CustRow As DataRow
            CustRow = Dscustomer.Tables("Customer").Rows(0)

            Name = CustRow.Item("Name")
            Address = CustRow.Item("Address")
            PhoneNo = CustRow.Item("PhoneNo")
            aCustomer = New Customer(Name, Address, PhoneNo)
            MessageBox.Show("Customer exists")
            'Else

            'Dscustomer = Nothing
            'Assign insert command and execute


            New_DataAdapter.UpdateCommand = New OleDbCommand(sqlUpdate)
            New_DataAdapter.UpdateCommand.Connection = conHanDler
            New_DataAdapter.UpdateCommand.ExecuteNonQuery()
            MessageBox.Show(" i am here 4")
            'ListRecord()

        End If
    End Sub

 

[InertiaM]

Look at cjard's excellent thread about parameterised queries - it will help you dramatically.

If you were then to convert your query to be parameterised, you'd see the error instantly :-

"Update Customer " & " Set Name = ' " & Name & " ', Address = ' " & Address & "'," & " Where PhoneNo = '" & Key & "'"

would become

"Update Customer Set Name = @Name, Address = @Address, Where PhoneNo = @Key"

See the additional comma?

 

[cjard]

Second IM's advice. Not only would a PQ help you debug faster, but it is more secure and easier for the database to plan execution of

 

[alim]

Public Shared Sub UpdateData(ByVal aCustomer As Customer)
        Dim name As String
        Dim address As String
        Dim phoneNo As String
        Dim Key As String

        Name = aCustomer.GetName()
        Address = aCustomer.GetAddress()
        PhoneNo = aCustomer.GetPhoneNo()
        Key = phoneNo
        conHanDler.Open()
        Dim sqlUpdate As String = " Update Customer " & " Set Name = '" & name & "',  Address = '" & address & "'," & " Where PhoneNo = '" & Key & "'"
        'Dim sqlUpdate As String = "Update Customer" & "Set Name =  " & name & " & " WHERE PhoneNo = '" & Key & "'"
        Dim New_DataAdapter As New OleDbDataAdapter()
        'Check if customer exists


        Dim Dscustomer As New DataSet


        'Define the Sql Statement using the phone key
        Dim sqlQue As String = " Select Name, Address,PhoneNo " & "from Customer where PhoneNo = '" & Key & "'"
        Dim adapCustomer As New OleDbDataAdapter(sqlQue, conHanDler)
        adapCustomer.Fill(Dscustomer, "Customer")
        If Dscustomer.Tables("Customer").Rows.Count > 0 Then
            Dim CustRow As DataRow
            CustRow = Dscustomer.Tables("Customer").Rows(0)

            Name = CustRow.Item("Name")
            Address = CustRow.Item("Address")
            PhoneNo = CustRow.Item("PhoneNo")
            aCustomer = New Customer(Name, Address, PhoneNo)
            MessageBox.Show("Customer exists")
            'Else

            'Dscustomer = Nothing
            'Assign insert command and execute


            New_DataAdapter.UpdateCommand = New OleDbCommand(sqlUpdate)
            New_DataAdapter.UpdateCommand.Connection = conHanDler
            New_DataAdapter.UpdateCommand.ExecuteNonQuery()
            MessageBox.Show(" i am here 4")
            'ListRecord()

        End If
    End Sub

can't figure out what do want ..... you want only update or update + query ...
just do step by step ... it will help u too learn better...

ok ...not

if you want to update records ,.... please do some change on
field to update bt bcoz ur update syntax is wrong .. and u r getting error ..
it won't work if you do some change on field .. bt yet you haven't make any
changes on your code

such as

     Name = CustRow.Item("Name")
            Address = CustRow.Item("Address")
            PhoneNo = CustRow.Item("PhoneNo")

u r getting this columns values on those strings bt no field change

aCustomer = New Customer(Name, Address, PhoneNo)

...and why u use this line i couldn't understand ..u want to update don't want to insert .... if you want to do both... u have to insert both commands...

by the way ,please do :

  CustRow.Item("Name")=   Name 
            CustRow.Item("Address")=Address 
            CustRow.Item("PhoneNo")=PhoneNo

'and please check no new row is added then execute update query
'i think adapters use is the best way..


....You are new in Vb.net.. why don't you use first drag and drop features first..
please drag ur datasoures tables grid in your form .. you will get every thing..

at the first time i learn from there

 

[cjard]

alim, will you please stop replying to threads that are months old

Let the OP bump it if he still has a problem