Trying to Update record into access db

[Sammys.Man]

Hi Guys,

wondered if someone could help please?
in my customer form i have managed to pull in the data from the table to the right txtboxes, and i can move forwards and backwards ("happy days") but now im trying to update a record, this one has seem to thrown me, because in a different form (login) i managed to pull data from lbls and insert it into the database, i thought it would be similar, but im guessing im wrong lol
here's my code
i did try and follow another users problem (INSERT ERROR) but i couldnt follow his code clearly
any ideas?

 Private Sub btn_Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Update.Click
        Dim con As New OleDb.OleDbConnection                     '"con" variable holds the Connection Object
        Dim dbProvider As String                                 'creates provider variable
        Dim dbSource As String

        dbProvider = "Provider = Microsoft.ACE.OLEDB.12.0;"      'specifies the provider technology
        dbSource = "Data Source = C:\Users\ComputerFirstAde\Desktop\Computer First Ade VB.net\Computer First Ade\Computer First Ade\CFA_DB.mdb" 'specifies the path to the Database

        con.ConnectionString = dbProvider & dbSource             'creates the connection string

        'ds.Tables.Add(dt)

        con.Open()

        Dim Connection As New OleDb.OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0" & _
        "Data Source = C:\Users\ComputerFirstAde\Desktop\Computer First Ade VB.net\Computer First Ade\Computer First Ade\CFA_DB.mdb")

        'Connection.Open()

        Dim InsertCommand As New OleDb.OleDbCommand("UPDATE INTO tbl_Customers(cust_Company)" & _
                "VALUES(@custCompany)", con)

        InsertCommand.Parameters.Add(New OleDb.OleDbParameter("@cust_Company", Cust_CompanyTextBox.Text))

        InsertCommand.ExecuteNonQuery()
        con.Close()

        lbl_Cust_Form.Text = "Updated Record"

    End Sub

[Begginnerdev]

You just simply need to change your query:

   'SQL Update statements will not have an INTO clause.
   Dim UpdateCommand As New OleDb.OleDbCommand("UPDATE tbl_Customers(cust_Company) VALUES(@custCompany)", con)

[Sammys.Man]

Hi mate,
thanks for you fast reply, i amended the code and code this error "OleDbException was unhandled (Syntax error in UPDATE statement) linked to the ' UpdateCommand.ExecuteNonQuery() ' line :/
ameded code :-

        Dim UpdateCommand As New OleDb.OleDbCommand("UPDATE tbl_Customers(cust_Company)" & _
                "VALUES(@custCompany)", con)

        UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@cust_Company", Cust_CompanyTextBox.Text))

        UpdateCommand.ExecuteNonQuery()
        con.Close()

        lbl_Cust_Form.Text = "Updated Record"

[Begginnerdev]

I'm sorry. The correct syntax will be:

"UPDATE tbl_Customers SET cust_Company=@custCompany"

That's what I get for absent mindedly typing responses.

[Sammys.Man]

Hi Dev,
thanks for your fast reply, no sorry needed mate :)
ok, that kind of works, it definatly updates but, problem is now it updates every record in that column(item) eeekk lol, do i have to link the records to the primary key or something?
any ideas?

[Begginnerdev]

You will need a WHERE clause and a unique value;

For example:

"UPDATE tbl_Customers SET cust_Company=@custCompany WHERE cust_ID = 3"

[Sammys.Man]

Hi Again,
going through the steps with you is starting to make more sense to me (slowly lol) i had a try with the update, butputting a number in stops any updating, but im guessing that numbers is to be linked to the primary key field in the form (somehow)? something like :-

  conn.Open()
        Dim cmd2 As New OleDb.OleDbCommand("UPDATE tbl_Customers SET cust_Company=@cust_Company WHERE CustomerID=@CustomerID", conn)

        cmd2.Parameters.Add("@CustomerID", CustomerIDTextBox.Text)
        cmd2.Parameters.Add("@cust_Company", Cust_CompanyTextBox.Text)

        cmd2.ExecuteNonQuery()
        conn.Close()

