Updating field in table

5 Jun 2006 2:42 AM

Bonzol

..Net 2003, 1.1, Access database. oledb connection

Hey there, I had my program updating to the database with a dataset,,,
doing things the easy way, binding the dataset to the text box n such,
then having a button to update it, now it has just stopped working. But
everything else works so the connection is still there.

Was wondering if anyone could just tell me the basic code to update a
field in table.

I have a combo box (cbocust) which displays the list of customers in
the table then I have a few txtboxes that display the current
information (this all works fine), all I want is for someone to select
the user from the combo box then to edit the text box's then press a
button and it will update the selected user in the table with the
updated information. Anyone got a quick solution for this? I've tried
looking around, but I'm brand new at this and don't understand most of
the syntax and stuff yet.

Any help would be greatly appreciated. Cheers.

5 Jun 2006 4:34 AM

Steven Nagy

Get the PK (CustomerID ??) from the combo box.
Create an update statement in SQL:

' Assumes you have an oledb connection called DBC
Dim sql as string = "UPDATE Customer SET FirstName = '" &
txtFirstName.Text
sql &= "' WHERE CustomerID=" & CustomerID.ToString()
Dim com as new oledb.oledbcommand(sql, dbc)
dbc.Open()
com.ExecuteNonQuery()
dbc.Close()

This solution doesn't use databindings.
If your dataset/datatable DOES have the correct data in it after you
have made changes in the textbox (verify this in the watch window in
debug mode) then you can just use a dataAdapter to update the dataset.

If this doesn't help, post your code for your save button.

SN

Bonzol wrote:
Show quoteHide quote

> .Net 2003, 1.1, Access database. oledb connection
>
> Hey there, I had my program updating to the database with a dataset,,,
> doing things the easy way, binding the dataset to the text box n such,
> then having a button to update it, now it has just stopped working. But
> everything else works so the connection is still there.
>
> Was wondering if anyone could just tell me the basic code to update a
> field in table.
>
> I have a combo box (cbocust) which displays the list of customers in
> the table then I have a few txtboxes that display the current
> information (this all works fine), all I want is for someone to select
> the user from the combo box then to edit the text box's then press a
> button and it will update the selected user in the table with the
> updated information. Anyone got a quick solution for this? I've tried
> looking around, but I'm brand new at this and don't understand most of
> the syntax and stuff yet.
>
> Any help would be greatly appreciated. Cheers.

5 Jun 2006 4:48 AM

Bonzol

ok im a bit confused with how this is set out,, ive clearly dont it
wrong

Dim sql As String = "UPDATE Customer SET CustName = '" & txtName.Text
sql &= "' WHERE CustomerID=" & CustomerID.ToString()
        Dim com As New OleDb.OleDbCommand(sql, OleDbConnection1)
        OleDbConnection1.Open()
        com.ExecuteNonQuery()
        OleDbConnection1.Close()

My Dim SQL string is wrong and I cant see where.

5 Jun 2006 6:13 AM

Bonzol

ok, ive made a new database, a new form, just very basic to figure this
out

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
        Dim First As String = "bob"
        Dim sql As String = "UPDATE People SET Last = '" & last.Text
sql & = "' WHERE First=" & First.ToString()
        Dim com As New OleDb.OleDbCommand(sql, OleDbConnection1)
        OleDbConnection1.Open()
        com.ExecuteNonQuery()
        OleDbConnection1.Close()
        ' OleDbDataAdapter1.Update(Custdata1)
    End Sub

just get an error in the SQL string (end of statment expected), can
anyone see it?

5 Jun 2006 9:21 AM

Steven Nagy

Maybe its the way your apostrophe's are ordered?

In sql, any strings need to be surrounded by single quotes.
Eg.

UPDATE People SET Last = 'Smith' WHERE First='John'

I don't think you have quotes around your first name.
If its an integer, it doesn't need quotes around it.

sql & = "' WHERE First='" & First.ToString() & "'"

Bonzol wrote:
Show quoteHide quote

> ok, ive made a new database, a new form, just very basic to figure this
> out
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>         Dim First As String = "bob"
>         Dim sql As String = "UPDATE People SET Last = '" & last.Text
> sql & = "' WHERE First=" & First.ToString()
>         Dim com As New OleDb.OleDbCommand(sql, OleDbConnection1)
>         OleDbConnection1.Open()
>         com.ExecuteNonQuery()
>         OleDbConnection1.Close()
>         ' OleDbDataAdapter1.Update(Custdata1)
>     End Sub
>
> just get an error in the SQL string (end of statment expected), can
> anyone see it?