How do I update records?

harlequeen

New member
Joined
Apr 16, 2005
Messages
2
Programming Experience
Beginner
I am trying to teach myself VB.net and am trying to replicate a small program a friend showed me.

I have a picture of our town and I want to be able to add information to a database about the area.

I have a database and am able to get the number of records in returned in a message box. I have a second form which fires up if I make a new label on the 1st form and the labels co-ordinates are passed from the first form to the 2nd in the shape of a couple of textboxes.

I also have other text boxes on form2 that I want to be able to inut info into and save to my db. Data such as Name, Address1, Address2, Address3, Phone.

I simply can't work out how to do it.

I have tried to use the following code but keep getting an error,
VB.NET:
pbman.Position = pbman.Count - 1
pbman.EndCurrentEdit()
        OleDbDataAdapter1.UpdateCommand(DataSet11)
but get the following error:

Property access must assign to the property or use its value.

I don't know how to connect the textboxes to the fields in the database, I don't think..I've looking at this stuff for hours and its beginning to swim now, I don't know anymore what I do know...
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,321
Location
Sydney, Australia
Programming Experience
10+
UpdateCommand is a property of the OleDbDataAdapter class. This means you must assign it a value. An OleDbCommand has a CommandText property, which is a string and should contain a valid SQL command, and a Connection property, which should have an OleDbConnection assigned to it. Here is a simple example, assuming we are using a form called Form1 with two text boxes called nameText and descriptionText:
VB.NET:
'Create the connection to the Access database.
Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\myDB.mdb"";")

'Create the data adapter with a select command.
Dim adapter As New OleDbDataAdapter("SELECT * FROM Table1", connection)

'Could have used:
'Dim selectCommand As New OleDbCommand("SELECT * FROM Table1", connection)
'Dim adapter As New OleDbDataAdapter
'adapter.SelectCommand = selectCommand

'Create table to hold data.
Dim table As New DataTable

'Get the data from the database.
adapter.Fill(table)

'Get first row in table.
Dim row As DataRow = table.Rows(0)

'Assume Table1 contains columns ID, Name and Description.
'Display the first row of data in the form's text boxes.
Me.nameText.Text = row("Name")
Me.descriptionText.Text = row("Description")

...
'User changes values in text boxes.
...

'Update first row in table.
row.BeginEdit()
row("Name") = Me.nameText.Text
row("Description") = Me.descriptionText.Text
row.EndEdit()

'Create update command.
'This is a parameterised command, which means that every row that has been updated will be saved to the database with the values for each field substituted for the "?" symbols.
Dim updateCommand As New OleDbCommand("UPDATE Table1 SET Name = ?, Description = ? WHERE ID = ?", connection)

'Add the parameter descriptions, which must be in the same order as in the SQL.
updateCommand.Parameters.Add("Name", OleDbType.VarChar, 0, "Name")
updateCommand.Parameters.Add("Description", OleDbType.VarChar, 0, "Description")
updateCommand.Parameters.Add("ID", OleDbType.Integer, 0, "ID")

'Add the update command to the data adapter.
adapter.UpdateCommand = updateCommand

'Update the database.
adapter.Update(table)
 

harlequeen

New member
Joined
Apr 16, 2005
Messages
2
Programming Experience
Beginner
Thanks

Thanks for your help

That explained it.

Cheers

Harlequeen
 

tommybeer

Member
Joined
May 4, 2005
Messages
15
Programming Experience
Beginner
hello,

i'm having a problem with this update, it gives an error : error converting varchar to float.

here is my code,
can anybody see the problem?

con.Open()

Dim table As New DataTable

datprijzen.Fill(table)

Dim row As DataRow = table.Rows(0)

row.BeginEdit()

'row("Prijs_id") = Txtprijs_id.Text

row("Soort") = txtsoort.Text

row("Type") = txttype.Text

row("Prijs1") =
CDec(txtprijs1.Text)

row("Prijs2") =
CDec(txtprijs2.Text)

row("Prijs3") =
CDec(txtprijs3.Text)

