WhereClause use in a Data Grid
-
I have a datagrid that needs to display records given by a Select statement with a WHERE clause that is passed in a given value by variable 'appID'. For some reason I've been unable to make it work any which way I tried. As I'm not totally sure how to effectively pass a value in the context of a SQL Adapter to the DB, The last thing I tried was passing it to a variable and then to the SQL string. The compiler screams with the error: " BC30311: Value of type 'String' cannot be converted to 'System.Data.SqlClient.SqlCommand'. " Has anyone ever tried this?? I've looked around and most data bound to datagrids are usually simple queries, i.e., "Select * From Clients" with no filtering whatsoever. Here's some code: Sub BindGrid() Dim dsAppl As DataSet Dim conMeta As SqlConnection Dim dadAppl As SqlDataAdapter Dim whereClause As String = "WHERE Application_ID = '"&session("appID")&"' " dsAppl = New DataSet() conMeta = New SqlConnection( "Server='DOHSDBS96';trusted_connection=true;Database='METADATAv2'" ) dadAppl = New SqlDataAdapter( "SELECT *, Q.Type AS QType, S.Type AS SType FROM A_Table A_T INNER JOIN Quality Q ON Q.Quality_ID=A_T.Quality_ID INNER JOIN Status S ON S.Status_ID=A_T.Status_ID" & whereClause & "ORDER BY & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString(), conMeta ) dadAppl.Fill( dsAppl,"A_Table" ) Try AppDataGrid.DataSource = dsAppl AppDataGrid.Databind() Catch exc As Exception Response.Write(exc) AppDataGrid.CurrentPageIndex = 0 End Try ShowStats End Sub :omg:
-
I have a datagrid that needs to display records given by a Select statement with a WHERE clause that is passed in a given value by variable 'appID'. For some reason I've been unable to make it work any which way I tried. As I'm not totally sure how to effectively pass a value in the context of a SQL Adapter to the DB, The last thing I tried was passing it to a variable and then to the SQL string. The compiler screams with the error: " BC30311: Value of type 'String' cannot be converted to 'System.Data.SqlClient.SqlCommand'. " Has anyone ever tried this?? I've looked around and most data bound to datagrids are usually simple queries, i.e., "Select * From Clients" with no filtering whatsoever. Here's some code: Sub BindGrid() Dim dsAppl As DataSet Dim conMeta As SqlConnection Dim dadAppl As SqlDataAdapter Dim whereClause As String = "WHERE Application_ID = '"&session("appID")&"' " dsAppl = New DataSet() conMeta = New SqlConnection( "Server='DOHSDBS96';trusted_connection=true;Database='METADATAv2'" ) dadAppl = New SqlDataAdapter( "SELECT *, Q.Type AS QType, S.Type AS SType FROM A_Table A_T INNER JOIN Quality Q ON Q.Quality_ID=A_T.Quality_ID INNER JOIN Status S ON S.Status_ID=A_T.Status_ID" & whereClause & "ORDER BY & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString(), conMeta ) dadAppl.Fill( dsAppl,"A_Table" ) Try AppDataGrid.DataSource = dsAppl AppDataGrid.Databind() Catch exc As Exception Response.Write(exc) AppDataGrid.CurrentPageIndex = 0 End Try ShowStats End Sub :omg:
instead of directly constructing your SQL, prepare it in a variable and see what exactly it is constructing. that way, it will be easy to track down. Bhaskara
-
I have a datagrid that needs to display records given by a Select statement with a WHERE clause that is passed in a given value by variable 'appID'. For some reason I've been unable to make it work any which way I tried. As I'm not totally sure how to effectively pass a value in the context of a SQL Adapter to the DB, The last thing I tried was passing it to a variable and then to the SQL string. The compiler screams with the error: " BC30311: Value of type 'String' cannot be converted to 'System.Data.SqlClient.SqlCommand'. " Has anyone ever tried this?? I've looked around and most data bound to datagrids are usually simple queries, i.e., "Select * From Clients" with no filtering whatsoever. Here's some code: Sub BindGrid() Dim dsAppl As DataSet Dim conMeta As SqlConnection Dim dadAppl As SqlDataAdapter Dim whereClause As String = "WHERE Application_ID = '"&session("appID")&"' " dsAppl = New DataSet() conMeta = New SqlConnection( "Server='DOHSDBS96';trusted_connection=true;Database='METADATAv2'" ) dadAppl = New SqlDataAdapter( "SELECT *, Q.Type AS QType, S.Type AS SType FROM A_Table A_T INNER JOIN Quality Q ON Q.Quality_ID=A_T.Quality_ID INNER JOIN Status S ON S.Status_ID=A_T.Status_ID" & whereClause & "ORDER BY & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString(), conMeta ) dadAppl.Fill( dsAppl,"A_Table" ) Try AppDataGrid.DataSource = dsAppl AppDataGrid.Databind() Catch exc As Exception Response.Write(exc) AppDataGrid.CurrentPageIndex = 0 End Try ShowStats End Sub :omg:
or try to create a command object and pass it to sqldataadapter Bhaskara
-
I have a datagrid that needs to display records given by a Select statement with a WHERE clause that is passed in a given value by variable 'appID'. For some reason I've been unable to make it work any which way I tried. As I'm not totally sure how to effectively pass a value in the context of a SQL Adapter to the DB, The last thing I tried was passing it to a variable and then to the SQL string. The compiler screams with the error: " BC30311: Value of type 'String' cannot be converted to 'System.Data.SqlClient.SqlCommand'. " Has anyone ever tried this?? I've looked around and most data bound to datagrids are usually simple queries, i.e., "Select * From Clients" with no filtering whatsoever. Here's some code: Sub BindGrid() Dim dsAppl As DataSet Dim conMeta As SqlConnection Dim dadAppl As SqlDataAdapter Dim whereClause As String = "WHERE Application_ID = '"&session("appID")&"' " dsAppl = New DataSet() conMeta = New SqlConnection( "Server='DOHSDBS96';trusted_connection=true;Database='METADATAv2'" ) dadAppl = New SqlDataAdapter( "SELECT *, Q.Type AS QType, S.Type AS SType FROM A_Table A_T INNER JOIN Quality Q ON Q.Quality_ID=A_T.Quality_ID INNER JOIN Status S ON S.Status_ID=A_T.Status_ID" & whereClause & "ORDER BY & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString(), conMeta ) dadAppl.Fill( dsAppl,"A_Table" ) Try AppDataGrid.DataSource = dsAppl AppDataGrid.Databind() Catch exc As Exception Response.Write(exc) AppDataGrid.CurrentPageIndex = 0 End Try ShowStats End Sub :omg:
The easier way to deal with this problem would be to have a command object that will take the string and then assign this command object to be the SqlDataAdapter's select command zimcoder What Democracy?? Jesus Christ is King and if you do not like... well you can go to hell!
-
I have a datagrid that needs to display records given by a Select statement with a WHERE clause that is passed in a given value by variable 'appID'. For some reason I've been unable to make it work any which way I tried. As I'm not totally sure how to effectively pass a value in the context of a SQL Adapter to the DB, The last thing I tried was passing it to a variable and then to the SQL string. The compiler screams with the error: " BC30311: Value of type 'String' cannot be converted to 'System.Data.SqlClient.SqlCommand'. " Has anyone ever tried this?? I've looked around and most data bound to datagrids are usually simple queries, i.e., "Select * From Clients" with no filtering whatsoever. Here's some code: Sub BindGrid() Dim dsAppl As DataSet Dim conMeta As SqlConnection Dim dadAppl As SqlDataAdapter Dim whereClause As String = "WHERE Application_ID = '"&session("appID")&"' " dsAppl = New DataSet() conMeta = New SqlConnection( "Server='DOHSDBS96';trusted_connection=true;Database='METADATAv2'" ) dadAppl = New SqlDataAdapter( "SELECT *, Q.Type AS QType, S.Type AS SType FROM A_Table A_T INNER JOIN Quality Q ON Q.Quality_ID=A_T.Quality_ID INNER JOIN Status S ON S.Status_ID=A_T.Status_ID" & whereClause & "ORDER BY & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString(), conMeta ) dadAppl.Fill( dsAppl,"A_Table" ) Try AppDataGrid.DataSource = dsAppl AppDataGrid.Databind() Catch exc As Exception Response.Write(exc) AppDataGrid.CurrentPageIndex = 0 End Try ShowStats End Sub :omg:
The easier way to deal with this problem would be to have a command object that will take the string and then assign this command object to be the SqlDataAdapter's select command query = "SELECT *, Q.Type AS QType, S.Type AS SType FROM A_Table A_T INNER JOIN Quality Q ON Q.Quality_ID=A_T.Quality_ID INNER JOIN Status S ON S.Status_ID=A_T.Status_ID" & WHERE Application_ID = '"&session("appID")&"'& "ORDER BY & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString()" Dim cmd As New SqlCommand(query,conMeta) dadAppl = New SqlDataAdapter(); dadAppl.SelectCommand = cmd 'do the rest dadAppl.Fill( dsAppl,"A_Table" ) Try AppDataGrid.DataSource = dsAppl AppDataGrid.Databind() Catch exc As Exception Response.Write(exc) AppDataGrid.CurrentPageIndex = 0 End Try ShowStats zimcoder What Democracy?? Jesus Christ is King and if you do not like... well you can go to hell!