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..
精彩评论