How to add adapters

I'm trying to use three stored procedures and three adapters but don't
know how to set it up. This is giving me errors. Can someone assist
me please.

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button2.Click

Dim conn As New
Data.SqlClient.SqlConnection(ConfigurationManager.Connection Strings("TrainUserConnectionString").ConnectionString)

Dim cmdALL As New Data.SqlClient.SqlCommand
Dim cmdTopic As New Data.SqlClient.SqlCommand 'cmd for 2nd
stored procedure
Dim cmdMedia As New Data.SqlClient.SqlCommand ' 3rd stored
procedure


With cmdALL
.CommandType = Data.CommandType.StoredProcedure

.CommandText = "GetAllTopics"

If DropDownList2.SelectedValue = "-1" Then
.Parameters.AddWithValue("@Type",
DropDownList2.SelectedValue)
End If
.Connection = conn

End With

Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdALL)

With cmdTopic
.CommandType = Data.CommandType.StoredProcedure

.CommandText = "GetByTopic"

If DropDownList3.SelectedIndex > 0 Then
.Parameters.AddWithValue("@classificationid",
Integer.Parse(DropDownList2.SelectedValue))
End If
.Connection = conn
End With

Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdTopic)

With cmdMedia
.CommandType = Data.CommandType.StoredProcedure

.CommandText = "GetByMedia"

If DropDownList3.SelectedValue > 0 Then
.Parameters.AddWithValue("@Mediaid",
DropDownList3.SelectedValue)
End If
.Connection = conn
End With

Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdMedia)

Try

conn.Open()

Dim ds As New Data.DataSet
adapter.Fill(ds)

GridView1.DataSource = ds

GridView1.DataBind()

Finally

conn.Close()

End Try

Re: How to add adapters

Please tell us what the error is that you're getting and on what line.

Ray Costanzo

"JJ297" wrote in message
news:d43fd4fc-c0da-413f-b7ea-fdb746c3b78c@v29g2000hsf.google groups.com...
> I'm trying to use three stored procedures and three adapters but don't
> know how to set it up. This is giving me errors. Can someone assist
> me please.
>
> Protected Sub Button2_Click(ByVal sender As Object, ByVal e As
> System.EventArgs) Handles Button2.Click
>
> Dim conn As New
> Data.SqlClient.SqlConnection(ConfigurationManager.Connection Strings("TrainUserConnectionString").ConnectionString)
>
> Dim cmdALL As New Data.SqlClient.SqlCommand
> Dim cmdTopic As New Data.SqlClient.SqlCommand 'cmd for 2nd
> stored procedure
> Dim cmdMedia As New Data.SqlClient.SqlCommand ' 3rd stored
> procedure
>
>
> With cmdALL
> .CommandType = Data.CommandType.StoredProcedure
>
> .CommandText = "GetAllTopics"
>
> If DropDownList2.SelectedValue = "-1" Then
> .Parameters.AddWithValue("@Type",
> DropDownList2.SelectedValue)
> End If
> .Connection = conn
>
> End With
>
> Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdALL)
>
> With cmdTopic
> .CommandType = Data.CommandType.StoredProcedure
>
> .CommandText = "GetByTopic"
>
> If DropDownList3.SelectedIndex > 0 Then
> .Parameters.AddWithValue("@classificationid",
> Integer.Parse(DropDownList2.SelectedValue))
> End If
> .Connection = conn
> End With
>
> Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdTopic)
>
> With cmdMedia
> .CommandType = Data.CommandType.StoredProcedure
>
> .CommandText = "GetByMedia"
>
> If DropDownList3.SelectedValue > 0 Then
> .Parameters.AddWithValue("@Mediaid",
> DropDownList3.SelectedValue)
> End If
> .Connection = conn
> End With
>
> Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdMedia)
>
> Try
>
> conn.Open()
>
> Dim ds As New Data.DataSet
> adapter.Fill(ds)
>
> GridView1.DataSource = ds
>
> GridView1.DataBind()
>
> Finally
>
> conn.Close()
>
> End Try

Re: How to add adapters

