SQLcommand serverside for gridview

[hpl]

I have a gridview, where I have made autocode using the wizard.
However I need a dynamic SQLcommand from server side.
I have tried this code, but it doesn't work, it works if I use this code from HTML (setting the sqlcommand in the SQLDataSource.
I have deleted the sqlcommand from the HTML and instead I have implemented the code in the page_load event:

Dim str As String = SqlDataSource1.SelectCommand = "SELECT DISTINCT Beregning.bId, Informationer.Dato,Beregning.bType, Informationer.pNavn, Informationer.pNr, Informationer.Beskr, Beregning.uId FROM Beregning CROSS JOIN Informationer WHERE (Beregning.UserId = @UserId) AND Beregning.uId = Informationer.uId AND (Beregning.bType=@berType2) ORDER BY Beregning.bId DESC"
SqlDataSource1.SelectCommand = str

But this code works, when I apply the SQLCommand in HTML:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IsoPlusConnectionStringDrift %>" SelectCommand='SELECT DISTINCT Beregning.bId, Informationer.Dato,Beregning.bType, Informationer.pNavn, Informationer.pNr, Informationer.Beskr, Beregning.uId FROM Beregning CROSS JOIN Informationer WHERE (Beregning.UserId = @UserId) AND Beregning.uId = Informationer.uId AND (Beregning.bType=@berType) ORDER BY Beregning.bId DESC'>
            <SelectParameters>
                <asp:SessionParameter Name="UserId" SessionField="userId" />
                <asp:Parameter DefaultValue="Optimering" Name="berType" />
                <asp:Parameter DefaultValue="Kapacitet" Name="berType2" />
                <asp:Parameter DefaultValue="Varmetab - Alle rørtyper" Name="berType3" />
                <asp:Parameter DefaultValue="Varmetab - Nuværdi" Name="berType4" />
                <asp:Parameter DefaultValue="Nomogram" Name="berType5" />
                <asp:Parameter DefaultValue="Temperatur - Afkølingsgraf" Name="berType6" />
            </SelectParameters>
        </asp:SqlDataSource>

[gep13]

Hey,

When you say that it doesn't work, what exactly do you mean? Not it simply not work, or are you getting an error? If an error, what is the error?

Given that you are moving out of the realm of what SqlDataSource can do for you "easily", I would recommend that you stop using them altogether. Create the SqlConnection, SqlCommand objects in code, and deal with the results, and then set the binding of the GridView up. You will find, in the long run, this offers you far greater flexibility, trust me.

If you are going to continue down the route that you are on now, you should do the work of changing the SqlCommand in the Selecting Event of the SqlDataSource:

http://msdn.microsoft.com/en-us/libr...selecting.aspx

This happens just before the SqlDataSource executes the SqlCommand, so it is at this point you have the opportunity to change it.

Gary

[hpl]

Of course I want to run all of my code from server side and not this messy mix that I have made :-D

So I need to bind the SQLdatasource with conn-string and sqlcommand to the gridview. Is that easy and do I do that in the page_load?

[gep13]

Hey,

I am not sure that I understand your question.

Are you saying you want to move away from SqlDataSources, or are you going to continue using them?

Gary

[nepalbinod]

I have a gridview, where I have made autocode using the wizard.
However I need a dynamic SQLcommand from server side.

That's your requirement.

I have tried this code, but it doesn't work, it works if I use this code from HTML (setting the sqlcommand in the SQLDataSource.
I have deleted the sqlcommand from the HTML and instead I have implemented the code in the page_load event:

Dim str As String = SqlDataSource1.SelectCommand = "SELECT DISTINCT Beregning.bId, Informationer.Dato,Beregning.bType, Informationer.pNavn, Informationer.pNr, Informationer.Beskr, Beregning.uId FROM Beregning CROSS JOIN Informationer WHERE (Beregning.UserId = @UserId) AND Beregning.uId = Informationer.uId AND (Beregning.bType=@berType2) ORDER BY Beregning.bId DESC"
SqlDataSource1.SelectCommand = str

Can you post the full code from the beginning, from "Page_Load(...". And also--Full markup of your GridView.

[nagasrikanth]

Hi hpl,

It worked like a champ for me when I tested with your case just now.

Like Gary Said,

When you say that it doesn't work, what exactly do you mean? Not it simply not work, or are you getting an error? If an error, what is the error?

??

Regards,
Naga.

[hpl]

Ok let me try to explain:
I wish to set the SQLCommand Serverside like:

Dim str As String = SqlDataSource1.SelectCommand = "SELECT DISTINCT Beregning.bId, Informationer.Dato,Beregning.bType, Informationer.pNavn, Informationer.pNr, Informationer.Beskr, Beregning.uId FROM Beregning CROSS JOIN Informationer WHERE (Beregning.UserId = @UserId) AND Beregning.uId = Informationer.uId AND (Beregning.bType=@berType2) ORDER BY Beregning.bId DESC"
        SqlDataSource1.SelectCommand = str

If I use this code and delete the sqlcommand from the source code I get this error, which seems as if the SQLCommand is not executed

Server Error in '/Isoplus' Application.
--------------------------------------------------------------------------------

Incorrect syntax near 'False'. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'False'.

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace: 


[SqlException (0x80131904): Incorrect syntax near 'False'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3430
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +52
   System.Data.SqlClient.SqlDataReader.get_MetaData() +130
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +371
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1139
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +45
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +162
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +35
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +32
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +183
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +307
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +152
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2860
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +84
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +153
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +99
   System.Web.UI.WebControls.GridView.DataBind() +23
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +92
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +100
   System.Web.UI.Control.EnsureChildControls() +133
   System.Web.UI.Control.PreRenderRecursiveInternal() +109
   System.Web.UI.Control.PreRenderRecursiveInternal() +233
   System.Web.UI.Control.PreRenderRecursiveInternal() +233
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4436

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

[nagasrikanth]

Well, First of all, You dont need to delete the sql command for Datasource. You can leave it as it is and change it in run time.

Majorly, It looks like there was some error in the SQL statement it self. Are you passing any parameters with bool value. If yes, try to send the value in terms of 1/0.

Also, As a trail, Remove the parameters and pass it directly to SQL query. What is the exception now you are getting ?

[gep13]

Hey,

Unless that is some strange syntax that I am not aware of, why not just do this:

SqlDataSource1.SelectCommand = "SELECT DISTINCT Beregning.bId, Informationer.Dato,Beregning.bType, Informationer.pNavn, Informationer.pNr, Informationer.Beskr, Beregning.uId FROM Beregning CROSS JOIN Informationer WHERE (Beregning.UserId = @UserId) AND Beregning.uId = Informationer.uId AND (Beregning.bType=@berType2) ORDER BY Beregning.bId DESC"

Have you tested that query directly against the database a confirmed that it works?

Gary

[GaryMazzone]

IS Beregning.BType a Bit type in the daabase (to represent True and False)? If so you can't query with True/False but with 1/0 (1 = True , 0 = False). SQL Server does not understand True/False