Retrieving Values From Stored Procedure Using FormView && SqlDataSource

Mar 3, 2008

Please excuse me if this question has been asked before; I couldn’t find it.  Perhaps someone could point me to the solution.

A few years ago I wrote an order-entry application in classic ASP that I am now re-writing in ASP.NET 2.0.  I am having very good success however I can’t figure out how to retrieve data from a stored procedure.

I am using the FormView & SqlDataSource controls with a stored procedure to insert items into an order.  Every time an item is inserted into the order the stored procedure does all kinds of business logic to figure out if the order qualifies for pricing promotions, free shipping, etc.  If something happens that the user needs to know about the stored procedure returns a message with this line (last line in the SP)

 SELECT @MessageCode AS MessageCode, @MessageText AS MessageText 

I need to retrieve both the @MessageCode and the @MessageText values in my application.  In classic ASP I retrieved these values by executing the SP with a MyRecordset.Open() and accessing them as normal fields in the recordset.

In ASP.NET I have specified the SP as the InsertCommand for the SqlDataSource control.  I have supplied all the parameters required by my SP and told the SqlDataSource that the insert command is a “StoredProcedureâ€?.  Everything actuly works just fine.  The items are added to the order and the business logic in the SP works fine.  I just have no way of telling the user that something important has happened.

Does anyone know how I can pickup these values so that I can show them to my users?  Bassicly if @MessageCode <> 0 I want to show @MessageText on the screen.

View 10 Replies


ADVERTISEMENT

Retrieving Default Values For Parameters In A Stored Procedure.

Jul 23, 2005

I'm generating a list of parameters needed by stored procedures, and
I'd like to know which ones have default values assigned to them.
To retrieve the parameter information I use:


sp_sproc_columns @Procedure_Name='InsertUser''


However, the column that is supposed to give the default value,
'COLUMN_DEF' always returns as NULL, even when that column has a
default value assigned to it.
i.e.
CREATE PROCEDURE InsertUser@UserID INT = 10,.....


And then if I do a sp_sproc_columns @Procedure_Name='InsertUser'', the COLUMN_DEF value for the @UserID column is still NULL.

Does anyone know what I'm doing wrong and how I can retrieve the default value?

Thanks

View 1 Replies View Related

Retrieving Selected Gridview Column Values For SQLDatasource Asp:controlparameters

Jun 12, 2006

Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure.  The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples.  It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error".  Maybe the DataKeyNames is not right??  I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same.  It's got to be something very simple, but I'm at a loss.  All parameters are spelled the same in the sp (with an added "@" at start) as in the asp:controlparameters.  Here's the gridview (asp.net 2.0 connecting to SQL Server 2005):
<asp:GridView ID="gvLoadEditProductPrices" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="ProductID">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID" HeaderStyle-BackColor="white" InsertVisible="False"
ReadOnly="True" SortExpression="ProductID" />
<asp:BoundField DataField="Product" HeaderText="Product" SortExpression="Product" ReadOnly="True" />
<asp:BoundField DataField="ProductCat" HeaderText="ProductCat" SortExpression="ProductCat" ReadOnly="True" />
<asp:BoundField DataField="VarRate" HeaderText="VarRate" SortExpression="VarRate" />
<asp:BoundField DataField="loadid" HeaderText="loadid" InsertVisible="False" ReadOnly="True"
SortExpression="loadid" />
<asp:BoundField DataField="loadamount" HeaderText="loadamount" SortExpression="loadamount" ReadOnly="True" />
<asp:BoundField DataField="ProductCol" HeaderText="ProductCol" SortExpression="ProductCol" ReadOnly="True" />
<asp:BoundField DataField="PageID" HeaderText="PageID" SortExpression="PageID" ReadOnly="True" />
</Columns>
</asp:GridView>
and the sqldatasource's info:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>" ProviderName="System.Data.SqlClient" SelectCommand="spGetLoadEditProductPrices" SelectCommandType="StoredProcedure" UpdateCommand="spUpdateProductPrices" UpdateCommandType="StoredProcedure" >
<UpdateParameters>
<asp:ControlParameter Name="ProductID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter>
<asp:ControlParameter Name="LoadID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter>
<asp:ControlParameter Name="PageID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter>
<asp:ControlParameter Name="ProductCol" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter>
<asp:ControlParameter Name="NewRate" Type="Double" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter>
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="ddlEstLoadsPerAcre" Name="LoadID" PropertyName="SelectedValue"
Type="Int32" />
<asp:ControlParameter ControlID="txtEditType" Name="PageName" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
 
