VB.NET Execute a looping query and display its result in 1 datagridview

Question

I have an Excel sheet loaded in dt_data and I want to compare the values of dt_data to my database and display it on another datagridview which is dt_sample. Inside my database there is past 3 months record and I want to get them all to be displayed here is my code below:

Try

            For i As Integer = 0 To dt_data.RowCount - 3
                Dim meter_number As String
                meter_number = dt_data.Rows(i).Cells(3).Value
                Dim query As String = "Select * from customer where meter_num = @meter_num"
                conn.Open()
                Dim command As New SqlCommand(query, conn)
                command.Parameters.AddWithValue("@meter_num", meter_number)
                Dim da As New SqlDataAdapter(command)
                Dim ds As New DataSet


                If i = dt_data.RowCount - 3 Then
                    da.Fill(ds, "customer")
                    dt_sample.DataSource = ds.Tables(0)
                End If



                conn.Close()

            Next
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
            'End Try
            'Catch ex As Exception
            'MessageBox.Show(String.Format("Error: {0}", ex.Message), "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error)
        End Try

https://i.imgur.com/23WVp8z.png

Please include your results as formatted text within the question, not as an external image. — Dale K
"I want to compare the values". How do you want to compare them? We shouldn't have to guess this stuff. — jmcilhinney
@jmcilhinney i want to compare them by a click of a button, i have a button for loading from excel to datagridview which is named dt_data and when i click another button it should compare the particular column from dt_data to my sql server database — BigDaddy
COMPARE THEM HOW? For equality? For relative value? For something else? Then do what with the results? Provide a FULL and CLEAR explanation of the problem. — jmcilhinney
For knowing the consumption for the past 3 months and getting it's average — BigDaddy

BigDaddy BigDaddy · Accepted Answer · 2019-10-28T05:51:34

Using command As New SqlCommand()
                command.Connection = conn

                Dim parameterNames As New List(Of String)(dt_data.RowCount - 2)
                For i As Integer = 0 To dt_data.RowCount - 3
                    Dim parameterName As String = "@meter_num_" & i
                    Dim meter_number As String = dt_data.Rows(i).Cells(3).Value
                    command.Parameters.AddWithValue(parameterName, meter_number)
                    parameterNames.Add(parameterName)
                Next

                command.CommandText = String.Format("SELECT * FROM customer WHERE cycle = @cycle and meter_num IN ({0})", String.Join(",", parameterNames), ("ORDER BY Client_Name ASC"))
                command.Parameters.AddWithValue("@cycle", cycle2last)

                Dim da As New SqlDataAdapter(command)
                Dim ds As New DataSet
                da.Fill(ds, "customer")
                Compare_Reading.dt_last2month.DataSource = ds.Tables(0)
            End Using

that's what i found solution and it successfully meet my problems

VB.NET Execute a looping query and display its result in 1 datagridview

1 Answers