Hi, I have another question regarding my database application. After my other question being answered here, I was able to populate a listbox with data from a column of an Access database.
I now need to edit a specific item of the listbox and then update it in the database. The idea is the following: below the Listbox is a Textbox and a button labeled 'Update'. The user selects an item from the listbox and then writes something in the textbox below. When the Save button is pressed, the content of the textbox replace the value of that specific item in the database.
How can this be achieved Or is there an easier way similar to what I have in mind And I don't have a DataSource associated with my project.
(please refer to the code for filling the listbox in my previous question here. Feel free to re-adapt it for this situation)
Thank you,
FC
Updating a specific row in Access database
bonni
Yes of course. I already made some adaptations to the code for filling the listbox, so if I made any conflict just warn me.
This is a test application with a listbox1, a textbox1 and a button1 in the main form. The code is as follows:
Imports System.Data.OleDb
Public Class Form1
Dim sqlStr As String
Dim cmd As OleDbCommand
Dim conn As OleDbConnection
Dim ds As New DataSet
Dim da As OleDbDataAdapter
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
sqlStr = "Select * From MyTable"
conn = New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\database.mdb;")
da = New OleDbDataAdapter(sqlStr, conn)
da.Fill(ds, "MyTable")
ListBox1.DataSource = ds.Tables("MyTable")
ListBox1.DisplayMember = "MyField"
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
ds.Tables("MyTable").Rows(ListBox1.SelectedIndex)("MyField") = TextBox1.Text
'da.Update(...)
'da.UpdateCommand = New OleDbCommand("Update MyTable Set MyField = '" + TextBox1.Text + "' Where MyField = '" + Me.ListBox1.SelectedItem.ToString() + "'")
'da.Update(...)
End Sub
End Class
Strangely enough, I can't seem to reproduce the exceptions again. The above code now works and now I just need to update the database with the modified data. So how can I quickly do this final step
Thank you
Rob Hounsell
Thanks, it certainly looks like it helps, but I will have to try it tomorrow. I really believe this can be the answer though. Tomorrow I'll post the results :-).
Thank you,
FC
Daikoku
yes I am also running into this issue. I will see what I can come up with however for now, make sure you place the square brackets [] around each name of the field in your Update statement.
example:
UPDATE MyTable SET [MyField] = @param1 WHERE [ID] = @theID
This will prevent any errors occuring during execution and also perhaps may resolve the current issue, it is a "safety measure" as if you use any reserved keywords for field names, it will get confused and not apply the changes.
I will investigate further
OscarKwok
hey no worries at all :-) It's all about trial and error
as for where the update command code should go, this is completely up to you. you can have it in either another button called "Update" so the user clicks on it and it updates everything by the click of the button, or by some other way.
if you do the above recommended approach, I would suggest you place, at the top of your class file (as a global property, just after "Public Class classname"):
as for the error you suggested, sorry this was my fault, which I have fixed now. you should be giving the Update() method the dataset. So Overall your code *may* look like the following, with some exceptions of course:
public class MyClass
Dim theDataSet as new DataSet()
Dim theOleDbDataAdapter as new OleDbDataAdapter()
Dim theConnection as new OleDbConnection(ConnectionString)
....
.....
private sub DoGetAndBindData()
Me.theDataSet = new DataSet()
Me.theOleDbDataAdapter = new OleDbDataAdapter("SELECT * FROM tableName", Me.theConnection)
Me.theOleDbDataAdapter.Fill(Me.theDataSet)
Me.theListBox.DataSource = Me.theDataSet.Tables(0).DefaultView
Me.theListBox.DisplayMember = ColumnName
end sub
private sub DoUpdateData()
Me.theDataAdapter.UpdateCommand = new OleDbCommand("UPDATE tableName SET Field1 = @param1 WHERE [ID] = @theID")
Dim theParam1 as new OleDbParameter("@param1", OleDbType.DataType, ColumnSize)
theParam1.Value = "YourNewValue"
Dim theIDParam as new OleDbParameter("@theID", OleDbType.Integer)
theIDParam.SourceColumn = "ID"
Me.theDataAdapter.UpdateCommand.Parameters.Add(theParam1)
Me.theDataAdapter.UpdateCommand.Parameters.Add(theIDParam)
Me.theDataAdapter.Update(Me.theDataSet)
end sub
the code above (again, you will have to customize/modify it to your needs):
does this help
feby
The code you provided in your first post (specifically this line "ds.Tables(0).Rows(Index)(ColumnName) = yourNewValue" sort of did the job. I could see the listbox items being updated as I typed a new value in the textbox and pressed the button. With the exception of the data in the listbox not being saved in the database (because the da.Update() not accepting 0 arguments).
I checked the overloads and I can use a DataSet, DataRows or DataTable as an argument (only don't know what to use).
Question, is the last code you provided really needed to make all this work I already have the data retreived from the database, and just need to update it with tha data from the updated listbox. I don't want to make this too complicated, so if you could just provide me what I need I would appreciate.
Thank you,
FC
Trevor Kennedy
Only one more question, the columnSize is supposed to be the lenght of the parameter right Does it means that I need to specify the number of characters of the textbox1 (using textbox1.text.lenght), or is it something else related to a column (as the name specifies)
Also, what do exaclty do those oledbParameters mean Specially the second parameter. I have a column in the database named 'ID', has it something to do with it
By the way, can you tell me if this modified part of your DoUpdateData() code is valid
Private Sub UpdateData()
Me.da.UpdateCommand = New OleDbCommand("UPDATE MyTable SET MyField = @param1 WHERE [ID] = @theID")
Dim theParam1 As New OleDbParameter("@param1", OleDbType.VarChar, TextBox1.Text.Length)
theParam1.Value = Me.TextBox1.Text
Dim theIDParam As New OleDbParameter("@theID", OleDbType.Integer)
theIDParam.SourceColumn = "ID"
Me.da.UpdateCommand.Parameters.Add(theParam1)
Me.da.UpdateCommand.Parameters.Add(theIDParam)
Me.da.Update(Me.ds, "MyTable")
End Sub
I had to add "MyTable" argument at the Fill (in DoGetAndBindData() Sub) and Update commands, or else the listbox wouldn't get filled (without the "MyTable" in the Fill command), or give me an exception (without the "myTable" in the Update command).
The code above does not give me any errors, but doesn't update the database either. I'm certainly doing something wrong, can you help me detect it
Thank you again,
FC
Seppe001
Hi, thanks for your suggestions. I've tried both and adapted them to my situation but I have some minor issues at those lines when I run the program (nothing that can't be resolved I'm sure). For instance, I first had to change OdbcCommand to OledbCommand, after that it gave me an exception "Object reference not set to an instance of an object." (don't know why this could happen. I have the 'new' keyword there).
In the second suggestion, it first gave me an exception that table 0 was not found, and when I put the actual name of the table instead of the index it gives me "Object reference not set to an instance of an object.". Also, the da.Update() command need arguments.
This just needs to be a little worked to make it work, I'm sure, and I don't have enough time at this very moment to try to develop your suggestions further. I will be able to reply to any further help in a few hours.
Thanks again,
FC
Soren D.
you do need that :-) That last piece of code is what the Update() command uses to update the values to the database. I always only supply code that is needed.
The code above does the following:
if you try to just call the Update() method without entering the code above, you will either experience:
how would you expect the Update() command to "just work" you need to give it the appropriate code in order to execute the update command, same thing happens with the INSERT, DELETE and SELECT commands :-)
the way this Update() function/method works is that it will see what rows have been marked as "edited" (row state) and will only execute the appropriate Update() command as necessary to commit the changes to the datasource (database in this case)
if you don't have an update method, you can't update
enric vives
Yes you're right, sorry. But let me put it in another way. Just look at the code I posted in my third post. In relation to that existing code, what/where will I need to replace/add this snippet you provided I don't want to make any mistakes, and maybe I'm just too tired because it's so late, and I can't think too well :-p but I could really use some straightforward directions, if you don't mind.
If I actually replace the code in the button1_click event with the one you provided in your las post (with the appropriate parts accordingly adapted of course, and assuming its place is in the button1_click event) the last line gets a blue underline with the error: "Overload resolution failed because no accessible 'Update' accepts this number of arguments."
Really sorry to bother you with this
Thank you,
FC
Neotech
well with the above solution in ken's example of binding the datasource to the listbox, I guess once you have replaced the item in the listbox, simply do an "Update()" on the dataAdapter. The dataAdapter will automatically update the datasource (database) with the updated items.
One way of doing it, is you can customize the way the UpdateCommand works, so in this example lets try (untested)
da.UpdateCommand = new OdbcCommand("UPDATE TableName SET FieldName = '" + theTextBox.Text + "' WHERE FieldName = '" + Me.theListBox.SelectedItem.ToString() + "'")
da.Update()
does this help
another way I guess would be to find the Row and column in the dataset table and then update the value, example:
ds.Tables(0).Rows(Index)(ColumnName) = yourNewValue
da.Update()
does this help
Dany V
Sorry, no. To be sure, I'll tell you what my database looks like. It has a table named MyTable, and this table contains two fienld, one named ID and other named MyField. It has 3 rows filled with data.
ID MyField
1 test1
2 test2
3 test3
I don't understant what I may be doing wrong, but I suppose it's something right under my nose..
Thank you again2,
FC
bennykavil
to update the database, you need to create the UpdateCommand appropriately. So overall:
dim theConnection as new OleDbConnection(ConnectionString)
Dim theOleDbCommand as new OleDbCommand("SELECT * FROM TableName", theConnection)
Dim theDataSet as new DataSet()
Dim theDataAdapter as new OleDbDataAdapter(theOleDbCommand)
theDataAdapter.UpdateCommand = new OleDbCommand("UPDATE TableName SET FieldName = @param1 WHERE [ID] = @theID", theConnection)
Dim theIDParameter as new OleDbParameter("@theID", OleDbType.Integer)
theIDParameter.SourceColumn = "ID"
Dim theParam1 as new OleDbParameter("@param1", OleDbType.TypeHere, ColumnSize)
theParam1.Value = "yourNewValue"
theDataAdapter.UpdateCommand.Parameters.Add(theIDParameter)
theDataAdapter.UpdateCommand.Parameters.Add(theParam1)
theDataAdapter.Fill(theDataSet) 'GET data
'After modifications of your data, call the update command:
theDataAdapter.Update(theDataSet)
of course, modify the code as needed
hope this helps and gives you a good starting point
Ed Allison
well yes, you have to check to see what parameters to give the objects as I was just referring to a method that exists in an object.
as for the null reference exceptions, can you post the code along with what line the exception occurs in
Prabu.
yes, the columnsize is the database field length.
OleDbParameters are just a number/collection of parameters, as I had shown in the code (theParam as new OleDbParameter......)
the SourceColumn for an OleDbParameter is the column name from the database for this parameter. Basically you set this IF you do not wish to update this column OR to get the recordID to SET your changes to. This will take the current value in the database and use it.
Can't see what is wrong, except for the ColumnSize property "TextBox1.Text.Length", should be the length of the source column (Database/table column)
What happens if you update the database like this, then close down the application - can you see the changes being made