On Jan 15, 9:04=A0am, "Ray Costanzo" commercial> wrote:
> Please tell us what the error is that you're getting and on what line.
>
> Ray Costanzo
>
> "JJ297" wrote in message
>
> news:d43fd4fc-c0da-413f-b7ea-fdb746c3b78c@v29g2000hsf.google groups.com...
>
>
>
> > I'm trying to use three stored procedures and three adapters but don't
> > know how to set it up. =A0This is giving me errors. =A0Can someone assis=
t
> > me please.
>
> > Protected Sub Button2_Click(ByVal sender As Object, ByVal e As
> > System.EventArgs) Handles Button2.Click
>
> > =A0 =A0 =A0 =A0Dim conn As New
> > Data.SqlClient.SqlConnection(ConfigurationManager.Connection Strings("Tra=
inU=ADserConnectionString").ConnectionString)
>
> > =A0 =A0 =A0 =A0Dim cmdALL As New Data.SqlClient.SqlCommand
> > =A0 =A0 =A0 =A0Dim cmdTopic As New Data.SqlClient.SqlCommand =A0'cmd for=
2nd
> > stored procedure
> > =A0 =A0 =A0 =A0Dim cmdMedia As New Data.SqlClient.SqlCommand ' 3rd store=
d
> > procedure
>
> > =A0 =A0 =A0 =A0 =A0 =A0With cmdALL
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.CommandType =3D Data.CommandType.StoredP=
rocedure
>
> > =A0 =A0 =A0 =A0 =A0 =A0.CommandText =3D "GetAllTopics"
>
> > =A0 =A0 =A0 =A0 =A0 =A0If DropDownList2.SelectedValue =3D "-1" Then
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Parameters.AddWithValue("@Type",
> > DropDownList2.SelectedValue)
> > =A0 =A0 =A0 =A0 =A0 =A0End If
> > =A0 =A0 =A0 =A0 =A0 =A0.Connection =3D conn
>
> > =A0 =A0 =A0 =A0End With
>
> > =A0 =A0 =A0 =A0Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdALL)
>
> > =A0 =A0 =A0 =A0With cmdTopic
> > =A0 =A0 =A0 =A0 =A0 =A0.CommandType =3D Data.CommandType.StoredProcedure=

>
> > =A0 =A0 =A0 =A0 =A0 =A0.CommandText =3D "GetByTopic"
>
> > =A0 =A0 =A0 =A0 =A0 =A0If DropDownList3.SelectedIndex > 0 Then
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Parameters.AddWithValue("@classification=
id",
> > Integer.Parse(DropDownList2.SelectedValue))
> > =A0 =A0 =A0 =A0 =A0 =A0End If
> > =A0 =A0 =A0 =A0 =A0 =A0.Connection =3D conn
> > =A0 =A0 =A0 =A0End With
>
> > =A0 =A0 =A0 =A0Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdTopic=
)
>
> > =A0 =A0 =A0 =A0With cmdMedia
> > =A0 =A0 =A0 =A0 =A0 =A0.CommandType =3D Data.CommandType.StoredProcedure=

>
> > =A0 =A0 =A0 =A0 =A0 =A0.CommandText =3D "GetByMedia"
>
> > =A0 =A0 =A0 =A0 =A0 =A0If DropDownList3.SelectedValue > 0 Then
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Parameters.AddWithValue("@Mediaid",
> > DropDownList3.SelectedValue)
> > =A0 =A0 =A0 =A0 =A0 =A0End If
> > =A0 =A0 =A0 =A0 =A0 =A0.Connection =3D conn
> > =A0 =A0 =A0 =A0End With
>
> > =A0 =A0 =A0 =A0Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdMedia=
)
>
> > =A0 =A0 =A0 =A0Try
>
> > =A0 =A0 =A0 =A0 =A0 =A0conn.Open()
>
> > =A0 =A0 =A0 =A0 =A0 =A0Dim ds As New Data.DataSet
> > =A0 =A0 =A0 =A0 =A0 =A0adapter.Fill(ds)
>
> > =A0 =A0 =A0 =A0 =A0 =A0GridView1.DataSource =3D ds
>
> > =A0 =A0 =A0 =A0 =A0 =A0GridView1.DataBind()
>
> > =A0 =A0 =A0 =A0Finally
>
> > =A0 =A0 =A0 =A0 =A0 =A0conn.Close()
>
> > =A0 =A0 =A0 =A0End Try- Hide quoted text -
>
> - Show quoted text -

Thanks Ray for your help!

I'm getting:
Local variable 'adapter' is already declared in the current block for:
Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdTopic)
and
Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdMedia)

Re: How to add adapters

"JJ297" wrote in message
news:201a0f71-9eaf-462e-a001-b0981dfcd11c@l1g2000hsa.googleg roups.com...

