none
how to make a good query with vb.net RRS feed

  • Question

  • Hi members

    i import textfile to a datagridview after i make a test if element (DO_Piece and AR_Ref) exist in database or not if exist i do somthing if not i turn the row to red

    after many test i see that test for DO_Piece work fine but for AR_Ref not

    this is my query

      For j As Integer = 0 To DataGridView1.Rows.Count - 2
                Dim cmd As New SqlCommand("select cbMarq,AR_Ref from F_DOCLIGNE where DO_Piece='" & DataGridView1.Rows(j).Cells(0).Value & "' and AR_Ref like '%" & DataGridView1.Rows(j).Cells(1).Value & "%'", cn)
                Using da As SqlDataReader = cmd.ExecuteReader
    
    
                    If da.HasRows Then
    please help

    Tuesday, July 2, 2019 8:58 AM

Answers

  • Hello,

    I would create the command object and parameters once as per below then set parameter values on each iteration of the for next (and on a trivial note, give better names to variables rather than j).

    Public Class Form1
        Private ConnectionString As String = ""
        Private Sub ValidateAgainstDatabaseTableButton_Click(sender As Object, e As EventArgs) _
            Handles ValidateAgainstDatabaseTableButton.Click
    
            Dim result As Object
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT count(*) " &
                                      " WHERE DO_Piece=@do_piece AND AR_Ref LIKE @ar_ref"
    
                    cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@do_piece"})
                    cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@ar_ref"})
    
                    cn.Open()
    
                    For rowIndex As Integer = 0 To DataGridView1.Rows.Count - 2
    
                        cmd.Parameters("@do_piece").Value = DataGridView1.Rows(rowIndex).Cells(0).Value.ToString
                        cmd.Parameters("@ar_ref").Value = $"%{DataGridView1.Rows(rowIndex).Cells(1).Value.ToString}%"
    
                        result = cmd.ExecuteScalar()
    
                        If result IsNot Nothing Then
                            If CInt(result) = 1 Then
                                DataGridView1.Rows(rowIndex).DefaultCellStyle.BackColor = Color.Red
                                DataGridView1.Rows(rowIndex).DefaultCellStyle.ForeColor = Color.White
                            Else
                                ' not found
                            End If
                        End If
    
                    Next
                End Using
            End Using
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, July 2, 2019 11:07 AM
    Moderator

All replies

  • Hi Houssem,
    it is better to use the following things:

    1. please, set OPTION STRICT ON for explicit type conversions.
    2. use parameter in SQL statements to prevent SQL Injection
    3. instead of HasRows at first check the number of records.

      For j As Integer = 0 To DataGridView1.Rows.Count - 2
                Dim cmd As New SqlCommand("select count(*) where DO_Piece=@do_piece and AR_Ref like @ar_ref", cn)
    	    cmd.Parameters.AddWithValue("@do_piece", DataGridView1.Rows(j).Cells(0).Value.ToString)
    	    cmd.Parameters.AddWithValue("@ar_ref", $"%{DataGridView1.Rows(j).Cells(1).Value.ToString}%")
                dim ret = cmd.ExecuteScalar
    	    if ret = 1 Then 
    ' ok!


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Tuesday, July 2, 2019 9:21 AM
  • Hello,

    I would create the command object and parameters once as per below then set parameter values on each iteration of the for next (and on a trivial note, give better names to variables rather than j).

    Public Class Form1
        Private ConnectionString As String = ""
        Private Sub ValidateAgainstDatabaseTableButton_Click(sender As Object, e As EventArgs) _
            Handles ValidateAgainstDatabaseTableButton.Click
    
            Dim result As Object
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT count(*) " &
                                      " WHERE DO_Piece=@do_piece AND AR_Ref LIKE @ar_ref"
    
                    cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@do_piece"})
                    cmd.Parameters.Add(New SqlParameter() With {.ParameterName = "@ar_ref"})
    
                    cn.Open()
    
                    For rowIndex As Integer = 0 To DataGridView1.Rows.Count - 2
    
                        cmd.Parameters("@do_piece").Value = DataGridView1.Rows(rowIndex).Cells(0).Value.ToString
                        cmd.Parameters("@ar_ref").Value = $"%{DataGridView1.Rows(rowIndex).Cells(1).Value.ToString}%"
    
                        result = cmd.ExecuteScalar()
    
                        If result IsNot Nothing Then
                            If CInt(result) = 1 Then
                                DataGridView1.Rows(rowIndex).DefaultCellStyle.BackColor = Color.Red
                                DataGridView1.Rows(rowIndex).DefaultCellStyle.ForeColor = Color.White
                            Else
                                ' not found
                            End If
                        End If
    
                    Next
                End Using
            End Using
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, July 2, 2019 11:07 AM
    Moderator