Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

gettting operand type clash: int is incompatible with date when using vb.net

Posted on 2015-02-22
8
398 Views
Last Modified: 2015-02-22
I have the following:
1) in a table called tables I have a Date column that is of type date
2) I added the date as 2/20/2015

then I have in my vb code behind the following:
Imports System.Data.SqlClient

Partial Class main
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        Dim ConnectionString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("dbconn").ToString()
        Dim dateval As Date = Now.Date


        Dim MyConn As New System.Data.SqlClient.SqlConnection(ConnectionString)
        Dim MyComm As New System.Data.SqlClient.SqlCommand("SELECT * FROM [dbo].[Table] WHERE date= " + dateval, MyConn)

        MyConn.Open()
        Dim DataReader As System.Data.SqlClient.SqlDataReader = MyComm.ExecuteReader()

        While (DataReader.Read())

            box1_lbdate_1.Text = DataReader("Date").ToString
            box1_lbdoc_1.Text = DataReader("Doc1_Name").ToString
            box1_lbtime_1.Text = DataReader("doc1_time").ToString
            box1_docname_2.Text = DataReader("doc2_name").ToString
            box1_doc2time.Text = DataReader("doc2_time").ToString

            If String.IsNullOrWhiteSpace(box1_docname_2.Text) Then
                box1_docname_2.Visible = False
                box1_doc2time.Visible = False
            End If


        End While
        DataReader.Close()
        MyConn.Close()



    End Sub
End Class

Open in new window


my goal is to parse the database for the current date and apply it to the box labels accordingly

I keep getting the error mentioned. do I need to do Cdate() ?
0
Comment
Question by:bbimis
8 Comments
 

Author Comment

by:bbimis
ID: 40624626
well I figured that out by adding "'" + dalval + "'"

but now I get the whole date including the time and not sure why cause I'm only calling now.date
0
 
LVL 12

Assisted Solution

by:FarWest
FarWest earned 250 total points
ID: 40624640
Date type includes is datetime in SQL
If you set the value to date part the 12:00 is assumed as time
0
 

Author Comment

by:bbimis
ID: 40624645
so how do I get the date only?
I tried
Dim dateval As Date = System.DateTime.Today.ToString("dd/mm/yy")

Open in new window


do I need to set the type in the database to something else?
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 250 total points
ID: 40624694
The pattern you are taking with building your query is a recipe for SQL Injection attacks. While this particular instance isn't so bad, if you repeat this behavior using input taken directly from a user, then your application will be vulnerable. You should prefer to use parameterized queries instead.
0
 

Author Comment

by:bbimis
ID: 40624696
I got it had to take the .tostring off the DataReader("date")
0
 

Author Closing Comment

by:bbimis
ID: 40624700
thanks and I agree on the sql injection issue and will keep that in mind. thanks!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40624810
Be very careful please

How the dbms interprets that sting format will depend on the dbms vendor and (worse) the settings of that dbms.

I will assume you are using Microsft SQL Server.

dd/mm/yy

is NOT a good string format to use.
a. always use 4 digit years
b. never assume month first

the safest possible sting representation of a date in SQL Server is:

YYYYMMDD

(no! delimiters)


Dim dateval As Date = System.DateTime.Today.ToString("yyyymmdd")

& then you SQL will be executed as

SELECT * FROM [dbo].[Table] WHERE date= '20150223'

nb: if the dbms is not SQL Server the approach would differ
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40624815
sorry, there was a lot of traffic I hadn't seen when I posted. (I didn't refresh the page, "my bad")
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question