?
Solved

VB.NET Updating data SQL using listview and dataset

Posted on 2013-06-13
9
Medium Priority
?
731 Views
Last Modified: 2013-11-22
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:

        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)

Open in new window


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.
0
Comment
Question by:u795696
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 12

Expert Comment

by:sachitjain
ID: 39244638
Can you try using dsPoints.Tables(0).Rows.Remove(dr) statement instead of  dr.Delete() statement?

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).Text Then
........


Might be possible for some reason that condition is never holding true
0
 

Author Comment

by:u795696
ID: 39247647
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.
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 39247699
Can you display the code where your data adapter's updatecommand property is being set?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:u795696
ID: 39252492
In my first post, line 13 if that is what you mean...
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 39252937
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("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    command.Parameters.Add("@CompanyName", 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
0
 

Author Comment

by:u795696
ID: 39252972
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 :-)
0
 
LVL 12

Accepted Solution

by:
sachitjain earned 200 total points
ID: 39253000
I guess you need to set updatecommand explicitly. Try doing that and let me know if you face issues.
0
 

Author Comment

by:u795696
ID: 39255552
I have created the following delete command:

        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

Open in new window


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.
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 39255560
You could create InsertCommand, UpdateCommand and DeleteCommand for dataadapter object

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.deletecommand.aspx
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question