What Does A Stored Procedure Recieve From A SqlDataSource Who's Parameter Is Empty And No Default Value Set?
Nov 24, 2006
If a sqldatasource is programed to send textbox1.text to a stored procedure, and the .text property is left empty, and there is no default value set for the parameter, what exactly is the stored procedure receiving?
I would like to run a IF BEGIN statement on the value of the parameter in the stored procedure but the following does not work:
IF @Parameter IS NULL BEGIN
or
IF @Parameter = '' BEGIN
The only way I've gotten it to work is if I set the default value of the parameter being sent to a specific alphanumeric value. Then do something like:
IF @Parameter = '99' BEGIN
<Code Here>
END
View 4 Replies
ADVERTISEMENT
Jul 14, 2004
I have web server with .aspx page from wich I call stored procedure on MSSQL server. In this procedure are lots of "select" statements and I need to show results of this statements in web page. Can I call this procedure in that manner that procedure output is writen in some file and this file then ir recieved by web server and included in web page.
View 2 Replies
View Related
Jul 20, 2007
Hi,This works:CREATE PROCEDURE MyProc @Date smalldatetime = '2005-01-01'AS... But this does not CREATE PROCEDURE MyProc @Date smalldatetime = GETDATE()AS... I'm talking about sql2005. Can anyone help how to overcome this?
View 3 Replies
View Related
Jun 5, 2008
Either method is in the “ASPX� file
This is a DataSource for a “DetailsView� which has on top of “DeleteCommand� an “InsertCommand� a “SelectCommand� and an “UpdateCommand�. It is related to a GridView and the “@DonationRecID� comes from this GridView.
Method 1. Using an SQL Query – this works fine <asp:SqlDataSource ID="donationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FUND %>" DeleteCommand="DELETE FROM [Donations] WHERE [DonationRecID] = @DonationRecID">
Method 2. – using a stored procedure – this bombs because I have no clue as to how to pass “@DonationRecID� to the stored procedure "Donations_Delete". <asp:SqlDataSource ID="donationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FUND %>" DeleteCommand="Donations_Delete" DeleteCommandType="StoredProcedure"> How do I pass “@DonationRecID� to the "Donations_Delete" stored procedure?
Does anyone have an example of how I can do this in the “ASPX.CS� file instead.
View 3 Replies
View Related
Feb 18, 2008
Hello
I've written a stored procedure with 4 parameters
create procedure dummy
( @a int, @b int, @c varchar(50), @d varchar(50))
now from front end(I'm using c#.net)
I want to send the values according to some criteria
So in the process...I've only values for @a & @c ...
so In order to reduce the code of sending Null values explicitly to other parameters...
can't I set like default values for it so that If I don't send values to certain parameters it will have the default value or Null value.
like I want something like this:
create procedure dummy
( @a int NULL, @b int NULL, @c varchar(50) NULL, @d varchar(50) NULL)
View 1 Replies
View Related
Aug 3, 2007
I'm trying to create a Grid View from a stored procedure call, but whenever I try and test the query within web developer it says I did not supply the input parameter even though I am.I've run the stored procedure from a regular ASP page and it works fine when I pass a parameter. I am new to dotNET and visual web developer, anybody else had any luck with this or know what I'm doing wrong? My connection is fine because when I run a query without the parameter it works fine.Here is the code that is generated from web developer 2008 if that helps any...... <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMS3TestConnectionString %>" ProviderName="<%$ ConnectionStrings:IMS3TestConnectionString.ProviderName %>" SelectCommand="usp_getListColumns" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:querystringparameter DefaultValue="0" Name="FormID" QueryStringField="FormID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
View 5 Replies
View Related
Nov 23, 2005
I am having a problem getting the output parameter from a stored procedure that deletes a record. I use the EXACT same process for the insert and update procs and they work fine. For some reason, the delete proc is either not returning an output parameter, or the SqlDataSource (dsCompany) is not able to get the information in the dsCompany_Deleted method. I simply want the proc to return an output value so that when I eventually get around to checking if the record can be deleted due to foreign key restraints, I can deliver the appropriate message to the user.I have highlighted the line that is giving the problem. I also highlighted a default value in the parameter, that when removed causes the app to crash because it is getting a null value back. Thanks in advance to anyone who can help!STORED PROC (eventually I will add foreign key restraints)
ALTER PROCEDURE CompanyDelete
@iCompanyId int,
@iOutput int OUTPUT
AS
BEGIN
DELETE
FROM tblCompany
WHERE iCompanyId = @iCompanyId
SELECT @iOutput = 1
END
CODE BEHIND (abbreviated)protected void btnCompany_Click(object sender, EventArgs e) {
dsCompany.InsertParameters["sCompany"].DefaultValue = txtCompany.Text;
dsCompany.Insert();
txtCompany.Text = "";
txtCompany.Focus(); }
protected void dsCompany_Inserted(object sender, SqlDataSourceStatusEventArgs e) {
if ((int)e.Command.Parameters["@iOutput"].Value > 0) {
lblMessage.Text = "Insert successful.";
}
else {
lblMessage.Text = "Insert failed. Duplicate record would be created.";
}
}
protected void dsCompany_Deleted(object sender, SqlDataSourceStatusEventArgs e) {
if ((int)e.Command.Parameters["@iOutput"].Value > 0) {
lblMessage.Text = "Delete successful.";
}
else {
lblMessage.Text = "Delete failed. Other records use this value.";
}
}
protected void dsCompany_Updated(object sender, SqlDataSourceStatusEventArgs e) {
if ((int)e.Command.Parameters["@iOutput"].Value > 0) {
lblMessage.Text = "Update successful.";
}
else {
lblMessage.Text = "Update failed. Duplicate record would be created.";
}
}
protected void gvCompany_RowEditing(object sender, GridViewEditEventArgs e) {
lblMessage.Text = "";
}
ASPX (abbreviated)<asp:SqlDataSource ID="dsCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="CompanyDelete" DeleteCommandType="StoredProcedure" InsertCommand="CompanyInsert" InsertCommandType="StoredProcedure" SelectCommand="CompanySelect" SelectCommandType="StoredProcedure" UpdateCommand="CompanyUpdate" UpdateCommandType="StoredProcedure" OnInserted="dsCompany_Inserted" OnDeleted="dsCompany_Deleted" OnUpdated="dsCompany_Updated">
<DeleteParameters>
<asp:Parameter Name="iCompanyId" Type="Int32" />
<asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0"/>
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="iCompanyId" Type="Int32" />
<asp:Parameter Name="sCompany" Type="String" />
<asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0" />
</UpdateParameters>
<SelectParameters>
<asp:Parameter DefaultValue="1" Name="iZero" Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="sCompany" Type="String" />
<asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0" />
</InsertParameters>
</asp:SqlDataSource>
View 3 Replies
View Related
Jan 6, 2006
Hi everyone,
I have created a stored procedure in sql server with parameters for my c# application. Wanted to know is there anyway to set the default value for @searchpostcode to select all the records?
Right now it brings the records based on the postcode specified .(I have dropdownlist in my c# application that passes the parameters for postcode)
My stored procedure:
CREATE PROCEDURE sp_accepting_practice (@searchpostcode as nvarchar(100)) AS
SELECT dbo.tbdentists.Title, dbo.tbdentists.FirstName, dbo.tbdentists.Surname, dbo.tbpractices.PracticeName, dbo.tbpractices.PracticeAddress1, dbo.tbpractices.PracticeAddress2, dbo.tbpractices.Town, dbo.tbpractices.Postcode, dbo.tbpractices.Phone, dbo.tbdentistspractices.ListNo, dbo.tbtreatment.treatmentNatureFROM dbo.tbdentists INNER JOIN dbo.tbdentistspractices ON dbo.tbdentists.DentistId = dbo.tbdentistspractices.DentistId INNER JOIN dbo.tbpractices ON dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId AND dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId INNER JOIN dbo.tbtreatment ON dbo.tbdentistspractices.TreatmentId = dbo.tbtreatment.treatmentIdWHERE dbo.tbpractices.Postcode LIKE '%' + @searchpostcode + '%'ORDER BY dbo.tbpractices.PracticeId
EXECUTE sp_accepting_practice G4GO
I greatly appreciate your help. Thanks in Advance
Regards
Shini
View 9 Replies
View Related
Oct 14, 2005
I have been building out an application and Service Broker has been working great but I have this one nagging problem.
View 15 Replies
View Related
Jul 7, 2007
Hi How do I set the default value of a SQLdatasource parameter to the current date-time <asp:Parameter Name="original_lastsaved" Type="DateTime" defaultvalue="???"/> The sql column field type is "datetime", so it will not accept a stringThanks for the help.Bones
View 3 Replies
View Related
Sep 13, 2006
Greetings! This is my first ever post here, so please be gentle.
I have a stored procedure that will be accepting many parameters (around 10). Any number of them can be empty (empty with a length of zero, but probably not Null per se).
How do I do a Select... Where... where if the parameter is empty it ignores it in the 'Where' but if the parameter had anything in it, it becomes part of the filter?
Thanks!
View 5 Replies
View Related
Jul 20, 2005
In the code below, the statement 'Print @Sites' prints nothing, eventhough the cursor contains 4 records, and 'Print @Site' prints theappropriate values. Can anyone see my mistake? I am attempting toprint a delimited string of the values referred to by @Sites.Thanks.Dan FishermanDECLARE SiteCursor CURSORGLOBALSCROLLSTATICFOR SELECT OfficeName FROM ClientOffices WHERE ClientID=12 ORDER BYOfficeNameOPEN SiteCursorDECLARE @Sites varchar(1000)DECLARE @Site varchar(100)FETCH NEXT FROM SiteCursor INTO @SiteWHILE @@FETCH_STATUS=0BEGINprint @SiteSET @Sites = @Sites + ', ' + @SiteFETCH NEXT FROM SiteCursor INTO @SiteENDPRINT @SitesCLOSE SiteCursorDEALLOCATE SiteCursorGO
View 4 Replies
View Related
Nov 30, 2005
hi, im new to this site so i don't know if i'm posting in the correct forum. anyway, this is my code:---Dim dbMac As DBLibrary = Nothing
dbMac = New DBLibrary(General.GetMACConnectionString)dbMac.OpenConnection("SPR_STAFFMAIN_GETEMPLOYEERECORDS")dbMac.CreateParameter("@USERENTITYID", GetUserEntityID(), Data.SqlDbType.Int)drpEmpNumbers.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataBind()---DBLibrary is a class that opens a connection to the SQL server. i'm getting an empty grid even though the stored procedure returns a row when i test it in the analyzer. is there to debug or test this code? thanks!
View 1 Replies
View Related
Jan 29, 2015
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
View 9 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
Nov 20, 2007
I've built a simple VS2005 ASP.Net web app that uses Crystal Reports for generating assorted reports. More specifically, when a report is called it executes the correct SQL Server Stored Procedure and returns a Data Table populated with with appropriate data. In my testing, everything seemed to be working fine.But I just did a test where I pressed the "Submit" button on two different client browsers at almost the same time. Without fail, one browser returns the report as it should but the other one returns an empty report; all of the Crystal Reports template info is there but the report is just empty of data. Considering that each browser is running in its own session, I'm confused about why this is happening.One thing: I did login as the same user in both cases. Might this be causing the problem?Robert W.Vancouver, BC
View 7 Replies
View Related
Feb 19, 2007
Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''
AS
BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end
Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
View 3 Replies
View Related
Apr 13, 2007
hi everyone,
I have a db based on the Tracking_Schema.sql / Tracking_Logic.sql (find in &windir%/Microsoft.NET/Framework/v3.0/Windows Workflow Foundation/SQL/EN), so after executing both of them I get several stored procedures, especially dbo.GetWorkflows. And I have a solution in VS05 which when executed is filling this stored procedure with Instance-Id´s. My question is: how is the working command (like exec, truncate,..) to empty my st.procedure, not to drop/delete it?
Thanks in advance, best regards
bg
View 1 Replies
View Related
Jul 2, 2007
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? 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 5, 2008
Hi,
I wanted to use a default for yearly instead of monthly for stored procedure, but couldn't figure out how. I usually used stored procedure @start and @end for date.
i.e. this is monthly by default
@start = jul 2007 and @end= aug 2007;
i.e. i want yearly by default
@start = jul 2007 and @end= jul 2008.
Is this too confusing?
Please help!!!!!!!!!
View 1 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