problem with code for dataset with mutilple tables

microsoft.public.dotnet.languages.vb

9 May 2006 2:20 AM

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

9 May 2006 4:51 AM

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

Show quoteHide quote

"Cindy H" <nonn***@nowhere.com> schreef in bericht
news:%232Agx8wcGHA.1272@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
>
>

9 May 2006 1:02 PM

Cindy H

Thanks, will give her a try.

Show quoteHide quote

"Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message
news:eGBNuPycGHA.1208@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" <nonn***@nowhere.com> schreef in bericht
> news:%232Agx8wcGHA.1272@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
>>
>>
>
>

26 May 2006 2:33 PM

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