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">
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],
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)
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.
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).
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.
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?
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
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
}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
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.
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?
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?
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.
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
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!
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
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.
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.
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
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
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...
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
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
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
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)
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.
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?
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
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
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>
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.
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