TIA,
John

View 1 Replies View Related

How To Pass The Value Of A Text Box Inside A FORMVIEW To A SQL Stored Procedure

May 28, 2006

Hi,
I'm new to ASP.NET 2.0. I have a sqldatasource and a formview controls on a web page and inside the formview control I have  two textboxes. When I click the UPDATE button in the formview, I'd like to be able to retrieve the values of the textboxes inside the formview control and pass these values to a 'Update' SQL stored procedure defined in the Sqldatasource.
Does anyone know how I can do this? Hope my question is clear.
Thanks in advance.
hakl

View 1 Replies View Related

Help Retrieving Values From 2 Similar Stored Procs

May 1, 2006

I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second.  Now I want to obtain the results from both like this:

ProjFee   ProjGross  DailyRunRate  Var1  InHouse1  InHouse2  GrossGoal  Group Name  PostedAmount
---------------------------------------------------------------------------------------------------------------------------
Row# 1 from current stored proc
Row # 2 from called stored proc

so something like this I should get in the end for example when both results are combined:

ProjFee   ProjGross  DailyRunRate  Var1  InHouse1  InHouse2  GrossGoal  Group Name  PostedAmount
---------------------------------------------------------------------------------------------------------------------------
100000   33455        200                 300    345555      4455555    5666666     Arizona           56000
103400   22455        900                 700    777555      3333555    5444666     Illinois            660000


The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.

I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.

What is the best approach to get the desired 2 rows back that I need in that fasion?

Here is the code for the 2 stored procs (very long so I will post as as links):

http://www.webfound.net/CurrentMonthCollections_AZ.txt
http://www.webfound.net/CurrentMonthCollections_IL.txt

Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee   ProjGross   DailyRunRate   Var1   InHouse1   InHouse2   GrossGoal   Var1   PostedAmount
) from both stored procs.

I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows

View 7 Replies View Related

Retrieving Scalar Or Calculated Values From Stored Procedures With C#

Feb 4, 2007

I am trying to build an Sql page hit provider.  I am having trouble getting a count back from the database.  If I use ExecuteScalar it doesn't see any value in the returned R1C1.  If I use ExecuteNonQuery with a @ReturnValue, the return value parameter value is always zero.  Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#.  My table has rvPathName, userName and a date.  I have the AddWebPageHit method working so I know data connection and sql support code in provider is working.  I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#.  Any help with this will be greatly appreciated.

View 5 Replies View Related

Sqldatasource And Formview Insert Problem

Aug 10, 2006

I m trying to INSERT a record to my Consultants table using sqldatasource and formview:
here is my sqldatasource code:
<asp:SqlDataSource ID="sqlDS1" runat="server" ConnectionString="<%$ ConnectionStrings:myDB %>"InsertCommand="INSERT INTO [Consultants] ([firstName],[lastName],[skillCategoryID],[resourceManagerID],[AMgroupID],[skillSet],[statusID],[location],[comments],[profile],[isAvailable],[dateModified],[focusID])
VALUES (@FNAME, @LASTNAME, 1, @RESOURCEMANAGERID, @AMGROUPID, @SKILLSET, @STATUSID, @LOCATION,@COMMENTS,@PROFILE,@ISAVAILABLE,getdate(),1)">

