VB.NET Error: Operand type clash: date is incompatible with int

Question

0.00/5 (No votes)

See more:

I am trying to display records from a table named staffpresentee from a database using the where clause...

i am using visual studio 2010...

the table design is as follows-
     fieldname                  datatype          allownull
1    staff_id (fk)              numeric(18,0)       yes
2      date                           date                no
3    present                       char(1)             yes

Note that the type of the 2nd field is date and not datetime...

I want to display all records from the table that have today's date int the 'date' field...
My current query is-

cmd = New SqlClient.SqlCommand("select * from staffpresentee where date=" & Date.Now.Date, cn)
dr = cmd.ExecuteReader

for dr = cmd.ExecuteReader i get the error "Operand type clash: date is incompatible with int"

i tried the select * from staffpresentee query without the where clause it runs correctly...

Posted 18-Mar-13 2:33am

Shraa1

Add a Solution

Comments

ZurdoDev 18-Mar-13 8:51am

What type of database? SQL? You likely just need to add single quotes around it, if it's SQL. SELECT * FROM staffpresentee WHERE date = '" & Date.Now.Date & "'"

Shraa1 18-Mar-13 10:25am

it was sql... i tried the ' ' marks but it did not help. i mean the "Operand type clash: date is incompatible with int" error was gone but the next line showed a related problem... it said cannot concatenate to string or something like that... never mind now anyways, the soltion by originalgriff was helpful...

Shraa1 18-Mar-13 10:25am

and dont forget to vote for the question :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-03-18T03:37:00

Solution 1

Two problems:
1) "date" is an SQL reserved word, so calling your column that is always going to give problems.
2) Send the date as a parameter rather that concatenating strings - that is always a better idea as it avoids SQL injection attacks as well.

VB

cmd = New SqlCommand("select * from staffpresentee where [date]=@DT", cn)
cmd.Parameters.AddWithValue("@DT", DateTime.Now.Date)
dr = cmd.ExecuteReader()

Posted 18-Mar-13 3:37am

OriginalGriff

Comments

Shraa1 18-Mar-13 10:19am

Thanks a lot the code was very much helpful..
thanks a lot...

OriginalGriff 18-Mar-13 10:35am

You're welcome!

Shraa1 18-Mar-13 10:21am

the 2 points you made, i did consider the first point when i first got the error... i tried changing the name of the column but it still showed the same error... so i thought i'd just keep the name of the field "date"

OriginalGriff 18-Mar-13 10:38am

Don't - try to avoid calling columns "name", "Date" or "password" because they are reserved words and it's too easy to forget the "[" and "]" and get an error - it is also generally easier to read code that talks about "startDate" or "birthDate" rather than a straight "date". And easier to not make mistakes and put data in the wrong table, too... :laugh:

Shraa1 18-Mar-13 10:25am

and dont forget to vote for the question :)

Shraa1 18-Mar-13 10:46am

i know i am very much organised when it comes to names... i use proper naming conventions... always... this is a project for my college and we have to create data flow diagrams and e-r diagrams and data dictionary... the case was that any other name apart from "date" would have been very hard to explain in the data dictionary, etc.
in the other 8 tables of my project, wherever there was a need for date related columns i have given names like appoint_date and date_of_birth, etc.