UNION in SQL for DataAdapter gives me error when configuring

  • Thread starter Thread starter darien.watkins
  • Start date Start date
D

darien.watkins

Here is my SQL:

SELECT tblIssue.IssueNumber, tblFacility.FacilityName,
tblIssue.IssueBrief, tblIssue.IssueStatus FROM (tblIssue INNER JOIN
tblFacility ON tblIssue.FacilityID = tblFacility.FacilityID)
UNION ALL
SELECT tblIssue.IssueNumber, tblFacility.FacilityName,
tblIssue.IssueBrief, tblIssue.IssueStatus FROM (tblIssueRO INNER JOIN
tblFacilityRO ON tblIssue.FacilityID = tblFacility.FacilityID)

tblFacility and tblFacilityRO are identical
tblIssue and tblIssueRO are identical

Any ideas why my sql doesn't work? It comes up saying there were
errors configuring the adapter.

Darien
 
Here is my SQL:

SELECT tblIssue.IssueNumber, tblFacility.FacilityName,
tblIssue.IssueBrief, tblIssue.IssueStatus FROM (tblIssue INNER JOIN
tblFacility ON tblIssue.FacilityID = tblFacility.FacilityID)
UNION ALL
SELECT tblIssue.IssueNumber, tblFacility.FacilityName,
tblIssue.IssueBrief, tblIssue.IssueStatus FROM (tblIssueRO INNER JOIN
tblFacilityRO ON tblIssue.FacilityID = tblFacility.FacilityID)

tblFacility and tblFacilityRO are identical
tblIssue and tblIssueRO are identical

Any ideas why my sql doesn't work? It comes up saying there were
errors configuring the adapter.

Darien

If it works in SQL Query Analyzer, then it's solid. Maybe, the
DataAdapter just can't deal with it.

Maybe, you can try Dynamic SQL code using SQL Command Object and a
DataReader.

The example is a C# example, but of course, you can do the same thing in VB.


MyDataReader[1] // equals field two of the fields being pulled back in
the recordset record.

Or you can use MyDataReader['caseid'] // if field 0 is the record's key.

You might have to go through a Convert to set a database field to the
proper variable type.


SqlConnection MyConnection = new SqlConnection(@"Data Source=(local);
Initial Catalog = CaseManager; Integrated Security=true");

MyConnection.Open();

SqlCommand MyCommand = new
SqlCommand("SELECT * FROM CaseInfo",
MyConnection);
SqlDataReader MyDataReader =
MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

while (MyDataReader.Read())
{
Console.WriteLine(MyDataReader[0] + " " +
MyDataReader[1]);
}

MyConnection.Close();
 
I have gotten a query like that to work directly on the database, it
seems the UNION just kills it for the data adapter.

Essentially what I'm trying to accomplish is this. I have two
identical tables in my database. One is a list of issues that have
been recorded, the other is a list of issues that have not been
recorded. (I have them seperate because I am syncing the unrecorded
table up to a master list, then back down to the recorded list. I
need a datagrid to show the records from both in case your viewing
between syncs.

Darien
 
I have gotten a query like that to work directly on the database, it
seems the UNION just kills it for the data adapter.

If it works on the database then, the its going to work with a SQL Command
Object with a ADO.DataReader.
Essentially what I'm trying to accomplish is this. I have two
identical tables in my database. One is a list of issues that have
been recorded, the other is a list of issues that have not been
recorded. (I have them seperate because I am syncing the unrecorded
table up to a master list, then back down to the recorded list. I
need a datagrid to show the records from both in case your viewing
between syncs.

Yes you can easily do it by making a Function that returns an ArrayList of
Objects, binding it to the datagrid.

Public class infodata

with Propery Gets and Lets
end class

Public class Getdata() as System.Arraylist

Public Function Getthedata() as System.Arraylist

dim strsql = " all of your Select stuff"

dim temparray as new System.Arraylist

SQL Command stuff in the Example

while (MyDataReader.Read())

dim info = new infodata

info.field = DataReader("recid") // you may need to use the Convert and
you
// may need to
wrap with IF checking for Null before
// populating data
or making field Null or 0
temparray,add(info)

end while

MyConnection.Close();

return temparray

end fucntion

end class

dim getd = new Getdata()

datagrid.datasource = getd.Getthedata

datagrid,databind

It's something like that.
 
Darien,

This is typical a question for the newsgroup

microsoft.public.dotnet.framework.adonet

Cor
 
Back
Top