Solved

When converting a string to DateTime, parse the string to take the date before putting each variable in the DateTime object.

Posted on 2009-05-09
24
3,213 Views
Last Modified: 2013-12-25
I want to correct this error when trying to pass a date time parameter from a textbox
I get the error : "cannot convert '4/5/2009' to type int" but I select the case=1 of a combobox and not the case = 0.
This is the code :

SELECT * FROM Taxinomisi_fortotikon WHERE date_fortosis LIKE @datef

and tried this also

SELECT * FROM Taxinomisi_fortotikon WHERE date_fortosis LIKE convert(datetime,@datef,103)
 

Case i = 0

                    Try

                        Dim a As New Int32

                        a = TextBox1.Text

                        Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand1)

                        Form2.SqlCommand1.Parameters.Item(0).Value =  a                        Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

                        Form5.Show()

                        Form5.DataGridView1.DataSource = (Form2.DataSet51)

                        Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"

                    Catch errobj As Exception

                        MsgBox(errobj.Message)

                    End Try

Case i = 1

Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand4 Form2.SqlCommand4.Parameters.Item(0).Value = TextBox1.Text

Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

Form5.Show()

Form5.DataGridView1.DataSource = (Form2.DataSet51)

Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"

Open in new window

0
Comment
Question by:kyriakos70
  • 11
  • 8
  • 2
  • +3
24 Comments
 
LVL 8

Expert Comment

by:MatthiasVance
ID: 24343267
I think MSSQL will accept the date if you put it into the following string format:
"yyyyMMddHmmss"

Kind regards,

Matthias Vance
0
 
LVL 4

Expert Comment

by:shrikantss
ID: 24343268
vb code :

Public Shared Function ParseExact ( _
    s As String, _
    format As String, _
    provider As IFormatProvider _
) As DateTime


then

Dim s As String
Dim format As String
Dim provider As IFormatProvider
Dim returnValue As DateTime

returnValue = DateTime.ParseExact(s, _
    format, provider)

now insert the return value  from above function to the db  
0
 
LVL 39

Expert Comment

by:abel
ID: 24343467
The format of your date seems to be dd/mm/yyyy, is it not? Maybe the dd/mm is mm/dd, I cannot see that from your text.

You mention a dropdownbox and a case i = 0/1, but you don't explicitly tell on what line the error comes... You can find out which case is actually hit by using a breakpoint and F10 to walk through the code.

The text you mention is not from the actual exception but is from the help of visual studio, which is not necessarily the correct reason (the help is often wrong for errors and very generic). Please show the exact line where the error occurs and show the exact text of the error (i.e., is it FormatException or something else?).

Can it be that the error is raised on the return of the SELECT statement? I.e., on the Fill?

If it is indeed the datetime format, and you want to convert it to an integer, first parse the datetime string (the code with ParseExact from shrikantss points in the correct direction, but will not work). I show you two ways that work with your string, the first will output 5 april, the second will output 4 may, depending on what country you are in, use the one you need:

Dim strDate As String = "4/5/2008"

Debug.WriteLine(DateTime.Parse(strDate, CultureInfo.CreateSpecificCulture("en-US")))

Debug.WriteLine(DateTime.Parse(strDate, CultureInfo.CreateSpecificCulture("de-DE")))

Open in new window

0
 
LVL 39

Expert Comment

by:abel
ID: 24343471
PS: going from a datetime to an integer is an uncommon cast. maybe you need the ticks, but since you are mentioning an SQL SELECT statement, I think not. SQL Server accepts the mm-dd-yyyy format for dates, you can use that.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24343950
Whe you want to search by dates in the SQL you shold define the period, from the 0:00:00 to 23:59:59. This way you will include all times in the search.
You should also define the parameter type, that in this case should be something like this:

' Define the date

Dim dt As DateTime = DateTime.Parse(TextBox1.Text)

Dim dtStart As New DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0)

Dim dtEnd As New DateTime(dt.Year, dt.Month, dt.Day, 23, 59, 59)
 

' Define the parameters

Dim SQL As String = "SELECT * FROM Taxinomisi_fortotikon WHERE date_fortosis BETWEEN @datefStart AND @datefEnd"

SqlDataAdapter3.SelectCommand.Parameters.Add("@datefStart", SqlDbType.DateTime).Value = dtStart

SqlDataAdapter3.SelectCommand.Parameters.Add("@datefEnd", SqlDbType.DateTime).Value = dtEnd

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24343958
>>The format of your date seems to be dd/mm/yyyy, is it not? Maybe the dd/mm is mm/dd, I cannot see that from your text.<<
Take a look at the code (and the Timezone for that matter) they are using style of 103, hence: British/French
0
 

Author Comment

by:kyriakos70
ID: 24344109
The error is on the first case of 0
===================================================
error message "convert from string "4/12/2009" to integer is not valid"
And when I enter the date in a different textbox eg.
===================================================
 Form2.SqlCommand1.Parameters.Item(0).Value =  textbox2.text                        Form2.SqlDataAdapter3.Fill(Form2.DataSet51)
                        Form5.Show()
