[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update a table with counts from another table

Posted on 2005-04-18
3
Medium Priority
?
200 Views
Last Modified: 2010-04-23
Hello.  I'm trying to get a count on several columns in one Access table and insert them into another Access table.  The second table has no primary key, just a bunch of columns to be used for the counts.  The problem comes when I try to update the changes because there is no primary key.

The way we used to do it in vb6 was:

'only one column for illustration purposes

SQLStr = "SELECT COUNT(*) AS MYCOUNT FROM TblTmp"
            Set TmpRS = MyDB.OpenRecordset(SQLStr, dbOpenSnapshot)
            MyCnt = TmpRS!MYCOUNT
            TmpRS.Close

            SQLStr = "INSERT INTO CountsTable VALUES (" & _
              MyCnt & ")"
            MyDB.Execute SQLStr

This is my code in VB.NET:

Dim objBuild As OleDb.OleDbCommandBuilder

        strConnMDB = ConnectMDB()

        With cmdDB
            .Connection = New OleDb.OleDbConnection(strConnMDB)
            .Connection.Open()

            .CommandText = "INSERT INTO Confirmed" & _
                " SELECT DISTINCT MyNUMBER FROM MyLIST"
            .ExecuteNonQuery()

            .Connection.Close()
        End With

        objDataSet = New DataSet

       objAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Confirmed", strConnMDB)
       objAdapter.Fill(objDataSet, "Confirmed")
            With cmdDB
                For Each dr As DataRow In objDataSet.Tables(0).Rows
                    .Connection = New OleDb.OleDbConnection(strConnMDB)
                    .Connection.Open()
                    .CommandText = "SELECT COUNT(MyNUMBER)AS MyCOUNT FROM MyLIST" & _                        
                    dr.Item("ISSUES") = .ExecuteScalar
                    .Connection.Close()
                Next
            End With

          strConnMDB = ConnectMDB()
            strSQL = "SELECT * FROM Confirmed"
            objAdapter = New OleDb.OleDbDataAdapter(strSQL, strConnMDB)
            objBuild = New OleDb.OleDbCommandBuilder(objAdapter)
            objAdapter.UpdateCommand = objBuild.GetUpdateCommand()
            objAdapter.Update(objDataSet, "Confirmed")
            objDataSet.AcceptChanges()
0
Comment
Question by:sg1nx01
  • 2
3 Comments
 
LVL 41

Accepted Solution

by:
graye earned 1060 total points
ID: 13812026
Well, the correct answer is to add a primary key (but that's so incredibly obvious, that I'll have to assume that you don't want to/couldn't do that for some reason).

A somewhat strange way arround not having a primary key is to avoid using the UpdateCommand.   This can be accomplished by a strange set of opperations:

1) load the DataTable... add, modify, delete rows to your heart's content
2) Accept changes at the DataTable level
3) Blow away the contents of the underlying table (with a "Delete * from Confirmed" in an ExecuteNonQuery)
4) Now instead of doing a DataAdapter Update(),  you use Merge().

This will force all of the rows in your DataTable to become marked as "Added" and will therefore use the InsertCommand (which oddly enough is not at all unhappy about not having a primary key)

Okay, Okay... I'll admit that it's a rather odd solution...  
0
 
LVL 41

Expert Comment

by:graye
ID: 13812035
oops, I forgot a step....

3a) pull a 2nd copy of the now empty Confirmed table using the normal DataAdapter Fill() method
0
 

Author Comment

by:sg1nx01
ID: 13819824
I was thinking I had to add a "real" primary key, and then add (one primary key = the other primary key) to the where clause.  But, I tried it with an arbitrary number and it worked fine.  I couldn't figure out the whole merge thing.

Thanks for your help!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Loops Section Overview
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

873 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