Visualstudio ODBC question

Hi experts,
   I have following statment written in vs 2005, but for some reason reader returns nothing. I think it would be something wrong with my SQL statment at "where" clause, but i dont know how to fix that problem, plz help. and i'm certain that date i entered has value stored in the table.    


Public Shared Function GetInwardGoodslist(ByVal ReportDate As Date) As List(Of InwardGoods)
        Dim inwardgood As New InwardGoods
        Dim connection As OdbcConnection = DatabaseConnection.VisionDB()
        Dim selectStatement As String

        selectStatement = "SELECT InwardTime, SignTime, InwardTo, SigReceive, AddNotes " _
                & "FROM InwardGoods " _
                & "WHERE InwardTime  = " & ReportDate & ""

        Dim selectCommand As New OdbcCommand(selectStatement, connection)
        connection.Open()
        Dim reader As OdbcDataReader = selectCommand.ExecuteReader(CommandBehavior.CloseConnection)
        Dim inward As New List(Of InwardGoods)
        'Dim i As Integer = 1
        Do While reader.Read

            Dim iu As New InwardGoods
            'iu.InwardGood_ID = i
            iu.InwardTime = reader.GetValue(1)
            iu.SignTime = reader.GetValue(2)
            iu.InwardTo = reader.GetValue(3)
            iu.SigReceive = reader.GetValue(4)
            iu.AddNote = reader.GetValue(5)

            inward.Add(iu)
            'i = i + 1
        Loop
        reader.Close()

        Return inward
LVL 2
miketonnyAsked:
Who is Participating?
 
sweetfa2Commented:
I don't have access to mssql to test the syntax.

http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm


Have a look at the link for the syntax you should be aiming at.

Probably just need to change
)) as datetime)
to
) as datetime))
0
 
sweetfa2Commented:
I am guessing you have a problem with your date format.

you need to wrap your ReportDate variable in a to_date expression.

The to_date will depend on what sort of date you have in your ReportDate variable.,
0
 
sweetfa2Commented:
you might also try TRUNC(InwardTime) as otherwise it will need an exact time as well.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
sweetfa2Commented:
The string output of reportdate will be needed to work out what you need in your to_date parameters.
0
 
miketonnyAuthor Commented:
i select my date from a datetimepicker, and converted into shortdatetimestring
how do you do the to_date?
0
 
sweetfa2Commented:
to_date( yourdate, dateformat)

so if yourdate string displayed as 11302010 then you would use

to_date( yourdatestring, 'MMDDYYYY')

I would also trunc(inwardtime)

See http://www.dba-oracle.com/f_to_date.htm

selectStatement = "SELECT InwardTime, SignTime, InwardTo, SigReceive, AddNotes " _
                & "FROM InwardGoods " _
                & "WHERE TRUNC(InwardTime)  = to_date(" & ReportDate & ", 'MMDDYYYY')"

Open in new window

0
 
sweetfa2Commented:
so if yourdate string displayed as 11/30/2010 then you would use

to_date( yourdatestring, 'MM/DD/YYYY')
0
 
miketonnyAuthor Commented:
i'm sry, both trunc() and to_date returns not built in function error, plz forgive my ignorance, should i specify my question not under oracle database? for somereason it added my question under that category. is there a way of doing the same thing in ODBC or sql?
0
 
sweetfa2Commented:
selectStatement = "SELECT InwardTime, SignTime, InwardTo, SigReceive, AddNotes " _
                & "FROM InwardGoods " _
                & "WHERE {fn trunc(InwardTime)}  =  {d " & ReportDate & ", 'MMDDYYYY'}"

Open in new window


Try this - forgot the odbc parameter handling
0
 
miketonnyAuthor Commented:
"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]'trunc' is not a recognized built-in function name."
still having the same error;(
0
 
sweetfa2Commented:
selectStatement = "SELECT InwardTime, SignTime, InwardTo, SigReceive, AddNotes " _
                & "FROM InwardGoods " _
                & "WHERE {fn cast(floor(cast(InwardTime as float)))}  =  {d " & ReportDate & ", 'MMDDYYYY'}"

Open in new window


Given that it is not for Oracle database try above
0
 
sweetfa2Commented:
selectStatement = "SELECT InwardTime, SignTime, InwardTo, SigReceive, AddNotes " _
                & "FROM InwardGoods " _
                & "WHERE {fn cast(floor(cast(InwardTime as float)) as datetime)}  =  {d " & ReportDate & ", 'MMDDYYYY'}"

Open in new window


Ooops
0
 
miketonnyAuthor Commented:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as'.

now it has this error showing up on screen, thanks a lot of that much help though, really appreciated sir.
0
 
miketonnyAuthor Commented:
thanks i tried a few methods on that none of them working, still has the syntax error around "as" . i also asked my colleague, he said the conversion should be automatically done as my windows is also in british timing, so all should be the same. i'm confused
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.