Solved

VB.NET string to datetime

Posted on 2006-10-27
9
1,969 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

732 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