bout union select....

can someone help me how can i access datas using union? or show my data in the gridview....pls....

coz i have 3 tables...

i need to output the datas of the 3 tables in 1 gridview.

tables are: TT0001,TM0011,TM0001

TT0001 has syain_id(PK),time_in,time_out,year(PK),month(PK),day(PK)

TM0011 has office_name,office_id(PK)

TM0001 has syain_id(PK) office_id(PK),empl_date

the scenario is:

i have a combo box for office_name, and a textbox for imputting the date( 2007/01/23).when i click the button OK.

the time_in,time_out and syain_name of the person who is present in the choosen date(ex. 2007/01/23) will be shown in my gridview_info.

i already have some codes but it still confuse me and has many errors...

my code:

GridView_info.Visible = True

'//for odbc
Dim StrConn As String = "Dsn=MS_PKG01;UID=emiline;APP=Microsoft? Visual Studio? 2005;WSID=MSHNP200603;DATABASE=MS_PKG01;Trusted_Connection=Yes"
Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn)

'Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MS_PKG01ConnectionString").ConnectionString)

MyConn.Open()

'//parsing 2
Dim MyString As String = TextBox_date.Text
Dim MyDateTime As DateTime = DateTime.Parse(MyString)
Console.WriteLine(MyDateTime)

Dim stringQuery2 As String = "SELECT * from tempo_db"

Dim SQLcommand2 As New Odbc.OdbcCommand("Create table tempo_db ( syain_name char(40),year char(4), month char(2), day char(2), in_hh int, in_mi int, out_hh int, out_mi int )", MyConn)
SQLcommand2.ExecuteNonQuery()

Dim SQLcommand1 As New Odbc.OdbcCommand("Drop table tempo_db", MyConn)
SQLcommand1.ExecuteNonQuery()

Dim da As New Odbc.OdbcDataAdapter("Select TT0001.Year,TT0001.Month,TT0001.Day,TT0001.in_hh,TT0001.in_mi,TT0001.out_hh,TT0001.out_min where TT0001.Year =" + MyString + " and TT0001.Month =" + MyString + " and TT0001.Day =" + MyString + " and TT0001.syain_id =" + TM0001.syain_id + " Union Select TM0001.syain_name where TM0001.syain_id =" + TT0001.syain_id + " 　Union Select　TM0011.office_name where TM0011.office_id =" + TM0001.office_id + "
Dim ds As New DataSet()
Dim foundrow As DataRow
Dim ds2 As New DataSet
'Dim temp_data_table As New DataTable

GridView_info.DataSource = ds
da.Fill(ds, "TT0001")

Dim sqldataadapter2 As New Odbc.OdbcDataAdapter(stringQuery2, MyConn)
sqldataadapter2.Fill(ds2, "tempo_db")
Dim date_ctr As Integer
date_ctr = 1

While date_ctr <= Date.DaysInMonth(Now.Year, Now.Month)

ds.Tables(0).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Year")}
'ds.Tables(1).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Month")}
'ds.Tables(2).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Day")}

foundrow = ds.Tables(0).Rows.Find(date_ctr)
'foundrow = ds.Tables(1).Rows.Find(date_ctr)
'foundrow = ds.Tables(2).Rows.Find(date_ctr)

Dim in_hh, in_mi, out_hh, out_mi As String

If foundrow IsNot Nothing Then
in_hh = foundrow.Item("in_hh")
in_mi = foundrow.Item("in_mi")
out_hh = foundrow.Item("out_hh")
out_mi = foundrow.Item("out_mi")
End If

End While

GridView_info.DataSource = ds2
GridView_info.DataBind()
MyConn.Close()

End Sub

/// ps: im having problem with my select statements....

can someone help me analyze what i had written... and what is my mistake...

any help is greatly appreciated...

thanks

nat!

Remember in union all select statement have to returns the same table structure, and you should use syntax like :

Select * from t1 where a=b

UNION

select * from t2 where b=c

instead of

Select * where a=b

UNION

select * where b=c

(you have it in da As New Odbc.OdbcDataAdapter)

If you select statements returns different number of columns just add dummy columns to where you need them

like

Select c1,c2,c3,c4 from t1 where a=b

UNION

select d1,d2,d3 ,'dummy value' from t2 where b=c

but remember that all columns have to have the same format like 1 select statement

Thanks

JPazgier