nvarchar到日期错误，未提供日期，onclick事件或存储过程或gridview中有问题

Question

为您的信息，我使用的是Visual Studio Professional 2010

有人可以帮助我，我认为@dateFound参数没有提供点击事件的值。

我有一个从存储过程填充的gridview，如下所示。

     <asp:SqlDataSource id="Sql20" 
     ConnectionString='<%$ ConnectionStrings:connectionString %>'
                      SelectCommand="dbo.StoredProcedure2"
                      Runat="server" SelectCommandType="StoredProcedure">
     </asp:SqlDataSource>

storedProcedure2如下所示，假设在gridview中显示所有数据，如果@dateFound为NULL，默认如下

    ALTER PROCEDURE dbo.StoredProcedure2
(
@dateFound DATE 
)
 AS
/* SET NOCOUNT ON */
    IF @dateFound IS NULL
    BEGIN
    SELECT 
        lc_orders_tb.lc_orderID, 
        lc_orders_tb.lc_orderSubTotal, 
        lc_orders_tb.lc_orderTotal,
        lc_orders_tb.LRNo,
        lc_orders_tb.LRDate, 
        lc_orders_tb.lc_orderPlacedDate,
        lc_orders_tb.LInvoiceLoc, 
        lc_orderStatus_tb.lc_orderStatusDescrip,
        lc_orderStatus_tb.lc_delType,
        lc_tb.lc_compName, 
        lc_tb.lc_addCity, 
        (SELECT SUM(lc_orderQuantity)
        FROM lc_orderQuantity_tb 
        WHERE 
    lc_orders_tb.lc_orderID=lc_orderQuantity_tb.lc_OrderID) as lc_orderQuantity
              FROM lc_orders_tb 
                    INNER JOIN
                    lc_tb ON lc_orders_tb.lc_id = lc_tb.lc_id
                    INNER JOIN
      lc_orderStatus_tb ON lc_orders_tb.lc_orderID = lc_orderStatus_tb.lc_orderID
                    ORDER BY lc_orders_tb.lc_orderPlacedDate DESC
    END
    ELSE
    BEGIN
        SELECT 
        lc_orders_tb.lc_orderID, 
        lc_orders_tb.lc_orderSubTotal, 
        lc_orders_tb.lc_orderTotal,
        lc_orders_tb.LRNo,
        lc_orders_tb.LRDate, 
        lc_orders_tb.lc_orderPlacedDate,
        lc_orders_tb.LInvoiceLoc, 
        lc_orderStatus_tb.lc_orderStatusDescrip,
        lc_orderStatus_tb.lc_delType,
        lc_tb.lc_compName, 
        lc_tb.lc_addCity, 
        (SELECT SUM(lc_orderQuantity)
        FROM lc_orderQuantity_tb 
        WHERE 
   lc_orders_tb.lc_orderID = lc_orderQuantity_tb.lc_OrderID) as lc_orderQuantity
              FROM lc_orders_tb 
                    INNER JOIN
                    lc_tb ON lc_orders_tb.lc_id = lc_tb.lc_id
                    INNER JOIN
                    lc_orderStatus_tb ON lc_orders_tb.lc_orderID = lc_orderStatus_tb.lc_orderID
WHERE  
    convert(varchar(10), lc_orders_tb.lc_orderPlacedDate, 103) = @dateFound
                    ORDER BY lc_orders_tb.lc_orderPlacedDate DESC
    END
     RETURN

@dateFound将在点击按钮时提供，但默认情况下，我的意思是，当页面加载时，它不提供，因此它应该执行第一个if语句，但它不同的错误，如@dateFound未提供或在转换nvarchar到日期的错误。如果在单击按钮后从文本框中提供@dateFound，则SP的ELSE语句应该执行。但是我似乎不能锻炼。请查看提供@dateFound

的按钮的click事件

    Protected Sub CustomGV3_onClick(ByVal sender As Object, ByVal e As EventArgs)
        Dim connectionString As String = 
   WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
        Dim con As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand("dbo.StoredProcedure2", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue
    ("@dateFound", Format(txtBoxDateFrom.Text, "dd/MM/yyyy"))
        Using con
            con.Open()
            cmd.ExecuteReader()
            con.Close()
        End Using
        GridView3.DataBind()
    End Sub

有谁能帮我一下吗?你觉得我的方法完全错了吗?谁能纠正我一下。

等待您的帮助，请…感谢你

Answer 1

尝试更改存储过程以使用@dateFound参数的默认值:

ALTER PROCEDURE dbo.StoredProcedure2
(
   @dateFound DATE = NULL
)
AS

Answer 2

我终于从其他论坛得到了答案，它通过简单地改变SP

    (
@dateFound DATE = '01/01/1900'
)

上述日期被设置为默认值，因为它永远不会出现在数据库中，我将通过SP中的IF语句检查数据库。在aspx页面中，如下所示。

    <SelectParameters>
        <asp:ControlParameter ControlID="txtBoxDateFrom" 
        Name="dateFound" PropertyName="Text" 
        DbType="Date" DefaultValue="01/01/1900" />

感谢所有试图帮助我的贡献者。