Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP .Net (http://www.velocityreviews.com/forums/f29-asp-net.html)
-   -   Passing Parameters to SQL and getting results back (http://www.velocityreviews.com/forums/t66416-passing-parameters-to-sql-and-getting-results-back.html)

Mike Dinnis 11-11-2003 09:11 AM

Passing Parameters to SQL and getting results back
 
Hi,

I've been working through a number of turorials to try to learn more about
retrieving data from a SQL database. I think i've mastered techniques where
i create a sql string in the page and pass it to the Db and retrieveing data
from a stored procedure, but I can't get the hang of parameters.

I have a method where I can get the parameters passed to the sp but it
doesn't want to return any results. Here's a copy of my code:

<democode>
Private objCnn as New SqlConnection
("server=(local);database=test;UID=sa;PWD=")

Sub Page_Load (Sender as Object, e as Eventargs)

'Stored procedure with parameters
Dim objCmd As New SqlCommand("sploginUser",objCnn)
objCmd.CommandType = CommandType.StoredProcedure

Dim objParam as New SqlParameter ("@UserName", SqlDbType.char)
objParam.Value = "Mike"
objCmd.Parameters.Add (objParam)

objParam = New SqlParameter ("@Password", SqlDbType.char)
objParam.Value = "m"
objCmd.Parameters.Add (objParam)

Dim objReader As SqlDataReader

Try
objCmd.Connection.Open()
objReader = objCmd.ExecuteReader()
Catch ex as Exception
lblMessage.Text = "Database error: " & ex.message
End Try

dgData.DataSource = objReader
dgData.DataBind()

' objReader.Close
objCmd.Connection.Close()
End Sub
</democode>

This calls the procedure in my database as follows:

<sampleproc>
CREATE PROCEDURE spLoginUser
@UserName varchar,
@Password varchar
AS
SELECT UserID FROM tblUsers
WHERE Username = @Username
AND Password = @Password
GO
</sampleproc>

All I get in return is a blank screen. If i give the parameters values in
the proc then I can get query analyser to return values, just not from my
page. When passing parameters I can see in Profiler that the parameter is
passed to the proc but nothing comes back.

Is there anything obvious here that i'm doing wrong?

Cheers,

<M>ike



S. Justin Gengo 11-11-2003 11:02 AM

Re: Passing Parameters to SQL and getting results back
 
Mike,

I see two things that could be a problem.

The first is in your stored procedure. Instead of "Go" at the end try
"Return".

The second is only a potential problem, I haven't tested if the way your are
adding your parameters to your SQLCommand works or not.

If changing "Go" to "Return" doesn't solve your problem you may also have to
create your parameters separately. Meaning don't re-use the same container
for the second parameter.

Here's how I create mine:

objCmd.Parameters.Add (New SqlClient.SqlParameter(("@UserName",
SqlDbType.varchar))
objCmd.Parameters("@UserName").Value = "m"

One other thing I noticed. In your stored procedure you are declaring your
input parameters as varchar but in the command you've declared them as char.

I hope this helps.

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche


"Mike Dinnis" <mike dot dinnis at abraxas-uk dot com> wrote in message
news:uNyUEPDqDHA.744@tk2msftngp13.phx.gbl...
> Hi,
>
> I've been working through a number of turorials to try to learn more about
> retrieving data from a SQL database. I think i've mastered techniques

where
> i create a sql string in the page and pass it to the Db and retrieveing

data
> from a stored procedure, but I can't get the hang of parameters.
>
> I have a method where I can get the parameters passed to the sp but it
> doesn't want to return any results. Here's a copy of my code:
>
> <democode>
> Private objCnn as New SqlConnection
> ("server=(local);database=test;UID=sa;PWD=")
>
> Sub Page_Load (Sender as Object, e as Eventargs)
>
> 'Stored procedure with parameters
> Dim objCmd As New SqlCommand("sploginUser",objCnn)
> objCmd.CommandType = CommandType.StoredProcedure
>
> Dim objParam as New SqlParameter ("@UserName", SqlDbType.char)
> objParam.Value = "Mike"
> objCmd.Parameters.Add (objParam)
>
> objParam = New SqlParameter ("@Password", SqlDbType.char)
> objParam.Value = "m"
> objCmd.Parameters.Add (objParam)
>
> Dim objReader As SqlDataReader
>
> Try
> objCmd.Connection.Open()
> objReader = objCmd.ExecuteReader()
> Catch ex as Exception
> lblMessage.Text = "Database error: " & ex.message
> End Try
>
> dgData.DataSource = objReader
> dgData.DataBind()
>
> ' objReader.Close
> objCmd.Connection.Close()
> End Sub
> </democode>
>
> This calls the procedure in my database as follows:
>
> <sampleproc>
> CREATE PROCEDURE spLoginUser
> @UserName varchar,
> @Password varchar
> AS
> SELECT UserID FROM tblUsers
> WHERE Username = @Username
> AND Password = @Password
> GO
> </sampleproc>
>
> All I get in return is a blank screen. If i give the parameters values in
> the proc then I can get query analyser to return values, just not from my
> page. When passing parameters I can see in Profiler that the parameter is
> passed to the proc but nothing comes back.
>
> Is there anything obvious here that i'm doing wrong?
>
> Cheers,
>
> <M>ike
>
>




Mike Dinnis 11-11-2003 01:48 PM

Re: Passing Parameters to SQL and getting results back
 
Thanks for your tips.

I've amended the sp to show Return rather than Go, but upon saving it SQL
adds the Go back again (and keeps the Return). The systax checker claims
that it is still valid. Should this cause concern?

Running it in this format still returns an empty page so I tried your
paramater assignment method. It seems to accept the first parameter but
ignores the second. Should the syntax be the same for both (excluding
paramenter name/value)?

I've also amended the code to reflect the sp's idea of paramater definition,
but still no joy.

I've trawled thorugh a whole load of Microsoft Library articles but most
rely on Windows security rather than mixed as I am attempting to use. Should
this make any difference?

Thanks,

<M>ike

"S. Justin Gengo" <sjgengo@aboutfortunate.com> wrote in message
news:%23BD98MEqDHA.1672@TK2MSFTNGP09.phx.gbl...
> Mike,
>
> I see two things that could be a problem.
>
> The first is in your stored procedure. Instead of "Go" at the end try
> "Return".
>
> The second is only a potential problem, I haven't tested if the way your

are
> adding your parameters to your SQLCommand works or not.
>
> If changing "Go" to "Return" doesn't solve your problem you may also have

to
> create your parameters separately. Meaning don't re-use the same container
> for the second parameter.
>
> Here's how I create mine:
>
> objCmd.Parameters.Add (New SqlClient.SqlParameter(("@UserName",
> SqlDbType.varchar))
> objCmd.Parameters("@UserName").Value = "m"
>
> One other thing I noticed. In your stored procedure you are declaring your
> input parameters as varchar but in the command you've declared them as

char.
>
> I hope this helps.
>
> Sincerely,
>
> --
> S. Justin Gengo, MCP
> Web Developer
>
> Free code library at:
> www.aboutfortunate.com
>
> "Out of chaos comes order."
> Nietzche
>
>
> "Mike Dinnis" <mike dot dinnis at abraxas-uk dot com> wrote in message
> news:uNyUEPDqDHA.744@tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I've been working through a number of turorials to try to learn more

about
> > retrieving data from a SQL database. I think i've mastered techniques

> where
> > i create a sql string in the page and pass it to the Db and retrieveing

> data
> > from a stored procedure, but I can't get the hang of parameters.
> >
> > I have a method where I can get the parameters passed to the sp but it
> > doesn't want to return any results. Here's a copy of my code:
> >
> > <democode>
> > Private objCnn as New SqlConnection
> > ("server=(local);database=test;UID=sa;PWD=")
> >
> > Sub Page_Load (Sender as Object, e as Eventargs)
> >
> > 'Stored procedure with parameters
> > Dim objCmd As New SqlCommand("sploginUser",objCnn)
> > objCmd.CommandType = CommandType.StoredProcedure
> >
> > Dim objParam as New SqlParameter ("@UserName",

SqlDbType.char)
> > objParam.Value = "Mike"
> > objCmd.Parameters.Add (objParam)
> >
> > objParam = New SqlParameter ("@Password", SqlDbType.char)
> > objParam.Value = "m"
> > objCmd.Parameters.Add (objParam)
> >
> > Dim objReader As SqlDataReader
> >
> > Try
> > objCmd.Connection.Open()
> > objReader = objCmd.ExecuteReader()
> > Catch ex as Exception
> > lblMessage.Text = "Database error: " & ex.message
> > End Try
> >
> > dgData.DataSource = objReader
> > dgData.DataBind()
> >
> > ' objReader.Close
> > objCmd.Connection.Close()
> > End Sub
> > </democode>
> >
> > This calls the procedure in my database as follows:
> >
> > <sampleproc>
> > CREATE PROCEDURE spLoginUser
> > @UserName varchar,
> > @Password varchar
> > AS
> > SELECT UserID FROM tblUsers
> > WHERE Username = @Username
> > AND Password = @Password
> > GO
> > </sampleproc>
> >
> > All I get in return is a blank screen. If i give the parameters values

in
> > the proc then I can get query analyser to return values, just not from

my
> > page. When passing parameters I can see in Profiler that the parameter

is
> > passed to the proc but nothing comes back.
> >
> > Is there anything obvious here that i'm doing wrong?
> >
> > Cheers,
> >
> > <M>ike
> >
> >

>
>




Mike Dinnis 11-11-2003 02:00 PM

Re: Passing Parameters to SQL and getting results back
 
Ah ha!

I think i've sussed it. The second parameter wasn't being accepted as I
hadn't changed the second line to reflect the new parameter name. (Doh!)

I also amended the proc as I noticed in Profiler that although it was now
expecting a varchar type I hadn't specified how many characters so it
defaulted to 1! By adding an arbitary figure (20) it did indeed return the
results I would have expected.

Thank you for the pointers!

<M>ike


"S. Justin Gengo" <sjgengo@aboutfortunate.com> wrote in message
news:%23BD98MEqDHA.1672@TK2MSFTNGP09.phx.gbl...
> Mike,
>
> I see two things that could be a problem.
>
> The first is in your stored procedure. Instead of "Go" at the end try
> "Return".
>
> The second is only a potential problem, I haven't tested if the way your

are
> adding your parameters to your SQLCommand works or not.
>
> If changing "Go" to "Return" doesn't solve your problem you may also have

to
> create your parameters separately. Meaning don't re-use the same container
> for the second parameter.
>
> Here's how I create mine:
>
> objCmd.Parameters.Add (New SqlClient.SqlParameter(("@UserName",
> SqlDbType.varchar))
> objCmd.Parameters("@UserName").Value = "m"
>
> One other thing I noticed. In your stored procedure you are declaring your
> input parameters as varchar but in the command you've declared them as

char.
>
> I hope this helps.
>
> Sincerely,
>
> --
> S. Justin Gengo, MCP
> Web Developer
>
> Free code library at:
> www.aboutfortunate.com
>
> "Out of chaos comes order."
> Nietzche
>
>
> "Mike Dinnis" <mike dot dinnis at abraxas-uk dot com> wrote in message
> news:uNyUEPDqDHA.744@tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I've been working through a number of turorials to try to learn more

about
> > retrieving data from a SQL database. I think i've mastered techniques

> where
> > i create a sql string in the page and pass it to the Db and retrieveing

> data
> > from a stored procedure, but I can't get the hang of parameters.
> >
> > I have a method where I can get the parameters passed to the sp but it
> > doesn't want to return any results. Here's a copy of my code:
> >
> > <democode>
> > Private objCnn as New SqlConnection
> > ("server=(local);database=test;UID=sa;PWD=")
> >
> > Sub Page_Load (Sender as Object, e as Eventargs)
> >
> > 'Stored procedure with parameters
> > Dim objCmd As New SqlCommand("sploginUser",objCnn)
> > objCmd.CommandType = CommandType.StoredProcedure
> >
> > Dim objParam as New SqlParameter ("@UserName",

SqlDbType.char)
> > objParam.Value = "Mike"
> > objCmd.Parameters.Add (objParam)
> >
> > objParam = New SqlParameter ("@Password", SqlDbType.char)
> > objParam.Value = "m"
> > objCmd.Parameters.Add (objParam)
> >
> > Dim objReader As SqlDataReader
> >
> > Try
> > objCmd.Connection.Open()
> > objReader = objCmd.ExecuteReader()
> > Catch ex as Exception
> > lblMessage.Text = "Database error: " & ex.message
> > End Try
> >
> > dgData.DataSource = objReader
> > dgData.DataBind()
> >
> > ' objReader.Close
> > objCmd.Connection.Close()
> > End Sub
> > </democode>
> >
> > This calls the procedure in my database as follows:
> >
> > <sampleproc>
> > CREATE PROCEDURE spLoginUser
> > @UserName varchar,
> > @Password varchar
> > AS
> > SELECT UserID FROM tblUsers
> > WHERE Username = @Username
> > AND Password = @Password
> > GO
> > </sampleproc>
> >
> > All I get in return is a blank screen. If i give the parameters values

in
> > the proc then I can get query analyser to return values, just not from

my
> > page. When passing parameters I can see in Profiler that the parameter

is
> > passed to the proc but nothing comes back.
> >
> > Is there anything obvious here that i'm doing wrong?
> >
> > Cheers,
> >
> > <M>ike
> >
> >

>
>




Erik J Sawyer 11-11-2003 02:50 PM

Passing Parameters to SQL and getting results back
 
ADO.Net can be particularly finicky when it comes to
matching parameter datatypes to the actual database
types. Try switching your parameters to
SqlDbType.VarChar.

For debugging, you can also try a couple of steps to see
what's being received. Use SqlDataReader.HasRows to
determine if anything was actually received. You might
also switch (for testing) to a Dataset, and check the
Rows.Count of the table to see how many rows you have.

Erik J Sawyer
Webmaster
Kingsport City Schools
>-----Original Message-----
>Hi,
>
>I've been working through a number of turorials to try

to learn more about
>retrieving data from a SQL database. I think i've

mastered techniques where
>i create a sql string in the page and pass it to the Db

and retrieveing data
>from a stored procedure, but I can't get the hang of

parameters.
>
>I have a method where I can get the parameters passed to

the sp but it
>doesn't want to return any results. Here's a copy of my

code:
>
><democode>
> Private objCnn as New SqlConnection
>("server=(local);database=test;UID=sa;PWD=")
>
> Sub Page_Load (Sender as Object, e as Eventargs)
>
> 'Stored procedure with parameters
> Dim objCmd As New SqlCommand("sploginUser",objCnn)
> objCmd.CommandType = CommandType.StoredProcedure
>
> Dim objParam as New SqlParameter

("@UserName", SqlDbType.char)
> objParam.Value = "Mike"
> objCmd.Parameters.Add (objParam)
>
> objParam = New SqlParameter ("@Password",

SqlDbType.char)
> objParam.Value = "m"
> objCmd.Parameters.Add (objParam)
>
> Dim objReader As SqlDataReader
>
> Try
> objCmd.Connection.Open()
> objReader = objCmd.ExecuteReader()
> Catch ex as Exception
> lblMessage.Text = "Database error: " &

ex.message
> End Try
>
> dgData.DataSource = objReader
> dgData.DataBind()
>
> ' objReader.Close
> objCmd.Connection.Close()
> End Sub
></democode>
>
>This calls the procedure in my database as follows:
>
><sampleproc>
>CREATE PROCEDURE spLoginUser
>@UserName varchar,
>@Password varchar
> AS
>SELECT UserID FROM tblUsers
>WHERE Username = @Username
> AND Password = @Password
>GO
></sampleproc>
>
>All I get in return is a blank screen. If i give the

parameters values in
>the proc then I can get query analyser to return values,

just not from my
>page. When passing parameters I can see in Profiler that

the parameter is
>passed to the proc but nothing comes back.
>
>Is there anything obvious here that i'm doing wrong?
>
>Cheers,
>
><M>ike




All times are GMT. The time now is 05:51 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.