Hi,

I am trying to bind to a datagrid using a function to get the data from an SQL database, using a parameterized query. I think I need to use "?" in the query rather than "@Theme_id", but my code still doesn't work. Can anyone help please? The code is:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DataGrid1.DataSource = GetTableData()
        DataGrid1.DataBind()
    End Sub

    Function GetTableData() As DataTable
        Dim ds As New DataSet()
        Dim strSql As String
        Dim id As Integer
        Dim objCommand As New SqlCommand()
        objCommand.CommandText = strSql
        strSql = "SELECT DISTINCT WHMetaRecord.meta_id, WHThemes.Theme_id, WHMetaRecord.Title, WHMetaRecord.URL, WHThemes.Description, WHMetaRecord.CreationDate, WHMetaRecord.Format,WHMetaRecord.Size,WHMetaRecord .detailsurl FROM WHMetaRecord INNER JOIN WHMetaThemes ON WHMetaRecord.meta_id = WHMetaThemes.meta_id INNER JOIN WHThemes ON WHMetaThemes.Theme_id = WHThemes.Theme_id WHERE (WHThemes.Theme_id = ?) ORDER BY WHMetaRecord.Title,WHMetaRecord.CreationDate DESC"
        Dim da As New SqlDataAdapter(strSql, SqlConnection1)
        Dim myparameters As SqlParameterCollection()
        da.SelectCommand.Parameters.Add("@Theme_id", SqlDbType.Int)
        da.SelectCommand.Parameters(0).Value = id
        da.Fill(ds, "tblSearchResults")
        lbl1.Text = (ds.Tables(0).Rows(0).Item(4))
        lbl2.Text() = (ds.Tables(0).Rows(0).Item(4))
        Return ds.Tables("tblSearchResults")
    End Function

Thanks in anticipation

Pauline

Hey,

Don't use ? for sql, use a parameter @Theme_id in the query.

Dim da As New SqlDataAdapter(strSql, SqlConnection1)
da.SelectCommand.Parameters.Add("@Theme_id", SqlDbType.Int, 10).Value = id

Dim ds as new DataSet
da.Fill(ds, "tblSearchResults")

Get rid of the command object; you aren't using it. Don't create a new dataset at the beginning, wait until later where I put it.

Brian

Brian, you are an absolute star - many thanks!
Pauline