Databases
StackOverflow2013
Postsdbo
POWriting a dreaded SQL search query (2nd phase)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWriting a dreaded SQL search query (2nd phase)
    text
    copied!<p>I am working on a search query (with an asp.net 3.5 front end) which seems quite simple, but is quite complex. The complete query is:</p> <pre><code>set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[usp_Item_Search] @Item_Num varchar(30) = NULL ,@Search_Type int = NULL ,@Vendor_Num varchar(10) = NULL ,@Search_User_ID int = 0 ,@StartDate smalldatetime = NULL ,@EndDate smalldatetime = NULL AS DECLARE @SQLstr as nvarchar(4000) Set @SQLstr = 'SELECT RecID, Vendor_Num, Vendor_Name, InvoiceNum, Item_Num, (SELECT CONVERT(VARCHAR(11), RecDate, 106) AS [DD MON YYYY]) As RecDate, NeedsUpdate, RecAddUserID FROM [tbl_ItemLog] where 1=1 ' IF (@Item_Num IS NOT NULL and LTRIM(@Item_Num) &lt;&gt; '') Begin If @Search_Type = 0 BEGIN Set @SQLstr = @SQLstr + 'AND Item_Num LIKE ''' + @Item_Num + '%''' END If @Search_Type = 1 BEGIN Set @SQLstr = @SQLstr + 'AND Item_Num LIKE ''%' + @Item_Num + '%''' END If @Search_Type = 2 BEGIN Set @SQLstr = @SQLstr + 'AND Item_Num LIKE ''%' + @Item_Num + '''' END End IF (@Vendor_Num IS NOT NULL and LTRIM(@Vendor_Num) &lt;&gt; '') Begin Set @SQLstr = @SQLstr + ' AND Vendor_Num = ''' + @Vendor_Num + '''' End IF (@Search_User_ID IS NOT NULL and @Search_User_ID &gt; 0) Begin Set @SQLstr = @SQLstr + ' AND RecAddUserID = ' + convert(nvarchar(20),@Search_User_ID) End Set @SQLstr = @SQLstr + ' AND (RecDate BETWEEN ''' + convert(nvarchar(10),@StartDate,106) + ''' AND ''' + convert(nvarchar(10),@EndDate,106) + ''')' PRINT (@SQLstr) --Execute (@SQLstr) </code></pre> <p>When I pass all empty parameter values, I get an error:</p> <blockquote> <p>"Failed to convert parameter value from a String to a Int32."</p> </blockquote> <p>The asp.net code that is calling the stored proc is:</p> <pre><code> //Display search results in GridView; SqlConnection con = new SqlConnection(strConn); //string sqlItemSearch = "usp_Item_Search"; SqlCommand cmdItemSearch = new SqlCommand(sqlItemSearch, con); cmdItemSearch.CommandType = CommandType.StoredProcedure; cmdItemSearch.Parameters.Add(new SqlParameter("@Item_Num", SqlDbType.VarChar, 30)); cmdItemSearch.Parameters["@Item_Num"].Value = txtItemNumber.Text.Trim(); cmdItemSearch.Parameters.Add(new SqlParameter("@Search_Type", SqlDbType.Int)); cmdItemSearch.Parameters["@Search_Type"].Value = ddlSearchType.SelectedItem.Value; cmdItemSearch.Parameters.Add(new SqlParameter("@Vendor_Num", SqlDbType.VarChar, 10)); cmdItemSearch.Parameters["@Vendor_Num"].Value = txtVendorNumber.Text.Trim(); cmdItemSearch.Parameters.Add(new SqlParameter("@Search_User_ID", SqlDbType.Int)); cmdItemSearch.Parameters["@Search_User_ID"].Value = ddlSeachUser.SelectedItem.Value; if (!string.IsNullOrEmpty(txtStartDate.Text)) { cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime)); cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime(txtStartDate.Text.Trim()); } else { cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime)); cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime("01/01/1996"); } if (!string.IsNullOrEmpty(txtEndDate.Text)) { cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime)); cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(txtEndDate.Text.Trim()); } else { cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime)); cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(DateTime.Now); } con.Open(); SqlDataAdapter ada = new SqlDataAdapter(cmdItemSearch); DataSet ds = new DataSet(); ada.Fill(ds); gvSearchDetailResults.DataSource = ds; gvSearchDetailResults.DataBind(); pnlSearchResults.Visible = true; </code></pre> <p>How can I resolve this?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload