Data Adapter question

Printable View

Jun 1st, 2004, 04:30 PM
indydavid32

Data Adapter question
I have the following code.
VB Code:
```
Dim datAdapt As New OleDb.OleDbDataAdapter("Select * From Weekly_Customers", Conn)
Dim commBuild As New OleDb.OleDbCommandBuilder(datAdapt)
        Dim datset As New Data.DataSet
        Dim datrow As Data.DataRow
```
This works fine. My questions is, say I have another table in the same database that Weekly_customers is in. Is there some command that I can use where I can just open that table with datAdapt, or do I have to declare a data adapter for each table in the database that I want to open?

Thanks
Jun 1st, 2004, 06:08 PM
Pirate

Use DataRelation obj that combines the two tables . I posted an example in the CodeBank .
Jun 2nd, 2004, 01:35 AM
robymix
you can use a single data adaptor and a single dataset. separate the select commands commands with a semi colon. eg
VB Code:
```
Dim datAdapt As New OleDb.OleDbDataAdapter("Select * From Weekly_Customers; SELECT * FROM another_table", Conn)
```
hope it will help.
Jun 2nd, 2004, 01:39 AM
mendhak

I don't know how effective this is, but the book I'm reading told me to declare one data adapter for each table that I'll be retrieving, even though I'll be storing them in the same dataset.

What difference does this make?

Robymix, I tried what you said but I'm getting an error. Here is my code.

VB Code:

Dim datAdapt As New OleDb.OleDbDataAdapter("Select * From Weekly_Customers; " & _
        "Select * From Weekly_Receipts", Conn)
        Dim commBuild As New OleDb.OleDbCommandBuilder(datAdapt)
        Dim datset As New Data.DataSet
        Dim datrow As Data.DataRow
commSQL1.CommandText = "Delete From Weekly_customers"
        datRead1 = commSQL1.ExecuteReader
        datRead1.Close()
        commSQL.CommandText = "Select * From Invoice_customers Where Inv_Cust_ID = '" & _
        strEmailCustID & "' and Inv_Statement_Date = '" & Format(dteEmailDate, "MM/dd/yyyy") & "'"
        datRead = commSQL.ExecuteReader
 
        If datRead.Read = True Then
            blnDataCopy = True
            datset.Clear()
            datAdapt.Fill(datset, "Weekly_Customers")
            datrow = datset.Tables("Weekly_Customers").NewRow
            datrow.Item("Inv_Invoice_Number") = datRead.Item("Inv_Invoice_Number")
            datrow.Item("Inv_Cust_Key") = datRead.Item("Inv_Cust_Key")
            datrow.Item("Inv_Cust_ID") = datRead.Item("Inv_Cust_ID")
            datrow.Item("Inv_Cust_Description") = datRead.Item("Inv_Cust_Description")
            datrow.Item("Inv_Statement_Date") = Format(datRead.Item("Inv_Statement_Date"), "MM/dd/yyyy")
            datrow.Item("Inv_Due_Date") = Format(datRead.Item("Inv_Due_Date"), "MM/dd/yyyy")
            datrow.Item("Inv_Payments") = datRead.Item("Inv_Payments")
            datrow.Item("Inv_Adjustments") = datRead.Item("Inv_Adjustments")
            datrow.Item("Inv_Transactions") = datRead.Item("Inv_Transactions")
            datrow.Item("Inv_Finance_Charge") = datRead.Item("Inv_Finance_Charge")
            datrow.Item("Inv_Ending_Balance") = datRead.Item("Inv_Ending_Balance")
            datrow.Item("Inv_Subtotal") = datRead.Item("Inv_Subtotal")
            If IsDBNull(datRead.Item("Inv_Cust_Address_1")) = False Then
                datrow.Item("Inv_Cust_Address_1") = Trim(datRead.Item("Inv_Cust_Address_1"))
            End If
            If IsDBNull(datRead.Item("Inv_Cust_Address_2")) = False Then
                datrow.Item("Inv_Cust_Address_2") = datRead.Item("Inv_Cust_Address_2")
            End If
            If IsDBNull(datRead.Item("Inv_Cust_City")) = False Then
                datrow.Item("Inv_Cust_City") = Trim(datRead.Item("Inv_Cust_City"))
            End If
            If IsDBNull(datRead.Item("Inv_Cust_State")) = False Then
                commSQL2.CommandText = "Select State_Code From States Where State_Key = " & _
                datRead.Item("Inv_Cust_State")
                datread2 = commSQL2.ExecuteReader
                If datread2.Read Then
                    If IsDBNull(datread2.Item("State_Code")) = False Then
                        datrow.Item("Inv_Cust_State") = datread2.Item("State_Code")
                    Else
                        datrow.Item("Inv_Cust_State") = "IN"
                    End If
                Else
                    datrow.Item("Inv_Cust_State") = "IN"
                End If
                datread2.Close()
            End If
            If IsDBNull(datRead.Item("Inv_Cust_Zip")) = False Then
                datrow.Item("Inv_Cust_Zip") = Trim(datRead.Item("Inv_Cust_Zip"))
            End If
            If IsDBNull(datRead.Item("Inv_Begin_Balance")) = False Then
                datrow.Item("Inv_Begin_Balance") = datRead.Item("Inv_Begin_Balance")
            End If
            If IsDBNull(datRead.Item("Inv_PmtAdjTypeDTotal")) = False Then
                datrow.Item("Inv_PmtAdjTypeDTotal") = datRead.Item("Inv_PmtAdjTypeDTotal")
            End If
            If IsDBNull(datRead.Item("Inv_TransactionsTypeDTotal")) = False Then
                datrow.Item("Inv_TransactionsTypeDTotal") = datRead.Item("Inv_TransactionsTypeDTotal")
            End If
            If IsDBNull(datRead.Item("CustBal_TypeDBalance")) = False Then
                datrow.Item("CustBal_TypeDBalance") = datRead.Item("CustBal_TypeDBalance")
            End If
            If IsDBNull(datRead.Item("Inv_Cust_Type_Key")) = False Then
                datrow.Item("Inv_Cust_Type_Key") = datRead.Item("Inv_Cust_Type_Key")
            End If
            datset.Tables("Weekly_Customers").Rows.Add(datrow)
            datAdapt.Update(datset, "Weekly_Customers")
        Else
            blnDataCopy = False
            Exit Sub
        End If
        datRead.Close()
        commSQL1.CommandText = "Delete From Weekly_Receipts"
        datRead1 = commSQL1.ExecuteReader
        datRead1.Close()
        commSQL.CommandText = "Select * From Invoice_Receipts Where Receipt_Cust_ID = '" & _
        strEmailCustID & "' and Receipt_Statement_Date = '" & Format(dteEmailDate, "MM/dd/yyyy") & "'"
        datRead = commSQL.ExecuteReader
        Do While datRead.Read
            datset.Clear()
            datAdapt.Fill(datset, "Weekly_Receipts")
            datrow = datset.Tables("Weekly_Receipts").NewRow
            If IsDBNull(datRead.Item("Receipt_ID")) = False Then datrow.Item("Receipt_ID") = datRead.Item("Receipt_ID")
            If IsDBNull(datRead.Item("Receipt_TranCode_Description")) = False Then datrow.Item("Receipt_TranCode_Description") = datRead.Item("Receipt_TranCode_Description")
            If IsDBNull(datRead.Item("Receipt_Amount")) = False Then datrow.Item("Receipt_Amount") = datRead.Item("Receipt_Amount")
            If IsDBNull(datRead.Item("Receipt_Date")) = False Then datrow.Item("Receipt_Date") = datRead.Item("Receipt_Date")
            If IsDBNull(datRead.Item("Receipt_Type")) = False Then datrow.Item("Receipt_Type") = datRead.Item("Receipt_Type")
            If IsDBNull(datRead.Item("Receipt_Cust_ID")) = False Then datrow.Item("Receipt_Cust_ID") = datRead.Item("Receipt_Cust_ID")
            If IsDBNull(datRead.Item("Receipt_Ref_No")) = False Then datrow.Item("Receipt_Ref_No") = datRead.Item("Receipt_Ref_No")
            If IsDBNull(datRead.Item("Receipt_Statement_Date")) = False Then datrow.Item("Receipt_Statement_Date") = datRead.Item("Receipt_Statement_Date")
            datset.Tables("Weekly_Receipts").Rows.Add(datrow)
            datAdapt.Update(datset, "Weekly_Receipts")
        Loop