row("ExtraF") =
CDec(txtextraf.Text)

row("ExtraG") =
CDec(txtextrag.Text)

row.EndEdit()

'Create update command.

'This is a parameterised command, which means that every row that has been updated will be saved to the database with the values for each field substituted for the "?" symbols.

Dim updateCommand As New OleDbCommand("UPDATE [Eures4].[dbo].[Prijzen] SET [Soort] = '" & txtsoort.Text & "', [Type] = '" & txttype.Text & "', [Prijs1] = '" & CDec(txtprijs1.Text) & "', [Prijs2] = '" & CDec(txtprijs2.Text) & "', [Prijs3] = '" & CDec(txtprijs3.Text) & "', [ExtraG] = '" & CDec(txtextrag.Text) & "', [ExtraF] = '" & CDec(txtextraf.Text) & "' WHERE [Prijs_id] = '" & CInt(Txtprijs_id.Text) & "' ", con)

'Add the parameter descriptions, which must be in the same order as in the SQL.

updateCommand.Parameters.Add("Prijs_id", OleDbType.BigInt, 0, "Prijs_id")

updateCommand.Parameters.Add("Soort", OleDbType.VarChar, 0, "Soort")

updateCommand.Parameters.Add("Type", OleDbType.VarChar, 0, "Type")

updateCommand.Parameters.Add("Prijs1", OleDbType.Decimal, 0, "Prijs1")

updateCommand.Parameters.Add("Prijs2", OleDbType.Decimal, 0, "Prijs2")

updateCommand.Parameters.Add("Prijs3", OleDbType.Decimal, 0, "Prijs3")

updateCommand.Parameters.Add("ExtraF", OleDbType.Decimal, 0, "ExtraF")

updateCommand.Parameters.Add("ExtraG", OleDbType.Decimal, 0, "ExtraG")



'Add the update command to the data adapter.

datprijzen.UpdateCommand = updateCommand





' Update the database.

Try

datprijzen.Update(table)

Catch ex As Exception

MsgBox(ex.Message.ToString)



End Try

' Close the database connection.

con.Close()


here is what my variables are in the sql database

Prijs_id,bigint
Soort,nvarchar(25)
Type,nvarchar(25)
Prijs1,float
Prijs2,float
Prijs3,float
ExtraF,float
ExtraG,float
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,321
Location
Sydney, Australia
Programming Experience
10+
The exception you are getting is because you have put single quotes around numerical values in your SQL. Single quotes go around strings and nothing else.