> I'm getting:
> Local variable 'adapter' is already declared in the current block for:
> Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdTopic)
> and
> Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdMedia)

That means exactly what it says - you are trying to declare two variables
with the same name within the same scope.

Dim adapter1 As New Data.SqlClient.SqlDataAdapter(cmdTopic)
Dim adapter2 As New Data.SqlClient.SqlDataAdapter(cmdMedia)

should fix it, though it's not particularly elegant or efficient...


--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Re: How to add adapters

"JJ297" wrote in message
news:201a0f71-9eaf-462e-a001-b0981dfcd11c@l1g2000hsa.googleg roups.com...
> I'm getting:
> Local variable 'adapter' is already declared in the current block for:

Give all three adapters different names e.g.
Dim adapter1 As New Data.SqlClient.SqlDataAdapter(cmdTopic)
and
Dim adapter2 As New Data.SqlClient.SqlDataAdapter(cmdMedia)

Re: How to add adapters

On Jan 15, 9:24=A0am, "Leon Mayne" wrote:
> "JJ297" wrote in message
>
> news:201a0f71-9eaf-462e-a001-b0981dfcd11c@l1g2000hsa.googleg roups.com...
>
> > I'm getting:
> > Local variable 'adapter' is already declared in the current block for:
>
> Give all three adapters different names e.g.
> =A0 Dim adapter1 As New Data.SqlClient.SqlDataAdapter(cmdTopic)
> and
> =A0 Dim adapter2 As New Data.SqlClient.SqlDataAdapter(cmdMedia)

Thanks Leon and Mark that worked but now I'm getting:

Procedure 'GETALLTopics' expects parameter '@type', which was not
supplied.

Here's my stored procedure:
CREATE PROCEDURE GETALLTopics

@type char(2)

AS

if @type =3D '-1'

Begin
Select distinct Titles.Titleid, titles.TITLE, titles.
[Descriptions],classifications.[Description] as TOPIC, media.
[description] as MEDIA

FROM Titles
JOIN resources
ON Titles.titleid =3D resources.titleid
JOIN titleclassification
ON Titles.titleid =3D titleclassification.titleid
Join classifications
on titleclassification.classificationid =3D
classifications.classificationid
Join media
on resources.mediaid =3D media.mediaid

order by classifications.[description]

End

GO

Is this the right wat to call it in the code behind?

With cmdALL
.CommandType =3D Data.CommandType.StoredProcedure

.CommandText =3D "GetAllTopics"

If DropDownList2.SelectedValue =3D "-1" Then
.Parameters.AddWithValue("@Type",
DropDownList2.SelectedValue)
End If
.Connection =3D conn

End With

Re: How to add adapters

This conditional seems to be wrong:

If DropDownList2.SelectedValue = "-1" Then

I think you should just add the parameter anyway, as the conditional is
redundant. Get rid of the if line and the end if line and just leave:

..Parameters.AddWithValue("@Type", DropDownList2.SelectedValue)

Re: How to add adapters

"JJ297" wrote in message
news:8172b67e-9452-4459-a206-a54483361716@m34g2000hsb.google groups.com...

Looking a bit further, it seems your code doesn't actually do anything
anyway. You are passing in the selected value of a dropdown box, and then
ignoring it by having:

if @type = '-1'

In your stored procedure without any query for when it isn't -1.

Re: How to add adapters

On Jan 15, 9:53=A0am, "Leon Mayne" wrote:
> "JJ297" wrote in message
>
> news:8172b67e-9452-4459-a206-a54483361716@m34g2000hsb.google groups.com...
>
> Looking a bit further, it seems your code doesn't actually do anything
> anyway. You are passing in the selected value of a dropdown box, and then
> ignoring it by having:
>
> if @type =3D '-1'
>
> In your stored procedure without any query for when it isn't -1.

Okay Leon,

I removed the code you asked me too and it now works. Don't quite
understand what you mean about "in your stored procedure without any
query for when it isn't -1."

I actually have another stored procedure I'm using called GetByTopic
if its not -1. This is why I put the if statement in there.

Do I leave the stored procedure as is? This is getting all topics if
the type is -1
CREATE PROCEDURE GETALLTopics
@type char(2)
AS

if @type =3D '-1'

Begin
Select distinct Titles.Titleid, titles.TITLE, titles.
[Descriptions],classifications.[Description] as TOPIC, media.
[description] as MEDIA

