Solution To Passing Parameters With Sql Text String.

I thought I would post this solution. I searched a long time and didnt see anything about how to solve my problem. After avoiding this and simply building strings I decided to dig in my heels and try and figure this out. Well, maybe I am just slow. :) Anyhow, here is some code that should help a lot of folks with this question...








Function GetProductCategories(ByVal departmentID) As DataSet





'set the connection string (comes from a property in this case)


Dim connection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringWeb"))





'set the sql text string notice the @DepartmentID is my parameter. protected from sql injections


Dim strSQL As String = "SELECT * FROM ProductCategory WHERE DepartmentID = @DepartmentID"





'set new command object to strsql and the connection. required


Dim command As New SqlCommand(strSQL, connection)





'set parameters to pass to through with the strsql. required for parameters. you can take this a set further. See commented fields below.


command.Parameters.Add(New SqlParameter("@DepartmentID", departmentID))





'additional set up for parameters if you like...


'command.Parameters.Add("@departmentID", SqlDbType.Int, 4)


'command.Parameters("@departmentID").Value = departmentID











'set SQLDataAdapter to your previously created command object


'this enables your adapter access to your strSQL, connection and parameters


Dim da As New SqlDataAdapter(command)





'set proper name of table for data set based upon departmentID


If departmentID = 1 Then


Dim ds As New DataSet()


da.Fill(ds, "dtCarriers")


Return ds


End If








'set proper name of table for data set based upon departmentID


If departmentID = 2 Then


Dim ds As New DataSet()


da.Fill(ds, "dtProducts")


Return ds


End If





End Function