Conversion failed when converting the varchar value from int conversation

Question

I have a query that filtered among drop down selected condition:

protected void Get_Data()
{
    try
    {
        string str = "";
        DataTable dt = new DataTable();
        str = "SELECT ROW_NUMBER() OVER (ORDER BY RowId DESC)AS RowNumber,p.CampaignName,";
        str += "p.MobileNo,";
        str += "p.Message,";
        str += "p.CharCount,";
        str += "p.strSenderID as Sender,";
        str += "u.strUserName as UserId,";
        str += "ds.strDR as DeliveryStatus,";
        str += "ds.strDiscription as Original_DRStatus,";
        str += "m.strMessageType as MessageType,";
        str += "CONVERT(varchar(20), p.ReceiveTime) as ReceiveTime,";
        str += "CONVERT(varchar(20), p.SendTime) as SendTime,";
        str += "CONVERT(varchar(20), p.DoneTime) as DoneTime,";
        str += "p.RootId as Root,";
        str += "sp.ProviderName,";
        str += "(CASE intAccountType WHEN 1  THEN 'Promotional' WHEN 2 THEN 'Transactional' WHEN 3 THEN 'OptIn' END)  as AccountType";
        str += " INTO ##Results3 ";
        //str += " FROM NEWSMSLOG_2019_01..LOG_010119  p ";
        str += " FROM NEWBULKSMS.dbo.LOG_010119  p ";
        str += " INNER JOIN deliverstatus ds ON p.DeliveryStatus = ds.intDR_status inner join users u on u.id = p.userid";
        str += " left join senderids b on b.id = p.senderid";
        str += " left join messagetype m on m.intcode = p.messagetype";
        str += " left join smppproviders sp on sp.RootId = p.RootId";
        str += " where 1 = 1 ";

        if(ddl_users.SelectedItem.Text.ToString() == "All")
        {
            str += string.Empty;
        }
        else
        {
            str += " and p.userid = ' + CONVERT(INT,"+ Convert.ToInt32(ddl_users.SelectedValue.ToString())+") + '";
        }

        if (ddl_sender.SelectedItem.Text.ToString() == "All")
        {
            str += string.Empty;
        }
        else
        {
            str += " and p.Senderid = '+CONVERT(INT,"+ Convert.ToInt32(ddl_sender.SelectedValue.ToString())+")+'";
        }

        if(!string.IsNullOrEmpty(txt_mobileno.Text.Trim()))
        {
            str += " and p.MobileNo like '' % '"+txt_mobileno.Text.Trim()+"' % ''";
        }
        else
        {
            str += string.Empty;
        }

        if(ddl_delevery.SelectedItem.Text.ToString() =="All")
        {
            str += string.Empty;
        }
        else
        {
            str += " and p.Deliverystatus in ('+CONVERT(INT,"+ Convert.ToInt32(ddl_sender.SelectedValue.ToString())+")+')'";
        }

        if(!string.IsNullOrEmpty(txt_CompaignName.Text.Trim()))
        {
            str += " and p.CampaignName like ''%'"+txt_CompaignName.Text.Trim()+"'%'' '";
        }
        else
        {
            str += string.Empty;
        }

        if(ddl_account.SelectedItem.Text.ToString() == "All")
        {
            str += string.Empty;
        }
        else
        {
            str += " and p.accounttype = '+CONVERT(INT,"+ Convert.ToInt32(ddl_account.SelectedValue.ToString())+")+'";
        }

        obj.Execute_Query(str);

        string str1 = " SELECT * FROM ##Results3";
        //str1 += " SELECT";
        //str1 += " CampaignName,MobileNo,Message,CharCount,Sender,UserId,DeliveryStatus,";
        //str1 += "Original_DRStatus,MessageType,ReceiveTime,SendTime,DoneTime,Root,ProviderName,AccountType";
        //str1 += " FROM ##Results3";
        //str1 += " WHERE RowNumber BETWEEN('1' - 1) * '500' + 1 AND((('1' - 1) * '500' + 1) + '500') - 1";

        dt = obj.Get_Data_Table_From_Str(str1);
        string str2 = " DROP TABLE ##Results3";
        obj.Execute_Query(str2);

        ViewState["data"] = dt;

        egrd.DataSource = dt;
        egrd.DataBind();

        ViewState["data"] = dt;
    }
    catch (Exception ex)
    {
        CommonLogic.SendMailOnError(ex);
    }
}

Now error is like when I pass selected text from drop down list to column variable. Here an error is shown:

Conversion failed when converting the varchar value ' + CONVERT(INT,2) + ' to data type int.

Please help me out guys..

Sorry to say this but your code is a security hazard. Please read about SQL Injection and parameterized queries. — Zohar Peled, Mar 15 '19 at 12:23
@ZoharPeled this is small kind project which is limited no of users with limited access. — Gajjar Shalin, Mar 15 '19 at 12:37

Amir Molaei · Accepted Answer · 2019-03-15T12:16:38.947

You are putting sql Convert methods into quotes so they are detected as varchar values in your sql code. For example in the following code:

str += " and p.userid = ' + CONVERT(INT,"+ Convert.ToInt32(ddl_users.SelectedValue.ToString())+") + '";

you need to remove the ' + and + ' before and after the Convert method and change it to this:

str += " and p.userid = CONVERT(INT,"+ Convert.ToInt32(ddl_users.SelectedValue.ToString())+")";

Also these three lines:

str += " and p.Senderid = '+CONVERT(INT,"+ Convert.ToInt32(ddl_sender.SelectedValue.ToString())+")+'";
str += " and p.Deliverystatus in ('+CONVERT(INT,"+ Convert.ToInt32(ddl_sender.SelectedValue.ToString())+")+')'";
str += " and p.accounttype = '+CONVERT(INT,"+ Convert.ToInt32(ddl_account.SelectedValue.ToString())+")+'";

must change to:

str += " and p.Senderid = CONVERT(INT,"+ Convert.ToInt32(ddl_sender.SelectedValue.ToString())+")";
str += " and p.Deliverystatus in (CONVERT(INT,"+ Convert.ToInt32(ddl_sender.SelectedValue.ToString())+"))'";
str += " and p.accounttype = CONVERT(INT,"+ Convert.ToInt32(ddl_account.SelectedValue.ToString())+")";

You are also having extra quotes in LIKE sections for example:

 str += " and p.MobileNo like '' % '"+txt_mobileno.Text.Trim()+"' % ''";

must change to :

str += " and p.MobileNo like '%"+txt_mobileno.Text.Trim()+"%'";

You can simply debug your code and see how the sql code has been created and then for being easier to fix you can copy it to sql server query editor and check the syntax.

ya rad can u simplify LIKE for full text search. please – Gajjar Shalin Mar 15 '19 at 12:09 — Gajjar Shalin, Mar 15 '19 at 12:09

Conversion failed when converting the varchar value from int conversation

1 Answers1