Querying between two date fields
Querying between two date fields
Querying between two date fields
Querying between two date fields
Querying between two date fields
Querying between two date fields Querying between two date fields Querying between two date fields Querying between two date fields Querying between two date fields Querying between two date fields Querying between two date fields Querying between two date fields
Querying between two date fields Querying between two date fields
Querying between two date fields
Go Back  Xtreme Visual Basic Talk > > > Querying between two date fields


Reply
 
Thread Tools Display Modes
  #1  
Old 05-10-2010, 05:54 PM
kawi6rr kawi6rr is offline
Newcomer
 
Join Date: Jan 2009
Posts: 9
Default Querying between two date fields


I’m trying to display a datagrid based on a query that is pulling dates from two input boxes. My limited knowledge of programming is preventing me from achieving this.

I have two asp:textbox listed below, one is for the beginDate and the other is for the endDate. I’m trying to fill the datagrid with my SQL string with data that occurs between those two dates.


<script language="vb" runat="server">

Sub Page_Load(sender as Object, e as EventArgs)
'BindData()
End Sub

'Sub BindData()
Public Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'1. Create a connection
Dim submit As Button
Dim strTxt as String
Dim conn as SQLconnection
Dim da as New SQLdataAdapter
Dim ds as New DataSet()
Dim cmd as SQLcommand
Dim newListItem1 as new ListItem()
Dim beginDate As string
Dim endDate As String

conn = New SQLconnection("server=xxxxx; database=xxxxx; uid=xxxxx; pwd=xxxxx")
strTxt = "SELECT ID, MRN, Name, CheckInDate, Dept, Prov, DX1, DX1Desc, CPT1, CPT1Desc FROM OPPE_Clinic WHERE CheckInDate >= '" & beginDate & "' and CheckInDate <= '" & endDate & "' "
cmd = New SQLcommand(strTxt, conn)

'Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

'2. Create the command object, passing in the SQL string
Dim myCommand as New SqlCommand(strTxt, conn)

'Set the datagrid's datasource to the datareader and databind
conn.Open()
PPROClinic.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
PPROClinic.DataBind()

End Sub

Here is my datagrid and text fields.

<form id="Form1" method="post" runat="server">
<table border=0 bgcolor="#F9F9F2" height=455 width="550">
<tr>
<td colspan=15 heigth=10 class="content">Begin Date:&nbsp;<asp:textbox ID="beginDate" NAME="beginDate" runat="server" class="content" />&nbsp;&nbsp;End Date:&nbsp;<asp:textbox id="endDate" NAME="endDate" runat="server" class="content" />&nbsp;&nbsp;<asp:Button id="submit" runat="server" Text="Submit" OnClick="Submit_Click"></asp:Button></td>
</tr>
<tr>
<td height=420><asp:datagrid id="PPROClinic" runat="server" class="content" />
</td>
</tr>
</table>

Any help is appreciated, thanks.
Reply With Quote
  #2  
Old 05-11-2010, 02:57 AM
JustinCase2 JustinCase2 is offline
Junior Contributor
 
Join Date: Apr 2006
Posts: 324
Default So

what is your actual problem? Populating the data? Retrieving the data? Need to know what you actually need help with, as it seems you have written all the code, but something doesn't work as expected?
Reply With Quote
  #3  
Old 05-11-2010, 12:00 PM
kawi6rr kawi6rr is offline
Newcomer
 
Join Date: Jan 2009
Posts: 9
Default

The datagrid does not populate. Do I need to do any formatting for the dates in the text boxes? I was also wondering if I had the text boxes dimmed correctly, I'm not real knowledgeable with programming, just learning. I can click on the button with no errors but also no data.
Reply With Quote
  #4  
Old 05-11-2010, 12:04 PM
PlausiblyDamp's Avatar
PlausiblyDampQuerying between two date fields PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

It would probably be easier to use a parametrised query rather than relying on string concatenation, that way you aren't relying on string formatting of dates to do comparisons - this can be error prone when dealing with different cultures.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #5  
Old 05-12-2010, 07:50 AM
JustinCase2 JustinCase2 is offline
Junior Contributor
 
Join Date: Apr 2006
Posts: 324
Default Well

When I look at your code:

Code:
Dim beginDate As string Dim endDate As String conn = New SQLconnection("server=xxxxx; database=xxxxx; uid=xxxxx; pwd=xxxxx") strTxt = "SELECT ID, MRN, Name, CheckInDate, Dept, Prov, DX1, DX1Desc, CPT1, CPT1Desc FROM OPPE_Clinic WHERE CheckInDate >= '" & beginDate & "' and CheckInDate <= '" & endDate & "' " cmd = New SQLcommand(strTxt, conn)

It doesn't really look like you actually fill the date variables before using them? Try this:

Code:
Dim beginDate As string Dim endDate As String beginDate=beginDate.text endDate=endDate.text conn = New SQLconnection("server=xxxxx; database=xxxxx; uid=xxxxx; pwd=xxxxx") strTxt = "SELECT ID, MRN, Name, CheckInDate, Dept, Prov, DX1, DX1Desc, CPT1, CPT1Desc FROM OPPE_Clinic WHERE CheckInDate >= '" & beginDate & "' and CheckInDate <= '" & endDate & "' " cmd = New SQLcommand(strTxt, conn)

But you don't really need the extra variable, you can just reference the textboxes directly:

Code:
strTxt = "SELECT ID, MRN, Name, CheckInDate, Dept, Prov, DX1, DX1Desc, CPT1, CPT1Desc FROM OPPE_Clinic WHERE CheckInDate >= '" & beginDate.Text & "' and CheckInDate <= '" & endDate.Text & "' " cmd = New SQLcommand(strTxt, conn)

Right now, you have a textbox and a variable which have the same names, and that isn't recommended. You never know which is being referenced.

Anyway, I agree with PlausiblyDamp, it would be much better to use a parameterized query, as you never know if the date-formats will be correct.
Reply With Quote
  #6  
Old 05-21-2010, 05:15 PM
kawi6rr kawi6rr is offline
Newcomer
 
Join Date: Jan 2009
Posts: 9
Default

Thanks! I got it to work, really appreciate all your help.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Querying between two date fields
Querying between two date fields
Querying between two date fields Querying between two date fields
Querying between two date fields
Querying between two date fields
Querying between two date fields Querying between two date fields Querying between two date fields Querying between two date fields Querying between two date fields Querying between two date fields Querying between two date fields
Querying between two date fields
Querying between two date fields
 
Querying between two date fields
Querying between two date fields
 
-->