System.Data.SqlClient.SqlException

[DekaFlash]

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value,

Private Sub DataFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataFilter.Click
Dim connstring As String = "Data Source=BM-PC;Initial Catalog=EquinexMasterSql;Integrated Security = True"
Dim sql As String = "SELECT * FROM VsrData WHERE RaceDate ='" & RaceDate.Text.Trim & "'"
Dim conn As SqlConnection = New SqlConnection(connstring)

Try
da = New SqlDataAdapter(sql, conn)
da.SelectCommand = New SqlCommand(sql, conn)
builder = New SqlCommandBuilder(da)

conn.Open()

ds.Clear()

da.Fill(ds, "VsrData")

VsrDataGridView.DataSource = ds.Tables("VsrData")

Catch ex As Exception
MsgBox(ex.ToString.Trim)
End Try
conn.Close()
End Sub

 

[JohnH]

You have to use parameters, see for example this post.

 

[DekaFlash]

I've looked at the example and modified my code as shown below but it says
- Must declare the scalar variable @RaceDate

Private Sub DataFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateFilter.Click
Dim connstring As String = "Data Source=BM-PC;Initial Catalog=EquinexMasterSql;Integrated Security = True"
Dim sql As String = "SELECT * FROM VsrData WHERE RaceDate=@RaceDate"

Dim conn As SqlConnection = New SqlConnection(connstring)

Try
da = New SqlDataAdapter(sql, conn)
da.SelectCommand.Parameters.AddWithValue("@RaceDate", RaceDate.Text)
da.SelectCommand = New SqlCommand(sql, conn)
builder = New SqlCommandBuilder(da)

conn.Open()

ds.Clear()

da.Fill(ds, "VsrData")

VsrDataDataGridView.DataSource = ds.Tables("VsrData")

Catch ex As Exception
MsgBox(ex.ToString.Trim)
End Try
conn.Close()
End Sub

 

[JohnH]

Put the parashute on before you jump out of the plane
cmd=new command
cmd.parameters.add

 

[DekaFlash]

This is new to me, is that what you mean?

When I run the code it displays Must declare the scalar variable @RaceDate

Private Sub DataFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateFilter.Click
        Dim connstring As String = "Data Source=BM-PC;Initial Catalog=EquinexMasterSql;Integrated Security = True"
        Dim sql As String = "SELECT * FROM VsrData WHERE RaceDate=@RaceDate"
        Dim cmd As New SqlCommand

        cmd.Parameters.AddWithValue("@RaceDate", RaceDate.Text)
        Dim conn As SqlConnection = New SqlConnection(connstring)

        Try
            da = New SqlDataAdapter(sql, conn)

            da.SelectCommand = New SqlCommand(sql, conn)

            builder = New SqlCommandBuilder(da)

            conn.Open()

            ds.Clear()

            da.Fill(ds, "VsrData")

            VsrDataDataGridView.DataSource = ds.Tables("VsrData")

        Catch ex As Exception
            MsgBox(ex.ToString.Trim)
        End Try
        conn.Close()
    End Sub

 

[JohnH]

da.SelectCommand = New SqlCommand(sql, conn)

does your command have a parameter now?

 

[DekaFlash]

Thanks for your help, I've resolved the problem

 

[cjard]

cmd.Parameters.AddWithValue("@RaceDate", RaceDate.Text)

mmmhh.. dumb idea. The whole point of using parameters is:

You have a column in the database that is a DATE type
You have a parameter that is a DATE type
You use a DAteTimePicket to ask the user for a DATE type

DATE DATE DATE all the way through.. NO converting from a string to a date and back, and forward, and back... Thats where errors occur. If string was good for everything, we wouldnt have other data types at all..

 

[DekaFlash]

When I filter my data using add with value to display only a subset of the database in a datagridview component.

How can I update that information without having to display all the database.

Dim connstring As String = "Data Source=BM-PC;Initial Catalog=EquinexMasterSql;Integrated Security = True"
        Dim sql As String = "SELECT * FROM RatingsData WHERE RaceDate=@RaceDate"

        Dim conn As SqlConnection = New SqlConnection(connstring)

        If (Form1.showndata = 1 And Form1.datedata = 0 And Form1.racedata = 0) Then
            MsgBox("save 1")
            conn.Open()

            builder.GetUpdateCommand()
            'da.Update(Me.EquinexMasterSqlDataSet, "RatingsData")
            RaceDateString = RaceDate.Text.Substring(6, 1).ToString + RaceDate.Text.Substring(7, 1).ToString() + RaceDate.Text.Substring(8, 1).ToString + RaceDate.Text.Substring(9, 1).ToString + RaceDate.Text.Substring(5, 1).ToString + RaceDate.Text.Substring(3, 1).ToString + RaceDate.Text.Substring(4, 1).ToString + RaceDate.Text.Substring(2, 1).ToString + RaceDate.Text.Substring(0, 1).ToString + RaceDate.Text.Substring(1, 1).ToString
            da = New SqlDataAdapter(sql, conn)

            da.SelectCommand = New SqlCommand(sql, conn)
            da.SelectCommand.Parameters.AddWithValue("@RaceDate", RaceDateString)

            builder = New SqlCommandBuilder(da)

            'da.Update(Me.EquinexMasterSqlDataSet, "RatingsData")
            'Me.EquinexMasterSqlDataSet.Clear()
            '1 da.Fill(Me.EquinexMasterSqlDataSet, "RatingsData")
            DataGridView1.DataSource = Me.EquinexMasterSqlDataSet.Tables("RatingsData")
            Me.RatingsDataTableAdapter.Update(Me.EquinexMasterSqlDataSet.RatingsData)
            da.Fill(Me.EquinexMasterSqlDataSet, "RatingsData")
            conn.Close()

 

[JohnH]

RaceDateString

Are you saying you don't have date values in your database? Only string fragments that resembles dates?

 

[DekaFlash]

The date values in my database are of the datetime format, but I found working with strings easier.

 

[cjard]

I think the idiom is "Forging a rod for your own back"


Strings are never easier than the native datatypes when you know how they work; were I you, I'd ask for help with the native types