populating dropdowns in aspx. Conversion failed

Question

I have two dropdowns in aspx page one for location and vendors. Based on the value selected in the location dropdown, The vendors dropdown must populate...I am trying to bind with location name

Location:

                <asp:DropDownList ID="ddlAllLocations" runat="server" DataSourceID="SqlDataSourceBusinessLocations"
                    DataTextField="Location_Name" DataValueField="Location_ID" AutoPostBack="True"  AppendDataBoundItems="True">
   <asp:ListItem value="" selected="True">

Vendors :

<asp:SqlDataSource ID="SqlDataSourceAllVendors" runat="server" ConnectionString="<%$ ConnectionStrings:xxxxx %>"
        ProviderName="<%$ ConnectionStrings:xxxxx.ProviderName %>" SelectCommand="GetAllVendorsForBUforLocation"
        SelectCommandType="StoredProcedure">
       <SelectParameters>
            <asp:SessionParameter Name="userBUIds" SessionField="BusinessUnitIds" Size="200"
                Type="String" />

             <asp:ControlParameter ControlID="ddlAllLocations" Name="LOCATION_ID" PropertyName="SelectedValue"
                Type="String" />
        </SelectParameters>

    </asp:SqlDataSource>

My stored procedure is

-- =============================================
ALTER PROCEDURE [dbo].[GetAllVendorsForBUforLocation]
    @userBUIds varchar(200),
    @LOCATION_ID int
AS
DECLARE @sql NVARCHAR(4000)   
BEGIN
    set @sql='SELECT DISTINCT tblVendor_Payees.PayeeID, RTRIM(ISNULL(a.Name1_Last, '''')) + '' '' + ISNULL(a.Name1_First, '''') AS VendorName, tblVendor_Business.BusinessID FROM tblVendor_Payees AS a left JOIN tblFields AS f ON a.PayeeID = f.VendorID INNER JOIN tblVendor_Business ON a.PayeeID = tblVendor_Business.PayeeID INNER JOIN INVENTORY.TBL_LOCATION  on INVENTORY.TBL_LOCATION.BusinessID = tblVendor_Business.BusinessID WHERE (a.VendorType = 1) AND (tblVendor_Business.BusinessID = '+cast(@userBUIds as varchar(50))+' and INVENTORY.TBL_LOCATION.LOCATION_ID = '+cast(@LOCATION_ID as int)+') ORDER BY VendorName'
 exec sp_executeSQL @sql 
END

I am getting this error :

Conversion failed when converting the varchar value 'SELECT DISTINCT tblVendor_Payees.PayeeID, RTRIM(ISNULL(a.Name1_Last, '')) + ' ' + ISNULL(a.Name1_First, '') AS VendorName, tblVendor_Business.BusinessID FROM tblVendor_Payees AS a left JOIN tblFields AS f ON a.PayeeID = f.VendorID INNER JOIN tblVendor_Business ON a.PayeeID = tblVendor_Business.PayeeID INNER JOIN INVENTORY.TBL_LOCATION on INVENTORY.TBL_LOCATION.BusinessID = tblVendor_Business.BusinessID WHERE (a.VendorType = 1) AND (tblVendor_Business.BusinessID = 2 and INVENTORY.TBL_LOCATION.LOCATION_ID = ' to data type int

Have you write down the code behind on dllAllLocations index changed event? If not, you can populate of that event, and bind the data with the vendor container. — Ruly
I think the SQL where clause needs looking at, around '+@userBUIds+' — AjV Jsy
"AND (tblVendor_Business.BusinessID" This bracket never gets closed — 5uperdan

Richard Deeming Richard Deeming · Accepted Answer · 2013-04-03T16:33:22

You're getting the error message because you're trying to add an integer to a string. Since int has a higher precedence than varchar, SQL will attempt to convert the varchar to an int. Since the varchar doesn't contain a valid integer, this conversion will fail.

More importantly, by using string concatenation and dynamic SQL within a stored procedure, you have left yourself wide open to SQL injection. Your query isn't even doing anything complicated enough to justify dynamic SQL!

If you're desperate to keep the dynamic SQL, use the parameterized version of sp_executesql:

set @sql = N'SELECT DISTINCT 
tblVendor_Payees.PayeeID, 
RTRIM(ISNULL(a.Name1_Last, '''')) + '' '' + ISNULL(a.Name1_First, '''') AS VendorName, 
tblVendor_Business.BusinessID 
FROM tblVendor_Payees AS a left 
JOIN tblFields AS f ON a.PayeeID = f.VendorID 
INNER JOIN tblVendor_Business ON a.PayeeID = tblVendor_Business.PayeeID 
INNER JOIN INVENTORY.TBL_LOCATION  on INVENTORY.TBL_LOCATION.BusinessID = tblVendor_Business.BusinessID 
WHERE (a.VendorType = 1) 
AND (tblVendor_Business.BusinessID = @userBUIds 
and INVENTORY.TBL_LOCATION.LOCATION_ID = @LOCATION_ID) 
ORDER BY VendorName'

exec sp_executeSQL 
   @stmt = @sql, 
   N'@userBUIds varchar(200), @LOCATION_ID int',
   @userBUIds = @userBUIds,
   @LOCATION_ID = @LOCATION_ID

Otherwise, skip the dynamic SQL and just execute the query directly.

populating dropdowns in aspx. Conversion failed

1 Answers