or have i gone totally the wrong direction?
(my CustomerIDtextbox is set to readonly)

[Begginnerdev]

That is correct, assuming that customer ID is a unique value in that table.

If customer ID appears more than once in that column/ in that table - the statement will update all of those records.

It is always a smart thing to use an identity column or something of the sort.

[Sammys.Man]

yea the primary keys for all records are different,

would you know how to amend the code above to link it the primary key? as the code above doesn't work as it is lol

[Begginnerdev]

If your table structure looks something like:

customerID|customerCompany|customerLocation
Identity  |    text       |     text

Then when you issue update, it is already tied to the same row.

If the CustomerIDTextBox.Text is the correct value that is.

If you are pulling the values into the form , as you stated earlier, I am assuming you have a select statement?

If so, you can use that ID for the customer ID

[Sammys.Man]

thats similar to my table, but i used a private sub to call out the data, i done that because (in my understanding) it was the easiest way to get the next and prvious btns to work
heres the function:-

 Public Sub autoinc()

        ConStr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ComputerFirstAde\Desktop\Computer First Ade VB.net\Computer First Ade\Computer First Ade\CFA_DB.mdb"
        sql = "SELECT * FROM tbl_Customers"

        Try
            con = New OleDbConnection(ConStr)
            con.Open()
            da = New OleDbDataAdapter(sql, con)
            ds = New DataSet

            da.Fill(ds)

            CustomerIDTextBox.Text = ds.Tables(0).Rows(inc)("CustomerID").ToString
            Cust_CompanyTextBox.Text = ds.Tables(0).Rows(inc)("cust_Company").ToString
            Cust_DepartmentTextBox.Text = ds.Tables(0).Rows(inc)("cust_Department").ToString
            Cust_First_NameTextBox.Text = ds.Tables(0).Rows(inc)("cust_First_Name").ToString
            Cust_Last_NameTextBox.Text = ds.Tables(0).Rows(inc)("cust_Last_Name").ToString
            Cust_Email_AddressTextBox.Text = ds.Tables(0).Rows(inc)("cust_Email_Address").ToString
            Cust_Phone_NumberTextBox.Text = ds.Tables(0).Rows(inc)("cust_Phone_Number").ToString
            Cust_Mobile_NumberTextBox.Text = ds.Tables(0).Rows(inc)("cust_Mobile_Number").ToString
            Cust_Address_Line1TextBox.Text = ds.Tables(0).Rows(inc)("cust_Address_Line1").ToString
            Cust_Address_Line2TextBox.Text = ds.Tables(0).Rows(inc)("cust_Address_Line2").ToString
            Cust_Town_CityTextBox.Text = ds.Tables(0).Rows(inc)("cust_Town/City").ToString
            Cust_PostcodeTextBox.Text = ds.Tables(0).Rows(inc)("cust_Postcode").ToString
            Cust_WebsiteTextBox.Text = ds.Tables(0).Rows(inc)("cust_Website").ToString
            Cust_NotesTextBox.Text = ds.Tables(0).Rows(inc)("cust_Notes").ToString

            MaxRw = ds.Tables(0)(ds.Tables(0).Rows.Count - 1)("CustomerID")

        Catch ex As Exception
            lbl_Cust_Form.Text = "No More Records"
        End Try

        con.Close()

    End Sub


    Private Sub btn_Next_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Next.Click
        lbl_Cust_Form.Text = ""
        inc = inc + 1
        autoinc()


    End Sub

[Begginnerdev]

If the CustomerIDTextBox is accessable, that will be the value you need.

If the problem is that the text box is on another form, you can create a Public Property in that form's class and call it from the other form:

Public Class Form1
'....
    Public Property CustomerID as String
        Get
            Return CustomerIDTextBox.Text
        End Get
        Set(value As String)
            CustomerIDTextBox.Text = Value
        End Set
    End Property
End Class