On the Weekly_Receipts part of the program, when I am trying to add the new line, I get an error saying...

Additional information: Column 'Receipt_ID' does not belong to table Weekly_Receipts.

I made sure that the table names and field names are correct.

Can you tell me what I'm doing wrong?

Thanks

Jun 2nd, 2004, 10:35 AM
salvelinus

For one thing, the code ought to be in try...catch blocks. Catch the exception and see if you get more error info.
Jun 2nd, 2004, 10:56 AM
indydavid32
A try and catch gave me the same System error message that I displayed above.

I think it has something to do with this statement.
VB Code:
```
Dim datAdapt As New OleDb.OleDbDataAdapter("Select * From Weekly_Customers; " & _
        "Select * From Weekly_Receipts", Conn)
```
And these statements.
VB Code:
```
datset.Clear()
            datAdapt.Fill(datset, "Weekly_Receipts")
            datrow = datset.Tables("Weekly_Receipts").NewRow
```
When I use the first table in my datAdapt, it works fine. All subsequent tables cause the error listed below.

Quote:

Additional information: Column 'Receipt_ID' does not belong to table Weekly_Receipts.
Jun 2nd, 2004, 12:17 PM
robymix

Is 'Receipt_ID' a member of 'Weekly_Receipts'? Is the dataset filled with the content of 'Weekly_Receipt'?

Since you are not using the two tables filled by the adaptor, why not consider re-instantiating it, or consider table mapping of the data adaptor?

At exactly what line of code is the exception thrown?
Jun 2nd, 2004, 02:16 PM
indydavid32
Quote:

Is 'Receipt_ID' a member of 'Weekly_Receipts'? Is the dataset filled with the content of 'Weekly_Receipt'?

Since you are not using the two tables filled by the adaptor, why not consider re-instantiating it, or consider table mapping of the data adaptor?

At exactly what line of code is the exception thrown?

I am using both tables that I declared in the data adapter. I just didn't include it in my code that I put in here.

It errors out on...
VB Code:
```
datrow.Item("Receipt_ID") = datRead.Item("Receipt_ID")
```
How would you re-instantiate it or map it?
Jun 2nd, 2004, 08:32 PM
Pirate

Quote:

Originally posted by mendhak
I don't know how effective this is, but the book I'm reading told me to declare one data adapter for each table that I'll be retrieving, even though I'll be storing them in the same dataset.

What difference does this make?

This way is , easier to maintain , clearer to understand . If the two tables are not related , then you would need two adapters , I guess . :D
Jun 3rd, 2004, 08:43 AM
robymix
Quote:

Originally posted by indydavid32
How would you re-instantiate it or map it?

You can re-instantiate with the New keyword. Table mapping is as follows:
VB Code:
```
DataAdaptor.TableMappings.Add(sourceTable, dataSetTable)
```