You are also making the mistake of putting explicit values into a parameterised command. If you use explicit values then the parameters are not needed. If you want to use parameters change this line:
VB.NET:
[size=2][color=#0000ff]Dim[/color][/size][size=2] updateCommand [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand("UPDATE [Eures4].[dbo].[Prijzen] SET [Soort] = '" & txtsoort.Text & "', [Type] = '" & txttype.Text & "', [Prijs1] = '" & [/size][size=2][color=#0000ff]CDec[/color][/size][size=2](txtprijs1.Text) & "', [Prijs2] = '" & [/size][size=2][color=#0000ff]CDec[/color][/size][size=2](txtprijs2.Text) & "', [Prijs3] = '" & [/size][size=2][color=#0000ff]CDec[/color][/size][size=2](txtprijs3.Text) & "', [ExtraG] = '" & [/size][size=2][color=#0000ff]CDec[/color][/size][size=2](txtextrag.Text) & "', [ExtraF] = '" & [/size][size=2][color=#0000ff]CDec[/color][/size][size=2](txtextraf.Text) & "' WHERE [Prijs_id] = '" & [/size][size=2][color=#0000ff]CInt[/color][/size][size=2](Txtprijs_id.Text) & "' ", con)[/size]
to this:
VB.NET:
[size=2][color=#0000ff]Dim[/color][/size][size=2] updateCommand [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand("UPDATE [Eures4].[dbo].[Prijzen] SET [Soort] = ?, [Type] = ?, [Prijs1] = ?[/size][size=2], [Prijs2] = ?[/size][size=2], [Prijs3] = ?[/size][size=2], [ExtraG] = ?[/size][size=2], [ExtraF] = ?[/size][size=2] WHERE [Prijs_id] = ?[/size][size=2]", con)[/size]
and the actual values will be automatically substituted for the question marks for each row of the table when you call Update().

Thirdly, when creating a string like you were I would recommend using the String.Format() function. It is more efficient and much more readable than concatenating using the "&" operator.
 

tommybeer

Member
Joined
May 4, 2005
Messages
15
Programming Experience
Beginner
@ jm

ok, i've changed them in to ?

but i still keep getting the error of converting varchar into float

can you please help me some more?

greetz
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,321
Location
Sydney, Australia
Programming Experience
10+
While this is not the cause of your error, I suggest you use OleDbType.Double for a database type of Float. It's not a big deal but it is more correct.

Your error is occurring because you have added your parameters in the wrong order. The primary key (Prijs_id) appears last in the SQL statement but you have added a parameter for it first. As the comment before those lines of code says, the parameters must be added in the same order as they appear in the SQL statement.

This means that
VB.NET:
[size=2]updateCommand.Parameters.Add("Prijs_id", OleDbType.BigInt, 0, "Prijs_id")
updateCommand.Parameters.Add("Soort", OleDbType.VarChar, 0, "Soort")
updateCommand.Parameters.Add("Type", OleDbType.VarChar, 0, "Type")
updateCommand.Parameters.Add("Prijs1", OleDbType.Decimal, 0, "Prijs1")
updateCommand.Parameters.Add("Prijs2", OleDbType.Decimal, 0, "Prijs2")
updateCommand.Parameters.Add("Prijs3", OleDbType.Decimal, 0, "Prijs3")
updateCommand.Parameters.Add("ExtraF", OleDbType.Decimal, 0, "ExtraF")
updateCommand.Parameters.Add("ExtraG", OleDbType.Decimal, 0, "ExtraG")[/size]
becomes
VB.NET:
[size=2]updateCommand.Parameters.Add("Soort", OleDbType.VarChar, 0, "Soort")
updateCommand.Parameters.Add("Type", OleDbType.VarChar, 0, "Type")
updateCommand.Parameters.Add("Prijs1", OleDbType.Double, 0, "Prijs1")
updateCommand.Parameters.Add("Prijs2", OleDbType.Double, 0, "Prijs2")
updateCommand.Parameters.Add("Prijs3", OleDbType.Double, 0, "Prijs3")
updateCommand.Parameters.Add("ExtraF", OleDbType.Double, 0, "ExtraF")[/size][size=2]
updateCommand.Parameters.Add("ExtraG", OleDbType.Double, 0, "ExtraG")
[/size][size=2]updateCommand.Parameters.Add("Prijs_id", OleDbType.BigInt, 0, "Prijs_id")[/size]
 
Last edited:

tommybeer

Member
Joined
May 4, 2005
Messages
15
Programming Experience
Beginner
great :) it works

but i have another question :eek:)

now i can save, modify,

how can i delete a record?

greetz
 

tommybeer

Member
Joined
May 4, 2005
Messages
15
Programming Experience
Beginner
how do i execute this query?
sorry if i ask so many questions but i'm a newbie :)

could you give me a example of code ?

thx
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,321
Location
Sydney, Australia
Programming Experience
10+
Use the same method as before. Create an OleDbCommand with the SQL statement from my previous post, substituting your table and field names. Add a parameter for the "Prijs_id" column as before, then assign the command to the DeleteCommand property of your OleDbDataAdapter. Then you just call Update on the adapter, which executes the DeleteCommand, InsertCommand and UpdateCommand of the adapter. Sorry, in a hurry. Let me know if you're still unsure and I'll try again later when I have more time.
 

tommybeer

Member
Joined
May 4, 2005
Messages
15
Programming Experience
Beginner
thx solved :)

you've been a great help

i hope i won't be needing you anymore, but i guess i'll need you some more in the next few days, do you happen to know something about printing a datagrid

?

greetz

tom
 
Top Bottom