Public Class Form2

    Private Sub btnUpdate.Click(ByVal sender as object, ByVal e as eventargs) Handles btnUpdate.Click
        Try

            Dim cmd2 As OleDBCommand("UPDATE tbl_Customers SET cust_Company=@cust_Company WHERE CustomerID=@CustomerID",conn)
            cmd2.Parameters.AddWithValue("@cust_Company",Cust_CompanyTextBox.Text)
            cmd2.Parameters.AddWithValue("@CustomerID",Form1.CustomerID) 'GETTING THE PROPERTY VALUE

            cmd2.ExecuteNonQuery()
        Catch ex As Exception
              MsgBox(ex.ToString)
        End Try
    End Sub
End Class

I hope this clarifies for you.

When you say tieing it to it, are you wanting to pull in the new values into a dataset?

[Sammys.Man]

hi mate,
no the customerid is only linked to 1 form at the moment

[Begginnerdev]

Have you verified that the number you are putting in the textbox is indeed the correct customer ID from the table?

[Sammys.Man]

uuummmm, nope lol, how do i do that lol

[Begginnerdev]

Open the access database, and open the table in datasheet view.

Once the table is open, look for the current record you are editing - see if the value that is being pulled in is correct.

[Sammys.Man]

yea ive opened access and im testing the query for just 'cust_Company' for now, the query updates all of the cust_company records not just the 1 i want :/

[Begginnerdev]

Before issueing the update, check to see if the parameters are passing in correctly:

MsgBox(cmd2.CommandText)
cmd2.ExecuteNonQuery

[Sammys.Man]

great thinking
the msg that comes back is

UPDATE tbl_Customers SET cust_Customer = ? WHERE (CustomerID)

[Sammys.Man]

update:-

got it sorted :), i decided to hide a lbl on the form, and throw the customerID at it, then add it to the sql qry :) and it works.

a big thank you to Dev for helping me out and nudging me in the right direction :)

here's the working code :)

 conn.ConnectionString = dbProvider & dbSource             'creates the connection string
        lbl_TEST.Text = CustomerIDTextBox.Text
        conn.Open()
        Dim sql As String = "UPDATE tbl_Customers SET cust_Company = ? WHERE (CustomerID = " & (lbl_TEST.Text) & ")"
        Dim cmd2 As New OleDb.OleDbCommand(sql, conn)

        cmd2.Parameters.AddWithValue("cust_Company", Cust_CompanyTextBox.Text)

        'MsgBox(cmd2.CommandText)                               'used for testing
        cmd2.ExecuteNonQuery()
        conn.Close()



        lbl_Cust_Form.Text = "Updated Record"

[TnTinMN]

I think the problem that you where having is the order that that you where adding the parameters.

OleDB ignores the parameter names and substitutes them in sequential order. In the SQL CommandText, you could type pretty much any unquoted text where it expects a value and it will treat it as a placeholder. Placeholders are filled Left to Right with the parameters in the order that the parameters exist in the parameter collection. This is why the documentation shows using "?" as placeholders.

     Dim cmd2 As New OleDb.OleDbCommand("UPDATE tbl_Customers SET cust_Company=@cust_Company WHERE CustomerID=@CustomerID", conn)
    cmd2.Parameters.Add("@CustomerID", CustomerIDTextBox.Text)
    cmd2.Parameters.Add("@cust_Company", Cust_CompanyTextBox.Text)

In this case you were add the ID field first when it should have been added second. DB's example had it in the correct order. This could have been written:

     Dim cmd2 As New OleDb.OleDbCommand("UPDATE tbl_Customers SET cust_Company=@cust_Company WHERE CustomerID=@CustomerID", conn)
     cmd2.Parameters.Add(Nothing, Cust_CompanyTextBox.Text)
     cmd2.Parameters.Add(Nothing, CustomerIDTextBox.Text)

[Sammys.Man]

aaahhhhh i see :), thank you very much,

now onto, delete then save (although i already have the syntax sorted for insert - used it previously)