none
help with data manipulation... RRS feed

  • Question

  • Hi,

    I'm having trouble with some of the basics of data manipulation.  I've been able to retrieve and navigate a table.  But am having trouble making any changes.  The following code uses 2 text boxes and a series of toolbar buttons to retrieve data from the northwind customers table.  However, my save, delete, and insert commands are not working.  I'm simply trying to duplicate the behavior of the old ado recordsets using the new .net data objects.

    Thanks for any help

    Ray
    Imports System.Data.SqlClient
    Public Class Form1
    
        Dim ds As DataSet
        Dim cn As SqlConnection
        Dim da As SqlDataAdapter
        Dim cb As SqlCommandBuilder
    
        Protected WithEvents bs As New BindingSource()
    
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
            Dim connectString As String = _
            "Integrated Security=SSPI;Persist Security Info=False;" + _
            "Initial Catalog=Northwind;Data Source=localhost\SQLEXPRESS"
    
    
            cn = New SqlClient.SqlConnection(connectString)
            cn.Open()
    
            da = New SqlDataAdapter("Select * From dbo.Customers", cn)
    
            cb = New SqlCommandBuilder(da)
            ds = New DataSet
    
            'Dim da As New SqlDataAdapter("Select * From dbo.Customer", cn)
            da.Fill(ds, "Customers")
    
            cn.Close()
    
            bs.DataSource = ds.Tables("Customers")
    
            ' Bind the CompanyName field to the TextBox control.
            Me.TextBox1.DataBindings.Add("Text", bs, "CompanyName")
            Me.TextBox2.DataBindings.Add("Text", bs, "ContactName")
    
        End Sub
    
        Private Sub tsFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsFirst.Click
            bs.MoveFirst()
        End Sub
    
        Private Sub tsPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsPrevious.Click
            bs.MovePrevious()
        End Sub
    
        Private Sub tsNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsNext.Click
            bs.MoveNext()
        End Sub
    
        Private Sub tsLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsLast.Click
            bs.MoveLast()
        End Sub
    
        Private Sub tsSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsSave.Click
            cn.Open()
            ds.AcceptChanges()
            da.Update(ds.Tables("Customers"))
            cn.Close()
        End Sub
    
        Private Sub tsCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsCancel.Click
            ds.RejectChanges()
        End Sub
    
        Private Sub tsDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsDelete.Click
            cn.Open()
            ds.Tables("Customers").Rows.RemoveAt(bs.Position)
            da.Update(ds.Tables("Customers"))
            cn.Close()
        End Sub
    
        Private Sub tsNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsNew.Click
            ds.Tables("Customers").Rows.Add()
        End Sub
    End Class
    
    • Moved by eryang Wednesday, October 14, 2009 1:51 AM (From:.NET Base Class Library)
    Tuesday, October 13, 2009 4:47 AM

Answers

  • Hi Ray,

     

    I modify some parts of your code.

     

    Imports System.Data.SqlClient

    Public Class Form1

     

        Dim ds As DataSet

        Dim cn As SqlConnection

        Dim da As SqlDataAdapter

        Dim cb As SqlCommandBuilder

     

        Protected WithEvents bs As New BindingSource()

     

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

     

            Dim connectString As String = _

            "Integrated Security=SSPI;Persist Security Info=False;" + _

            "Initial Catalog=Northwind;Data Source=localhost\SQLEXPRESS"

     

     

            cn = New SqlClient.SqlConnection(connectString)

            cn.Open()

     

            da = New SqlDataAdapter("Select * From dbo.Customers", cn)

     

            cb = New SqlCommandBuilder(da)

            ds = New DataSet

     

            'Dim da As New SqlDataAdapter("Select * From dbo.Customer", cn)

            da.Fill(ds, "Customers")

     

            cn.Close()

     

            bs.DataSource = ds.Tables("Customers")

     

            ' Bind the CompanyName field to the TextBox control.

            Me.TextBox1.DataBindings.Add("Text", bs, "CompanyName")

            Me.TextBox2.DataBindings.Add("Text", bs, "ContactName")

     

        End Sub

     

        Private Sub tsFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsFirst.Click

            bs.MoveFirst()

        End Sub

     

        Private Sub tsPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsPrevious.Click

            bs.MovePrevious()

        End Sub

     

        Private Sub tsNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsNext.Click

            bs.MoveNext()

        End Sub

     

        Private Sub tsLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsLast.Click

            bs.MoveLast()

        End Sub

     

        Private Sub tsSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsSave.Click

            cn.Open()

            bs.EndEdit()

            da.Update(ds.Tables("Customers"))

            ds.AcceptChanges()’ It means to clear all the changes to the table. Call it after updating.

            cn.Close()

        End Sub

     

        Private Sub tsCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsCancel.Click

            ds.RejectChanges()

        End Sub

     

        Private Sub tsDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsDelete.Click

            cn.Open()

            bs.RemoveCurrent()

            bs.EndEdit()

            da.Update(ds.Tables("Customers"))

            cn.Close()

        End Sub

     

        Private Sub tsNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsNew.Click

            bs.Add()

        End Sub

    End Class

     

    DataSet.AcceptChanges clears all the changes made to the table. So you can not use it before updating. And the changes you makes to datatable actually not recognized by the bindingsource nor the dataadapter. So it is a good idea to make it known to bindingsource.

     

     

    Does this work for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Monday, October 19, 2009 1:38 AM
    Thursday, October 15, 2009 5:28 AM
  • Hi Ray,

    If you just one to set the current bindingsource's record, you can wirte like this,

    bs.Current("ModifiedBy") = sUserID

    If you want to modify it in to table, you can write like this,

    ds.Tables("Customer")(rowindex)("ModifiedBy") = sUserID


    Does this works for you?


    Best Regards
    Yichun Feng

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by ray.n Tuesday, October 20, 2009 8:09 PM
    Tuesday, October 20, 2009 2:05 AM

