Solved

Either Update DataTable to Access Database or send DataTable to a text file

Posted on 2004-10-29
251 Views
Last Modified: 2008-01-09
I really need this solution and I am giving full points. I have looked over every  page and non of them have answered my question. I have a database and I need to clean data daily on a particular table. There are two tables. One has the correct number of records by SSN's and the other table has too many.  I am developing a program to that will get the SSN from the table that correlates to the database table that has too much data and compare it against the other table that has correct records, which is a lookup table to determine if I should delete the row in the table that correlates to the database table . I used two datatables one for the database table and the other as a lookup table. I get the first SSN record of the database table then see if it is in the other table. If it is, I do not delete the row from the database table - if its not in the lookup table I delete the row in the datatable that correlates to my database table. My code first sets the two datatables to my database tables. Then I use this code to do the work. Here is the code:

' Declare connection variables
    dim connectionString as string
    dim queryString as string
    dim data as new dataset()
    dim dbConnection as OleDbConnection
    dim dataAdapter as OleDbDataAdapter
    Dim table1 As New DataTable("table1")
    Dim table2 As New DataTable("table2")



sub page_load(sender as Object, E as EventArgs)

    table1.PrimaryKey = New DataColumn() {table1.Columns("SSN")}

    setTable1()      'SET DATA TABLE
    setTable2()      'SET LOOKUP TABLE



    msgbox(countRows(table1)) ' Count rows before function call
    delete(table1, table2)     ' Call the delete function
    msgbox(countRows(table1))  ' Count rows after fuction call to see what happened

    CreateCmdsAndUpdate()  ' Call the update function that gives me the error





end sub

Public sub CreateCmdsAndUpdate()


   
  ' =================================================================
  ' generate the update commands
  Dim commandBuilder    As OleDbCommandBuilder

  commandBuilder = New OleDbCommandBuilder(dataAdapter)
  dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand()
  dataAdapter.InsertCommand = commandBuilder.GetInsertCommand()
  dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand()


  ' =================================================================



End sub



public sub delete(Byval myTable1 As DataTable, Byval myTable2 As DataTable)

    Dim numRows_DatabaseTable as integer

    numRows_DatabaseTable = countRows(myTable1)

    dim Counter1 as integer = 0
    Dim Counter2 as integer = 0
    dim databaseSSN as string
    dim lookupSSN as string
    dim foundRow as datarow()
    dim deleteRow as datarow()
    dim row as datarow
    dim strString as string

    Do While Counter1 < numRows_DatabaseTable

        databaseSSN = getSSN(myTable1, counter1)
        dim i as string = "SSN = " & "'" + databaseSSN & "'"
        foundRow = myTable2.Select(i)

        for each row in foundRow

             strString = cstr(foundRow(Counter2)("SSN"))

        next

        Counter2 = 0

            'msgbox("Count is " & cstr(Counter))
            If databaseSSN = strString then
                    msgbox(cstr(Counter1))
                    msgbox("Found SSN")
                Else
                    msgbox("Deleting SSN " + strString)
                    table1.Rows(Counter1).delete
            End If



    Counter1 += 1
    Loop

    table1.acceptchanges

    msgbox("Number rows in table before delete = " + cstr(numRows_DatabaseTable))
    numRows_DatabaseTable = countRows(myTable1)
    msgbox("Number rows in table after delete = " + cstr(countRows(myTable1)))
    DataGrid3.DataSource = table1
    DataGrid3.DataBind()

end sub




Private function countRows(Byval myTable As DataTable)as string

    ' For each table in the DataSet, print the values of each row.

    dim numRow as integer

        ' For each row, print the values of each column.
        Dim Row As DataRow
        For Each Row In  myTable.Rows
            numRow += 1
        Next Row


    return cstr(numRow)

End function




Public function getSSN(Byval myTable As DataTable, byval row as integer)

    dim returnString as string
    'msgbox(cstr(row))
    returnString = myTable.Rows(row)(0)

    return returnString

End function



private function SSN_Find(Byval myTable As DataTable, byval ssn as String)as string

    Dim deleted as string
    Dim selectedRows() As DataRow
    dim i as string = "SSN = " & "'" + ssn & "'"
    msgbox(i)

    ' Find the row to change
    selectedRows = myTable.Select(i)

    return deleted

End function


private sub setTable1()

    'set the connection and query details
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                    "Data Source=C:\Documents and Settings\MICHAEL.ALLEN.ROSE\My Documents\ClearanceData.mdb"
    queryString = "SELECT SSN, CURRENT_RANK, NAME, DOB,UIC, PRP_ASG_STAT_CD, PRP_INELG_RSN_CD, PRP_QUAL_CD, SCTY_CLR_AUTHTY_CD, SCTY_CLR_DT, SCTY_CLR_LVL_CD, SCTY_INVES_CMPL_DT, SCTY_INVES_INIT_TYPE, CTZSP_CNTRY_CD, PERSON_BIRTH_CITY_NM, PERSON_BIRTH_STATE_AB, PPA_CD, PSC_CD FROM 13_FG_CLRNC_INFO;"


    dbConnection = New OledbConnection(connectionString)
    dataAdapter = New OleDbDataAdapter(queryString, dbConnection)
    dataAdapter.Fill(data, "13_FG_CLRNC_INFO")
    DataGrid1.DataSource = data.Tables("13_FG_CLRNC_INFO")
    DataGrid1.DataBind()
    table1 = data.Tables("13_FG_CLRNC_INFO")