<InsertParameters>      <asp:FormParameter FormField="txtFName" Name="FNAME" />     <%--other parameters--%></InsertParameters></asp:SqlDataSource>
now in my formview i have this:<asp:FormView DefaultMode="Insert" ID="FormView1" runat="server" DataSourceID="sqlDS1" DataKeyNames="id">
<table border="0" cellspacing="5" cellpadding="0"><tr>  <td class="blacktextbold">First Name:</td>  <td class="blacktext"><asp:TextBox ID="txtFName" CssClass="txtfield" Width="200" runat="server" Text="<% #Bind('firstName')%>" /> <asp:RequiredFieldValidator ControlToValidate="txtFName" runat="server" ValidationGroup="gpInsert" ErrorMessage="Required Field" Display="Dynamic" CssClass="redtextsmallbold" /> </td></tr> </table>
THE PROBLEM:no matter what i put the txtFName field its ALWAYS null and i get INSERT error as column firstName cant accept NULLany ideas? ur help would be appreciated
 

View 4 Replies View Related

FormView W/SqlDataSource - Auto Disposed?

Mar 17, 2007

Hi everyone,  I'm in a scenario where I am using a FormView control to edit records.  The data access is being done with an associated SqlDataSource control.
The pipeline I use is usually setup using this pattern:
(1) SqlDataSource.OnInserting -> (2) SqlDataSource.OnInserted -> (3) FormView.OnItemInserted
The SqlDataSource events expose a SqlDataSourceCommandEventArg parameter that is much like a DbCommand object where you can start a transaction and associate other command objects with the command flowing through the pipeline.
 My question is does asp.net automatically dispose of the SqlDataSourceCommandEventArg command or must I do it manually?  I assumed that after the SqlDataSource.OnInserted  event the object would be disposed.  If anyone has articles on this or any insight would be appreciated.
Thanks

View 3 Replies View Related

Retrieving Values From Print Statemens In Stored Proc In Sql Into Asp.net Page