FROM Titles
JOIN resources
ON Titles.titleid =3D resources.titleid
JOIN titleclassification
ON Titles.titleid =3D titleclassification.titleid
Join classifications
on titleclassification.classificationid =3D
classifications.classificationid
Join media
on resources.mediaid =3D media.mediaid

order by classifications.[description]

End

GO

After running it again I'm now getting:
The IListSource does not contain any data sources. on
GridView1.DAtaSource =3D ds

Here's the code behind again after I've updated it:


Protected Sub Button2_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button2.Click

Dim conn As New
Data.SqlClient.SqlConnection(ConfigurationManager.Connection Strings("TrainUs=
erConnectionString").ConnectionString)

Dim cmdALL As New Data.SqlClient.SqlCommand
Dim cmdTopic As New Data.SqlClient.SqlCommand 'command for
second stored procedure
Dim cmdMedia As New Data.SqlClient.SqlCommand ' 3rd stored
procedure


With cmdALL
.CommandType =3D Data.CommandType.StoredProcedure

.CommandText =3D "GetAllTopics"


.Parameters.AddWithValue("@Type",
DropDownList2.SelectedValue)

.Connection =3D conn

End With

Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdALL)

With cmdTopic
.CommandType =3D Data.CommandType.StoredProcedure

.CommandText =3D "GetByTopic"
If DropDownList2.SelectedValue <> "-1" Then
.Parameters.AddWithValue("@classificationid",
Integer.Parse(DropDownList2.SelectedValue))
End If

.Connection =3D conn
End With

Dim adapter1 As New Data.SqlClient.SqlDataAdapter(cmdTopic)

With cmdMedia
.CommandType =3D Data.CommandType.StoredProcedure

.CommandText =3D "GetByMedia"

If DropDownList3.SelectedIndex > 0 Then
.Parameters.AddWithValue("@Mediaid",
DropDownList3.SelectedValue)
End If
.Connection =3D conn
End With

Dim adapter2 As New Data.SqlClient.SqlDataAdapter(cmdMedia)

Try

conn.Open()

Dim ds As New Data.DataSet
adapter.Fill(ds)

GridView1.DataSource =3D ds

GridView1.DataBind()

Finally

conn.Close()

End Try

Re: How to add adapters

Lets start from the top. What exactly are you trying to do? It looks like
you have two drop down lists (DropDownList2 and DropDownList3) and you want
to populate a gridview based on what the user has selected? If the user has
not selected anything in DropDownList2 then you want to display all records.
If they have selected something then you want to restrict the results based
on what they have selected?

Is the above correct? If so then you only need one stored procedure,
SQLCommand, and SQLDataAdapter object, something like this:

Dim conn As New
Data.SqlClient.SqlConnection(ConfigurationManager.Connection Strings("TrainUserConnectionString").ConnectionString)

Dim cmdTopics As New Data.SqlClient.SqlCommand

With cmdTopics
.CommandType = Data.CommandType.StoredProcedure

.CommandText = "GetTopics"

.Parameters.AddWithValue("@Type",
DropDownList2.SelectedValue)

.Connection = conn

End With

Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdTopics)

Try

conn.Open()

Dim ds As New Data.DataSet
adapter.Fill(ds)

GridView1.DataSource = ds

GridView1.DataBind()

Finally

conn.Close()

End Try

And your stored procedure (GetTopics):

CREATE PROCEDURE GETALLTopics
@type char(2)
AS

if @type = '-1'
Begin
Select distinct Titles.Titleid, titles.TITLE, titles.
[Descriptions],classifications.[Description] as TOPIC, media.
[description] as MEDIA
FROM Titles
JOIN resources
ON Titles.titleid = resources.titleid
JOIN titleclassification
ON Titles.titleid = titleclassification.titleid
Join classifications
on titleclassification.classificationid =
classifications.classificationid
Join media
on resources.mediaid = media.mediaid
order by classifications.[description]
End
ELSE
BEGIN
-- Query here for restricting results, e.g.
Select distinct Titles.Titleid, titles.TITLE, titles.
[Descriptions],classifications.[Description] as TOPIC, media.
[description] as MEDIA
FROM Titles
JOIN resources
ON Titles.titleid = resources.titleid
JOIN titleclassification
ON Titles.titleid = titleclassification.titleid
Join classifications
on titleclassification.classificationid =
classifications.classificationid
Join media
on resources.mediaid = media.mediaid
WHERE
sometable.columnname = @type
order by classifications.[description]

END

I'm not sure what the third adapter and DropDownList3 are for. Is that a
separate query for something else or are you trying to put the results from
that into the same gridview?

