Solved

Visualstudio ODBC question

Posted on 2010-11-29
15
388 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
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.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 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