Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Visualstudio ODBC question

Posted on 2010-11-29
15
Medium Priority
?
400 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
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
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

971 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