Solved

Visualstudio ODBC question

Posted on 2010-11-29
15
392 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
[X]
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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Many to one in one row 2 37
Return Rows as per Quantity of Columns Value In SQL 6 27
MySQL-Design Help 12 39
What Is an Error? 2 21
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

740 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