Dim myDateTime As DateTime = DateTime.Now Dim month As String = myDateTime.Month.ToString() Label4.Text = "<b>Todays Date:</b> " & DateTime.Now.ToLongDateString() Label4.Text += "<br /><b>Period:</b>" & month
Without the WHERE statement the code outputs:
Todays Date: 9th April 2012
Month: 4
That month number from the Label4.Text needs to match the p_id P_k field in
the mocSetsTbl. This will enable me to display all records for the month of April in a GridView
<asp:SqlDataSource ID="MOCSetsDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConString %>"
SelectCommand="SELECT * FROM [mocSetsTbl] LEFT JOIN [periodTbl] ON [mocSetsTbl].[p_id] = [periodTbl].[p_id] LEFT JOIN [setsTbl] ON [mocSetsTbl].[s_id] = [setsTbl].[s_id] Where [mocSetsTbl].[p_id] = @month "></asp:SqlDataSource>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
Dim myDateTime As DateTime = DateTime.Now
Dim month As String = myDateTime.Month.ToString()
Label4.Text = "<b>Todays Date:</b> " & DateTime.Now.ToLongDateString()
Label4.Text += "<br /><b>Period:</b>" & month
End Sub
Protected Sub MOCSetsDataSource_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
e.Command.Parameters("@month").Value = DateTime.Now.Month
End Sub
I-Weedy
Member
67 Points
239 Posts
convert month datetime string to integer and place in select statement!
Apr 08, 2012 09:22 PM|LINK
My code below displays todays date and the month as a number (April = 4, May = 5 etc) in a label (Label4)
Dim myDateTime As DateTime = DateTime.Now
Dim month As String = myDateTime.Month.ToString()
Label4.Text = "<b>Todays Date:</b> " & DateTime.Now.ToLongDateString()
Label4.Text += "<br /><b>Period:</b>" & month
What I want to do is add the variable value output by the label into a select statement ( Where id = ' & month') however I'm met with an error '
Conversion failed when converting the varchar value ' & month' to data type int.
Now I realise that my Id is an integer and the month is a string how can i get the value into my select statement without this error happening?
mm10
Contributor
6455 Points
1187 Posts
Re: convert month datetime string to integer and place in select statement!
Apr 08, 2012 09:53 PM|LINK
Dim where as String = string.Format("Where id={0}", month)
I-Weedy
Member
67 Points
239 Posts
Re: convert month datetime string to integer and place in select statement!
Apr 08, 2012 10:20 PM|LINK
Dim where As String???
Not really following why you're using WHERE as a variable? Maybe I explain my problem incorrectly!
This is not in code behind but in design source view.
The method you've supplied seems to throw up the same error!
This is the exact WHERE STATEMENT I have in the source code
<asp:Label ID="Label4" runat="server" Text="Label"></asp:Label>
WHERE [mocSetsTbl].[p_id] = ' & month ' "
and this is in the codebehind.
Dim myDateTime As DateTime = DateTime.Now
Dim month As String = myDateTime.Month.ToString()
Label4.Text = "<b>Todays Date:</b> " & DateTime.Now.ToLongDateString()
Label4.Text += "<br /><b>Period:</b>" & month
Without the WHERE statement the code outputs:
Todays Date: 9th April 2012
Month: 4
That month number from the Label4.Text needs to match the p_id P_k field in the mocSetsTbl. This will enable me to display all records for the month of April in a GridView
Need this resolving ASAP
mm10
Contributor
6455 Points
1187 Posts
Re: convert month datetime string to integer and place in select statement!
Apr 09, 2012 07:24 AM|LINK
I-Weedy
Member
67 Points
239 Posts
Re: convert month datetime string to integer and place in select statement!
Apr 09, 2012 08:31 AM|LINK
Markup (aspx)
<asp:GridView ID="GridView3" runat="server" AutoGenerateColumns="False"
DataKeyNames="ms_id" DataSourceID="MOCSetsDataSource">
<Columns>
<asp:BoundField DataField="ms_id" HeaderText="ms_id" InsertVisible="False"
ReadOnly="True" SortExpression="ms_id" Visible="false" />
<asp:BoundField DataField="p_id" HeaderText="p_id" SortExpression="p_id" Visible="false" />
<asp:BoundField DataField="s_id" HeaderText="s_id" SortExpression="s_id" Visible="false" />
<asp:BoundField DataField="setName" HeaderText="Set Name" SortExpression="s_id" />
<asp:BoundField DataField="link" HeaderText="Link" SortExpression="s_id" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="MOCSetsDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConString %>"
SelectCommand="SELECT * FROM [mocSetsTbl] LEFT JOIN [periodTbl] ON [mocSetsTbl].[p_id] = [periodTbl].[p_id] LEFT JOIN [setsTbl] ON [mocSetsTbl].[s_id] = [setsTbl].[s_id] Where [mocSetsTbl].[p_id] = ' & month ' "></asp:SqlDataSource>
Codebehind
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim myDateTime As DateTime = DateTime.Now
Dim month As String = myDateTime.Month.ToString()
Label4.Text = "<b>Todays Date:</b> " & DateTime.Now.ToLongDateString()
Label4.Text += "<br /><b>Period:</b>" & month
End Sub
mm10
Contributor
6455 Points
1187 Posts
Re: convert month datetime string to integer and place in select statement!
Apr 09, 2012 08:46 AM|LINK
Use a select parameter in your SqlDataSource:
<asp:SqlDataSource ID="MOCSetsDataSource" runat="server"
OnSelecting="MOCSetsDataSource_Selecting">
ConnectionString="<%$ ConnectionStrings:ConString %>"
SelectCommand="SELECT * FROM [mocSetsTbl] LEFT JOIN [periodTbl] ON [mocSetsTbl].[p_id] = [periodTbl].[p_id] LEFT JOIN [setsTbl] ON [mocSetsTbl].[s_id] = [setsTbl].[s_id] Where [mocSetsTbl].[p_id] = @Month">
<SelectParameters>
<asp:Parameter Name="Month" DefaultValue="1" />
</SelectParameters>
</asp:SqlDataSource>
..and then you set the value of the parameter to the current month in the selecting event in code behind:
protected void MOCSetsDataSource_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
e.Command.Parameters["@Month"].Value = DateTime.Now.Month;
}
I-Weedy
Member
67 Points
239 Posts
Re: convert month datetime string to integer and place in select statement!
Apr 09, 2012 09:07 AM|LINK
That produces this error!
Must declare the scalar variable "@month".
mm10
Contributor
6455 Points
1187 Posts
Re: convert month datetime string to integer and place in select statement!
Apr 09, 2012 09:35 AM|LINK
Did you add a select parameter with the name "month"? Please post your code again for review.
I-Weedy
Member
67 Points
239 Posts
Re: convert month datetime string to integer and place in select statement!
Apr 09, 2012 10:18 AM|LINK
Here's the code in full. VB
<asp:SqlDataSource ID="MOCSetsDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConString %>"
SelectCommand="SELECT * FROM [mocSetsTbl] LEFT JOIN [periodTbl] ON [mocSetsTbl].[p_id] = [periodTbl].[p_id] LEFT JOIN [setsTbl] ON [mocSetsTbl].[s_id] = [setsTbl].[s_id] Where [mocSetsTbl].[p_id] = @month "></asp:SqlDataSource>
<SelectParameters>
<asp:Parameter Name="month" DefaultValue="1" />
</SelectParameters>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
Dim myDateTime As DateTime = DateTime.Now
Dim month As String = myDateTime.Month.ToString()
Label4.Text = "<b>Todays Date:</b> " & DateTime.Now.ToLongDateString()
Label4.Text += "<br /><b>Period:</b>" & month
End Sub
Protected Sub MOCSetsDataSource_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
e.Command.Parameters("@month").Value = DateTime.Now.Month
End Sub
mm10
Contributor
6455 Points
1187 Posts
Re: convert month datetime string to integer and place in select statement!
Apr 09, 2012 10:20 AM|LINK
Place the select parameter inside the SqlDataSource and it will work:
<asp:SqlDataSource ID="MOCSetsDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConString %>"
SelectCommand="SELECT * FROM [mocSetsTbl] LEFT JOIN [periodTbl] ON [mocSetsTbl].[p_id] = [periodTbl].[p_id] LEFT JOIN [setsTbl] ON [mocSetsTbl].[s_id] = [setsTbl].[s_id] Where [mocSetsTbl].[p_id] = @month ">
<SelectParameters>
<asp:Parameter Name="month" DefaultValue="1" />
</SelectParameters>
</asp:SqlDataSource>