Solved

VB.NET string to datetime

Posted on 2006-10-27
9
1,968 Views
Last Modified: 2009-07-29
I am wrestling with some code to convert string to datetime in VB.NET, and then insert it into an MSSQL database.

I am using MSSQL, and the format of the datetime field in the MSSQL database is:  m/d/yyyy h:mm:ss tt  (where tt is AM/PM).

I am combining a string for this from:

a) retrieval from another database table, for the date part
   --> MyDate = "SELECT convert(varchar,gamedatetime,101) "

b) Adding in the time from input from textboxes
   --> MyTime = Scoretime3.Text & ":" & Scoretime4.Text & ":" & Scoretime5.Text & " PM"

On screen (using a label) I look at the date and it looks fine. But I am having trouble converting it.

I have tried MyDateTime = DateTime.Parse(MyDate & " " & MyTime)

and also tried the Convert and Format function to not avail - always getting an error that string was in the wrong format.

Can someone help me with the correct way to convert my string so it can be inserted in the database ?

Thanks, Peter

0
Comment
Question by:pbissegger
[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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 7

Accepted Solution

by:
whityum earned 100 total points
ID: 17821571
CType(MyDate & " " & MyTime, DateTime)
0
 
LVL 6

Expert Comment

by:Roopesh_7
ID: 17821648
hey no way for error... check the value of  Mydate, Mytime...

its right formate...i mean values are current if it is curret check the culture you want is 12 hour or 24 hour?

below code will help you in all the three cases

Imports System
Imports System.Globalization

Class Class1
   Public Shared Sub Main()
      ' Assume the current culture is en-US.
      ' The date is February 16, 1992, 12 hours, 15 minutes and 12 seconds.

      Dim myDateTimeValue As String = "2/16/1992 12:15:12"
      Dim myDateTime As DateTime = DateTime.Parse(myDateTimeValue)
      Console.WriteLine("1) myDateTime       = {0}", myDateTime)
     
      ' Reverse month and day to conform to a different culture.
      ' The date is February 16, 1992, 12 hours, 15 minutes and 12 seconds.

      Dim culture As CultureInfo = New CultureInfo("fr-FR", True)
      Dim myDateTimeFrenchValue As String = "    16/02/1992 12:15:12"
      Dim myDateTimeFrench As DateTime = _
                           DateTime.Parse(myDateTimeFrenchValue, _
                                          culture, _
                                          DateTimeStyles.NoCurrentDateDefault)
      Console.WriteLine("2) myDateTimeFrench = {0}", myDateTimeFrench)
     
      ' The date is February 16, 1992, 12 hours, 15 minutes and 12 seconds.

      Dim expectedFormats As String() =  {"G", "g", "f", "F"}
      myDateTimeFrench = DateTime.ParseExact(myDateTimeFrenchValue, _
                                          expectedFormats, _
                                          culture, _
                                          DateTimeStyles.AllowWhiteSpaces)
      Console.WriteLine("3) myDateTimeFrench = {0}", myDateTimeFrench)
   End Sub 'Main
End Class 'Class1


0
 
LVL 10

Expert Comment

by:jnhorst
ID: 17822530
Could you post exactly what your Label control displays?

John
0
Technology Partners: 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!

 

Author Comment

by:pbissegger
ID: 17829209
Here is what the label control - Gettingstarted.text - displays:
   Datetime: 10/25/2006 12:15:00 PM

Here is my error when I run it:
   Incorrect syntax near '12'. An unhandled exception occurred during the execution of the current web request.  
   Please review the stack trace for more information about the error and where it originated in the code. This
   happens on the line marked with a **** below.

on occasion I also get the following error
   Conversion from string "10/25/2006 ::00 PM" to type 'Date' is not valid. This happens on the line marked with a
   #### below.


    Sub AddGoal(ByVal Sender As Object, ByVal E As EventArgs)

        Dim A1, A2 As Integer
        Dim MyDate, MyTime, Whichperiod, DT, Shoe As String
        Dim MyDateTime As New DateTime
       
        'Get the game date
        cmd.Connection = MyConn
        cmd.CommandText = "SELECT convert(varchar,gamedatetime,101) as Gamedate FROM games WHERE gamenum = " & Session("Resultsgamenum")
        cmd.Connection.Open()
        MyDate = cmd.ExecuteScalar()
        cmd.Connection.Close()      

       ' Get the time of the event
            MyTime = Scoretime.Text & ":" & Scoretime1.Text & ":00 PM"
            Whichperiod = Periodnum.SelectedItem.Value

        ' Convert to Datetime
       
        #### MyDateTime = CType(MyDate & " " & MyTime, DateTime)
        Gettingstarted.Text = " Datetime: " & MyDate & " " & MyTime
       
            cmd.CommandText = "Insert into stats(Teamnum, Playernum, Gamenum, Period, Eventtime, Eventtype, Assist1, Assist2, Goaltype) Values(" & Session("ResultsHometeamnum") & ", " & Goalscorer.SelectedItem.Value & ", " & Session("ResultsGamenum") & ", '" & Whichperiod & "', " & MyDateTime & " , 'goal', " & A1 & ", " & A2 & ", '" & Goaltype.SelectedItem.Text & "')"
       
        cmd.Connection = MyConn
        cmd.Connection.Open()
        **** cmd.ExecuteNonQuery()
        cmd.Connection.Close()

     End Sub

and i also have the following HTML


                           
                            <td>
                                <asp:TextBox ID="Scoretime" runat="server" Width="20" />:<asp:TextBox
                                 ID="Scoretime1" runat="server" Width="20" />
                            </td>
                             <td>
                                <asp:DropDownList ID="Goalscorer" runat="server" Width="100" DataTextField="Players"
                                    DataValueField="Usernum" OnSelectedIndexChanged="getAssist1" AutoPostBack="true">
                                </asp:DropDownList>
                            </td>
                            <td
                                <asp:DropDownList ID="Assist1" runat="server" Width="100" DataTextField="Players"
                                    DataValueField="Usernum" OnSelectedIndexChanged="getAssist2" AutoPostBack="true">
                                </asp:DropDownList>
                            </td>
                            <td
                                <asp:DropDownList ID="Assist2" runat="server" Width="100" DataTextField="Players"
                                    DataValueField="Usernum">
                                </asp:DropDownList>
                            </td>
                            <td
                                <asp:DropDownList ID="Goaltype" runat="server" Width="90">
                                </asp:DropDownList>

Thanks for your help.
0
 
LVL 6

Assisted Solution

by:Roopesh_7
Roopesh_7 earned 100 total points
ID: 17829282
check the value of scoretime and scoretime1 becouse i think its not getting the value of this two....thats y it is getting 10/25/2006 ::00 PM first two fields =''  so check the value of that
0
 
LVL 6

Expert Comment

by:Roopesh_7
ID: 17829286
during exicution check the value of mytime
0
 
LVL 10

Assisted Solution

by:jnhorst
jnhorst earned 50 total points
ID: 17829407
I am curious about what 'convert(varchar,gamedatetime,101)' returns.  Could you run with a break point on MyDate = cmd.ExecuteScalar() and post just that piece?

John
0
 

Author Comment

by:pbissegger
ID: 17836548
OK, here is an update....

1. For MyDate, the results of MyDate = trim(cmd.ExecuteScalar()) is "10/25/2006". I added in the trim().

2. The "10/25/2006 ::00 PM" only happens in cases where I do not start the process from scratch (ie: when I upload a page change and just try to reload the page and get it to work). In other words, there seems to be something that's not happening when the page is posting back.

3. I am not able to check the value of MyTime during execution, because I do not have all the components on my development computer. However, I upload the a page change and simply comment out the "cmd.ExecuteNonQuery()" statement, and the Gettingstarted.Text label is always displaying the correct string.

I have changed  the line
MyTime = Scoretime.Text & ":" & Scoretime1.Text & ":00 PM"
to
MyTime = Trim(Scoretime.Text.ToString) & ":" & Trim(Scoretime1.Text.ToString) & ":00 PM"
but it has no improvement

Finally, always the datebase problem is
"Incorrect syntax near 'XX'"
where XX is always the value in my Scoretime.Text box.

Any other ideas ?
0
 

Author Comment

by:pbissegger
ID: 17883122
I have solved the problem.

Using MyDate from:
        cmd.CommandText = "SELECT convert(varchar,gamedatetime,101) as Gamedate FROM games WHERE gamenum = " & Session("Resultsgamenum")
        cmd.Connection.Open()
        MyDate = Trim(cmd.ExecuteScalar())
        cmd.Connection.Close()

and MyTime from:
        MyTime = Trim(Scoretime.Text.ToString) & ":" & Trim(Scoretime1.Text.ToString) & ":00PM"

MyString = "Insert into stats(Eventtime) Values('" & MyDate & " " & MyTime & "')"

Here's where I went wrong ...

a) Even though I am using a datetime field, since I am setting the Commandtext variable with my SQL query, it seems that I just needed to pass the datetime using normal strings, and never did have to do any conversions to datetime.  
b) Although using a cut and paste from an SQL datetime database field, there is a space between the Time and the AM/PM designation, in order to populate the CommandText variable, through trial and error, I found that the time and PM designation had to be adjacent (for example 12:45:56PM, notice no space between the time and the AM/PM designation).
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

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