Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-29
6
Medium Priority
?
255 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
Comment
Question by:xrosem
[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
  • 3
  • 3
6 Comments
 
LVL 4

Expert Comment

by:andrewharris
ID: 12449449
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
ID: 12450026
'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
ID: 12466100
'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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Accepted Solution

by:
andrewharris earned 2000 total points
ID: 12467635
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
ID: 12468181
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
ID: 12468715
Its standard SQL...Not sure when it was introdocued to Access but it works in 2003.

Andrew
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

636 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