How do I update records?

[harlequeen]

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,

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...

 

[jmcilhinney]

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:

'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]

Thanks

Thanks for your help

That explained it.

Cheers

Harlequeen

 

[tommybeer]

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]

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:

[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:

[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]

@ 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]

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

[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

[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]

 

[tommybeer]

great it works

but i have another question )

now i can save, modify,

how can i delete a record?

greetz

 

[jmcilhinney]

DELETE FROM Table1 WHERE KeyField = ?

Then add a parameter for the primary key field.

 

[tommybeer]

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]

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]

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

 

[jmcilhinney]

I know a bit about printing but I've never specifically printed the contents of a DataGrid. Let's cross that bridge when you come to it.

 

[Paszt]

Here is a link to an article on Codeproject.com that inlcudes a custom DataGrid control which includes print preview and print support: Printing with a custom DataGrid
And here's an article that deals with printing a DataSet: How do I print a DataSet in VB.NET?