Passing Parameters to SQL and getting results back

[Mike Dinnis]

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]

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:uN*************@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]

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" <sj*****@aboutfortunate.com> wrote in message
news:%2****************@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:uN*************@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]

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" <sj*****@aboutfortunate.com> wrote in message
news:%2****************@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:uN*************@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]

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 aboutretrieving data from a SQL database. I think i've mastered techniques wherei create a sql string in the page and pass it to the Db and retrieveing datafrom 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 itdoesn'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 inthe proc then I can get query analyser to return values, just not from mypage. When passing parameters I can see in Profiler that the parameter ispassed to the proc but nothing comes back.

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

Cheers,

<M>ike