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 GetSelectedProg ressReport(ByVa l Email As String) As
DataSet

Dim dsCustProg As New DataSet

Dim cmdCustProg As New OleDbCommand

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

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

cmdCustProg.Com mandText = cSelect

cmdCustProg.Com mandText = pSelect

' Add Parameters to cmd

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

parameterEmail. Value = Email

cmdCustProg.Par ameters.Add(par ameterEmail)

cmdCustProg.Con nection = MyConnection()

Dim daCustomer As New OleDbDataAdapte r

Dim daProgress As New OleDbDataAdapte r

daCustomer.Sele ctCommand = cmdCustProg

daProgress.Sele ctCommand = cmdCustProg

daCustomer.Fill (dsCustProg, "ConstructionCu stomers")

daProgress.Fill (dsCustProg, "ConstructionPr ogress")

Dim relation As DataRelation = _

dsCustProg.Rela tions.Add("Cust Prog", _

dsCustProg.Tabl es("Constructio nCustomers").Co lumns("Customer ID"), _

dsCustProg.Tabl es("Constructio nProgress").Col umns("CustomerI D"))

Return dsCustProg

End Function

Thanks,

CindyH

[Cor Ligthert [MVP]]

Re: problem with code for dataset with mutilple tables

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 OledbDataAdapte r(SelectString, connection)

I hope this helps,

Cor

"Cindy H" <nonname@nowher e.com> schreef in bericht
news:%232Agx8wc GHA.1272@TK2MSF TNGP03.phx.gbl. ..[color=blue]
> 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 GetSelectedProg ressReport(ByVa l Email As String) As
> DataSet
>
> Dim dsCustProg As New DataSet
>
> Dim cmdCustProg As New OleDbCommand
>
> Dim cSelect As String = "SELECT * FROM ConstructionCus tomers WHERE Display
> =
> 'y' and Email = @Email"
>
> Dim pSelect As String = "SELECT * FROM ConstructionPro gress WHERE Display
> =
> 'y'"
>
> cmdCustProg.Com mandText = cSelect
>
> cmdCustProg.Com mandText = pSelect
>
> ' Add Parameters to cmd
>
> Dim parameterEmail As OleDbParameter = New OleDbParameter( "@Email",
> OleDbType.VarCh ar, 100)
>
> parameterEmail. Value = Email
>
> cmdCustProg.Par ameters.Add(par ameterEmail)
>
> cmdCustProg.Con nection = MyConnection()
>
> Dim daCustomer As New OleDbDataAdapte r
>
> Dim daProgress As New OleDbDataAdapte r
>
> daCustomer.Sele ctCommand = cmdCustProg
>
> daProgress.Sele ctCommand = cmdCustProg
>
> daCustomer.Fill (dsCustProg, "ConstructionCu stomers")
>
> daProgress.Fill (dsCustProg, "ConstructionPr ogress")
>
> Dim relation As DataRelation = _
>
> dsCustProg.Rela tions.Add("Cust Prog", _
>
> dsCustProg.Tabl es("Constructio nCustomers").Co lumns("Customer ID"), _
>
> dsCustProg.Tabl es("Constructio nProgress").Col umns("CustomerI D"))
>
> Return dsCustProg
>
> End Function
>
> Thanks,
>
> CindyH
>
>[/color]

[Cindy H]

Re: problem with code for dataset with mutilple tables

Thanks, will give her a try.




"Cor Ligthert [MVP]" <notmyfirstname @planet.nl> wrote in message
news:eGBNuPycGH A.1208@TK2MSFTN GP02.phx.gbl...[color=blue]
> 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 OledbDataAdapte r(SelectString, connection)
>
> I hope this helps,
>
> Cor
>
> "Cindy H" <nonname@nowher e.com> schreef in bericht
> news:%232Agx8wc GHA.1272@TK2MSF TNGP03.phx.gbl. ..[color=green]
>> 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 GetSelectedProg ressReport(ByVa l Email As String)
>> As
>> DataSet
>>
>> Dim dsCustProg As New DataSet
>>
>> Dim cmdCustProg As New OleDbCommand
>>
>> Dim cSelect As String = "SELECT * FROM ConstructionCus tomers WHERE
>> Display =
>> 'y' and Email = @Email"
>>
>> Dim pSelect As String = "SELECT * FROM ConstructionPro gress WHERE Display
>> =
>> 'y'"
>>
>> cmdCustProg.Com mandText = cSelect
>>
>> cmdCustProg.Com mandText = pSelect
>>
>> ' Add Parameters to cmd
>>
>> Dim parameterEmail As OleDbParameter = New OleDbParameter( "@Email",
>> OleDbType.VarCh ar, 100)
>>
>> parameterEmail. Value = Email
>>
>> cmdCustProg.Par ameters.Add(par ameterEmail)
>>
>> cmdCustProg.Con nection = MyConnection()
>>
>> Dim daCustomer As New OleDbDataAdapte r
>>
>> Dim daProgress As New OleDbDataAdapte r
>>
>> daCustomer.Sele ctCommand = cmdCustProg
>>
>> daProgress.Sele ctCommand = cmdCustProg
>>
>> daCustomer.Fill (dsCustProg, "ConstructionCu stomers")
>>
>> daProgress.Fill (dsCustProg, "ConstructionPr ogress")
>>
>> Dim relation As DataRelation = _
>>
>> dsCustProg.Rela tions.Add("Cust Prog", _
>>
>> dsCustProg.Tabl es("Constructio nCustomers").Co lumns("Customer ID"), _
>>
>> dsCustProg.Tabl es("Constructio nProgress").Col umns("CustomerI D"))
>>
>> Return dsCustProg
>>
>> End Function
>>
>> Thanks,
>>
>> CindyH
>>
>>[/color]
>
>[/color]

[jmbledsoe@gmail.com]

Re: problem with code for dataset with mutilple tables

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