SQL query returning data, but not populating within code

Question

I have the following SQL code:

CREATE PROCEDURE [dbo].[sproc_tblUser_GetAllInfo]
    @Email NCHAR(25),
    @UserID INT
AS
    SELECT 
        tblUser.Firstname, tblUser.Lastname, tblPersonal.Age, tblPersonal.Sex
    FROM 
        tblUser, tblPersonal
    WHERE 
        tblUser.UserID = @UserID AND tblPersonal.UserID = @UserID

    IF @@ROWCOUNT = 0
        SELECT Firstname, Lastname
        FROM tblUser
        WHERE UserID = @UserID

    RETURN @@IDENTITY

This stored procedure is run within the following:

protected void Page_Load(object sender, EventArgs e)
{
        //copy email from query string
        Email = Convert.ToString(Request.QueryString["Email"]);
        lblWelcome.Text = "Welcome to Love Finder, " + Email;
        UserID = Convert.ToInt32(Request.QueryString["UserID"]);

        //add params
        db.AddParameter("Email", Email);
        db.AddParameter("UserID", UserID);

        //execute GetAllInfo query
        db.Execute("sproc_tblUser_GetAllInfo");

        //if entries in both tables found, do this
        if (db.Count == 1)
        {
            //get parameters
            User.ThisID.Firstname = Convert.ToString(db.DataTable.Rows[0]["Firstname"]);
            User.ThisID.Lastname = Convert.ToString(db.DataTable.Rows[0]["Lastname"]);
            User.ThisID.Sex = Convert.ToString(db.DataTable.Rows[0]["Sex"]);
            User.ThisID.Age = Convert.ToInt32(db.DataTable.Rows[0]["Age"]);

            //send to text boxes
            txtName.Text = Convert.ToString(User.ThisID.Firstname + "" + User.ThisID.Lastname);
            txtAge.Text = Convert.ToString(User.ThisID.Age);
            txtSex.Text = Convert.ToString(User.ThisID.Sex);
        }
        else 
        {
            //if only one table entry found (User), do this
            User.ThisID.Firstname = Convert.ToString(db.DataTable.Rows[0]["Firstname"]);
            User.ThisID.Lastname = Convert.ToString(db.DataTable.Rows[0]["Lastname"]);

            txtName.Text = Convert.ToString(User.ThisID.Firstname + "" + User.ThisID.Lastname);
        }
    }

The query relies on there being columns in both the User and Personal table. If I run the application with a member who has entered columns into both tables, it returns what I want it to. However, for the sub query that only collects data from the User table, I get the following error:

An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code

Additional information: There is no row at position 0.

TLDR:

When I execute the query by itself by giving it the parameters its being passed by the query string, it works and returns what it should. But, when run through the code, I'm getting the above error.

Can anyone shed light on this? It's probably something glaringly obvious, but I'm quite new to C# programming.

Kind regards

Huh? @@Identity should be used with SELECT INTO or INSERT. It is not appropriate for a mere SELECT. What are you trying to accomplish? — Gordon Linoff
– Gordon Linoff, Commented Apr 2, 2018 at 21:27
if db.Count is 0, then we would expect Rows[0] to throw an indexoutofrange — Marc Gravell
– Marc Gravell, Commented Apr 2, 2018 at 21:30
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged — marc_s
– marc_s, Commented Apr 3, 2018 at 4:52

Gordon Linoff · Accepted Answer · 2018-04-02 21:30:12Z

1

Why not just use a left join?

CREATE PROCEDURE [dbo].[sproc_tblUser_GetAllInfo] (
    @Email NVARCHAR(255),
    @UserID Int 
) AS
BEGIN
    SELECT u.Firstname, u.Lastname, p.Age, p.Sex
    FROM tblUser u LEFT JOIN
         tblPersonal p
         ON p.UserID = u.UserId
    WHERE u.UserID = @UserID ;
END;

This will always return four columns. The ones from tblPersonal will be NULL if no match is found.

Your problem is that your stored procedure sometimes returns two columns and sometimes returns four columns. Then, C# has a problem parsing the result set.

Notes:

If you are going to be using a result set, use a table-function instead of a stored procedure. That is the appropriate thing to use.
Don't use commas in the from clause. Use proper, correct join syntax.
Table aliases make a query easier to write and to read.
@@IDENTITY is not appropriate for SELECT statements.
NCHAR(25) is not appropriate for an email address. First, 25 characters might not be sufficient. Second, NCHAR() pads the address with spaces, and those are not appropriate.

answered Apr 2, 2018 at 21:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mause Over a year ago

This worked perfectly after I swapped out the IF in the C# with a try catch. Thanks a ton. Thanks for the notes as well. Will take those on board!

Mattkwish · Accepted Answer · 2018-04-02 21:43:21Z

0

If you want to just make what you currently have work, just change your stored proc from this:

SELECT Firstname, Lastname
FROM tblUser
WHERE UserID = @UserID

to this:

SELECT Firstname, Lastname, Age='', Sex=''
FROM tblUser
WHERE UserID = @UserID

You always need to return the same number of columns.

answered Apr 2, 2018 at 21:43

Mattkwish

7707 silver badges16 bronze badges

2 Comments

Mause Over a year ago

I went with a prior solution, but I will keep this in mind! Thank you.

Mattkwish Over a year ago

No problem! The chosen solution is definitely a better way of solving your problem :)

JaYdipD · Accepted Answer · 2018-04-02 21:58:52Z

return @@identity? any ways...I will modify your stored procedure as..

CREATE PROCEDURE [dbo].[sproc_tblUser_GetAllInfo](
    @Email NCHAR (25),
    @UserID Int
) AS
BEGIN
    SELECT tblUser.Firstname, tblUser.Lastname, tblPersonal.Age, tblPersonal.Sex
    FROM tblUser
    LEFT OUTER JOIN tblPersonal
    ON   tblUser.UserID = tblPersonal.UserID
    WHERE tblUser.UserID = @UserID
RETURN 0
END

and .net code if condition as, get rid of else

if (db.Count >= 1)
{
    //get parameters
    User.ThisID.Firstname = Convert.ToString(db.DataTable.Rows[0]["Firstname"]);
    User.ThisID.Lastname = Convert.ToString(db.DataTable.Rows[0]["Lastname"]);

//send to text boxes
txtName.Text = string.Format("{0} {1}", User.ThisID.Firstname, User.ThisID.Lastname);

if (!string.IsNullOrEmpty(db.DataTable.Rows[0]["Sex"]))
{
    User.ThisID.Sex = Convert.ToString(db.DataTable.Rows[0]["Sex"]);
    txtSex.Text = Convert.ToString(User.ThisID.Sex);
}

if (!string.IsNullOrEmpty(db.DataTable.Rows[0]["Age"]))
{
    User.ThisID.Age = Convert.ToInt32(db.DataTable.Rows[0]["Age"]);
    txtAge.Text = Convert.ToString(User.ThisID.Age);
}
}

Collectives™ on Stack Overflow

SQL query returning data, but not populating within code

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related