problem with code for dataset with mutilple tables

[Cindy H]

Hi

I'm not getting any data back on this code.

Can someone tell me what I'm doing wrong?

What I really want is to select fields out of two table where the display
= 'y' from both tables and in one table the email is = to variable I'm
passing.

Public Shared Function GetSelectedProgressReport(ByVal Email As String) As
DataSet

Dim dsCustProg As New DataSet

Dim cmdCustProg As New OleDbCommand

Dim cSelect As String = "SELECT * FROM ConstructionCustomers WHERE Display =
'y' and Email = @Email"

Dim pSelect As String = "SELECT * FROM ConstructionProgress WHERE Display =
'y'"

cmdCustProg.CommandText = cSelect

cmdCustProg.CommandText = pSelect

' Add Parameters to cmd

Dim parameterEmail As OleDbParameter = New OleDbParameter("@Email",
OleDbType.VarChar, 100)

parameterEmail.Value = Email

cmdCustProg.Parameters.Add(parameterEmail)

cmdCustProg.Connection = MyConnection()

Dim daCustomer As New OleDbDataAdapter

Dim daProgress As New OleDbDataAdapter

daCustomer.SelectCommand = cmdCustProg

daProgress.SelectCommand = cmdCustProg

daCustomer.Fill(dsCustProg, "ConstructionCustomers")

daProgress.Fill(dsCustProg, "ConstructionProgress")

Dim relation As DataRelation = _

dsCustProg.Relations.Add("CustProg", _

dsCustProg.Tables("ConstructionCustomers").Columns ("CustomerID"), _

dsCustProg.Tables("ConstructionProgress").Columns( "CustomerID"))

Return dsCustProg

End Function

Thanks,

CindyH

[Cor Ligthert [MVP]]

Cindy

Both adapters needs there own commands.
(The commandtext is no collection so you are now just overwriting it)

You can use the constructer by this by the way, which is much easier

Dim da as new OledbDataAdapter(SelectString, connection)

I hope this helps,

Cor

"Cindy H" <no*****@nowhere.com> schreef in bericht
news:%2****************@TK2MSFTNGP03.phx.gbl...

Hi

I'm not getting any data back on this code.

Can someone tell me what I'm doing wrong?

What I really want is to select fields out of two table where the
display
= 'y' from both tables and in one table the email is = to variable I'm
passing.

Public Shared Function GetSelectedProgressReport(ByVal Email As String) As
DataSet

Dim dsCustProg As New DataSet

Dim cmdCustProg As New OleDbCommand

Dim cSelect As String = "SELECT * FROM ConstructionCustomers WHERE Display
=
'y' and Email = @Email"

Dim pSelect As String = "SELECT * FROM ConstructionProgress WHERE Display
=
'y'"

cmdCustProg.CommandText = cSelect

cmdCustProg.CommandText = pSelect

' Add Parameters to cmd

Dim parameterEmail As OleDbParameter = New OleDbParameter("@Email",
OleDbType.VarChar, 100)

parameterEmail.Value = Email

cmdCustProg.Parameters.Add(parameterEmail)

cmdCustProg.Connection = MyConnection()

Dim daCustomer As New OleDbDataAdapter

Dim daProgress As New OleDbDataAdapter

daCustomer.SelectCommand = cmdCustProg

daProgress.SelectCommand = cmdCustProg

daCustomer.Fill(dsCustProg, "ConstructionCustomers")

daProgress.Fill(dsCustProg, "ConstructionProgress")

Dim relation As DataRelation = _

dsCustProg.Relations.Add("CustProg", _

dsCustProg.Tables("ConstructionCustomers").Columns ("CustomerID"), _

dsCustProg.Tables("ConstructionProgress").Columns( "CustomerID"))

Return dsCustProg

End Function

Thanks,

CindyH

[Cindy H]

Thanks, will give her a try.


"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:eG**************@TK2MSFTNGP02.phx.gbl...

Cindy

Both adapters needs there own commands.
(The commandtext is no collection so you are now just overwriting it)

You can use the constructer by this by the way, which is much easier

Dim da as new OledbDataAdapter(SelectString, connection)

I hope this helps,

Cor

"Cindy H" <no*****@nowhere.com> schreef in bericht
news:%2****************@TK2MSFTNGP03.phx.gbl...

Hi

I'm not getting any data back on this code.

Can someone tell me what I'm doing wrong?

What I really want is to select fields out of two table where the
display
= 'y' from both tables and in one table the email is = to variable I'm
passing.

Public Shared Function GetSelectedProgressReport(ByVal Email As String)
As
DataSet

Dim dsCustProg As New DataSet

Dim cmdCustProg As New OleDbCommand

Dim cSelect As String = "SELECT * FROM ConstructionCustomers WHERE
Display =
'y' and Email = @Email"

Dim pSelect As String = "SELECT * FROM ConstructionProgress WHERE Display
=
'y'"

cmdCustProg.CommandText = cSelect

cmdCustProg.CommandText = pSelect

' Add Parameters to cmd

Dim parameterEmail As OleDbParameter = New OleDbParameter("@Email",
OleDbType.VarChar, 100)

parameterEmail.Value = Email

cmdCustProg.Parameters.Add(parameterEmail)

cmdCustProg.Connection = MyConnection()

Dim daCustomer As New OleDbDataAdapter

Dim daProgress As New OleDbDataAdapter

daCustomer.SelectCommand = cmdCustProg

daProgress.SelectCommand = cmdCustProg

daCustomer.Fill(dsCustProg, "ConstructionCustomers")

daProgress.Fill(dsCustProg, "ConstructionProgress")

Dim relation As DataRelation = _

dsCustProg.Relations.Add("CustProg", _

dsCustProg.Tables("ConstructionCustomers").Columns ("CustomerID"), _

dsCustProg.Tables("ConstructionProgress").Columns( "CustomerID"))

Return dsCustProg

End Function

Thanks,

CindyH

[jmbledsoe]

I've run into this exact problem a whole lot of times. I've been using
a tool called the DataSet Toolkit so that I don't have to write the
code for all the data adapters that your example contains. Check it
out and let me know if it works for you.

http://www.hydrussoftware.com

John B.
http://johnsbraindump.blogspot.com