I get no error


Case i = 0

                    Try

                        Dim a As New Int32

                        a = TextBox1.Text

Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand1)

Form2.SqlCommand1.Parameters.Item(0).Value =  a                        Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

                        Form5.Show()

Form5.DataGridView1.DataSource = (Form2.DataSet51) ={error here)Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"

                    Catch errobj As Exception

                        MsgBox(errobj.Message)

                    End Try

Case i = 1

Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand4 Form2.SqlCommand4.Parameters.Item(0).Value = TextBox1.Text

Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

Form5.Show()

Form5.DataGridView1.DataSource = (Form2.DataSet51)

Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"

Open in new window

0
 

Author Comment

by:kyriakos70
ID: 24344228
When I transfer the code to a different button I get no error (only the code for the date command4) and to a different textbox, when I have the code to the same button and textbox as the command1 (with the case of) I get the error like the textbox is locked to the first option (the int parameter of sqlcommand1).
 
Kyriakos


code for different button

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Try

            Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand4)

            Form2.SqlCommand4.Parameters.Item(0).Value = TextBox4.Text

            Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

            Form5.Show()

            Form5.DataGridView1.DataSource = (Form2.DataSet51)

            Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"

            Catch errobj As Exception

            MsgBox(errobj.Message)

        End Try

    End Sub

Open in new window

0
 
LVL 39

Expert Comment

by:abel
ID: 24344233
you still don't say what line causes the error (whichis because of the Try/Catch, remove that and your debugger will show you the line), but reading again through your code, it gets quite obvious:

Dim a As New Int32a = TextBox1.Text
if really not allowed. What did you mean to do there? If TextBox1.Text indeed contains a number, you would still need to parse it using something like Integer.Parse or Integer.TryParse.

But your TextBox1.Text seems to contain a date. Did you intend "a" to be a DateTime? Have you tried any of the suggestions above for converting the string into a datetime? Either way, you will have to change the declaration of "a". Something like this:

Dim a As DateTime = DateTime.Parse(TextBox1.Text)
but I'm not sure if that will fit with the rest of your code.


0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24344250
Have you tried my code ?
0
 
LVL 39

Expert Comment

by:abel
ID: 24344259
Btw, the reason that you do not see the whole exception is because you are only showing a messagebox with the text of the exception, not the exception type itself, or the line where the error occurred. And these two are also very important. In general, it is a "best practice" to use as little Try/Catch as possible, and definitely not for type "Exception" because then you will catch every exception and loose much information.

Anyway, your last code bit you are showing doesn't indicate anything that is similar to the other pieces which might cause an error:

Form2.SqlCommand4.Parameters.Item(0).Value = TextBox4.Text
we have no idea what's in your textbox or what the type should be of the parameter. You do not receive an error because apparently they match.

0
 

Author Comment

by:kyriakos70
ID: 24344358
Abel yes,
It is in the fill of the first option case i=0, in the textbox4 I give a value "4/12/2008" I removed the code a=textbox1.text but the same error in the fill
jpaulino yes,
I tried but the result is the same the datetime parameter of the command4 accepts the parameter in the format dd/mm/yyyy but not in the combobox there the error is in the fill method of the first case of


Select Case ComboBox1.Items.IndexOf(i)
                Case i = 0 'ComboBox1.Items.Item(0)
                    Try
                       Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand1)
                        Form2.SqlCommand1.Parameters.Item(0).Value = TextBox1.Text
                        Form2.SqlDataAdapter3.Fill(Form2.DataSet51) <<<<<<<<<<<<<error HERE
                        Form5.Show()
                        Form5.DataGridView1.DataSource = (Form2.DataSet51)
                        Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"
                        Form2.SqlDataAdapter3.Fill(Form2.DataSet51)
                    Catch errobj As Exception
                        MsgBox(errobj.Message)
                    End Try

What shall I do?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:kyriakos70
ID: 24344374
Excuse me code again :
Select Case ComboBox1.Items.IndexOf(i)
                Case i = 0
                    Try
                       Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand1)
                        Form2.SqlCommand1.Parameters.Item(0).Value = TextBox1.Text
                        Form2.SqlDataAdapter3.Fill(Form2.DataSet51) <<<<<<<<<<<<<error HERE
                        Form5.Show()
                        Form5.DataGridView1.DataSource = (Form2.DataSet51)
                        Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"
                       Catch errobj As Exception
                        MsgBox(errobj.Message)
                    End Try
0
 

Author Comment

by:kyriakos70
ID: 24344383
The error is without the try:
"unable to convert the parameter value from string to int32" (it is in Greek so not exactly but the same)
0
 

Author Comment

by:kyriakos70
ID: 24344467
Error on Line 5
Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

Select Case ComboBox1.Items.IndexOf(i)

                Case i = 0 

Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand1)

Form2.SqlCommand1.Parameters.Item(0).Value = TextBox1.Text

Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

Form5.Show()

Form5.DataGridView1.DataSource = (Form2.DataSet51)

Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"

Open in new window

0
 
LVL 39

Expert Comment

