#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    72
    Rep Power
    6

    Help with ADO.net for an ADO programmer


    I have worked with ADO for a while but am having trouble with ADO.net. Below is code I have tried with no luck getting the database to update.

    The code opens the table and can read the values present but I can't get it to update or add new rows, it looks like it is in code but no changes are made to the underlying database.



    Code:
    	Dim mySqlConnection As New SqlConnection
            Dim DS As DataSet
            Dim MyCommand As SqlDataAdapter
    
    
            mySqlConnection.ConnectionString = "Server=Myserver\SQLEXPRESS2012;Database=Mydatabase;User Id=Myuser;Password=mypassword;"
            mySqlConnection.Open()
    
            MyCommand = New SqlDataAdapter("select * from companyinfo", mySqlConnection)
    
            DS = New DataSet
            MyCommand.Fill(DS)
    
            '--------------------------------------
            Dim T As New DataTable
            T = DS.Tables(0)
            Dim myRow As DataRow
            myRow = T.NewRow()
    
            myRow("Company") = 2
            myRow("username") = "NortWest Trade Company"
            myRow("password") = "NortWest Trade Company"
    
            ' Add the row.
            T.Rows.Add(myRow)
            T.AcceptChanges()
    
            DS.AcceptChanges()
            MyCommand.Update(DS.Tables(0))
            '------------------------------------------------------
    
    
    
    	'another try
    	'--------------------------------------------------------	
            Dim DT As DataTable = DS.Tables(0)
    
            Dim i As Integer
            For i = 0 To DT.Rows.Count - 1
                Dim DR As DataRow = DT.Rows(i)
    
                Dim someValue As String = DR("username").ToString().Trim()
    
                DR.BeginEdit()
                DR("username") = "edited"
                DR.AcceptChanges()
                DT.AcceptChanges()
    
            Next i
            MyCommand.Update(DS.Tables(0))
    
            mySqlConnection.Close()
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    72
    Rep Power
    6

    Finally found a site with code I could get to work


    I was amazed to find ADO.net needs FIVE components to do what ADO does with 1. But after seeing how it works I am learning more about ADO.net. It appears the data once retrieved is no longer connected to the database, so it has to be manipulated then updated back to database with the appropriate update, insert or delete SQL string commands, which are generated by SQLcommandbuilder based on what you did with the disconnected dataset.


    Site is below:
    http://support.microsoft.com/kb/301248

    Code is below, in case it can help somebody else:
    Code:
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    
    Module Module1
    
        Sub Main()
            Dim sConnectionString As String
            ' Modify the following code to correctly connect to your SQL Server.
            sConnectionString = "Password=StrongPassword;User ID=UserName;" & _
                                "Initial Catalog=pubs;" & _
                                "Data Source=(local)"
    
            Dim objConn As New SqlConnection(sConnectionString)
            objConn.Open()
    
            ' Create an instance of a DataAdapter.
            Dim daAuthors As _
                New SqlDataAdapter("Select * From Authors", objConn)
    
            ' Create an instance of a DataSet, and retrieve data from the Authors table.
            Dim dsPubs As New DataSet("Pubs")
            daAuthors.FillSchema(dsPubs, SchemaType.Source, "Authors")
            daAuthors.Fill(dsPubs, "Authors")
    
            '*****************
            'BEGIN ADD CODE 
            ' Create a new instance of a DataTable
            Dim tblAuthors As DataTable
            tblAuthors = dsPubs.Tables("Authors")
    
            Dim drCurrent As DataRow
            ' Obtain a new DataRow object from the DataTable.
            drCurrent = tblAuthors.NewRow()
    
            ' Set the DataRow field values as necessary.
            drCurrent("au_id") = "993-21-3427"
            drCurrent("au_fname") = "George"
            drCurrent("au_lname") = "Johnson"
            drCurrent("phone") = "800 226-0752"
            drCurrent("address") = "1956 Arlington Pl."
            drCurrent("city") = "Winnipeg"
            drCurrent("state") = "MB"
            drCurrent("contract") = 1
    
            'Pass that new object into the Add method of the DataTable.Rows collection.
            tblAuthors.Rows.Add(drCurrent)
            MsgBox("Add was successful.")
    
            'END ADD CODE    
            '*****************
            'BEGIN EDIT CODE
    
            drCurrent = tblAuthors.Rows.Find("213-46-8915")
            drCurrent.BeginEdit()
            drCurrent("phone") = "342" & drCurrent("phone").ToString.Substring(3)
            drCurrent.EndEdit()
            MsgBox("Record edited successfully")
    
            'END EDIT CODE   
            '*****************
            'BEGIN SEND CHANGES TO SQL SERVER
    
            Dim objCommandBuilder As New SqlCommandBuilder(daAuthors)
            daAuthors.Update(dsPubs, "Authors")
            MsgBox("SQL Server updated successfully" & chr(13) & "Check Server explorer to see changes")
    
            ' END SEND CHANGES TO SQL SERVER 
            '*****************
            'BEGIN DELETE CODE 
    
            drCurrent = tblAuthors.Rows.Find("993-21-3427")
            drCurrent.Delete()
            MsgBox("Record deleted successfully")
    
            'END DELETE CODE 
            '*****************
            ' CLEAN UP SQL SERVER
            daAuthors.Update(dsPubs, "Authors")
            MsgBox("SQL Server updated successfully" & Chr(13) & Chr(13) & "Check Server Explorer to see changes")
        End Sub
    
    End Module

IMN logo majestic logo threadwatch logo seochat tools logo