SELECT SQL Server VB NET error

Question

Using con As SqlConnection = getconnectionSQL()
Try
    con.Open()

    Dim cmd As New SqlCommand("SELECT * From Diaria where data='" & 
            data_SQL(TextBox4.Text) & "' and prefixo='" & 
            Trim(TextBox14.Text) & "'", con)

    Dim dr As SqlDataReader '= command.ExecuteReader()
    dr = cmd.ExecuteReader()
    If dr.Read() Then
        con.Close()
        con.Open()  
    end if

When searching for values in the table I see the following error:

startIndex cannot be longer than the string length. Name of the parameter: startIndex. dbo. Diaria: date nchar prefix(6) smallint bomb Numeric diesel(4,1)

Observing: this was a program made in VB6, I am beginner in VB6 and VB.net, I am trying to migrate to . net

Answer 1

Basically, if you’re doing it wrong, a simple example solution is like this:

^Implicit:

---

Dim SQL As String
Dim Dados As New DataTable
SQL = "SELECT * From Diaria where data=@data and prefixo=@prefixo"
Using db = New SqlConnection("")
   Using command = New SqlCommand(SQL, db)
        command.Parameters.AddWithValue("@data", New DateTime())
        command.Parameters.AddWithValue("@prefixo", "texto")
        Dados.Load(command.ExecuteReader())
   End Using
End Using

where Data is Datatable with the information, but that was really just for show. The problem with your code is because it has parameters where you were going wrong and can cause conversion problems, security problems and architecture. NET already plays the heavy role for you with the help of Parameters as exemplified in that reply, instead of New DateTime() pass a variable of the type with the search value and so serves for all others Parameters, always pass the data type with the reference value.

A small example has been made, but it covers the exchange of an untreated data with that of the Parameters, avoiding many problems mainly conversion and handling a code most expected by the architecture. You can also specify the type of data sent, example:

command.Parameters.Add("@data", SqlDbType.Date).Value = New DateTime()

This is more usual code:

^Explicit:

---

Dim SQL As String
Dim Dados As New DataTable
SQL = "SELECT * From Diaria where data=@data and prefixo=@prefixo"
Using db = New SqlConnection("")
    Using command = New SqlCommand(SQL, db)
        command.Parameters.Add("@data", SqlDbType.Date).Value = New DateTime()
        command.Parameters.Add("@prefixo", SqlDbType.NChar, 6).Value = "123456"
        Dados.Load(command.ExecuteReader())
    End Using
End Using

_References

• Sqlconnection Class
• Sqlcommand Class
• Datatable
• Parameters

_Examples:

• Problem with parameters in Sqlcommand
• Sqlcommand and Executereader parameters
• How can I make an if if Database data is True or False
• Type conversion error when saving to database