开发者

error in nvarchar to date, date not supplied, either problem in onclick event or stored procedure or in gridview

For your information, I am using Visual Studio Professional 2010

Can someone please help me out, I think the @dateFound parameter is not supplied with a value on click event.

I have a gridview populated from stored procedure as given below.

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

The storedProcedure2 is as follow which is suppose to show all the data in gridview if @dateFound IS NULL, by default as below

    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_orderPlac开发者_Python百科edDate,
        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 will be supplied when clicked on a button click but by default, I mean, when page loads it is not supplied and therefore it should execute the first if statement but it different errors like @dateFound not supplied or error on converting nvarchar to date. The ELSE statement of the SP should get executed if @dateFound supplied from the textbox after clicking on the button. But I cant seem to work out. Please have a look at the click event of the button which is supplying the @dateFound

    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

Can someone please help me out. Does it seem like I have a totally wrong approach ? Can someone please correct me.

awaiting your kind help plz.. Thanking You


Try changing your stored procedure to use a default value for the @dateFound parameter:

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


I finally got the answer from other forum, it worked by simply changing following in the SP

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

The above date is set as default as it will never be going to be in the database against which I will be checking by IF STATEMENT in SP. And in the aspx page to as below.

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

Thanks to all contributor who tried to help me out here..

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