Passing Parameters to SQL and getting results back

Discussion in 'ASP .Net' started by Mike Dinnis, Nov 11, 2003.

  1. Mike Dinnis

    Mike Dinnis Guest

    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, Nov 11, 2003
    #1
    1. Advertising

  2. 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:...
    > 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, Nov 11, 2003
    #2
    1. Advertising

  3. Mike Dinnis

    Mike Dinnis Guest

    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" <> wrote in message
    news:%...
    > 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:...
    > > 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, Nov 11, 2003
    #3
  4. Mike Dinnis

    Mike Dinnis Guest

    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" <> wrote in message
    news:%...
    > 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:...
    > > 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, Nov 11, 2003
    #4
  5. 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
    Erik J Sawyer, Nov 11, 2003
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul D. Fox
    Replies:
    1
    Views:
    14,185
  2. Carlos Alejandro Pérez

    Passing back and forth parameters to modal browser windows

    Carlos Alejandro Pérez, Jun 8, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    509
    Eliyahu Goldin
    Jun 8, 2005
  3. Michael Cohen

    getting and passing variable number of parameters

    Michael Cohen, Dec 25, 2003, in forum: C Programming
    Replies:
    2
    Views:
    401
    David M. Wilson
    Dec 26, 2003
  4. Replies:
    2
    Views:
    467
    Peter Hansen
    May 7, 2005
  5. Belinda
    Replies:
    4
    Views:
    343
    Bob Barrows [MVP]
    Jun 11, 2004
Loading...

Share This Page