Hi GTTravis,
Refer the below Sample code that how to pass DateTime parameter value to StoredProceduer to bind Grid With using SQLDataSource and with using Coding.
I used Northwind Database to Create a sample. you can downlaod it from below link.
Install Microsoft Northwind and Pubs Sample databases in SQL Server Management Studio
HTML
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<cc1:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</cc1:ToolkitScriptManager>
<div>
Order date: <asp:TextBox ID="txtOrder" runat="server"></asp:TextBox>
<cc1:CalendarExtender ID="calOrderdate" runat="server" TargetControlID="txtOrder">
</cc1:CalendarExtender>
Search
<asp:Button ID="btnSearch" Text="Search without SqlDataSource" runat="server" OnClick="Search" />
<br />
<asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="Customer Id" />
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" />
<asp:BoundField DataField="ShipName" HeaderText="Ship Name" />
<asp:BoundField DataField="ShipVia" HeaderText="Ship Via" />
<asp:BoundField DataField="Freight" HeaderText="Freight" />
</Columns>
</asp:GridView>
</div>
<br />
<br />
Order date: <asp:TextBox ID="txtOrderdate" runat="server"></asp:TextBox>
<cc1:CalendarExtender ID="calOrderdate2" runat="server" TargetControlID="txtOrderdate">
</cc1:CalendarExtender>
<asp:Button ID="Button1" Text="Search By Sql DataSource" runat="server" OnClick="SearchBySqlDataSource" />
<div>
<asp:GridView ID="GvOrders2" runat="server" AutoGenerateColumns="false" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="Customer Id" />
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" />
<asp:BoundField DataField="ShipName" HeaderText="Ship Name" />
<asp:BoundField DataField="ShipVia" HeaderText="Ship Via" />
<asp:BoundField DataField="Freight" HeaderText="Freight" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="GetOrderDetailsbasedOnOrderdate" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="OrderDate" DbType="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
C#
protected void Search(object sender, EventArgs e)
{
this.PopulateOrder();
}
public void PopulateOrder()
{
string constr = ConfigurationManager.ConnectionStrings["ConStr1"].ConnectionString;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand("GetOrderDetailsbasedOnOrderdate");
cmd.Parameters.AddWithValue("@OrderDate", DateTime.Parse(txtOrder.Text).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture));
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
gvOrders.DataSource = dt;
gvOrders.DataBind();
}
protected void SearchBySqlDataSource(object sender, EventArgs e)
{
SqlDataSource1.SelectParameters["OrderDate"].DefaultValue = DateTime.Parse(txtOrderdate.Text).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
}
VB.Net
Protected Sub Search(sender As Object, e As EventArgs)
Me.PopulateOrder()
End Sub
Public Sub PopulateOrder()
Dim constr As String = ConfigurationManager.ConnectionStrings("ConStr1").ConnectionString
Dim con As New SqlConnection(constr)
Dim cmd As New SqlCommand("GetOrderDetailsbasedOnOrderdate")
cmd.Parameters.AddWithValue("@OrderDate", DateTime.Parse(txtOrder.Text).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture))
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
da.Fill(dt)
gvOrders.DataSource = dt
gvOrders.DataBind()
End Sub
Protected Sub SearchBySqlDataSource(sender As Object, e As EventArgs)
SqlDataSource1.SelectParameters("OrderDate").DefaultValue = DateTime.Parse(txtOrderdate.Text).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)
End Sub
SQL
-- EXEC GetOrderDetailsbasedOnOrderdate '1998-05-05 00:00:00'
-- EXEC GetOrderDetailsbasedOnOrderdate '05/05/1998 00:00:00.000'
CREATE PROCEDURE GetOrderDetailsbasedOnOrderdate
@OrderDate DATETIME
AS
BEGIN
SELECT[CustomerID]
,[OrderDate]
,[ShipVia]
,[Freight]
,[ShipName]
,[ShipAddress]
FROM [Orders]
WHERE [OrderDate] = @OrderDate
END
GO
ScreenShot
