Update sql table through vb error

Question

My teacher gave me the following code to ammend data:

    If Datatable.rows.count<>0 Then
    datatable.rows(rowposition)("company name") = txtcompanyname.text

dataadapter.update(datatable)
Msgbox("Record has been updated")
End if 
End sub

This is what I tried but it didn't work:

 Dim companyname As String
    If reader.Read Then
        reader.Close()
        companyname = txtCompanyName.Text
        sqlstring = "UPDATE `client_details` SET `companyname` =  @companyname "
        objcommand = New MySqlCommand(sqlstring, objconnection)
        objcommand.Parameters.AddWithValue("@companyname", companyname)
        objcommand.ExecuteNonQuery()
    Else
        MsgBox("Unable to add details", MsgBoxStyle.Critical, "Updating Failed")
        reader.Close()
        Me.Cursor = Cursors.Arrow

Basically I have a datagrid view in my form and I have a textbox which corresponds to a column so when I click a record all the details go into their corresponding textboxes then what I want to do is to change the details of one textbox and press update button for the record to be ammended? I've got no idea, been researching and using trial and error, but no fix?

EDIT 1:

objconnection.Open()
        Dim companytype As String
        Dim vatregistrationnumber As String
        Dim payeandtaxreference As String
        Dim addressline1 As String
        Dim city As String
        Dim postcode As String
        Dim phonenumber As String
        Dim email As String
        postcode = txtPostcode.Text
        sqlstring = "UPDATE `client_details` SET companytype=@companytype, vatregistrationnumber=@vat, payeandtaxreference=@paye, addressline1=@address, city=@city, postcode=@postcode, phonenumber=@phone, email=@email"
        objcommand = New MySqlCommand(sqlstring, objconnection)
        objcommand.Parameters.AddWithValue("@companytype", CompanyType)
        objcommand.Parameters.AddWithValue("@vat", vatregistrationnumber)
        objcommand.Parameters.AddWithValue("@paye", payeandtaxreference)
        objcommand.Parameters.AddWithValue("@address", addressline1)
        objcommand.Parameters.AddWithValue("@city", city)
        objcommand.Parameters.AddWithValue("@postcode", postcode)
        objcommand.Parameters.AddWithValue("@phone", phonenumber)
        objcommand.Parameters.AddWithValue("@email", email)
        objcommand.ExecuteNonQuery()
        objconnection.Close()
    End Sub
End Class

I declared everything at the top, there is too much code to post? Do you mean this: Dim objconnection As New MySqlConnection("Server=localhost;database=ba-solutions;user id=root;password=") — Livaren
– Livaren, Commented Feb 1, 2014 at 10:46

pankeel · Accepted Answer · 2014-02-01 12:12:32Z

Write this way:

 objconnection.Open()
        Dim companyname As String
        Dim companytype As String
        Dim vatregistrationnumber As String
        Dim payeandtaxreference As String
        Dim addressline1 As String
        Dim city As String
        Dim postcode As String
        Dim phonenumber As String
        Dim email As String
        postcode = txtPostcode.Text
        companyname = txtCompanyName.Text
        companytype = cbxCompanyType.Text
        payeandtaxreference = txtPAYE.Text
        vatregistrationnumber = txtVAT.Text
        addressline1 = txtAddressLine.Text
        city = txtCity.Text
        phonenumber = txtPhoneNumber.Text
        email = txtEmail.Text
        sqlstring = "UPDATE client_details SET companytype=@companytype, vatregistrationnumber=@vat, payeandtaxreference=@paye, addressline1=@address, city=@city, postcode=@postcode, phonenumber=@phone, email=@email where companyname=  @companyname "
        objcommand = New MySqlCommand(sqlstring, objconnection)
        objcommand.Parameters.AddWithValue("@companyname", companyname)
        objcommand.Parameters.AddWithValue("@companytype", CompanyType)
        objcommand.Parameters.AddWithValue("@vat", vatregistrationnumber)
        objcommand.Parameters.AddWithValue("@paye", payeandtaxreference)
        objcommand.Parameters.AddWithValue("@address", addressline1)
        objcommand.Parameters.AddWithValue("@city", city)
        objcommand.Parameters.AddWithValue("@postcode", postcode)
        objcommand.Parameters.AddWithValue("@phone", phonenumber)
        objcommand.Parameters.AddWithValue("@email", email)
        objcommand.ExecuteNonQuery()
        objconnection.Close()

I'm getting error "connection must be valid and open" at the last line
i have changed my answer, pls give me proper code to help you you must open objconnection
Yep I did that, then I try change record to '21' and get error: Duplicate entry '21' for key 'PRIMARY' I'm assuming this is something to do with my sql table? If I provide you with my dropbox link to my vb and sql, can you help me please?
Okay I tried the same code for another record, it worked and changed it, but it changed the whole columns data, so changed the company name for everysingle record?
give me table structure & you can change all the row or one row

Collectives™ on Stack Overflow

Update sql table through vb error

1 Answer 1

17 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Related