u795696
asked on
VB.NET Updating data SQL using listview and dataset
Hello,
i am writing a Windows Forms application that allows users to manipulate data in a SQL 2008 database.
I use 3 listboxes and 1 listview on my form to hold the data from various tables in the database. The listboxes and listview are populated using a specific data-adapter that fill a specific dataset.
When I remove a row in the listview, i am searching the dataset for that particular row and I delete the row from the dataset.
After that I use the specific dataadapter again to send the update command to the SQL-server.
Unfortunately the data does not get updated but I do not get any errors.
I am using the following code:
What am I doing wrong? I've searched the internet for hours and hours but am not able to solve it.... any help would be appreciated.
i am writing a Windows Forms application that allows users to manipulate data in a SQL 2008 database.
I use 3 listboxes and 1 listview on my form to hold the data from various tables in the database. The listboxes and listview are populated using a specific data-adapter that fill a specific dataset.
When I remove a row in the listview, i am searching the dataset for that particular row and I delete the row from the dataset.
After that I use the specific dataadapter again to send the update command to the SQL-server.
Unfortunately the data does not get updated but I do not get any errors.
I am using the following code:
MsgBox("Really delete PI-tag: " & lstPoints.SelectedItems(0).Text & "?")
For Each dr As DataRow In dsPoints.Tables(0).Rows
If dr("tag_name") = lstPoints.SelectedItems(0).Text Then
dr.Delete()
dsPoints.AcceptChanges()
Exit For
End If
Next
For Each lstItem As ListViewItem In lstPoints.SelectedItems
lstItem.Remove()
Next
pointsbuilder.GetDeleteCommand()
adapPoints.Update(dsPoints, dsPoints.Tables(0).TableName.ToString)
What am I doing wrong? I've searched the internet for hours and hours but am not able to solve it.... any help would be appreciated.
ASKER
Hi,
I've tried your suggestion but that still does not remove the row from the database. When I refresh my selection the data shows again.
I am sure that the row is found and removed from the dataset because the rows.count property of the dataset show a row less after the remove command.
I've tried your suggestion but that still does not remove the row from the database. When I refresh my selection the data shows again.
I am sure that the row is found and removed from the dataset because the rows.count property of the dataset show a row less after the remove command.
Can you display the code where your data adapter's updatecommand property is being set?
ASKER
In my first post, line 13 if that is what you mean...
No that's your update method call
I mean you need to set updatecommand property for data adapter object. Something like this:
// Create the UpdateCommand.
command = new SqlCommand(
"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID", connection);
// Add the parameters for the UpdateCommand.
command.Parameters.Add("@C ustomerID" , SqlDbType.NChar, 5, "CustomerID");
command.Parameters.Add("@C ompanyName ", SqlDbType.NVarChar, 40, "CompanyName");
SqlParameter parameter = command.Parameters.Add(
"@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
parameter.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand = command;
You could see following MSDN link for more details
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx
I mean you need to set updatecommand property for data adapter object. Something like this:
// Create the UpdateCommand.
command = new SqlCommand(
"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID", connection);
// Add the parameters for the UpdateCommand.
command.Parameters.Add("@C
command.Parameters.Add("@C
SqlParameter parameter = command.Parameters.Add(
"@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
parameter.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand = command;
You could see following MSDN link for more details
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx
ASKER
Ohw, that you mean :) Sorry, I'm new with working with databases via .NET
I understood that .NET has a thing called the SQLCommandBuilder that constructs all the necessary commands for you.
I've created an instance of the SQLCommandBuilder just after I filled the data-adapter wich filles the dataset that is used for the listview.
As you see at line 12 of my first post, I request the delete command from that specific SQLCommandbuilder and the next line should update my table...
At least that was what I was hoping for :-)
I understood that .NET has a thing called the SQLCommandBuilder that constructs all the necessary commands for you.
I've created an instance of the SQLCommandBuilder just after I filled the data-adapter wich filles the dataset that is used for the listview.
As you see at line 12 of my first post, I request the delete command from that specific SQLCommandbuilder and the next line should update my table...
At least that was what I was hoping for :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have created the following delete command:
This works OK, the record gets deleted from the database.
Is this the best way to perform the deletion of a row in the database? It is not using the dataset and the dataadapter in any way.
Can you also create a delete command for the dataadapter and then use the dataadapter to delete the row instead of using the code above?
I do not really care about performance as it is a very small database and only 2 or 3 people are working in it at the same time. It is also not used very often as it is a configuration database for another program.
Dim delstring As String
delstring = "Delete from tbllist_points where group_name='" & lboGroups.Text & _
"' and list_name='" & lboList_Area.Text & "' and list_area_name='" & lboArea.Text & "'"
MsgBox(delstring)
Try
Dim ret
Dim delCMD As SqlCommand = New SqlCommand(delstring, dbCon)
ret = delCMD.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
This works OK, the record gets deleted from the database.
Is this the best way to perform the deletion of a row in the database? It is not using the dataset and the dataadapter in any way.
Can you also create a delete command for the dataadapter and then use the dataadapter to delete the row instead of using the code above?
I do not really care about performance as it is a very small database and only 2 or 3 people are working in it at the same time. It is also not used very often as it is a configuration database for another program.
You could create InsertCommand, UpdateCommand and DeleteCommand for dataadapter object
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.deletecommand.aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.deletecommand.aspx
See this link for example of Remove method of DataRowCollection object
http://msdn.microsoft.com/en-us/library/system.data.datarowcollection.remove.aspx
Also make sure that you are able to debug through statements inside your following condition
If dr("tag_name") = lstPoints.SelectedItems(0)
........
Might be possible for some reason that condition is never holding true