How to add adapters

J

JJ297

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
 
R

Ray Costanzo

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

Ray Costanzo
 
J

JJ297

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

Ray Costanzo






























- Show quoted text -
Click to expand...

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)
 
M

Mark Rae [MVP]

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)
Click to expand...

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...
 
L

Leon Mayne

JJ297 said:
I'm getting:
Local variable 'adapter' is already declared in the current block for:
Click to expand...

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)
 
J

JJ297

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)
Click to expand...

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
 
L

Leon Mayne

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)
 
L

Leon Mayne

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.
 
J

JJ297

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.
Click to expand...

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
 
L

Leon Mayne

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?
 
J

JJ297

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("TrainU­serConnectionString").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?
Click to expand...

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!
 
L

Leon Mayne

JJ297 said:
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.
Click to expand...

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.
 
J

JJ297

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.
Click to expand...

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!
 
L

Leon Mayne

JJ297 said:
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!
Click to expand...

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.
 
J

JJ297

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.
Click to expand...

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 = '-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 classifications.classificationid=@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 = Titles.Titleid
join media on media.mediaid = resources.mediaid
join titleclassification on titleclassification.titleid =
titles.titleid
join classifications on classifications.classificationid =
titleclassification.classificationid

where media.mediaid = @mediaid

order by titles.titleid
End
 
J

JJ297

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.
Click to expand...

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 = '-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 classifications.classificationid=@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 = Titles.Titleid
join media on media.mediaid = resources.mediaid
join titleclassification on titleclassification.titleid =
titles.titleid
join classifications on classifications.classificationid =
titleclassification.classificationid

where media.mediaid = @mediaid

order by titles.titleid
End
Click to expand...

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 = "-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...)
 
J

JJ297

THANKS SO MUCH LEON IT WORKS!!!  This way is so much easier than what
I was trying to do.  Of course you knew that!
Click to expand...
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.
Click to expand...
alter PROCEDURE GETALLTopics
@type char(2),
@classificationid  int,
@mediaid int
AS
Click to expand...
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
Click to expand...
JOIN titleclassification
ON Titles.titleid = titleclassification.titleid
Click to expand...
Join classifications
on titleclassification.classificationid =
classifications.classificationid
Click to expand...
Join media
on resources.mediaid = media.mediaid
Click to expand...
where classifications.classificationid=@classificationid
order by titles.title
END
Click to expand...
Else
Begin
select Distinct Titles.Titleid, Titles.TITLE,
Titles.descriptions,classifications.[description] as TOPIC,  media.
[description] as MEDIA
Click to expand...
from Titles
join resources on resources.Titleid = Titles.Titleid
join media on media.mediaid = resources.mediaid
join titleclassification on titleclassification.titleid =
titles.titleid
join classifications on classifications.classificationid =
titleclassification.classificationid
Click to expand...
where media.mediaid = @mediaid
Click to expand...
order by titles.titleid
End
Click to expand...

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 = "-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 -
Click to expand...

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Binding 3 dropdowns with 1 gridview 0
Error with dropdown list 2
Sending email... incorrect value(parameter) comes back 1
Question about... 2
Need help setting up cast... 4
Need help deleting and updating items on .net page 0
Request.querystring problem 1
Need help with code... 10

Members online

Total: 44 (members: 1, guests: 43)
Robots: 199

Forum statistics

Threads
473,764
Messages
2,569,566
Members
45,041
Latest member
RomeoFarnh

Latest Threads

Top