end sub



private sub setTable2()



    'set the connection and query details

    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                    "Data Source=C:\Documents and Settings\MICHAEL.ALLEN.ROSE\My Documents\ClearanceData.mdb"
    queryString = "SELECT SSN FROM LookUpTable;"


    dbConnection = New OledbConnection(connectionString)
    dataAdapter = New OleDbDataAdapter(queryString, dbConnection)


    dataAdapter.Fill(data, "LookUpTable")

    table2 = data.Tables("LookUpTable")

    DataGrid2.DataSource = table2
    DataGrid2.DataBind()

end sub


Everything works well except for when I call the dataAdapter to Update it states: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. I used this code to set the primary key - found on this website:  table1.PrimaryKey = New DataColumn() {table1.Columns("SSN")}. SSN is the primary key in the table I need to update. I really need to update this table and if I have to I would be willing to send the data in the datatable that is correct now to a text file then truncate the darn database table then append the new information. If you need any more information please let me know.


Thanks to anyone who can help

Mike

0
Question by:xrosem
    6 Comments
     
    LVL 4

    Expert Comment

    by:andrewharris
    Could this not be done straight at the database? With a DELETE Statement, ie

    DELETE FROM table1 WHERE primary_key NOT IN SELECT primary_key FROM table2


    Andrew
    0
     
    LVL 8

    Expert Comment

    by:wguerram
    'set the connection and query details
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                        "Data Source=C:\Documents and Settings\MICHAEL.ALLEN.ROSE\My Documents\ClearanceData.mdb"

        dbConnection = New OledbConnection(connectionString)

        dbConnection.Open

       Dim cmd As New OleDb.OleDbCommand
       cmd.Connection = dbConnection

            Dim sbr As New System.Text.StringBuilder
            Dim queryString As String

            Dim dr As OleDb.OleDbDataReader        

            queryString = "SELECT SSN FROM LookUpTable;"
            cmd.CommandText = queryString

            'Get all SSN's
            dr = cmd.ExecuteReader
            Do While dr.Read
                sbr.Append("'" & dr.GetString(0) & "',")
            Loop

            If Not dr.IsClosed Then dr.Close()

            sbr.Remove(sbr.Length - 1, 1) ' Remove the extra comma

            'Delete records that are not in the lookup table
            queryString = "DELETE FROM 13_FG_CLRNC_INFO WHERE NOT IN(" & sbr.ToString & ")"
            cmd.CommandText = queryString
            cmd.ExecuteNonQuery
    0
     
    LVL 8

    Expert Comment

    by:wguerram
    'Sorry i had a mistake in the delete query:

    'set the connection and query details
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                        "Data Source=C:\Documents and Settings\MICHAEL.ALLEN.ROSE\My Documents\ClearanceData.mdb"

        dbConnection = New OledbConnection(connectionString)

        dbConnection.Open

       Dim cmd As New OleDb.OleDbCommand
       cmd.Connection = dbConnection

            Dim sbr As New System.Text.StringBuilder
            Dim queryString As String

            Dim dr As OleDb.OleDbDataReader        

            queryString = "SELECT SSN FROM LookUpTable;"
            cmd.CommandText = queryString

            'Get all SSN's
            dr = cmd.ExecuteReader
            Do While dr.Read
                sbr.Append("'" & dr.GetString(0) & "',")
            Loop

            If Not dr.IsClosed Then dr.Close()

            sbr.Remove(sbr.Length - 1, 1) ' Remove the extra comma

            'Delete records that are not in the lookup table
            queryString = "DELETE FROM 13_FG_CLRNC_INFO WHERE NOT SSN IN(" & sbr.ToString & ")"
            cmd.CommandText = queryString
            cmd.ExecuteNonQuery
    0
     
    LVL 4

    Accepted Solution

    by:
    That exact same block of code could be run as just

        Dim dbConnection = OleDb.OledbConnection
        Dim cmd As New OleDb.OleDbCommand
        Dim connectionString as String
        Dim queryString As String

        'set the connection and query details
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\MICHAEL.ALLEN.ROSE\My Documents\ClearanceData.mdb"

        dbConnection = New OledbConnection(connectionString)

        dbConnection.Open

        cmd.Connection = dbConnection

        'Delete records that are not in the lookup table
        queryString = "DELETE FROM 13_FG_CLRNC_INFO WHERE NOT SSN IN(SELECT SSN FROM LookUpTable)"
        cmd.CommandText = queryString
        cmd.ExecuteNonQuery

    Andrew
    0
     
    LVL 8

    Expert Comment

    by:wguerram
    I didn't know the jet engine supported this:

    "DELETE FROM 13_FG_CLRNC_INFO WHERE NOT SSN IN(SELECT SSN FROM LookUpTable)"

    Andrew, Where did you find the documentation for that statement?
    0
     
    LVL 4

    Expert Comment

    by:andrewharris
    Its standard SQL...Not sure when it was introdocued to Access but it works in 2003.

    Andrew
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now