Re: How to add adapters

On Jan 15, 10:39=A0am, "Leon Mayne" wrote:
> Lets start from the top. What exactly are you trying to do? It looks like
> you have two drop down lists (DropDownList2 and DropDownList3) and you wan=
t
> to populate a gridview based on what the user has selected? If the user ha=
s
> not selected anything in DropDownList2 then you want to display all record=
s.
> If they have selected something then you want to restrict the results base=
d
> on what they have selected?
>
> Is the above correct? If so then you only need one stored procedure,
> SQLCommand, and SQLDataAdapter object, something like this:
>
> =A0 =A0 =A0 Dim conn As New
> Data.SqlClient.SqlConnection(ConfigurationManager.Connection Strings("Train=
U=ADserConnectionString").ConnectionString)
>
> =A0 =A0 =A0 =A0 Dim cmdTopics As New Data.SqlClient.SqlCommand
>
> =A0 =A0 =A0 =A0 =A0 =A0 With cmdTopics
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .CommandType =3D Data.CommandType.StoredPr=
ocedure
>
> =A0 =A0 =A0 =A0 =A0 =A0 .CommandText =3D "GetTopics"
>
> =A0 =A0 =A0 =A0 =A0 =A0 .Parameters.AddWithValue("@Type",
> DropDownList2.SelectedValue)
>
> =A0 =A0 =A0 =A0 =A0 =A0 .Connection =3D conn
>
> =A0 =A0 =A0 =A0 End With
>
> =A0 =A0 =A0 =A0 Dim adapter As New Data.SqlClient.SqlDataAdapter(cmdTopics=
)
>
> =A0 =A0 =A0 =A0 Try
>
> =A0 =A0 =A0 =A0 =A0 =A0 conn.Open()
>
> =A0 =A0 =A0 =A0 =A0 =A0 Dim ds As New Data.DataSet
> =A0 =A0 =A0 =A0 =A0 =A0 adapter.Fill(ds)
>
> =A0 =A0 =A0 =A0 =A0 =A0 GridView1.DataSource =3D ds
>
> =A0 =A0 =A0 =A0 =A0 =A0 GridView1.DataBind()
>
> =A0 =A0 =A0 =A0 Finally
>
> =A0 =A0 =A0 =A0 =A0 =A0 conn.Close()
>
> =A0 =A0 =A0 =A0 End Try
>
> And your stored procedure (GetTopics):
>
> CREATE PROCEDURE GETALLTopics
> @type char(2)
> AS
>
> if @type =3D '-1'
> Begin
> Select distinct Titles.Titleid, titles.TITLE, titles.
> [Descriptions],classifications.[Description] as TOPIC, media.
> [description] as MEDIA
> FROM Titles
> =A0 JOIN resources
> =A0 =A0 ON Titles.titleid =3D resources.titleid
> =A0 JOIN titleclassification
> =A0 =A0 ON Titles.titleid =3D titleclassification.titleid
> Join classifications
> on titleclassification.classificationid =3D
> classifications.classificationid
> Join media
> on resources.mediaid =3D media.mediaid
> order by classifications.[description]
> End
> ELSE
> BEGIN
> =A0 =A0 -- Query here for restricting results, e.g.
> Select distinct Titles.Titleid, titles.TITLE, titles.
> [Descriptions],classifications.[Description] as TOPIC, media.
> [description] as MEDIA
> FROM Titles
> =A0 JOIN resources
> =A0 =A0 ON Titles.titleid =3D resources.titleid
> =A0 JOIN titleclassification
> =A0 =A0 ON Titles.titleid =3D titleclassification.titleid
> Join classifications
> on titleclassification.classificationid =3D
> classifications.classificationid
> Join media
> on resources.mediaid =3D media.mediaid
> WHERE
> sometable.columnname =3D @type
> order by classifications.[description]
>
> END
>
> I'm not sure what the third adapter and DropDownList3 are for. Is that a
> separate query for something else or are you trying to put the results fro=
m
> that into the same gridview?

Yes this is exactly what I want to do. This is what it the page looks
like (it's in a table):

Browse by topic(DD2) Browse by
Media(DD3) Browse by Title (TitleSearch Text Box)

ALL
ALL Textbox to sort by letter


I have seven stored procedures to use to populate one gridview. I was
trying to get three working first then I would be able to add the
others.

I want if ALL is selected in DD2 or DD3 then use stored procedure
GetALLTOPICS
If a topic is selected in DD2 I want to use GetByTopic (which will
pull the classificationid of the selected item & display in gridview1)
If a Media is selected in DD3 I want to use GetByMedia (which will
pull the mediaid of the selected item & display in gridview1)

Yes DD3 and the other adapter is a separate query for something else I
am trying to put the results from
into the same gridview?

I hope this is more clear for you.
Thanks so much!

Re: How to add adapters

"JJ297" wrote in message
news:91cb259a-2188-4dde-8f1d-d0ff8ce67f9e@l32g2000hse.google groups.com...
> I have seven stored procedures to use to populate one gridview. I was
> trying to get three working first then I would be able to add the
> others.

OK, note that (as explained in my previous post) you don't need two separate
stored procedures to display all records and limited records if the query is
the same, you can either use:

IF @var = -1
BEGIN
-- Select everything
END
ELSE
BEGIN
-- Select limited results
END

Or even better:

SELECT
col1,
col2
FROM
yourtable
WHERE
(@var = -1 OR (col3 = @var))

You also do not need one data adapter and one command object per query if
you are populating the same gridview. Just conditionally set the CommandText
and parameters based on what the user has selected in the drop downs.

Re: How to add adapters

On Jan 15, 11:19=A0am, "Leon Mayne" wrote:
> "JJ297" wrote in message
>
> news:91cb259a-2188-4dde-8f1d-d0ff8ce67f9e@l32g2000hse.google groups.com...
>
> > I have seven stored procedures to use to populate one gridview. =A0I was=

> > trying to get three working first then I would be able to add the
> > others.
>
> OK, note that (as explained in my previous post) you don't need two separa=
te
> stored procedures to display all records and limited records if the query =
is
> the same, you can either use:
>
> IF @var =3D -1
> BEGIN
> =A0 =A0 -- Select everything
> END
> ELSE
> BEGIN
> =A0 =A0 -- Select limited results
> END
>
> Or even better:
>
> SELECT
> =A0 =A0 col1,
> =A0 =A0 col2
> FROM
> =A0 =A0 yourtable
> WHERE
> =A0 =A0 (@var =3D -1 OR (col3 =3D @var))
>
> You also do not need one data adapter and one command object per query if
> you are populating the same gridview. Just conditionally set the CommandTe=
xt
> and parameters based on what the user has selected in the drop downs.

Okay this sounds great Leon. I will use one stored procedure and
then set it up as you have suggested. So the way you are telling me
to set it up is to place all of my code in the one stored procedure
and take care of the if else statements there. Makes sense. I will
try it and get back to you. Thanks!

Re: How to add adapters

"JJ297" wrote in message
news:b89fb759-066f-434c-9523-3175f0ae8a1d@l1g2000hsa.googleg roups.com...
> Okay this sounds great Leon. I will use one stored procedure and
> then set it up as you have suggested. So the way you are telling me
> to set it up is to place all of my code in the one stored procedure
> and take care of the if else statements there. Makes sense. I will
> try it and get back to you. Thanks!

If you need a hand then zip and mail me the code and I can give it a tweak
for you if I get time one evening.

Re: How to add adapters

On Jan 15, 11:43=A0am, "Leon Mayne" wrote:
> "JJ297" wrote in message
>
> news:b89fb759-066f-434c-9523-3175f0ae8a1d@l1g2000hsa.googleg roups.com...
>
> > Okay this sounds great Leon. =A0 I will use one stored procedure and
> > then set it up as you have suggested. =A0So the way you are telling me
> > to set it up is to place all of my code in the one stored procedure
> > and take care of the if else statements there. =A0Makes sense. =A0I will=

> > try it and get back to you. =A0Thanks!
>
> If you need a hand then zip and mail me the code and I can give it a tweak=

> for you if I get time one evening.

THANKS SO MUCH LEON IT WORKS!!! This way is so much easier than what
I was trying to do. Of course you knew that!

Another question for you...I'm now trying to add all of my other
queries and getting incorrect syntex near the keyword Else. Don't
know what I'm doing wrong. Can you take a peek I got it when I added
the next query to get the mediaid.

alter PROCEDURE GETALLTopics
@type char(2),
@classificationid int,
@mediaid int
AS

if @type =3D '-1'
Begin
Select distinct Titles.Titleid, titles.TITLE, titles.
[Descriptions],classifications.[Description] as TOPIC, media.
[description] as MEDIA
FROM Titles
JOIN resources
ON Titles.titleid =3D resources.titleid
JOIN titleclassification
ON Titles.titleid =3D titleclassification.titleid
Join classifications
on titleclassification.classificationid =3D
classifications.classificationid
Join media
on resources.mediaid =3D media.mediaid
order by classifications.[description]
End
ELSE
BEGIN
-- Query here for restricting results, e.g.
Select distinct Titles.Titleid, titles.TITLE, titles.
[Descriptions],classifications.[Description] as TOPIC, media.
[description] as MEDIA
FROM Titles
JOIN resources
ON Titles.titleid =3D resources.titleid

JOIN titleclassification
ON Titles.titleid =3D titleclassification.titleid

Join classifications
on titleclassification.classificationid =3D
classifications.classificationid

Join media
on resources.mediaid =3D media.mediaid

where classifications.classificationid=3D@classificationid
order by titles.title
END

Else
Begin
select Distinct Titles.Titleid, Titles.TITLE,
Titles.descriptions,classifications.[description] as TOPIC, media.
[description] as MEDIA

from Titles
join resources on resources.Titleid =3D Titles.Titleid
join media on media.mediaid =3D resources.mediaid
join titleclassification on titleclassification.titleid =3D
titles.titleid
join classifications on classifications.classificationid =3D
titleclassification.classificationid

where media.mediaid =3D @mediaid

order by titles.titleid
End

Re: How to add adapters

On Jan 15, 12:20=A0pm, JJ297 wrote:
> On Jan 15, 11:43=A0am, "Leon Mayne" wrote:
>
> > "JJ297" wrote in message
>
> >news:b89fb759-066f-434c-9523-3175f0ae8a1d@l1g2000hsa.google groups.com...
>
> > > Okay this sounds great Leon. =A0 I will use one stored procedure and
> > > then set it up as you have suggested. =A0So the way you are telling me=

> > > to set it up is to place all of my code in the one stored procedure
> > > and take care of the if else statements there. =A0Makes sense. =A0I wi=
ll
> > > try it and get back to you. =A0Thanks!
>
> > If you need a hand then zip and mail me the code and I can give it a twe=
ak
> > for you if I get time one evening.
>
> THANKS SO MUCH LEON IT WORKS!!! =A0This way is so much easier than what
> I was trying to do. =A0Of course you knew that!
>
> Another question for you...I'm now trying to add all of my other
> queries and getting incorrect syntex near the keyword Else. =A0Don't
> know what I'm doing wrong. =A0Can you take a peek =A0I got it when I added=

> the next query to get the mediaid.
>
> alter PROCEDURE GETALLTopics
> @type char(2),
> @classificationid =A0int,
> @mediaid int
> AS
>
> if @type =3D '-1'
> Begin
> Select distinct Titles.Titleid, titles.TITLE, titles.
> [Descriptions],classifications.[Description] as TOPIC, media.
> [description] as MEDIA
> FROM Titles
> =A0 JOIN resources
> =A0 =A0 ON Titles.titleid =3D resources.titleid
> =A0 JOIN titleclassification
> =A0 =A0 ON Titles.titleid =3D titleclassification.titleid
> Join classifications
> on titleclassification.classificationid =3D
> classifications.classificationid
> Join media
> on resources.mediaid =3D media.mediaid
> order by classifications.[description]
> End
> ELSE
> BEGIN
> =A0 =A0 -- Query here for restricting results, e.g.
> Select distinct Titles.Titleid, titles.TITLE, titles.
> [Descriptions],classifications.[Description] as TOPIC, media.
> [description] as MEDIA
> FROM Titles
> JOIN resources
> ON Titles.titleid =3D resources.titleid
>
> JOIN titleclassification
> ON Titles.titleid =3D titleclassification.titleid
>
> Join classifications
> on titleclassification.classificationid =3D
> classifications.classificationid
>
> Join media
> on resources.mediaid =3D media.mediaid
>
> where classifications.classificationid=3D@classificationid
> order by titles.title
> END
>
> Else
> Begin
> select Distinct Titles.Titleid, Titles.TITLE,
> Titles.descriptions,classifications.[description] as TOPIC, =A0media.
> [description] as MEDIA
>
> from Titles
> join resources on resources.Titleid =3D Titles.Titleid
> join media on media.mediaid =3D resources.mediaid
> join titleclassification on titleclassification.titleid =3D
> titles.titleid
> join classifications on classifications.classificationid =3D
> titleclassification.classificationid
>
> where media.mediaid =3D @mediaid
>
> order by titles.titleid
> End

Okay I figured out what I need...I need a big if statement and then
else at the end. How would I write that to add my other queries. If
I close the first if statement (if @type =3D "-1")
my code
end if
Then the next query to check for classificationid
If (don't know what to put here)
Code
End if

Then the next query to check for mediaid (and so on...)

Re: How to add adapters

On Jan 15, 12:30=A0pm, JJ297 wrote:
> On Jan 15, 12:20=A0pm, JJ297 wrote:
>
>
>
>
>
> > On Jan 15, 11:43=A0am, "Leon Mayne" wrote:
>
> > > "JJ297" wrote in message
>
> > >news:b89fb759-066f-434c-9523-3175f0ae8a1d@l1g2000hsa.google groups.com..=
..
>
> > > > Okay this sounds great Leon. =A0 I will use one stored procedure and=

> > > > then set it up as you have suggested. =A0So the way you are telling =
me
> > > > to set it up is to place all of my code in the one stored procedure
> > > > and take care of the if else statements there. =A0Makes sense. =A0I =
will
> > > > try it and get back to you. =A0Thanks!
>
> > > If you need a hand then zip and mail me the code and I can give it a t=
weak
> > > for you if I get time one evening.
>
> > THANKS SO MUCH LEON IT WORKS!!! =A0This way is so much easier than what
> > I was trying to do. =A0Of course you knew that!
>
> > Another question for you...I'm now trying to add all of my other
> > queries and getting incorrect syntex near the keyword Else. =A0Don't
> > know what I'm doing wrong. =A0Can you take a peek =A0I got it when I add=
ed
> > the next query to get the mediaid.
>
> > alter PROCEDURE GETALLTopics
> > @type char(2),
> > @classificationid =A0int,
> > @mediaid int
> > AS
>
> > if @type =3D '-1'
> > Begin
> > Select distinct Titles.Titleid, titles.TITLE, titles.
> > [Descriptions],classifications.[Description] as TOPIC, media.
> > [description] as MEDIA
> > FROM Titles
> > =A0 JOIN resources
> > =A0 =A0 ON Titles.titleid =3D resources.titleid
> > =A0 JOIN titleclassification
> > =A0 =A0 ON Titles.titleid =3D titleclassification.titleid
> > Join classifications
> > on titleclassification.classificationid =3D
> > classifications.classificationid
> > Join media
> > on resources.mediaid =3D media.mediaid
> > order by classifications.[description]
> > End
> > ELSE
> > BEGIN
> > =A0 =A0 -- Query here for restricting results, e.g.
> > Select distinct Titles.Titleid, titles.TITLE, titles.
> > [Descriptions],classifications.[Description] as TOPIC, media.
> > [description] as MEDIA
> > FROM Titles
> > JOIN resources
> > ON Titles.titleid =3D resources.titleid
>
> > JOIN titleclassification
> > ON Titles.titleid =3D titleclassification.titleid
>
> > Join classifications
> > on titleclassification.classificationid =3D
> > classifications.classificationid
>
> > Join media
> > on resources.mediaid =3D media.mediaid
>
> > where classifications.classificationid=3D@classificationid
> > order by titles.title
> > END
>
> > Else
> > Begin
> > select Distinct Titles.Titleid, Titles.TITLE,
> > Titles.descriptions,classifications.[description] as TOPIC, =A0media.
> > [description] as MEDIA
>
> > from Titles
> > join resources on resources.Titleid =3D Titles.Titleid
> > join media on media.mediaid =3D resources.mediaid
> > join titleclassification on titleclassification.titleid =3D
> > titles.titleid
> > join classifications on classifications.classificationid =3D
> > titleclassification.classificationid
>
> > where media.mediaid =3D @mediaid
>
> > order by titles.titleid
> > End
>
> Okay I figured out what I need...I need a big if statement and then
> else at the end. =A0How would I write that to add my other queries. =A0If
> I close the first if statement (if @type =3D "-1")
> my code
> end if
> Then the next query to check for classificationid
> If (don't know what to put here)
> Code
> End if
>
> Then the next query to check for mediaid (and so on...)- Hide quoted text =
-
>
> - Show quoted text -

Thanks so much Leon I figured it out! Thanks for all of your
assistance. I'm certain I will be sending another question in the
near future ;-) Thanks also for the great lesson learned in regards
to stored procedures and datasets!