SqlDataSource And Stored Procedure Not Getting Called
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
ADVERTISEMENT
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
Dec 28, 2005
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies
View Related
Mar 31, 2008
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
View 1 Replies
View Related
Jul 11, 2007
OK, I have been raking my brains with this and no solution yet. I simply want to update a field in a table given the record Id. When I try the SQL in standalone (no sp) it works and the field gets updated. When I do it by executing the stored procedure from a query window in the Express 2005 manager it works well too. When I use the stored procedure from C# then it does not work:
1. ExecuteNonQuery() always returns -1 2. When retrieving the @RETURN_VALUE parameter I get -2, meaning that the SP did not find a matching record.
So, with #1 there is definitely something wrong as I would expect ExecuteNonQuery to return something meaningful and with #2 definitely strange as I am able to execute the same SQL code with those parameters from the manager and get the expected results.
Here is my code (some parts left out for brevity):1 int result = 0;
2 if (!String.IsNullOrEmpty(icaoCode))
3 {
4 icaoCode = icaoCode.Trim().ToUpper();
5 try
6 {
7 SqlCommand cmd = new SqlCommand(storedProcedureName);(StoredProcedure.ChangeAirportName);
8 cmd.Parameters.Add("@Icao", SqlDbType.Char, 4).Value = newName;
9 cmd.Parameters.Add("@AirportName", SqlDbType.NVarChar, 50).Value = (String.IsNullOrEmpty(newName) ? null : newName);
10 cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
11 cmd.Connection = mConnection; // connection has been opened already, not shown here
12 cmd.CommandType = CommandType.StoredProcedure;
13 int retval = cmd.ExecuteNonQuery(); // returns -1 somehow even when RETURN n is != -1
14 result = (int)cmd.Parameters["@RETURN_VALUE"].Value;
15
16 }
17 catch (Exception ex)
18 {
19 result = -1;
20 }
21 }
And this is the stored procedure invoked by the code above:1 ALTER PROCEDURE [dbo].[ChangeAirfieldName]
2 -- Add the parameters for the stored procedure here
3 @Id bigint = null,-- Airport Id, OR
4 @Icao char(4) = null,-- ICAO code
5 @AirportName nvarchar(50)
6 AS
7 BEGIN
8 -- SET NOCOUNT ON added to prevent extra result sets from
9 -- interfering with SELECT statements.
10 SET NOCOUNT ON;
11
12 -- Parameter checking
13 IF @Id IS NULL AND @Icao IS NULL
14 BEGIN
15 RETURN -1;-- Did not specify which record to change
16 END
17 -- Get Id if not known given the ICAO code
18 IF @Id IS NULL
19 BEGIN
20 SET @Id = (SELECT [Id] FROM [dbo].[Airports] WHERE [Icao] = @Icao);
21 --PRINT @id
22 IF @Id IS NULL
23 BEGIN
24 RETURN -2;-- No airport found with that ICAO Id
25 END
26 END
27 -- Update record
28 UPDATE [dbo].[Airfields] SET [Name] = @AirportName WHERE [Id] = @Id;
29 RETURN @@ROWCOUNT
30 END
As I said when I execute standalone UPDATE works fine, but when approaching it via C# it returns -2 (did not find Id).
View 2 Replies
View Related
Jul 23, 2005
HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
View 7 Replies
View Related
Jul 23, 2005
Since RDMBS and its language SQL is set-based would it make more senseto call a given stored process "Stored Sets" instead of currenttheorically misleading Stored Procedure, as a measure to prodprogrammers to think along the line of sets instead of procedure?
View 4 Replies
View Related
May 29, 2005
Hi all,i have a problem and couldnt find anything even close to it. please help me, here is the description of what i m trying to accomplish:I have a trigger that is generating a column value and calling a stored procedure after the value is generated. And this stored procedure is setting this generated value as an output parameter. But my problem is:my asp.net page is only sending an insert parameter to the table with the trigger, trigger is running some code depending on the insert parameter and calling this other stored procedure internally. So basically i m not calling this last stored procedure that sets the output parameter within my web form. How can i get the output parameter in my webform? Everthing is working now, whenever an insert hits the table trigger runs and generates this value and called stored procedure sets it as an output parameter. I can get the output parameter with no problem in query analyzer, so the logic has no problem but i have no idea how this generated output parameter can be passed in my webform since its not initiated there.any help will greately be appreciated, i m sure asp.net and sql server 2000 is powerful and flexible enough to accomplish this but how??-shane
View 8 Replies
View Related
Dec 6, 2007
When running a stored procedure, how can i retrieve the warnings that are issued within the stored procedure?
the code used is below,
the jdbc is connecting fine, it is running the stored procedure, but when an error is raised in the stored procedure, it is not coming back into s.getwarnings()
warning raised in stored proc with
Code Block
RAISERROR ('Error', 16, 1)with nowait;
there are no results for the stored procedure, I am just wanting to get the warnings
Code Block
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localh...........);
CallableStatement s = con.prepareCall("{call procedure_name}");
s.execute();
//running in seprate thread
SQLWarning warn = s.getWarnings();
while (m.running) {
if(warn == null) {
warn = s.getWarnings();
}
safeOut(warn);
if(warn != null)
warn = warn.getNextWarning();
m.wait(100);
}the code is not complete, but should show what is happening
it is continually outputting null for the warning, though the strored proc is definately raising errors, which is proven by running it in a query window in sql server.
it is retreiving warning if the statement is a raiseerror instead of a call to the proc
Code Block
CallableStatement s = con.prepareCall("RAISERROR ('Error', 1, 1)with nowait;");
this thread is quite related, but doesnt offer a working solution
Getting messages sent while JDBC Driver calls stored procedure
any help much appreciated,
thankyou
Simon
View 1 Replies
View Related
Mar 3, 2008
I need help debugging a CLR stored procedure that is being called from an SSIS package. I can debug the procedure itself from within Visual Studio by using "Step into stored procedure" from Server Explorer, but really need to debug it as it is being called from SSIS.
View 4 Replies
View Related
Jan 26, 2007
I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?
View 20 Replies
View Related
Jul 15, 2015
I seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.
Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005
USE YourDb;
SELECT qt.[text] AS [SP Name],
qs.last_execution_time,
qs.execution_count AS [Execution Count]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = DB_ID()
AND objectid = OBJECT_ID('YourProc')
The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?
View 3 Replies
View Related
Mar 3, 2008
I need help debugging a CLR stored procedure that is being called from an SSIS package. I can debug the procedure itself from within Visual Studio by using "Step into stored procedure" from Server Explorer, but really need to debug it as it is being called from SSIS.
View 3 Replies
View Related
Oct 5, 2006
Hi,
I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks
View 4 Replies
View Related
Oct 18, 2005
I have a report based on our product names that consists of two parts.Both insert data into a temporary table.1. A single grouped set of results based on all products2. Multiple tables based on individual product names.I am getting data by calling the same stored procedure multipletimes... for the single set of data I use "product like '%'"To get the data for individual products, I am using a cursor to parsethe product list.It's working great except that I have no idea how to identify theresults short of including a column with the product name. While thatis fine, I'm wondering if there is something that is like a header ortitle that I could insert prior to generating the data that would looka little tighter.Thanks in advance-DanielleJoin Bytes!
View 3 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
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
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
Aug 1, 2006
I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.
This is the stored procedure:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Lance Colton
-- Create date: 7/31/06
-- Description:Find the contest winner
-- =============================================
ALTER PROCEDURE [dbo].[AppcheckContest]
-- Add the parameters for the stored procedure here
@BeginDate datetime = '1/1/2006',
@EndDate datetime = '12/31/2006',
@SectionID int = 10,
@WinnerID int = 0 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT top 1 @WinnerID = P.UserID
FROM cs_Posts P
WHERE P.PostDate BETWEEN @BeginDate AND @EndDate
AND P.SectionID = @SectionID
AND P.UserID <> 2100 -- I don't want to win my own contest...
AND SettingsID = 1000 -- This number could be different if i had more than one CS installed?
AND IsApproved = 1
ORDER BY NEWID() -- yes this is slow, but it works...
RETURN @WinnerID
END
It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.
Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.
Here's the code from the aspx codebehind file:Partial Class Contest
Inherits System.Web.UI.Page
Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click
Dim stuff As New System.Web.UI.DataSourceSelectArguments
SqlDataSource1.Select(stuff)
SqlDataSource1.DataBind()
lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString
End Sub
End Class
As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @WinnerID parameter, but instead it sets it to "WinnerID".
Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server">
<asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" />
<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar>
<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa %>"
SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False">
<SelectParameters>
<asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" />
<asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" />
<asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" />
<asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="lblWinnerID" runat="server" Text="???"></asp:Label>
</asp:Content>
View 3 Replies
View Related
Aug 22, 2006
Hi,
I'm developing a website using vwd express and I have created a GridView that bounds data from a stored procedure. The stored procedure takes one parameter. I tested it by using a default value and it works fine.
Now, instead of the default value i want to pass the current logged in user name as a parameter.
How do i do this. All the info i found around are for passing parameters to the select command of sqldatasource but i cant get it to work when i use a stored procedure.
Thanks, M.
View 4 Replies
View Related
Oct 12, 2006
Everyone,I have an application that pulls a list of employees into a web form. I use the same stored procedure to search for employees by various demographic characteristics, and have set all of these search parameters to be optional in the stored procedure.Everything works fine when I pull the entire list (without declaring any select parameters). However, on my Search page, I have declared a number of SelectParameter (ControlParameters, to be specific) objects and mapped each one to its respective control (text boxes and a checkbox). When I attempt a search, the SqlDataSource does not select any records unless I specify an argument for ALL of the search parameters. If I omit even a single search value, the page just posts back and does not pull any information from the database. I have verified with SQL Profiler that it is not attempting to hit the database.I have experimented with various declarations in the ControlParameter object, including the DefaultValue and ConvertEmptyStringToNull items. If I specify a non-empty string as a DefaultValue for all of the parameters, it will perform the query, but it does not query if I use an empty string as a default value - unless my user specifies a value in the form. Any ideas?ThanksTim
View 1 Replies
View Related
