473,716 Members | 2,558 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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("spl oginUser",objCn n)
objCmd.CommandT ype = CommandType.Sto redProcedure

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

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

Dim objReader As SqlDataReader

Try
objCmd.Connecti on.Open()
objReader = objCmd.ExecuteR eader()
Catch ex as Exception
lblMessage.Text = "Database error: " & ex.message
End Try

dgData.DataSour ce = objReader
dgData.DataBind ()

' objReader.Close
objCmd.Connecti on.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
Nov 17 '05 #1
4 2998
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.Paramete rs.Add (New SqlClient.SqlPa rameter(("@User Name",
SqlDbType.varch ar))
objCmd.Paramete rs("@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******** *****@tk2msftng p13.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("spl oginUser",objCn n)
objCmd.CommandT ype = CommandType.Sto redProcedure

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

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

Dim objReader As SqlDataReader

Try
objCmd.Connecti on.Open()
objReader = objCmd.ExecuteR eader()
Catch ex as Exception
lblMessage.Text = "Database error: " & ex.message
End Try

dgData.DataSour ce = objReader
dgData.DataBind ()

' objReader.Close
objCmd.Connecti on.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

Nov 17 '05 #2
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*****@aboutf ortunate.com> wrote in message
news:%2******** ********@TK2MSF TNGP09.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.Paramete rs.Add (New SqlClient.SqlPa rameter(("@User Name",
SqlDbType.varch ar))
objCmd.Paramete rs("@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******** *****@tk2msftng p13.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("spl oginUser",objCn n)
objCmd.CommandT ype = CommandType.Sto redProcedure

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

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

Dim objReader As SqlDataReader

Try
objCmd.Connecti on.Open()
objReader = objCmd.ExecuteR eader()
Catch ex as Exception
lblMessage.Text = "Database error: " & ex.message
End Try

dgData.DataSour ce = objReader
dgData.DataBind ()

' objReader.Close
objCmd.Connecti on.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


Nov 17 '05 #3
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*****@aboutf ortunate.com> wrote in message
news:%2******** ********@TK2MSF TNGP09.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.Paramete rs.Add (New SqlClient.SqlPa rameter(("@User Name",
SqlDbType.varch ar))
objCmd.Paramete rs("@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******** *****@tk2msftng p13.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("spl oginUser",objCn n)
objCmd.CommandT ype = CommandType.Sto redProcedure

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

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

Dim objReader As SqlDataReader

Try
objCmd.Connecti on.Open()
objReader = objCmd.ExecuteR eader()
Catch ex as Exception
lblMessage.Text = "Database error: " & ex.message
End Try

dgData.DataSour ce = objReader
dgData.DataBind ()

' objReader.Close
objCmd.Connecti on.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


Nov 17 '05 #4
ADO.Net can be particularly finicky when it comes to
matching parameter datatypes to the actual database
types. Try switching your parameters to
SqlDbType.VarCh ar.

For debugging, you can also try a couple of steps to see
what's being received. Use SqlDataReader.H asRows 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=(loca l);database=tes t;UID=sa;PWD=")

Sub Page_Load (Sender as Object, e as Eventargs)

'Stored procedure with parameters
Dim objCmd As New SqlCommand("spl oginUser",objCn n)
objCmd.CommandT ype = CommandType.Sto redProcedure

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

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

Dim objReader As SqlDataReader

Try
objCmd.Connecti on.Open()
objReader = objCmd.ExecuteR eader()
Catch ex as Exception
lblMessage.Text = "Database error: " & ex.message End Try

dgData.DataSour ce = objReader
dgData.DataBind ()

' objReader.Close
objCmd.Connecti on.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


Nov 17 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2300
by: gregpinero | last post by:
Hey everyone, I'm trying to call a system command "svnlook log \arms" from within python and process the results. However I'm having trouble getting the results of the command back into python. I'm using windows if that matters. Here's what I have so far: os.system("svnlook") #works but doesn't give me the output from
2
17354
by: zlatko | last post by:
There is a form in an Access Project (.adp, Access front end with SQL Server) for entering data into a table for temporary storing. Then, by clicking a botton, several action stored procedures (update, append) should be activated in order to transfer data to other tables. I tried to avoid any coding in VB, as I am not a professional, but I have found a statement in an article, that, unlike select queries, form's Input Property can't be...
7
49580
by: Pavils Jurjans | last post by:
Hallo, I have been programming for restricted environments where Internet Explorer is a standard, so I haven't stumbled upon this problem until now, when I need to write a DOM-compatible code. The question is about best practices for passing parameters to an event function. I have, say, the following HTML:
2
46397
by: Akira | last post by:
Hello. I'm having problem with passing parameters from .aspx file to user control. Could anyone tell me how to pass parameters from .aspx file to user control(.ascx) and how to recieve parameters at .ascx? Thank you for your help!
0
1550
by: Paul Allan | last post by:
I am new to ASP.net (intermediate ASP developer). I am developing a ASP.net web application and I am having some difficulty calling and passing parameters to a function that is declared in my "codebehind" page. Function declaration: Sub MyFunction(ByVal sender As Object, ByVal e As System.EventArgs)
4
7156
by: David Freeman | last post by:
Hi There! I'm just wondering if there's a way to pass parameters (as if you were passing parameters to a ASCX web control) when calling an ASPX page? e.g. MyDetailsPage.UserName = "david" OR... the only way to do it is to use the QueryString or Session object?
2
2024
by: Nab | last post by:
I have just tried to pass parameters to a procedure in VB 2005 and realised that you only need to pass the input parameter. The output parameter's value will be returned without the need to pass it as was the case in the previous version of VB e.g. VB .Net 2003. Here's the procedure in the web service: Public Sub Convert2Dollar(ByVal euroAmount As Double, ByRef usDollarAmount As Double) Dim exchangeRate As Double exchangeRate = 10 / 6
1
3473
by: Gert Wurzer | last post by:
Hi! I have an asp.net 2.0 web application that uses an iframe to access another asp.bet 2.0 webapp via https. I've already found a working prototype solution to create a dynamic iframe taking URL parameters with an asp.net 2.0 literal. My problem is, that i can't use the URL parameters due to security reasons. Are there any other possibilities for passing parameters to another asp.net 2.0 web application, so that the parameters cannot...
4
2813
by: =?Utf-8?B?UGF1bA==?= | last post by:
Hi, just wondering if anyone can provide a brief example of passing parameters from one webpage to another, C# VS2005? I need to pass several selected values of dropdown list boxes to a secondary page. I have a response.redirect("secondpage") in a button click event. Thanks. -- Paul G Software engineer.
0
8823
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8718
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9344
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9200
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9047
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7980
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5969
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
2543
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2119
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.