by:abel
ID: 24344788
I have to ask you something, before we continue:

1. The value in the textbox is a date
2. The value that the parameter wants is an integer

what is the value you expect it to be? Do you want to convert a date to an integer, or do you want help in how to change your query to accept dates? The last thing can be fixed by doing:

Form2.SqlCommand1.Parameters.Item(0).Value = DateTime.Parse(TextBox1.Text)
Form2.SqlCommand1.Parameters.Item(0).DbType = DbType.Date

0
 

Author Comment

by:kyriakos70
ID: 24345032
I believe i didn't make it clear, I have a combobox with 22 values and 22 sqlcommands when I choose the any other item in the combobox this exception is raised, like the code doesn't move to the next item of the combo box.

1.The value in the textbox is a date
2.The value  that the parameter needs is an integer

But the value I choose is for sqlcommand4 case i = 2 which parameter is a date

The whole code

If CheckBox2.Checked = True Then

Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand45)

Form2.SqlCommand45.Parameters.Item(0).Value = TextBox2.Text

Form2.SqlCommand45.Parameters.Item(1).Value = TextBox3.Text

Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

Form5.Show()

Form5.DataGridView1.DataSource = (Form2.DataSet51)

Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"

        ElseIf CheckBox1.Checked = True Then

CheckBox1.Checked = False And CheckBox2.Checked = False

        Else

            Select Case ComboBox1.Items.Item(i)

                Case i = 0 

Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand1)

Form2.SqlCommand1.Parameters.Item(0).Value = TextBox1.Text

Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

Form5.Show()

Form5.DataGridView1.DataSource = (Form2.DataSet51)

Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"

                Case i = 1

Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand3)

Form2.SqlCommand3.Parameters.Item(0).Value = TextBox1.Text

Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

Form5.Show()

Form5.DataGridView1.DataSource = (Form2.DataSet51)

Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"

                Case i = 2 

Form2.SqlDataAdapter3.SelectCommand = (Form2.SqlCommand4)

Form2.SqlCommand4.Parameters.Item(0).Value = TextBox1.Text

Form2.SqlDataAdapter3.Fill(Form2.DataSet51)

Form5.Show()

Form5.DataGridView1.DataSource = (Form2.DataSet51)

Form5.DataGridView1.DataMember = "Taxinomisi_fortotikon"

                    

Open in new window

0
 
LVL 39

Expert Comment

by:abel
ID: 24345059
> But the value I choose is for sqlcommand4 case i = 2 which parameter is a date

sorry, you got me lost now. Earlier the discussion seemed to be about case i = 0, which was the code that was called.

The error you get is clearly about trying to put a date into an integer. The case i = 2 is not throwing an error, your case i = 0 is, however (at least, so much I understood from your information so far).

All your cases seem to have something like
Form2.SqlCommand3.Parameters.Item(0).Value = TextBox1.Text
or

Form2.SqlCommand4.Parameters.Item(0).Value = TextBox1.Text
and your error comes from the Fill() one line later. You clearly have a data type mismatch, which needs to be fixed.

To help you, it is vital for us that you tell us where the error is raised (you may even choose to use a screenshot showing the yellow line, the Greek won't be a problem), what you expected instead and what you want us to help you with (i.e., clear the error, for which you have many options now, or change the case=0 to case=2).
0
 

Author Comment

by:kyriakos70
ID: 24345061
The error appears at sqlcommand1 line16.
0
 
LVL 39

Expert Comment

by:abel
ID: 24345082
which is under case i - 0, where a date is pushed into a parameter that expects an integer. Have you tried setting the parameter to expect a date, as suggested earlier?
0
 

Author Comment

by:kyriakos70
ID: 24345248
It seems that when I select item 2 of the combobox it doesn't select it and remains in item 0 of the combobox.
------------------Microsoft-Word.doc
0
 

Author Comment

by:kyriakos70
ID: 24345288
I tried the code
Form2.SqlCommand1.Parameters.Item(0).Value = DateTime.Parse(TextBox1.Text)
Form2.SqlCommand1.Parameters.Item(0).DbType = DbType.Date
It returns empty records in the dbgrid because the sqlcommand1 commandtext is
"SELECT * FROM Taxinomisi_fortotikon WHERE aa (type int32) LIKE @aaa"


0
 

Author Comment

by:kyriakos70
ID: 24345308
And it expects an integer, the question is why it is not moving to the selected item of the combobox.
0
 
LVL 39

Accepted Solution

by:
abel earned 500 total points
ID: 24345322
Hmm, seems that we have been overseeing something for a long time. Thanks very much for that screenshot.

You are testing i = something on each line, but what is i, where is it set? Shouldn't you better be doing:

Select Case ComboBox1.SelectedIndex
   Case 0
   ....
   Case 1
   ....
   Case X
    ....
End Select

On a side note: why are you using names like DataSet1, DataSet2 and Form1, Form2 and ComboBox1, ComboBox2 etc? In general it is considered good programming practice to give descriptive names to your objects, which will make your code much more readable and prevent many hard-to-find errors.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now