Sep 3, 2007

 Hi allI hv made a stored procedure which printsvarious messages using Print statement(shown in bold)------------------------------------------------------------------------------ .....if (@current_date<@ed) and (@current_date>@sd)    begin    print 'Date Lies Between Boundary Limits'    select * from membership where uid=@uid    end    else    begin    if(@pipe=1)            begin                if(@plan_id=1)                begin                 print 'Monthly Plan Activated'                update membership set   start_date=@opt_sd,end_date=DateAdd(M,1,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=20 where uid=@uid                select * from membership where uid=@uid                end                else                    begin                    print 'Weekly Plan Activated'                    update membership set start_date=@opt_sd,end_date=DateAdd(D,7,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=10 where uid=@uid                    select * from membership where uid=@uid                    end            end    end --------------Now I want to retrieve the messages disp by these Print statements in my asp.net page where i m calling this stored proc.Pls suggest RegardsMunish 

View 2 Replies View Related

Sqldatasource, Does It Select If Not Bound To A Gridview, Formview On The Page?

Feb 22, 2008

Using 3.5
If I have a sqldatasource on the page, is it run if it is not bound to a data object like a gridview?
Seems like if i want to access the data (like set a label text) from the sqldatasource I have to use code to first create a dataview then pick throught it. This seems like I'm running it twice. I'm new at .net so I dont know how to tell.
I don't want to write data select code programatically when I can just through an SDS on the page, but wondered it it ran just because it's on the page.

View 2 Replies View Related

SqlDataSource With Different Number Of Columns And Insert Parameters And FormView

Dec 7, 2005

Dear All,
i have a SqlDataSource with a simple select command(e.g. "select a,b,c from foo"). The insert command is a stored procedure and takes less parameters than there are columns in select statement (e.g. "insertFoo(a char(10))").
When used in combination with form view, i get "Procedure or function insertFoo has too many arguments specified" error.
It seems that form view always posts all columns as parameter collection (breakpoint in formview_inserting event shows this) to insert command.
Am I doing something wrong or is this by design? Is the only solution to manualy tweak parameters in formview_inserting event?
TIA
   Jernej

View 2 Replies View Related

Retrieving Data Using A Stored Procedure

Mar 31, 2007

I have created the following stored procedure and tried to retrieve it's output value in C#, however I am getting exceptions. Can anyone tell me what I am doing wrong? Thanks!  1 ALTER PROCEDURE [dbo].[GetCustomerById]
2
3 @CustId NCHAR(5),
4 @CustomerName NVARCHAR(50) OUTPUT
5
6 AS
7 BEGIN
8
9 SELECT @CustomerName = ContactName
10 FROM Customers
11 WHERE CustomerId = @CustId
12
13 END
14
15 RETURN
16
17
18
19
20
21
22 SqlConnection conn = GetConnection(); //retrieves a new SqlConnection
23 SqlCommand cmd = new SqlCommand();
24 cmd.Connection = conn;
25 cmd.CommandType = CommandType.StoredProcedure;
26 cmd.CommandText = "GetCustomerById";
27
28 SqlParameter paramCustId = new SqlParameter();
29 paramCustId.ParameterName = "@CustId";
30 paramCustId.SqlDbType = SqlDbType.NChar;
31 paramCustId.Direction = ParameterDirection.Input;
32 paramCustId.Value = "ALFKI";
33
34 SqlParameter paramCustomerName = new SqlParameter();
35 paramCustomerName.ParameterName = "@CustomerName";
36 paramCustomerName.SqlDbType = SqlDbType.NVarChar;
37 paramCustomerName.Direction = ParameterDirection.Output;
38
39 cmd.Parameters.Add(paramReturn);
40 cmd.Parameters.Add(paramCustId);
41 cmd.Parameters.Add(paramCustomerName);
42
43 conn.Open();
44 SqlDataReader reader = cmd.ExecuteReader();
45
46 string custName = cmd.Parameters["@CustomerName"].Value.ToString();
  

View 6 Replies View Related

Retrieving Data From Stored Procedure

May 25, 2008

Hi,

For a particular application i retrieve records using stored procedure and bind the same to the data grid and display the same. The data are fetched from a table say A joined with couple of tables to staisfy the requirement. Now this table A has around 3700000 + records. The Select statement is as

Select column1, column 2, column 3...................... column 25
from table A

inner join hash table D
on A.Column3 = D.Column3
and A.Column4 = D.Column4
and a.nColumn1 = @ParameterVariable
Inner join table B
on a.nColumn1 =b.nColumn1
inner join table C
on A.column2 = C.column2


Indices defined on A are
IDX1 : Column 3, Column 4
IDX2 : Column 1

Around 350 records per second are inserted into the table A during the peak operation time. During this time when executing the procedure, it takes around 4 mins to fetch just 25000 records.

I need to fine tune the procedure. Please suggest me on same.

Thanks


View 4 Replies View Related

Retrieving Return Value From Stored Procedure Declaratively

Nov 1, 2007

Hi.
I have a stored procedure "sp1" which returns a value (with the sql statement Return @ReturnValue).
Is it possible for my asp.net page to retrieve this return value, and to do it declaratively (meaning without writing code to connect to the database in the code behind). If it is possible to do it like this please tell me how, and if not please tell me how to do it with code.
Thanks in advance .

View 5 Replies View Related

Getting Error When Retrieving Data From Stored Procedure

Jan 9, 2008

Hi,
I'm at my wit's end with this. I have a simple stored procedure:
PROCEDURE [dbo].[PayWeb_getUserProfile] ( @user_id_str varchar(36), @f_name varchar(50) OUTPUT)AS
BEGINDECLARE @user_id uniqueidentifier;
SELECT @user_id = CONVERT(uniqueidentifier, @user_id_str);
select @f_name=f_name FROM dbo.tbl_user_profile WHERE user_id = @user_id;
ENDRETURN
The proc runs perfectly from the Management Console. In my ASP.net form (C#), the following always results in: "Invalid attempt to read when no data is present." (I'll highlight the line where the error results):
SqlConnection conn = new SqlConnection(<connection string data>);SqlDataReader reader;
SqlCommand comm = new SqlCommand("dbo.PayWeb_getUserProfile", conn);comm.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter f_name = new SqlParameter("@f_name", SqlDbType.VarChar, 50);
f_name.Direction = ParameterDirection.Output;comm.Parameters.Add(f_name);
comm.Parameters.Add("@user_id_str", SqlDbType.VarChar, 36).Value = Membership.GetUser().ProviderUserKey.ToString().ToUpper();reader = comm.ExecuteReader();Response.Write("Num: " + reader["@f_name"].ToString()); // ERROR: Invalid attempt to read when no data is present.
reader.Close();conn.Close();
The data is there. When I put a watch on 'Reader', I see the correct first_name data there.
Your help is appreciated.

View 8 Replies View Related

Retrieving Output Parameter From A Stored Procedure

Feb 28, 2008

Lets say I have the following stored procedure

Code:

View 2 Replies View Related

Stored Procedure Retrieving Duplicate Data

Apr 17, 2008

Hi i have the following stored procedure which should retrieve data, the problem is that when the user enters a name into the textbox and chooses an option from the dropdownlist it brings back duplicate data and data which should be appearing because the user has entered the exact name they are looking for into the textbox. For instance
Pmillio Jones
Pmillio Jones
Pmillio Jones
Robert Walsh
Here is my stored procedure;
ALTER PROCEDURE [dbo].[stream_UserFind]
-- Add the parameters for the stored procedure here
@userName varchar(100),
@subCategoryID INT,@regionID INT
AS
SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategoriesON
Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHEREuserName LIKE COALESCE(@userName, userName) OR
SubCategories.subCategoryID = COALESCE(@subCategoryID,SubCategories.subCategoryID);

View 11 Replies View Related

Retrieving Parameter List For A Stored Procedure

Jul 23, 2004

Is there a way to retrieve the parameter list for a given stored procedure?

I am trying to create a program that will autogenerate a list of stored procedures and their parameters so that changes to the database can be accurately reflected in code.

Thanks,
Allen K.

View 1 Replies View Related

Retrieving Server Time Using Stored Procedure

Jul 21, 2000

Hello all,

We have a domain where all computers are on GMT(Greenwitch Mean Time). We have an access front end that timestamps certain fields according to the client time that the program is running on, but we will be moving our client workstations off of GMT time and keep our SQL Server on GMT time, and want to keep the timestamps GMT.

So, I wanted to know if it was possible to create a stored procedure that gets the Server's time and returns it to the Access frontend for entry into the timestamp fields?

Or, if anyone has a better idea of how to get the time from the server to use on the clients, I would greatly appreciate it!!!

Kevin Kraus

View 2 Replies View Related

Problem With Stored Procedure And Retrieving Inserted Identity Value

Jul 11, 2006

Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne

View 3 Replies View Related

SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.

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">&quot;@CatId&quot;</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;

gdvCategories.DataBind(); &lt;&lt;--- Error occured here


 

View 1 Replies View Related

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.

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

Problem With Storing Datetime Values In Formview

Jan 20, 2007

Hello all,I'm kind of new to ASP.NET and I've hit my first unsolvable roadblock. I'm trying to create a formview insertcommand that allows me to place the current date into a DateTime field but I continuously get  an Input string was not in a correct format error. I may be using the wrong functions, I'm unsure. I've messed with DateTime.Now.ToString() as well as GETDATE() but I have no idea how to get it to work. I've also tried to switch the variable type of the field to a String type as well as DateTime. I am running visual basic .net with Microsoft SQL Server 2005. Here is my code right now:   Experiencing problems with technology? Just fill out the form below and your problem will be forwarded to the Information Technology department.</em></strong><br />        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TechticketdbConnectionString %>"                            SelectCommand="SELECT [CRDL#] AS column1, [Employee ID] AS Employee_ID, [Problem description] AS Problem_description, [Date submitted] AS Date_submitted, [Ticket number] AS Ticket_number FROM [Tickets]" DeleteCommand="DELETE FROM [Tickets] WHERE [Ticket number] = @Ticket_number" InsertCommand="INSERT INTO [Tickets] ([CRDL#], [Employee ID], [Problem description], [Date submitted]) VALUES (@column1, @Employee_ID, @Problem_description, GETDATE() )" UpdateCommand="UPDATE [Tickets] SET [CRDL#] = @column1, [Employee ID] = @Employee_ID, [Problem description] = @Problem_description, [Date submitted] = @Date_submitted WHERE [Ticket number] = @Ticket_number">            <DeleteParameters>                <asp:Parameter Name="Ticket_number" Type="Int32" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="column1" Type="Int32" />                <asp:Parameter Name="Employee_ID" Type="Int32" />                <asp:Parameter Name="Problem_description" Type="String" />                <asp:Parameter Name="Date_submitted" Type="String" />                <asp:Parameter Name="Ticket_number" Type="Int32" />            </UpdateParameters>            <InsertParameters>                <asp:Parameter Name="column1" Type="Int32" />                <asp:Parameter Name="Employee_ID" Type="Int32" />                <asp:Parameter Name="Problem_description" Type="String" />                <asp:Parameter Name="Date_submitted" Type="String" />            </InsertParameters>        </asp:SqlDataSource>    I've left out the EditItemTemplate because it isn't going to be used for this form. Here is the InsertItemTemplate:     <InsertItemTemplate>                column1:                <asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>'>                </asp:TextBox><br />                Employee_ID:                <asp:TextBox ID="Employee_IDTextBox" runat="server" Text='<%# Bind("Employee_ID") %>'>                </asp:TextBox><br />                Problem_description:                <asp:TextBox ID="Problem_descriptionTextBox" runat="server" Text='<%# Bind("Problem_description") %>'>                </asp:TextBox><br />                                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"                    Text="Insert">                </asp:LinkButton>                <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"                    Text="Cancel">                </asp:LinkButton>            </InsertItemTemplate>            <ItemTemplate>                column1:                <asp:Label ID="column1Label" runat="server" Text='<%# Bind("column1") %>'></asp:Label><br />                Employee_ID:                <asp:Label ID="Employee_IDLabel" runat="server" Text='<%# Bind("Employee_ID") %>'>                </asp:Label><br />                Problem_description:                <asp:Label ID="Problem_descriptionLabel" runat="server" Text='<%# Bind("Problem_description") %>'>                </asp:Label><br />                Date_submitted:                <asp:Label ID="Date_submittedLabel" runat="server" Text='<%# Bind("Date_submitted") %>'>                </asp:Label><br />                Ticket_number:                <asp:Label ID="Ticket_numberLabel" runat="server" Text='<%# Eval("Ticket_number") %>'>                </asp:Label><br />                <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"                    Text="Edit">                </asp:LinkButton>                <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"                    Text="Delete">                </asp:LinkButton>                <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"                    Text="New">                </asp:LinkButton>            </ItemTemplate>        </asp:FormView> I would like to get the value formatted as a DateTime but a String will do just fine if a DateTime variable won't work.     Any help would be greatly appreciated. Thanks! 

View 2 Replies View Related

Sqldatasource And Stored Procedure

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

SqlDataSource With Stored Procedure

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

Using Stored Procedure W/SqlDataSource

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

SQLdatasource With Stored Procedure

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

SqlDataSource - Stored Procedure

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

Can I Use A Stored Procedure For INSERT In SqlDataSource?

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

Using Qualified Stored Procedure Name With SqlDataSource

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

Sqldatasource And Stored Procedure Prob

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" />&nbsp;
</td>
</tr>
</table>
&nbsp;<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

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="&lt;%$ 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

SQLDataSource, Stored Procedure, DetailsView

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







Copyrights 2005-15 www.BigResource.com, All rights reserved