SQLTeam.com | Weblogs | Forums

SQL DataAdapter


#1

How do I use Parameters on the following line of code....

< Case Is > 1
            If DTRC > 1 Then
                Dim constr1 As String = "Data Source=i7borgmatrix2\sqlexpress;Initial Catalog=Petrol_Costs;Integrated Security=True;"

                ds.Tables.Add(New DataTable("Table1"))
                ds.Tables.Add(New DataTable("Table2"))
                ds.Tables.Add(New DataTable("Table3"))
                ds.Tables.Add(New DataTable("Table4"))
                ds.Tables.Add(New DataTable("Table5"))
                ds.Tables.Add(New DataTable("Table6"))
                ds.Tables.Add(New DataTable("Table7"))
                ds.Tables.Add(New DataTable("Table8"))
                ds.Tables.Add(New DataTable("Table9"))
                '=========================
                'Get Average Price@Liter
                '=========================
                Using con As New SqlConnection
                    con.ConnectionString = constr1
                    con.Open()
                    '========================
                    'Get Average Price@Liter
                    '=========================
                    Using AD As SqlDataAdapter = New SqlDataAdapter("select AVG(Price_a_liter) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                        AD.Fill(ds.Tables("Table1"))
                    End Using
                    '==========================
                    'Get Average Total_Liters
                    '==========================
                    Using AD As SqlDataAdapter = New SqlDataAdapter("select AVG(total_liters) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                        AD.Fill(ds.Tables("Table2"))
                    End Using
                    '==========================
                    'Get Average Total_Dollars
                    '==========================
                    Using AD As SqlDataAdapter = New SqlDataAdapter("select AVG(total_dollars) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                        AD.Fill(ds.Tables("Table3"))
                    End Using>

Many Thanks for reading.


#2

SqlDataAdapter has a ctor that can take a command object. Create a SqlCommand object and set the command, parameters, and connection on the command object. Then use that constructor instead of the string + connection as you are doing.