CODEWITHSUNDEEP
c#asp.netintvarchar
Hani Honey
Hani Honey

Reputation: 2131

Converting varchar to monthDate

I'm receiving this error message: Conversion failed when converting the varchar value 'monthDate' to data type int.

I have this SQL statement: 

String[][] results = lm.SqlSelect("SELECT COUNT(*) FROM members_ WHERE DATEPART(month, DateUnsub_) = 'monthDate' AND DATEPART(year, DateUnsub_) = 2011 AND DATEDIFF(day, DateJoined_, DateUnsub_) <= 30");

and I have an int here whose value changes depending on the dateLookup value:

switch (dateLookup)
        {
            case "January":
                monthDate = 01;
                break;
            case "February":
                monthDate = 02;
                break;
            case "March":
                monthDate = 03;
                break;
            case "April":
                monthDate = 04;
                break;
            case "May":
                monthDate = 05;
                break;
            case "June":
                monthDate = 06;
                break;
            case "July":
                monthDate = 07;
                break;
            case "August":
                monthDate = 08;
                break;
            case "September":
                monthDate = 09;
                break;
            case "October":
                monthDate = 10;
                break;
            case "November":
                monthDate = 11;
                break;
            case "December":
                monthDate = 12;
                break;
        }

My question is - where is the varchar coming from? When I take the "monthDate" variable out of the SQL statement and replace it with the numbers 05, for example, everything works fine.

Upvotes: 0

Views: 77

Answers (2)

tugberk
tugberk

Reputation: 58494

your query is only selecting Count of the rows. you didn't select anything else as I look at your t-sql code.

after seeing your properties, please use the following code;

 switch (dateLookup)
    {
        case "January":
            monthDate = "01";
            break;
        case "February":
            monthDate = "02";
            break;
        case "March":
            monthDate = "03";
            break;
        case "April":
            monthDate = "04";
            break;
        case "May":
            monthDate = "05";
            break;

And so on. In English, put quotes before and after your numbers.

Upvotes: 0

Bala R
Bala R

Reputation: 109037

'monthDate' in your query string is not a variable.

I think it should be 

String[][] results = lm.SqlSelect("SELECT COUNT(*) FROM members_ WHERE DATEPART(month, DateUnsub_) = " +monthDate + " AND DATEPART(year, DateUnsub_) = 2011 AND DATEDIFF(day, DateJoined_, DateUnsub_) <= 30");

Consider using string.Format() to make it more readable or better yet, parametrized command to protect against sql injection attacks.

Upvotes: 2

Related Questions