Issue with SqlCommand Parameters

Question

When I execute the following query in SSMS I get the expected result i.e. '1'

SELECT TSID
FROM tblTimesheets
WHERE TSUser = 'PJW' AND TSDate = '2012-01-18';

However, when the SqlCommand is produced by the code in my application the ExecuteScalar fails (it simply causes the method to exit with no error message).

public int GetID(string paramUser, DateTime paramDate)
    {
        string strSql = "SELECT TSID " +
                        "FROM tblTimesheets " +
                        "WHERE TSUser = @TSUser AND TSDate = @TSDate;";

        string strConnection = BuildConnectionString();
        SqlConnection linkToDB = new SqlConnection(strConnection);
        linkToDB.Open();

        SqlCommand sqlCom = new SqlCommand(strSql, linkToDB);
        sqlCom.Parameters.Add("@TSUser", SqlDbType.Text);
        sqlCom.Parameters.Add("@TSDate", SqlDbType.Date);
        sqlCom.Parameters["@TSUser"].Value = paramUser;
        sqlCom.Parameters["@TSDate"].Value = paramDate;


        int intResult = (Int32)sqlCom.ExecuteScalar();
        linkToDB.Close();
        return intResult;
    }

I've stepped through the code and can confirm the parameters are PJW and 2012-01-18 as required, but the ExecuteScalar returns any data, which I know should be there based on my comparable query in SSMS.

Please assist.

Can you run SQL Profiler, and find that statement hitting the DB? Grab the statement, and try running that manually in SSMS - should be easier to spot if you have the exact SQL hitting the db. — AdaTheDev
– AdaTheDev, Commented Jan 18, 2012 at 12:18
I know this is trivial, but are you sure the connection string is pointing to the same database? Do you connect using the same database user (maybe you are connecting through SSMS using your domain account, while your code is using a named account with less privileges)? — Strillo
– Strillo, Commented Jan 18, 2012 at 12:20
also, is paramDate exactly a date (i.e. midnight or similar)? could it be due to time-part, perhaps? — Marc Gravell
– Marc Gravell, Commented Jan 18, 2012 at 12:23
OK I've answered my own question after some considerable head scratching - but apparently I'm not allowed to pot an answer to my own questions yet - so I'll have to do it in the comments. — PJW
– PJW, Commented Jan 18, 2012 at 13:47

Daniel Hilgarth · Accepted Answer · 2012-01-18 12:21:38Z

1

Instead of SqlDbType.Text try any of the following, depending on the type of the column:

SqlDbType.VarChar
SqlDbType.NVarChar
SqlDbType.NText
SqlDbType.NChar
SqlDbType.Char

answered Jan 18, 2012 at 12:21

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

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

Comments

Sergey Kalinichenko · Accepted Answer · 2012-01-18 12:29:41Z

1

When the parameter is of DB type date, it is a good practice to defensively strip the time part on setting the parameter, like this:

sqlCom.Parameters.Add("@TSDate", SqlDbType.Date);
sqlCom.Parameters["@TSDate"].Value = paramDate.Date;

Please let me know if this does not help, and I'll remove my answer.

answered Jan 18, 2012 at 12:29

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Comments

Peter Monks · Accepted Answer · 2012-01-18 12:35:26Z

1

You say the ExecuteScalar fails with no error message. Wrap your code in a try-catch block to make sure any exceptions that ExecuteScalar() might be throwing are caught.

Other than that try and do as others have suggested and view the SQL produced using SQL Profiler, then run that SQL in SSMS to compare results.

answered Jan 18, 2012 at 12:35

Peter Monks

4,4292 gold badges26 silver badges38 bronze badges

Comments

Louis Kottmann · Accepted Answer · 2012-01-18 12:48:00Z

1

SELECT TSID
FROM tblTimesheets
WHERE TSUser = 'PJW' AND TSDate = '2012-01-18';

Here U r passing the exact date as parameter

Where as while passing the parameter in to the stored procedure you are passing "DateTime paramDate"

A date time variable

May be you need to parse to exact date format as supported by the stored procedure i.e you need to format the paramDate variable to 'YYYY-mm-DD'

I am not sure.. Try it.. and reply if it helps or not !

edited Jan 18, 2012 at 12:48

Louis Kottmann

16.8k5 gold badges67 silver badges89 bronze badges

answered Jan 18, 2012 at 12:29

mR.idiOt

1072 silver badges9 bronze badges

1 Comment

AdaTheDev Over a year ago

The SqlDbType of the parameter is Date though, so even if paramDate has a time, that should be stripped off before hitting the DB.

Collectives™ on Stack Overflow

Issue with SqlCommand Parameters

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related