Problems with Filling Data Adapter

[crjunk]

I have a 3 table in my DataSet that I'm filling with data. After I've
filled these 3 tables, I'm then trying to run a query that will fill a
4th table in the DataSet with data from the three tables that are
already pouplated.

When my code tries to fill the 4th table, ReportData, I receive the
following error message: Invalid object name 'tmpPROJECTINFO'. Invalid
object name 'tmpContacts'. Invalid object name 'tmpRECEIVEREC'.

Can someone tell me what I'm doing wrong? Here is a copy of the code
that I am using.

Thanks,
C.R.

'MY CODE
Dim ProgramDS As New ProjectRelationDS
Dim MyDA As SqlDataAdapter = New SqlDataAdapter

Dim tmpContactSelect As String = "Select * from CONTACTS where
WHODELETE is Null"
Dim tmpReceivedRecSelect As String = "Select * from RECEIVEREC
where WHODELETE is Null"
Dim tmpProjectInfoSelect As String = "Select * from
PROJECTINFO where WHODEL is Null"

' Here I am filling my DataSet with Data
MyDA.SelectCommand = New SqlCommand(tmpContactSelect,
SqlConnection1)
MyDA.Fill(ProgramDS, "tmpContacts")

MyDA.SelectCommand = New SqlCommand(tmpReceivedRecSelect,
SqlConnection1)
MyDA.Fill(ProgramDS, "tmpReceivedRec")

MyDA.SelectCommand = New SqlCommand(tmpProjectInfoSelect,
SqlConnection1)
MyDA.Fill(ProgramDS, "tmpProjectInfo")


Dim SelectStatement As String
Dim tmpProjectInfoFields As String
Dim tmpReceivedRecFields As String
Dim tmpContactFields As String
Dim tmpThreeTables As String

' Begin building my final select statement
tmpProjectInfoFields = " IGNORE, PROJECT, COUNTY, GAMINGREG, "
tmpReceivedRecFields = " FYYEAR, TYPERECORD, DATESENT,
DATEREC, "
tmpContactFields = " FNAME, LNAME, EMAIL, ADD1, ADD2, CITY,
STATE, "

tmpThreeTables = tmpProjectInfoFields + tmpReceivedRecFields +
tmpContactFields

Dim tmpInnerJoin As String = " (tmpProjectInfo LEFT JOIN tmpContacts
ON tmpProjectInfo.PROJNUM = tmpContacts.PROJNUM) LEFT JOIN
tmpReceiveRec ON tmpPROJECTINFO.PROJNUM = tmpReceiveRec.PROJNUM "

SelectStatement = "Select " + tmpThreeTables + " From " +
tmpInnerJoin

MyDA.SelectCommand = New SqlCommand(SelectStatement,
SqlConnection1)
' The program bombs out on the MyDAFill statement listed
below.
MyDA.Fill(ProgramDS, "ReportData")

 

[Guest]

To make the 4th table from the others, you have to either build the rows one
by one, or you have to clone rows from the other tables using .CopyTo() in
the new DataTable.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[crjunk]

In the physical DataSet file that I created in my project, I have a
table, or element, named ReportData. Table ReportData contains all
the fields that I am using in the last query. Is this what you are
refering to when you say "build the rows one by one" ?

As a side note, this DataSet also contains the tables tmpProjectInfo
and tmpReceiveRec.

Thanks for your help.

C.R.

 

[crjunk]

I've tried filling the table after I built it in my dataSet, but I'm
still receiving the same message. Any suggestions?

Thanks,
C.R.