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

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

Kind regards,

Matthias Vance

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  

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")))

Select allOpen in new window

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.

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

Select allOpen in new window

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

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"

Select allOpen in new window

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

Select allOpen in new window

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:

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:

but I'm not sure if that will fit with the rest of your code.

Have you tried my code ?

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:

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.

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?

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

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)

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"

Select allOpen in new window

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

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"
                    

Select allOpen in new window

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

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).

The error appears at sqlcommand1 line16.

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?

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

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"

And it expects an integer, the question is why it is not moving to the selected item of the combobox.