Date in VB.NET vs. MSAccess and SELECT

[bixfeldt]

I have a calendar on a simple windows form and when I choose a date it will put the date into a textbox with the format YYYY-MM-DD

When I choose a date i look up in a MSAccess database if there is any appointments for the selected date.

In the file (where the database query is in) calendar.xsd i can use the query below and all my appontments is listed in my datagrid.
<CommandText>SELECT * FROM tbNotification</CommandText>

But If I want to use the selected date that I have in my textbox, i get an error saying that "There is some values or parameters missing" (a simple translation from Swedish, don´t know the exact English translation). The Query I try to use is the one below:
<CommandText>SELECT * FROM tbNotification WHERE ([Date] = '#'+txtSelDate+'#')</CommandText>

I have tried:
- ALOT of syntaxes in the query string for the ## around the date
- putting the date as String, as Date etc etc
- putting the date as a variable (selDate = txtSelDate.text)

I have now tried everything I can think of. Does anyone of you guys a solution for my problem?

I'm extremely thankful for any help!

Have a nice easter! :)

[CroCrew]

Hello bixfeldt,

In your Access database how is the field “Date” being stored? Text or Date/time?

CroCrew~

[bixfeldt]

I have tried both Date/Time and Text but for the moment I store it as Text, not in Date/Time format.

[CroCrew]

Well the correct way is to store it as a Date/Time. Also “Date” is a reserved word and should not be used as your field name. Be clever with your field names. Like “NotificationDate”.

With that said let us know how you’re going to store it and the name of the field and we will try to provide you with your answer. Also, if you are going to store the data as a “text” give us some examples of what you have stored.

Again, I personally would not store dates as text.

CroCrew~

[bixfeldt]

Hi,
Thanks for trying to help me! I have now renamed the column name from Date to notificationDate to avoid any problems with reserved words. I have also changed it to Date/Time instead of Text. The correct date format for Sweden is yyyy-mm-dd, so I save the dates as this into the database, for example 2010-01-01.

One "problem" that may be involved is that I have to use the character # before and after the date when working toward an Access database.

I hope this information is understandable. :)

Thanks in advance for you help!

[bixfeldt]

I forgott to include the code below:
In the Form_Onload event in frmCalendar I can use the code below to show all entries in the database:

<!-- --------------------------------------------------------------------------------------------------- -->
txtSelDate.Text = MonthCalendar1.SelectionStart
lblCalMonth.Text = DatePart(DateInterval.Month, MonthCalendar1.SelectionStart)
lblCalDay.Text = DatePart(DateInterval.Day, MonthCalendar1.SelectionStart)
selDate = FormatDateTime(txtSelDate.Text, DateFormat.ShortDate)
txtSelDate.Text = MonthCalendar1.TodayDate
selDate = FormatDateTime(txtSelDate.Text, DateFormat.ShortDate)
selDate = "#" & selDate & "#"
lblCalMonth.Text = DatePart(DateInterval.Month, MonthCalendar1.TodayDate)
lblCalDay.Text = DatePart(DateInterval.Day, MonthCalendar1.TodayDate)
Me.TbNotificationTableAdapter.Fill(Me.CalendarData Set.tbNotification)
<!-- --------------------------------------------------------------------------------------------------- -->

And the SELECT statement in the calendarDataSet.xsd is as below:

<!-- --------------------------------------------------------------------------------------------------- -->
<SelectCommand>
<DbCommand CommandType="Text" ModifiedByUser="false">
<CommandText>SELECT ID, NotificationDate, Notification, SE, [NO], DK, FI, PL, HK FROM tbNotification</CommandText>
<Parameters />
</DbCommand>
</SelectCommand>
<!-- --------------------------------------------------------------------------------------------------- -->

The thing that I want to do is to replace the code above, in the calendarDataSet.xsd, with this one:

<!-- --------------------------------------------------------------------------------------------------- -->
<SelectCommand>
<DbCommand CommandType="Text" ModifiedByUser="false">
<CommandText>SELECT ID, NotificationDate, Notification, SE, [NO], DK, FI, PL, HK FROM tbNotification WHERE NotificationDate = '"#"'+selDate+"#"'</CommandText>
<Parameters />
</DbCommand>
</SelectCommand>
<!-- --------------------------------------------------------------------------------------------------- -->

I hope this also makes it more possible to locate the problem. Thank you very much for your kind help!

[CroCrew]

As the wise Tlhintoq says:

TIP: When you are writing your question, there is a button on the tool bar that wraps the [code] tags around your copy/pasted code. It helps a bunch. Its the button with a '#' on it. More on tags. They're cool. Check'em out.

Thanks,
CroCrew~

[CroCrew]

Hello bixfeldt,

Within your Access database can you tell me how the “NotificationDate” data looks when you run “SELECT NotificationDate FROM tbNotification”. I know you want it to display in a Sweden format “yyyy-mm-dd” through your application but within your Access database when you run the query how does it look? “mm-dd-yyyy” or “yyyy-mm-dd”

CroCrew~

[bixfeldt]

Hi,
God tip, with the wrap code (#)!
If I look into my Access database, it has the format "yyyy-mm-dd".

// Bixfeldt

[bixfeldt]

One more thing that might be a problem:
In my .xsd-file, wich is a XML-file, I have the line

SELECT ID, NotificationDate, Notification, SE, [NO], DK, FI, PL, HK FROM tbNotification WHERE NotificationDate = selDate

selDate is declared under Form_Load in Calender.vb. Can i reference to a declared value in a form from an XML-file?

Can this be the problem?

[CroCrew]

Hello bixfeldt,

Ok, with the changes you have made (changing the field name from “Date” to “NotificationDate” and changing the field type from “Text” to “Date/Time”) your SQL query should be:

SELECT * FROM tbNotification WHERE (NotificationDate = #1970-01-09#)

Don’t wrap the date with single quotes.

Hope this helps,
CroCrew~

[bixfeldt]

Yes, that works. But i don´t want to put a static date value in the code, but pick it up from my textbox called txtselDate.

How is the syntax to use the value that I have in a textbox at the design view called frmCalendar. I have today the following code at the form page:

selDate = txtSelDate.Text

Then, the value above, I want to use in the SQL statement. It is this SQL-statement that I might belive is wrong:

SELECT * FROM tbNotification WHERE (NotificationDate = #+ selDate +#

So, the problem is that I don't wabt to use a static value, but instead use the value that is given in the textbox. Do you understand what i mean?

[CroCrew]

Hello bixfeldt,

Your SQL query should be a string that you’re building.

Example:
“SELECT * FROM tbNotification WHERE (NotificationDate = #” & selDate & “#”

Disclaimer! This is an example. You should always use parameters to protect yourself from SQL injection attacks.

Hope this helps,
CroCrew~

[bixfeldt]

It doesn´t work. I get the error messages below:
Character ' ', hexadecimal values 0x20 is illegal in an XML name
It seems that the character "&" is not allowed in an XML-file.

I want to remind of that the SQL statement is not in the code-behind vb-file but in the file called calendarDataSet.xsd that is an XML-file that Visual studio automatically creates when adding a dataset, binding source and table adapter. Visual Studio automatically creates SELECT, INSERT, UPDATE, DELETE commands and it is this commands that I try to manipulate because I just want to see the notification for a specific date, not all notifications in the database.

[bixfeldt]

Isn't there any simple solution where I can put an SQL command in the code-behind for each button instead of using an XML file with the SQL statements? Do you have a better solution than using XML?