Can Sqldatasource Automatically Sort Retrieved Data From Stored Procedure ?
Jan 6, 2006
Hi,
This is my problem :
I have listBox1, listBox2 controls bound to sqldatasource1 and sqldatasource2.
I wrote one stored procedure to retrieve data from my sql database like :
select Id, Name,Address,Amount from KalakaDB
Now I want my listBox1 to display Name By name and my listBox2 to display Amount by decrease order
how can i connect my sqldatacontrol to the stored procedure ?
Thanks
View 1 Replies
ADVERTISEMENT
May 4, 2015
I'm trying to replace a view with stored procedure for faster performance. the View is called by end user using a query as below, I need to pass the date as parameter for sp to execute with quotes for it to execute with correct results. I tried to pass the date as parameter but could not execute stored procedure with correct results. Is there any way to put quotes around returned date from sub query :
Execute statement is like
Exec dbo.storedProc1 select max(date) from table
I need to pass the above as :
Exec dbo.storedProc '2015-03-24' .
Somehow passing the date as parameter is giving me empty result set.
View 2 Replies
View Related
Mar 21, 2007
I have a Gridview bound to a SQLDataSource that uses a Stored Procedure expecting 1 or 2 parameters. The parameters are bound to textbox controls. The SP looks up a person by name, the textboxes are Last Name, First Name. It will handle last name only (ie. pass @ln ='Jones' and @fn=null and you get all the people with last name=Jones. I tested the SP in Management Studio and it works as expected. When I enter a last name in the Last Name textbox and no First Name I get no results. If I enter a Last Name AND First Name I get results. I don't understand.
Here's the HTML View of the page. The only code is to bind the Gridview when the Search button is pressed.
<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:TextBox ID="TextBox1" runat="server" TabIndex=1></asp:TextBox> <asp:TextBox ID="TextBox2" runat="server" TabIndex=2></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Search" TabIndex=3 /> <hr /> </div> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="EmpID" CellPadding="4" EnableSortingAndPagingCallbacks="True" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="EmpID" HeaderText="Emp ID" ReadOnly="True" SortExpression="EmpID" /> <asp:BoundField DataField="FullName" HeaderText="Full Name" SortExpression="FullName" /> <asp:BoundField DataField="Nickname" HeaderText="Nickname" ReadOnly="True" SortExpression="Nickname" /> <asp:BoundField DataField="BGS2" HeaderText="BGS2" SortExpression="BGS2" /> <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /> <asp:BoundField DataField="email" HeaderText="Email" SortExpression="email" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EmpbaseConnectionString %>" SelectCommand="GetByName" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="TextBox2" Name="fn" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> <asp:ControlParameter ControlID="TextBox1" Name="ln" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> </SelectParameters> </asp:SqlDataSource> </form> </body></html>
View 7 Replies
View Related
Feb 5, 2007
Hello:I forgot what to do in a case like this. I have a SqlDataSource control within a label and I want to allow auto sorting, but when I click on the sort link the page comes back with:The SqlDataSource control 'sqlViewIncompleteForms' does not have a naming container. Ensure that the control is added to the page before calling DataBind.[HttpException (0x80004005): The SqlDataSource control 'sqlViewIncompleteForms' does not have a naming container. Ensure that the control is added to the page before calling DataBind.] System.Web.UI.WebControls.DataBoundControlHelper.FindControl(Control control, String controlID) +1590679 System.Web.UI.WebControls.ControlParameter.Evaluate(HttpContext context, Control control) +76 System.Web.UI.WebControls.Parameter.UpdateValue(HttpContext context, Control control) +46 System.Web.UI.WebControls.ParameterCollection.UpdateValues(HttpContext context, Control control) +103 System.Web.UI.WebControls.SqlDataSource.LoadCompleteEventHandler(Object sender, EventArgs e) +40 System.EventHandler.Invoke(Object sender, EventArgs e) +0 System.Web.UI.Page.OnLoadComplete(EventArgs e) +2010392 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1209How do I over come this? Do I have to use the find control method on page_load? Here is the code portion .vb<% 'Label which contains the default.aspx home content %><asp:Label ID="recip_home_display" runat="server" Visible="false"> <div align="center" class="HeaderSmall">Incomplete Recip Submissions</div> <asp:GridView ID="RecipStatusGridView" runat="server" AutoGenerateColumns="False" BorderWidth="0px" DataKeyNames="queue_id" DataSourceID="sqlViewIncompleteForms" AllowPaging="True" AllowSorting="True" CellPadding="2" CellSpacing="2" CssClass="TextSmall" HorizontalAlign="Center" Width="500px" Visible="False"> <Columns> <asp:BoundField DataField="queue_id" HeaderText="Incomplete Listings" HtmlEncode="False" InsertVisible="False" ReadOnly="True" SortExpression="queue_id"> <ControlStyle CssClass="LinkNormal" /> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> <asp:BoundField DataField="form_type" HeaderText="Listing Type" SortExpression="form_type"> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> <asp:BoundField DataField="listing_address" HeaderText="Listing Address" ReadOnly="True" SortExpression="listing_address"> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> <asp:BoundField DataField="last_modified_date" DataFormatString="{0:d}" HeaderText="Last Modified" HtmlEncode="False" SortExpression="last_modified_date"> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> </Columns> <HeaderStyle BackColor="#5C6F8D" /> <AlternatingRowStyle BackColor="#e9eaf0" /> </asp:GridView> <asp:SqlDataSource ID="sqlViewIncompleteForms" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ Appsettings:connectionstring %>" SelectCommandType="StoredProcedure" SelectCommand="spGetIncompleteForms"> <SelectParameters> <asp:ControlParameter ControlID="active_member_id" Name="AgentId" /> </SelectParameters></asp:SqlDataSource></asp:Label>
View 1 Replies
View Related
Apr 27, 2007
With a Gridview Control, I set the SqlDataSource to be a stored procedure in a Sql Sever database.
Using the wizzard to configure the datasource, the test returns lots of rows. After completing the wizzard, the gridview control does not show the column names in the VS2005 designer. For the gridview column headers the values are Databound Col0, Databound Col1, Databound Col2, ....) This tells me I have a problem.
I tried the same thing with a simpler stored procedure. This test stored procedure does not call anything else, takes several input parameters, returns rows. The column names show in the gridview control as expected.
So I am trying to figure out why the first case of gridview with sqldatasource is not working and the second case works . The stored procedure that is not working with my gridview calls multiple inner stored procedures and has #TEMP tables. My complex stored procedure ends with Select * from #Temp.
Could the calling of other inner stored procedures and use of #temp tables contribute to the problem? If yes then what is the strategy for using a gridview and loading it with the data that the complex stored procedure returns?
View 8 Replies
View Related
Aug 30, 2007
Hi every experts
I have a exist Stored Procedure in SQL 2005 Server, the stored procedure contain few output parameter, I have no problem to get result from output parameter to display using label control by using SqlCommand in Visual Studio 2003. Now new in Visual Studio 2005, I can't use sqlcommand wizard anymore, therefore I try to use the new sqldatasource control. When I Configure Datasource in Sqldatasource wizard, I assign select field using exist stored procedure, the wizard control return all parameter in the list with auto assign the direction type(input/ouput....), after that, whatever I try, I click on Test Query Button at last part, I always get error message The Query did not return any data table.
My Question is How can I setup sqldatasource to access Stored Procedure which contain output parameter, and after that How can I assign the output parameter value to bind to the label control's Text field to show on web?
Thanks anyone, who can give me any advice.
Satoshi
View 2 Replies
View Related
Feb 28, 2008
Hi,
I'm reasonably new to ASP.NET 2.0
I'm in my wizard_FinishButtonClick event, and from here, I want to take data from the form and some session variables and put it into my database via a stored procedure. I also want the stored procedure to return an output value. I do not need to perform a select or a delete.
For the life of me, I can't find a single example online or in my reference books that tells me how to accomplish this task using a SqlDataSource control. I can find lots of examples on sqldatasources that have a select statements (I don't need one) and use insert and update sql statements instead of stored procedures (I use stored procedures).
I desperately need the syntax to:
a) create the SqlDataSource with the appropriate syntax for calling a stored procedure to update and/or insert (again, this design side of VS2005 won't let me configure this datasource without including a select statement...which I don't need).
b) syntax on how to create the parameters that will be sent to the stored procedure for this sqldatasource (including output parameters).
c) syntax on how to set the values for these parameters (again...coming from form controls and session variables)
d) syntax on how to execute, in the code-behind, the stored procedure via the sqldatasource.
If anybody has sample code or a link or two, I would be most appreciative.
Thank you in advance for any help!
View 5 Replies
View Related
Jul 23, 2005
Hi All,I'm wondering if anyone can point me at some online resources thatdemonstrate how to run a stored procedure against an SQL Server 2000server at 6am each day?Any help much appreciated!Much warmth,planetthoughtful
View 2 Replies
View Related
Dec 13, 2006
I don't know if this is a newbie question or not, I want SQL Server to automatically execute a stored procedure at the first of each month, how can I do this ?
Regards,
AJ
Be nice to nerds. Chances are you'll end up working for one. (Bill Gates)
View 1 Replies
View Related
Jun 12, 2007
Hello,
I have a Repeater control getting data from a SqlDataSource.The SqlDataSource uses a stored procedure which accepts a input parameter with the name "sort" (@sort).
How do I use the FilterExpression with FilterParameter for defining the input value for the stored procedure ?The input value comes from an asp:QueryStringParameter.Or can the FilterExpression only be used with SQL Statements defined directly (as for defining the where clause) ?
I guess the best way is to use the FilterExpression soo the data is cached in the Dataset which is needed/required when using FilterExpression.Alternativly I could use the SelectParameter instead FilterParameters, but I guess the data wont be cached then.Anyone who can help me and guide me in the right direction ?
Best regardsMartin
View 2 Replies
View Related
Apr 20, 2006
I have a SQL Server 2005 Stored Procedure that takes a parameter to sort on:
-----------------------------------------------------------------
ALTER PROCEDURE [dbo].[GetAllApplications_Sorted]
@sortType varchar(25)
AS
SET NOCOUNT ON;
SELECT aev.ApplicationID,
aev.ApplicationName,
aev.EventMessage,
aev.MachineID,
aev.MachineName,
aev.EventLevel,
ellu.EventImg,
ellu.EventLevelID,
dbo.GetMaxEventLevelByApplicationID(aev.ApplicationID) AS maxEventLevel
FROM dbo.ApplicationEventView aev, dbo.EventLevelLookUp ellu
WHERE ellu.EventLevelID=dbo.GetMaxEventLevelByApplicationID(aev.ApplicationID)
AND aev.EventLevel=ellu.EventLevel
ORDER BY ellu.EventLevelID + ' ' + @sortType
-----------------------------------------------------------------
Problem is I keep getting this error:
Conversion failed when converting the varchar value 'desc' to data type int.
And I can't figure out why...
Thanks,
Doug
View 1 Replies
View Related
Nov 8, 2006
Hi,
I want to run a stored procedure automatically at regular intervals.The time duration is given by me as input details.for example i will
give the date and i should send email to some recipients just one day before the given date.I want this to be done in sql stored procedures.
Can anyone help?
Thanks in advance,
Nithya
View 7 Replies
View Related
Dec 12, 2007
I am using SQL 2005, and I am wondering if there is a way to set up a role such that when a new stored procedure is created, the role will automatically be given "execute" permission on that stored procedure.
We have our own dedicated server, but I also administer a database on a shared server (on DiscountASP.net), and they have it set up that way -- when a new stored procedure is created, the user set up by DiscountASP automatically has EXECUTE permission on that procedure.
I keep trying to duplicate this on our dedicated server, but to no avail. I would like to make it so a specific role always has execute permission on every new stored procedure, without having to explicitly add permissions to the role using the properties of the stored procedure.
Is this possible? It seems like it should be, but I can't figure out how. (I'm primarily a programmer, not a DBA, so apologies if this is a dumb question.)
View 3 Replies
View Related
Oct 29, 2007
hi,
i searched a lot to find how to pass an orderBy parameter finally i used a case block in my code and it works now how can i add a second parameter for ascending and descending order(@sortAscOrDesc)
when i use it after the end of case statement i get error
here is my sp:CREATE PROCEDURE [userPhotos]
@userID int,@orderBy varchar(100)
ASSELECT ID,UserID,Photo,ALbumID,Title,views,date_added from userAlbumPic where userID=@userID and albumID=0 order by
case @orderBy
when 'date_added' then date_added
when 'views' then [views]
else date_added
end
GO
View 7 Replies
View Related
Sep 12, 2006
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005
Stored procedure:
Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId <span class="kwd">int</span> = 0
AS
BEGIN
SET NOCOUNT ON;
Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End
oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span>
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add(<span class="st">"@CatId"</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;
gdvCategories.DataBind(); <<--- Error occured here
View 1 Replies
View Related
Jan 19, 2007
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View 9 Replies
View Related
Jul 22, 2006
Hello, I am trying to make this.
CREATE PROCEDURE [dbo].[P_SEL_ALLPERSONAS]
@nmpersona int,
@sortorder varchar(20)
AS
BEGIN
select nmpersona, dsprimernombre, dssegundonombre,
dsprimerapellido, dssegundoapellido
from personas
order by @sortorder
END
But I got this error. Please help
Msg 1008, Level 16, State 1, Procedure P_SEL_ALLPERSONAS, Line 13
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
View 5 Replies
View Related
May 12, 2007
Hi frdz, I m creating my web-application in asp.net with C# 2005 and using sql server 2005. I have created the stored procedure for the insert,update. I want to know how to write the mathematical calculations in the stored procedure.. Pls tell me from the below stored procedure were i m making the mistake ?? As the discount and the total amount are not calculated by itself....and stored in the database How to convert the @discpercent numeric(5,2) to@discpercent ="NoDiscount" should be displayed when no discount is being given to the customers.... ALTER PROCEDURE CalculationStoredProcedure
@accountid int output,
@accountname varchar(20),
@opbal numeric(10, 2),
@opbalcode char(2),
@total numeric(10, 2),
@clbal numeric(10, 2),
@clbalcode char(2),
@discpercent numeric(5,2),
@discamt numeric(10, 2)
as
begin
set nocount on
if @opbal IS NULL OR @opbal = 0
begin
select @opbal=0
select @opbalcode= ' '
select @clbal= 0
select @total= 0
select @clbalcode= ' '
@discpercent ="NoDiscount"
@discamt=0
end
select @accountid = isnull(max(accountid),0) + 1 from accountmaster
select @total=@opbal - @clbal from accountmaster
select @discamt=@total* @discpercent/100 from accountmaster
begin
insert into accountmaster
(
accountname,opbal,opbalcode,clbal,clbalcode
)
values
(
@accountname,@opbal,@opbalcode,@clbal,@clbalcode
)
end
set nocount off
end
Thanxs in adv...
View 7 Replies
View Related
May 27, 2007
I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks
View 1 Replies
View Related
Oct 23, 2007
hi
using sqldatasource how can i read the return value from a storedprocedure
thanks alot for help
View 6 Replies
View Related
Nov 6, 2007
Hi All,
I am using SQL Server 2000. I create a Stored Procedure(SP) with some Parameters.
I used the SqlDataSource1 to link this SP and set the Parameters (Control Parameter) & display the result in GridView.
When I click the Submit or OK button, the SP doesn't get the parameters value. So the GridView returns 0 records.
See the code below.
<asp:GridView ID="GridView1" runat="server" Visible="False" Width="100%" AllowPaging="True" DataSourceID="SqlDataSource2" EmptyDataText="No Records Found." PageSize="100">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:nfmseConnectionString %>"
SelectCommand="ldc_nncc_sp" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="ddlSwitch" Name="switch" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="txtCaller" Name="calling"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtCalled" Name="called"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtSDate" Name="sdate"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtEDate" Name="edate"
PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
ThanX in advance for your adivce.
Regards
~FAAS
View 5 Replies
View Related
Apr 15, 2008
Am running into the same problem and am manking no headway. I have a working stored procedure which uses an input parameter:
[dbo].[PayWeb_getMCRApprProcView] (@mcr_id int)
In the .aspx file, have set up a SqlDataSource:
<asp:SqlDataSource ID="sds3" runat="server" ConnectionString="<%$ ConnectionStrings:payweb_dbConnectionString %>" SelectCommandType="StoredProcedure">
In the code-behind, I'm trying to set the stored procedure name and parameter in a loop (to create multiple DetailsViews):
for (int i = 0; i < reqs.Length; i++) {sds3.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
sds3.SelectCommand = "PayWeb_getMCRApprProcView";sds3.SelectParameters.Add("@mcr_id", TypeCode.Int32, reqs[i]);
sds3.SelectParameters["@mcr_id"].Direction = ParameterDirection.Input;fv = new DetailsView();
fv.DataSource = sds3;
fv.DataBind();
Its on this last line that I keep getting the following error:
'PayWeb_getMCRApprProcView' expects parameter '@mcr_id', which was not supplied
Any suggestions are greatly appreciated.
Thanks
View 11 Replies
View Related
Jun 3, 2008
I am moving from .net 2003 to 2008. I am trying to populate a gridview with the SQL datasource.
The goal is to have a textbox and when I click a button, I want the gridview to be filtered based on the textbox.
I have all my stored procedure, SQL datasource all set. But how do you implement this.
I dont want to set my textbox a hard coded value. I am trying to achieve a simple task of taking the value from the textbox,and return results based on the grid view.
Any thoughts on this? I am new to SQL datasource and gridviews.
Thanks,
Topcatin
View 9 Replies
View Related
Dec 20, 2005
Hi,
I have an SqldataSource which calls a SP. that SP returns two datatables.
If I bind my SqlDataSource to a Gridview, it shows the first DataTable,
which is logical.
How can I retrieve the next Datatable?
I would like the results of DataTable1 to be shown in the Header of my Grid,
and DataTable2 in the Rows...
Can anybody give a direction?
thx
View 4 Replies
View Related
Jun 20, 2006
Hello,
I have created a web page with a FormView that allows me to add and edit data in my database. I configured the SqlDataSource control and it populated the SELECT, INSERT, UPDATE and DELETE statements. I've created a stored procedure to do the INSERT and return to new identity value. My question is this: Can I configure the control to use this stored procedure? If so, how? Or do I have to write some code for one of the event handlers (inserting, updating???)
Any help would be appreciated.
-brian
View 1 Replies
View Related
Sep 15, 2006
I am trying to populate a GridView from a stored procedure. The stored procedure's schema name is not the same as the user id logged into the database. In the SqlDataSource wizard, I am able to select the stored procedure name (unqualified), but Test SQL fails: "Could not find stored procedure 'devSelLineOverview'." Running the page also fails with the same error.Well, of course, because the procedure name must be qualified as line16l2.devSelLineOverview. However, the wizard doesn't pick up the schema name and I'm unable to change it there. So I changed it in the page source, but then I get this error: "Invalid object name 'line16l2.devSelLineOverview'." I tried changing the command type to custom SQL statement (not a stored procedure) but that deleted all the select parameters. Here is the data source, with the qualified name. I've also tried [line16l2.devSelLineOverview] (Could not find stored procedure) and [line16l2].[devSelLineOverview] (Invalid object name).<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PlantMetricsConnectionString %>"SelectCommand="line16l2.devSelLineOverview" SelectCommandType="StoredProcedure"><SelectParameters><asp:ControlParameter ControlID="date" Name="date" PropertyName="Text" Type="DateTime" /><asp:ControlParameter ControlID="shift" Name="shift" PropertyName="SelectedValue"Type="Byte" /><asp:ControlParameter ControlID="SKU" Name="sku" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="Lot" Name="lot" PropertyName="Text" Type="String" /></SelectParameters></asp:SqlDataSource>Thanks,Stephanie Giovannini
View 1 Replies
View Related
Nov 17, 2006
I m using Sqldatasource Control to fill a gridview.I wrote a Stroed Procedure.and assign Controlparameter value of sqldatasource to Proc.
Whatever the value i assigned to controls the Gridview filled with all records,whereas it must filter record by Parameter Value.
Following the Proc and Grid view Code.
CREATE PROCEDURE GetAllUsers( @persontype varchar(100)="", @name varchar(100)="", @adddatetime datetime="", @activeaccount int =-1, @country varchar (20)="") ASdeclare @cond varchar(1000) ;
if @persontype<>""beginset @cond= @cond+"and persontype="+@persontypeend if @name<>""beginset @cond= @cond+"and (charindex("0'>+@name+",x_firstname)>0 or charindex("0'>+@name+",x_lastname)>0 or charindex("0'>+@name+",x_email)>0) "end if @activeaccount<>-1beginset @cond= @cond+'and activeaccount='+@activeaccountend if @adddatetime<>""beginset @cond= @cond+'and adddatetime='+@adddatetimeend if @country<>""beginset @cond= @cond+'and x_country='+@countryendprint @cond exec( " select * from users where 1=1 "+@cond)GO
<asp:GridView ID="grdusers" runat="server" AllowPaging="True" AutoGenerateColumns="False" Width="780px" DataKeyNames="userID" CellPadding="4" CssClass="header" ForeColor="#333333" GridLines="None" allowsorting="false" DataSourceID="sqldatasource1">
<Columns>
<asp:boundfield
HeaderText="FirstName" datafield="x_firstname" sortexpression="x_firstname"/>
<asp:boundField DataField ="x_lastName" HeaderText="Lastname" sortexpression="x_lastname"/>
<asp:boundField DataField ="x_Address" HeaderText="Address" sortexpression="x_address"/>
<asp:boundField DataField ="x_country" HeaderText="Country" sortexpression="x_country"/>
<asp:boundField DataField ="Activeaccount" HeaderText="Active" sortexpression="activeaccount"/>
<asp:HyperLinkField DataNavigateUrlFields ="userID" HeaderText="Update" DataNavigateUrlFormatString="userdetail.aspx?id={0}" Text="Update" />
<asp:TemplateField HeaderText="Delete">
<ItemTemplate>
<asp:CheckBox ID="Chkdelete" runat=server />
</ItemTemplate>
</asp:TemplateField>
<asp:boundField DataField ="adddatetime" HeaderText="Creation Date" sortexpression="adddatetime"/>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:Label ID="lblmsg" runat="server" Width="770px" CssClass="errormsg"></asp:Label>
</td>
</tr>
<tr>
<td align="right" runat=server id="AllBtnCell" >
<pnwc:ExportButton ID="btnExcel" runat="server" CssClass="btncls" Text="Export to Excel" ExportType="Excel" FileNameToExport="ExportData.xls" Separator="TAB" OnClick="btnExcel_Click" />
<asp:Button ID="btnaddnew" runat="server" Text="Add New User" OnClick="btnaddnew_Click" CssClass="btncls" />
<asp:Button ID="Button1" runat="server" Text="Delete Selected" OnClick="Button1_Click" OnClientClick="return confirm('Are you sure want to delete selected users');" CssClass="btncls" />
</td>
</tr>
</table>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:VROAConnectionString %>"
SelectCommand="GetAllUsers" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter=False >
<SelectParameters>
<asp:ControlParameter ControlID="txtname" PropertyName="text" Name="name" ConvertEmptyStringToNull="False" Size="200" Type="String" />
<asp:ControlParameter ControlID="lsttype" PropertyName="selectedvalue" Name="activeaccount" ConvertEmptyStringToNull="False" Size="1" Type="Int16" />
<asp:ControlParameter ControlID="lstutype" PropertyName="selectedvalue" Name="persontype" ConvertEmptyStringToNull="False" Size="20" Type="String" />
<asp:ControlParameter ControlID="txtdate" PropertyName="text" Name="adddatetime" ConvertEmptyStringToNull="False" Size="15" />
<asp:ControlParameter ControlID="lstcountry" PropertyName="selectedvalue" Name="country" ConvertEmptyStringToNull="False" Size="200" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
I have tried so many things but nothing helped me.
View 2 Replies
View Related
Feb 23, 2007
Im using a SqlDataSource control. Ive got my "selectcommand" set to the procedure name, the "selectcommandtype" set to "storedprocedure"What am i doing wrong ? Ive got a Sql 2005 trace window open and NO sql statements are coming through "ds" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" SelectCommand="my_proc_name" SelectCommandType="StoredProcedure">
"txtF1" Name="param1" Type="String" />
"txtF2" Name="param2" Type="String" />
"" FormField="txtF3" Name="param3" Type="String" />
"" FormField="txtF4" Name="param4" Type="String" />
View 2 Replies
View Related
Jul 30, 2007
For some reason I can't make the stars align. Could someone spot the problem here? I'm looking to select the Identity of my last inserted record in the code behind my DetailsView. Here are the relavent bits:
Stored Procedure:ALTER PROCEDURE usp_EW_INSERTMajor
@StartDate datetime, @Finishdate datetime, @ProjectName nvarchar(1000), @WorkCell nvarchar(1000),
@JobName nvarchar(1000), @PartName nvarchar(1000), @StatusID int, @ResponsibleID int, @FacilityID int
AS
Set nocount on
DECLARE @ProjectID int
/*This saves the bits to the Project table*/
INSERT INTO EW_Project (ProjectTypeID,FacilityID,StartDate,FinishDate,Status,EmployeeID)
VALUES(1,@FacilityID,@StartDate,@FinishDate,@StatusID,@ResponsibleID)
/*This saves the bits to the Major table*/
SET @ProjectID = SCOPE_IDENTITY()
INSERT INTO EW_MajorBasic(ProjectID,ProjectName,WorkCell,JobName,PartName)
VALUES (@ProjectID,@Projectname,@WorkCell,@JobName,@PartName)
Set nocount off
SELECT @ProjectID as ProjectID
SQLDataSource<asp:SqlDataSource ID="ProjectData" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
SelectCommand="usp_EW_GETMajorBasic"
SelectCommandType="StoredProcedure"
UpdateCommand="usp_EW_UPDATEMajorBasic"
InsertCommand="usp_EW_INSERTMajor"
InsertCommandType="StoredProcedure"
UpdateCommandType="StoredProcedure"
DeleteCommand="DELETE FROM EW_Project WHERE ProjectID = @ProjectID"
>
<UpdateParameters>
<asp:Parameter Name="ProjectID" Type="Int32" />
<asp:Parameter Name="StartDate" Type="DateTime" />
<asp:Parameter Name="Finishdate" Type="DateTime" />
<asp:Parameter Name="ProjectName" Type="String" />
<asp:Parameter Name="WorkCell" Type="String" />
<asp:Parameter Name="JobName" Type="String" />
<asp:Parameter Name="PartName" Type="String" />
<asp:Parameter Name="StatusID" Type="Int32" />
<asp:Parameter Name="ResponsibleID" Type="Int32" />
<asp:CookieParameter CookieName="EW_FacilityID" Name="FacilityID" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="StartDate" Type="DateTime" />
<asp:Parameter Name="Finishdate" Type="DateTime" />
<asp:Parameter Name="ProjectName" Type="String" />
<asp:Parameter Name="WorkCell" Type="String" />
<asp:Parameter Name="JobName" Type="String" />
<asp:Parameter Name="PartName" Type="String" />
<asp:Parameter Name="StatusID" Type="Int32" />
<asp:Parameter Name="ResponsibleID" Type="Int32" />
<asp:CookieParameter CookieName="EW_FacilityID" Name="FacilityID" />
</InsertParameters>
<SelectParameters>
<asp:Parameter Name="ProjectID" Type="Int32" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="ProjectID" />
</DeleteParameters>
</asp:SqlDataSource>
DetailsView<asp:DetailsView
ID="DetailsView1"
runat="server"
AutoGenerateRows="False"
DataKeyNames="ProjectID"
DataSourceID="ProjectData"
SkinID="SimpleDetailsView"
OnItemDeleted="ProjectData_Deleted"
OnItemInserted="DetailsView1_ItemInserted">
Code-Behind: Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
Dim newID As Integer = e.Values("ProjectID")
testlabel.Text = newID
End Sub
To be clear, everything is working. When I come to this page with an ID, the records display fine (Select works). The Update and Delete work just fine. The Insert works fine too. It's just that the ItemInserted part does not want to grab the ProjectID. Specifically, testlabel displays a Zero.
View 2 Replies
View Related
Jan 19, 2008
I want to know how to set parameters for Update Stored Procedure in SQLDataSource
View 5 Replies
View Related
Dec 16, 2005
Someone, please help me understand:
I have the following code:
Dim conString As String = ConfigurationManager.ConnectionStrings("WebAllianceConnectionString").ConnectionStringDim dsrc As New SqlDataSource()dsrc.ConnectionString = conStringdsrc.SelectCommandType = SqlDataSourceCommandType.StoredProceduredsrc.CancelSelectOnNullParameter = Falsedsrc.SelectCommand = "sp_GetCustomerInvoiceList"dsrc.SelectParameters.Clear()dsrc.SelectParameters.Add("@QueryType", "DATE")dsrc.SelectParameters.Add("@CustCode", "BAM7")dsrc.SelectParameters.Add("@OrdNumber", " ")dsrc.SelectParameters.Add("@InvStartDate", "1/1/2005")dsrc.SelectParameters.Add("@InvStopDate", "1/31/2005")dsrc.SelectParameters.Add("@PONumber", " ")Return dsrc.Select(DataSourceSelectArguments.Empty)
When this runs, it throws an exception:Prodecure or Function 'sp_GetCustomerInvoiceList' expects parameter '@QueryType', which was not supplied.
I'm confused... I clearly added that using the .Add method?What am I doing wrong?
View 5 Replies
View Related
Apr 17, 2006
Help!I am trying to fill my datagrid using the SQLDataSource, using a stored procedure.The stored procedure expects a parameter which I can collect via the querystring, or a string. How can I pass the parameter through the SQLDatasSource?My SQLDataSource is SQLData1. I already have:SQLData1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureSQLData1.SelectCommand = "dbo.get_players"Thanks in advance,Karls
View 2 Replies
View Related
Jul 14, 2006
Can someone show me how to use SqlDataSource to call stored procedure in a c# class (a support class for aspx) and not in aspx or code-behind?
View 20 Replies
View Related
