Solved

Visualstudio ODBC question

Posted on 2010-11-29
15
387 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:miketonny
  • 9
  • 5
15 Comments
 
LVL 17

Expert Comment

by:sweetfa2
ID: 34236058
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
 
LVL 17

Expert Comment

by:sweetfa2
ID: 34236067
you might also try TRUNC(InwardTime) as otherwise it will need an exact time as well.
0
 
LVL 17

Expert Comment

by:sweetfa2
ID: 34236070
The string output of reportdate will be needed to work out what you need in your to_date parameters.
0
 
LVL 2

Author Comment

by:miketonny
ID: 34236300
i select my date from a datetimepicker, and converted into shortdatetimestring
how do you do the to_date?
0
 
LVL 17

Expert Comment

by:sweetfa2
ID: 34236327
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
 
LVL 17

Expert Comment

by:sweetfa2
ID: 34236330
so if yourdate string displayed as 11/30/2010 then you would use

to_date( yourdatestring, 'MM/DD/YYYY')
0
 
LVL 2

Author Comment

by:miketonny
ID: 34236340
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 17

Expert Comment

by:sweetfa2
ID: 34236405
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
 
LVL 2

Author Comment

by:miketonny
ID: 34236873
"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]'trunc' is not a recognized built-in function name."
still having the same error;(
0
 
LVL 17

Expert Comment

by:sweetfa2
ID: 34243477
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
 
LVL 17

Expert Comment

by:sweetfa2
ID: 34243487
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
 
LVL 2

Author Comment

by:miketonny
ID: 34243545
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
 
LVL 17

Accepted Solution

by:
sweetfa2 earned 500 total points
ID: 34243596
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
 
LVL 2

Author Comment

by:miketonny
ID: 34244009
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now