All replies

  • Hi Ray,

     

    I modify some parts of your code.

     

    Imports System.Data.SqlClient

    Public Class Form1

     

        Dim ds As DataSet

        Dim cn As SqlConnection

        Dim da As SqlDataAdapter

        Dim cb As SqlCommandBuilder

     

        Protected WithEvents bs As New BindingSource()

     

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

     

            Dim connectString As String = _

            "Integrated Security=SSPI;Persist Security Info=False;" + _

            "Initial Catalog=Northwind;Data Source=localhost\SQLEXPRESS"

     

     

            cn = New SqlClient.SqlConnection(connectString)

            cn.Open()

     

            da = New SqlDataAdapter("Select * From dbo.Customers", cn)

     

            cb = New SqlCommandBuilder(da)

            ds = New DataSet

     

            'Dim da As New SqlDataAdapter("Select * From dbo.Customer", cn)

            da.Fill(ds, "Customers")

     

            cn.Close()

     

            bs.DataSource = ds.Tables("Customers")

     

            ' Bind the CompanyName field to the TextBox control.

            Me.TextBox1.DataBindings.Add("Text", bs, "CompanyName")

            Me.TextBox2.DataBindings.Add("Text", bs, "ContactName")

     

        End Sub

     

        Private Sub tsFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsFirst.Click

            bs.MoveFirst()

        End Sub

     

        Private Sub tsPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsPrevious.Click

            bs.MovePrevious()

        End Sub

     

        Private Sub tsNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsNext.Click

            bs.MoveNext()

        End Sub

     

        Private Sub tsLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsLast.Click

            bs.MoveLast()

        End Sub

     

        Private Sub tsSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsSave.Click

            cn.Open()

            bs.EndEdit()

            da.Update(ds.Tables("Customers"))

            ds.AcceptChanges()’ It means to clear all the changes to the table. Call it after updating.

            cn.Close()

        End Sub

     

        Private Sub tsCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsCancel.Click

            ds.RejectChanges()

        End Sub

     

        Private Sub tsDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsDelete.Click

            cn.Open()

            bs.RemoveCurrent()

            bs.EndEdit()

            da.Update(ds.Tables("Customers"))

            cn.Close()

        End Sub

     

        Private Sub tsNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsNew.Click

            bs.Add()

        End Sub

    End Class

     

    DataSet.AcceptChanges clears all the changes made to the table. So you can not use it before updating. And the changes you makes to datatable actually not recognized by the bindingsource nor the dataadapter. So it is a good idea to make it known to bindingsource.

     

     

    Does this work for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Monday, October 19, 2009 1:38 AM
    Thursday, October 15, 2009 5:28 AM
  • Hi,

    Thanks.  Most of your suggestion worked great.  The only problem I had was that the bs.Add() command wanted parameters for the data to add.  But it did lead me to try bs.AddNew, which worked.  I do have some more questions, but I'll post them to new threads as I don't want to unmark your response as the answer.

    Thanks,

    Ray



    Monday, October 19, 2009 4:16 PM
  • Hi,

    Actually...I do have a related question.  If I want to manipulate the data in a field in code...what would be the best way to do that?  For example, the Save button code is as follows:

        Private Sub tsSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsSave.Click

            cn.Open()

            bs.EndEdit()

            da.Update(ds.Tables("Customers"))

            ds.AcceptChanges()’ It means to clear all the changes to the table. Call it after updating.

            cn.Close()

        End Sub


    If, prior to saving, I wanted to update a field called "ModifiedBy" with the name of the user making the changes, how do I do that?  In VB6 it would have been something like

    rs.fields("ModifiedBy") = sUserID
    rs.Update

    I would have thought something like this:

            ds.Tables("Customers").Columns("ModifiedBy") = sUserID

    but the columns property refers to the column object...not the data itself.

    I appreciate the help.  I'm still trying to get my head around all the different objects required to get anything done in .net.

    Ray

     

     

    Monday, October 19, 2009 4:38 PM
  • Hi Ray,

    If you just one to set the current bindingsource's record, you can wirte like this,

    bs.Current("ModifiedBy") = sUserID

    If you want to modify it in to table, you can write like this,

    ds.Tables("Customer")(rowindex)("ModifiedBy") = sUserID


    Does this works for you?


    Best Regards
    Yichun Feng

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by ray.n Tuesday, October 20, 2009 8:09 PM
    Tuesday, October 20, 2009 2:05 AM
  • Hi,


    bs.Current("ModifiedBy") = sUserID

    That works great.  It's exactly what I was looking for.  Thanks.
    Tuesday, October 20, 2009 8:10 PM