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.ConnectionStrings("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

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

Ray Costanzo

"JJ297" <(E-Mail Removed)> wrote in message
news:d43fd4fc-c0da-413f-b7ea-(E-Mail Removed)...
> 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.ConnectionStrings("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

On Jan 15, 9:04*am, "Ray Costanzo" <my first name at lane34 dot
commercial> wrote:
> Please tell us what the error is that you're getting and on what line.
>
> Ray Costanzo
>
> "JJ297" <nc...@yahoo.com> wrote in message
>
> news:d43fd4fc-c0da-413f-b7ea-(E-Mail Removed)...
>
>
>
> > 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.ConnectionStrings("TrainU*serConnectionString").ConnectionString)
>
> > * * * *Dim cmdALL As New Data.SqlClient.SqlCommand
> > * * * *Dim cmdTopic As New Data.SqlClient.SqlCommand *'cmd for2nd
> > 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- 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)

"JJ297" <(E-Mail Removed)> wrote in message
news:201a0f71-9eaf-462e-a001-(E-Mail Removed)...

> 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

"JJ297" <(E-Mail Removed)> wrote in message
news:201a0f71-9eaf-462e-a001-(E-Mail Removed)...
> 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)

On Jan 15, 9:24*am, "Leon Mayne" <leon@rmv_me.mvps.org> wrote:
> "JJ297" <nc...@yahoo.com> wrote in message
>
> news:201a0f71-9eaf-462e-a001-(E-Mail Removed)...
>
> > 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)

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 = '-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

GO

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

With cmdALL
.CommandType = Data.CommandType.StoredProcedure

.CommandText = "GetAllTopics"

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

End With

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)

"JJ297" <(E-Mail Removed)> wrote in message
news:8172b67e-9452-4459-a206-(E-Mail Removed)...

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.

On Jan 15, 9:53*am, "Leon Mayne" <leon@rmv_me.mvps.org> wrote:
> "JJ297" <nc...@yahoo.com> wrote in message
>
> news:8172b67e-9452-4459-a206-(E-Mail Removed)...
>
> 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.

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 = '-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

GO

After running it again I'm now getting:
The IListSource does not contain any data sources. on
GridView1.DAtaSource = 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.ConnectionStrings("TrainUserConnectionString").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 = Data.CommandType.StoredProcedure

.CommandText = "GetAllTopics"


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

.Connection = conn

End With

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

With cmdTopic
.CommandType = Data.CommandType.StoredProcedure

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

.Connection = conn
End With

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

With cmdMedia
.CommandType = Data.CommandType.StoredProcedure

.CommandText = "GetByMedia"

If DropDownList3.SelectedIndex > 0 Then
.Parameters.AddWithValue("@Mediaid",
DropDownList3.SelectedValue)
End If
.Connection = 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 = ds

GridView1.DataBind()

Finally

conn.Close()

End Try

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.ConnectionStrings("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?