Je travaille sur une requête de recherche (avec un frontal asp.net 3.5) qui semble assez simple, mais qui est assez complexe. La requête complète est la suivante
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) <> '')
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) <> '')
Begin
Set @SQLstr = @SQLstr + ' AND Vendor_Num = ''' + @Vendor_Num + ''''
End
IF (@Search_User_ID IS NOT NULL and @Search_User_ID > 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)Lorsque je passe toutes les valeurs de paramètres vides, j'obtiens une erreur :
"Échec de la conversion de la valeur du paramètre d'un String à un Int32".
Le code asp.net qui appelle la procédure stockée est le suivant :
//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;Comment puis-je résoudre ce problème ?
