6 Replies - 846 Views - Last Post: 17 December 2013 - 05:35 AM Rate Topic: -----

#1 MElT04   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 13-May 08

Interacting with SQL database via SQL statements

Posted 16 December 2013 - 12:23 PM

Hello,

I've got an issue extracting information from my MS SQL database. Its a SQL 2000 database and the application is written in VB.NET 2008. I have a SQL statement that extracts the date from the database and stores it in a datatime variable. However if I run the app in debug mode & look at the statement, the data stored in the variable is just a date not date & time. I've also noted that the format of the date in the database is yyyy/MM/dd 00:00:00:000 whereas in the variable its MM/dd/yyyy. Added to my worries, this vairable is used in another SELECT statement and returns zero values when there is data in the database for the date being selected. I've tried datetime.tryparse but that doesn't seem to change the format and i am my wits end here.

Here's a sample of the code i'm using to extract the date from the database:

 
Dim sDeleteCounted As String = "SELECT Date FROM Table WHERE reference = '" & sSearch & "'"
            Dim daDelCount As SqlDataAdapter = New SqlDataAdapter(sDeleteCounted, getconn)
            Dim dsDelCount As DataSet = New DataSet
            daDelCount.Fill(dsDelCount, "CountDate")

            If dsDelCount.Tables.Count > 0 AndAlso dsDelCount.Tables("CountDate").Rows.Count > 0 Then
                CountedDate = dsDelCount.Tables("CountDate").Rows(0).Item("Date_counted")
            Else
                CountedDate = Nothing

            End If

            time = DateTime.Parse(CountedDate) 


Can anyone give me some guidance please

Is This A Good Question/Topic? 0
  • +

Replies To: Interacting with SQL database via SQL statements

#2 Wrath_014   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 50
  • Joined: 19-July 13

Re: Interacting with SQL database via SQL statements

Posted 16 December 2013 - 12:38 PM

You can customize your date time variable. Check it out :

http://msdn.microsof...(v=vs.110).aspx

This post has been edited by Wrath_014: 16 December 2013 - 12:39 PM

Was This Post Helpful? 0
  • +
  • -

#3 MElT04   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 13-May 08

Re: Interacting with SQL database via SQL statements

Posted 16 December 2013 - 01:26 PM

Thanks for responding Wrath_014 and i tried both suggestions on the microsoft page but it didn't help. my date is still coming in the format MM/dd/yyyy and it is still reading zero values from the database. maybe i can show the select statement that is giving me the headache...

Dim CumCount As String = "SELECT Colcounted FROM Table WHERE (date = CONVERT(DATETIME,'" & CummCdate & "',103)) ORDER BY Denomination"

        Dim CumC As SqlDataAdapter = New SqlDataAdapter(CumCount, getconn)
        Dim CumCds As DataSet = New DataSet
        CumC.Fill(CumCds, "Count")

        If CumCds.Tables.Count > 1 AndAlso CumCds.Tables("Count").Rows.Count > 1 Then
          
            CTwenty = CumCds.Tables("Count").Rows(0).Item("Colcounted")
            Chun = CumCds.Tables("Count").Rows(1).Item("Colcounted")
            CFive = CumCds.Tables("Count").Rows(2).Item("Colcounted")
            CThou = CumCds.Tables("Count").Rows(3).Item("Colcounted")
            CFThou = CumCds.Tables("Count").Rows(4).Item("Colcounted")
        Else
            CTwenty = 0
            Chun = 0
            CFive = 0
            CThou = 0
            CFThou = 0
        End If 


Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1730
  • View blog
  • Posts: 5,709
  • Joined: 25-September 09

Re: Interacting with SQL database via SQL statements

Posted 16 December 2013 - 02:28 PM

If cumCDate is a Date type, then you could use

cumCDate.ToString("yyyy/MM/dd") in your query.

But... I would think it would still find the correct range as long as the field in the database is a datetime type

However, try passing this using a parameterized query and see if that solves this. Not only does parameterized statements keep your database more secure against injection attacks, it handles a lot of things like this behind the scenes.


Dim CumCount As String = "SELECT Colcounted FROM Table WHERE (date = @cumDate) ORDER BY Denomination"
cumC.SelectCommand.Parameters.AddWithValue("@cumDate", CummCDate)


See if that helps.

Alternatively you could use the .ToString Overloads to pass the date as yyyy/MM/dd too)
cumC.SelectCommand.Parameters.AddWithValue("@cumDate", CummCDate.ToString("yyyy/MM/dd"))


but again no sure it would make a difference between the two.

You might msg a Mod or ForumLeader to see if they could put this in the SQL Database forum since this is more a question of how the two different date formats affect the query and not necessarily a VB.Net programming issue.
Was This Post Helpful? 0
  • +
  • -

#5 MElT04   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 13-May 08

Re: Interacting with SQL database via SQL statements

Posted 16 December 2013 - 02:55 PM

Thanks for your reply. I tried your suggestion but it didn't help either. How do i message the Mod or ForumLeader to move the thread?

Regards
Was This Post Helpful? 0
  • +
  • -

#6 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5338
  • View blog
  • Posts: 14,234
  • Joined: 18-April 07

Re: Interacting with SQL database via SQL statements

Posted 16 December 2013 - 05:25 PM

Well it would probably not find the records because it appears you are using a mix of british and american date formats here. If I ask SQL to find records matching 05/01/2013 am I talking about January 5th or May 1st? It is going to assume one or the other and not realize it is a different date format. Check out the thread below and the accepted answer to give you some testing options to help you sort out the issue.

I am not sure this is exactly a database issue per say, so I will leave it here in VB.NET since the code you are working with is focused on that language. Worst case scenario is that you would have to convert between the two where appropriate, but I think if you check your regional settings and try the code suggested in the link that you may find out how to fix this.

http://social.msdn.m...ual-studio-2008

Hope it helps. :)
Was This Post Helpful? 0
  • +
  • -

#7 MElT04   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 44
  • Joined: 13-May 08

Re: Interacting with SQL database via SQL statements

Posted 17 December 2013 - 05:35 AM

Hi,
Thanks for responding. I tried the suggestion in the thread you suggested. What I found was when I changed the date format in the regional settings on my machine to English (United Kingdom), then the reading from the database came in as MM/dd/yyyy. However when I changed it back to English US (and forced the format to dd/MM/yyyy) the data coming in from the database was in the format dd/MM/yyyy. Iím confused. I'm not certain where the is the mixup happening? Could it be server end you think? I tried getting from the Support section what the date format on the server is but i was unable to get a feedback.

Any further suggestions?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1