SQLcommand serverside for gridview
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:
Code:
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:
Code:
<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>
Re: SQLcommand serverside for gridview
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
Re: SQLcommand serverside for gridview
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?
Re: SQLcommand serverside for gridview
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
Re: SQLcommand serverside for gridview
Quote:
Originally Posted by
hpl
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. :)
Quote:
Originally Posted by
hpl
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:
Code:
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.
Re: SQLcommand serverside for gridview
Hi hpl,
It worked like a champ for me when I tested with your case just now.
Like Gary Said,
Quote:
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.
Re: SQLcommand serverside for gridview
Ok let me try to explain:
I wish to set the SQLCommand Serverside like:
Code:
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
Code:
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
Re: SQLcommand serverside for gridview
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 ?
Re: SQLcommand serverside for gridview
Hey,
Unless that is some strange syntax that I am not aware of, why not just do this:
Code:
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
Re: SQLcommand serverside for gridview
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
