• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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

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
xrosem
Asked:
xrosem
  • 3
  • 3
1 Solution
 
andrewharrisCommented:
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
 
wguerramCommented:
'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
 
wguerramCommented:
'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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
andrewharrisCommented:
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
 
wguerramCommented:
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
 
andrewharrisCommented:
Its standard SQL...Not sure when it was introdocued to Access but it works in 2003.

Andrew
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now