.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to select cuurent month in sqldatasource?

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :ASP.Net

Hi all,

I have a sqldatasource statement. How can i select current month in my statement?

Here is what i done.

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
                SelectCommand="SELECT SUM(Amount) AS Total2 FROM Budget WHERE (UserId = @UserId) AND (CategoryId = 1) AND (CreateDate = @CreateDate)">
                    <asp:SessionParameter Name="UserId" SessionField="UserId" DefaultValue="" />
                    <asp:Parameter Name="CreateDate" Type="DateTime" />


in page load C#,


if (!this.IsPostBack)
                SqlDataSource3.SelectParameters["CreateDate"].DefaultValue = tnow.Month.ToString();


But i get this error <String was not recognized as a valid DateTime>.

How can i do it?

Any help is appreciated.

Thank you.



View Complete Post

More Related Resource Links

optional select criteria for an SqlDataSource


I have a form that searches for images in a database.  I have several DropDownLists with various search criteria e.g. Genus, Photographer, State, Project.

I'd like the user to be able to filter using any combination of these.  For example they might pick a specific photographer and a specific genus but choose not filter by project or state.

I'd like to find a good, clean, elegent way to do this.

Any advice??

Here is an example of the code I have now, it has only two possible criteria.  It works but I have a feeling there are better ways to do this....

<script runat="server">

    Sub Page_Load()
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim thisGenus As String = ddlGenus.SelectedValue
        Dim thisDeterminer As String = ddlDeterminer.SelectedValue
        Dim whereClause As String
        whereClause = "1=1"
        If (thisGenus <> "") Then whereClause = whereClause + " AND genus like '" + thisGenus + "'"
        If (thisDeterminer <> "") Then whereClause = whereClause + " AND determiner like '" + thisDeterminer + "'"
        dsImageList.SelectCommand = "SELECT 

Use of CASE statement in Select using SqlDataSource


Hi All,

I need help regarding the use of CASE in Select of SQLDataSource. Actually I am using FORMVIEW and in the Grid i have "Consumer Numbers" as link and on click I save the Consumer Number in Session Variable. When user go into Edit and change the Consumer Number after updation the formview was coming blank cause i was using that number for select statement now. Just need to correct the SELECT and use the case properly and want to do is




How to do that please correct.

       <asp:SqlDataSource ID="SqlDSBillers" runat="server" 
ConnectionString="<%$ ConnectionStrings:SQLE2K8 %>"

SelectCommand="SELECT Bill_Master.Bill_Master_ID,
CONVERT (varchar(20),
Bill_Master.Enroll_Date, 105) AS Enroll_Date,

SELECT 1st of the month and last of the month


How do I retrieve the first of the month for the min and the end of the month for the max


My query looks like this below…


select min(date)

       , max(date)




AJAX GridView Checkbox Select All in ASP.Net

In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

Select value in gridview


Hi, I am new to gridview. I want to make a gridview. Its row when selected should direct me to a specific .aspx page.Each row should direct to a different page. Should I use querystrings for this? Please suggest. Regards, ap.

How to retrieve data from SqlDataSource and pass to a variable?


Hi there,

I have a page in my website where there is very little code-behind. I only have two lines of code in Page_Load() and the rest of the code is declarative ASP.NET code. I have a FormView control which uses a SqlDataSource to get some order information. The SqlDataSource calls a stored procedure called usp_GetOrderDetails, here's the code for the SqlDataSource:

    <asp:SqlDataSource ID="sdsFormOrderDetails" runat="server" 
ConnectionString="<%$ ConnectionStrings:ForexDB %>"
ProviderName="<%$ ConnectionStrings:ForexDB.ProviderName %>"
SelectCommand="usp_GetOrderDetails" SelectCommandType="StoredProcedure"
UpdateCommand="usp_UpdateOrder" UpdateCommandType="StoredProcedure"
<asp:ControlParameter Name="orderId" ControlID="grdOrders" PropertyName="SelectedDataKey.Value" />
<asp:ControlParameter Name="orderId" ControlID="frmOrderDetails" PropertyName="SelectedValue" />

Binding GridView with SqlDataSource at runtime?


Hi, I want to create a generic page which contains a gridview and sqldatasource. I want to put these two controls on .aspx (C#) page but bind them at runtime. It should have selectcommand, updatecommand, deletecommand and insertcommand. Stored procedures are created for that. Help is needed to make it. Please post some link or code. Regards, ap.

SqlDataSource how to change the stored procedures at run time



Is it possible to change the select, insert and update stored procedure of an SqlDataSource at run time. I don't mean to change the definition of the stored procedure, but I mean to change the assignment, i.e. To change the select stored procedure from "selectSP1" to "selectSP2" at run time. Is it possible?



SqlDataSource UpdateCommand using 2 tables


I have two tables

Trans  with fields TransID, Date, CustomerID and some other stuff

Customer with fields CustomerID, Name, TaxId

On the screen the user only sees the fields Date and Customer Name. CustomerID is behind the scenes only.


I'm using SqlDataSource. Having no problems with SelectCommand. I don't know how to construct the UpdateCommand and InsertCommand.

Let's say the user changes the date, then I need to do an UPDATE.

UPDATE Trans SET Date = @Date, CustomerID = @CustomerID results in an error message and the record is not updated.

I get an error on the page that says "Sys.WebForms.PageRequestManagerServerErrorException: Input string was not in correct format".


I tried taking out the set for CustomerID and I still get the error on page.


Also, for inserting, the users will see a dropdownlist with Customer Names. I need to convert that to a CustomerID to be used in the new record being inserted in the database. I'm not sure how to do this.


Do I need to do something with Control Parameters?

The XML Files: XSLT Keys, Select vs. Match, Conflict Resolution, and More


This month Aaron Skonnard covers how keys work in XSLT, XSLT match and select attributes, SelectNodes order, finding