.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SqlDataSource, FormView, and Update with stored procedure in code behind

Posted By:      Posted Date: September 11, 2010    Points: 0   Category :ASP.Net
Hello, Using a FormView with a SqlDataSource, I'm attempting to Update data by calling a stored proc in code behind. I was having trouble getting parameters using Update Parameters in the SqlDataSource, but found a working solution by coding the parameters. The problem now is I'm getting an "Updating is not supported by data source 'XYZ' unless UpdateCommand is specified'. I saw some previous posts on the forums, but didn't find them very enlightening. 

View Complete Post

More Related Resource Links

Trying to run a stored procedure from vb code with oracle data provider.



Here is my SP:

create or replace
 Open p_getuserssignon_recordset1 for
 SELECT Distinct(Userid), UserPassword, SecurityLevel, ActiveStatus
WHERE substr(UserId,1,2) <> vUid
Order By UserId;

I would like to run this SP from code and fill a gridview with the result. 

I am not sure how to go about this, as I have found several different examples, other than the one I  think I need.

I am using the oracle data provider and I have an input parameter (vUid, which will equal "zz").

First question. When filling a gridview with a result set from a stored procedure should the recordset OUT be defined as a REFCURSOR (like i did above)? 


Do you have example code as to how to execute the SP and fill a gridview?  I keep trying different variations of code i've found on the internet without any success other than getting more confused.

(I am using VS 2005, VB).

Thank you.



Using the BDC API how to catch the returned code when executing a stored procedure?

using BDC API, how can i catch the return code a stored procedure returns?

I tried Entity.Execute to execute a generic method. However, it returns an empty Entity table.

I know you can do it by setting up an output variable. I prefer returning an integer for tracking errors.

Any ideas?



FormView EditItemTemplate DropDownList loaded from codebehind via stored procedure

I'm attempting to load DropDownLists from the C# codebehind as the FormView switches modes to Edit and displays the EditItemTemplate.  I present the data in a FormView ItemTemplate using text boxes.  When the user selects Edit, I have been attempting to load the EditItemTemplate dropdownlists by creating a dataset and binding it to the control, without success.  I've successfully loaded them in previous projects from within the .aspx code using <asp:ListItem> and within the C# code behind using Control.Items.Add(new ListItem), which are essentially the same thing of course .  This project is different in that I want to get the data from a stored procedure on a MSSQL server. In my latest attempt, I can see the data in the datasets.  I'm getting "Error connecting to database.'eitRace' has a SelectedValue which is invalid because it does not exist in the list of items.Parameter name: value" I include the code below.  If someone could point me to an example I would be greatful.  There has to be an elegant way of accomplishing this. Thank You, rlbaum *************************************************** <EditItemTemplate> <asp:Button ID="updatebutton" runat="server" CausesValidation="false" Text="Update&quo

Update Database Record using GridView and Stored Procedure

Hi, I am trying to update record via stored procedure, but i got error at very start point. Problem is when i click on Edit link button within the Gridview it produce error. I can populate values from database fine but its produce error when i click on edit link button. Please see the code below. *********** ASP.net GridView Code I am using *****************<asp:GridView ID="GridView1" runat="server" DataKeyNames="RollID, EmpID" AutoGenerateColumns="False" Width="700px"> <Columns> <asp:TemplateField HeaderText="ApplicationID" Visible="false"> <ItemTemplate> <asp:Label ID="AppsID" runat="server" Visible="false"><%# Eval("RollID") %>'</asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="ReleaseID" Visible="false"> <ItemTemplate> <asp:Label ID="rlsID" runat="server" Visible="false"><%# Eval("EmpID") %></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Roll Name"> <ItemTemplate> <asp:Label ID="rlsVersion" runat="server"><%# Eval("Roll_Name")%></asp:Label> </ItemTemplate> <EditI

Combine Common Code in Stored Procedure

I have the following stored procedure: ALTER PROCEDURE dbo.tc_TopicsGetTopics ( @StartRow int, @MaxRows int, @LocationID int, @GroupID int ) AS BEGIN SET NOCOUNT ON -- Return topics IF @GroupID <> 0 BEGIN WITH TopTemp AS (SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY TopCreated DESC) AS RowID, TopID, TopTitle, TopCreated FROM Topics WHERE TopGroupID = @GroupID) SELECT TopID, TopTitle, TopCreated FROM TopTemp WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows) ORDER BY RowID END ELSE BEGIN WITH TopTemp AS (SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY TopCreated DESC) AS RowID, TopID, TopTitle, TopCreated FROM Topics WHERE TopLocationID = @LocationID) SELECT TopID, TopTitle, TopCreated FROM TopTemp WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows) ORDER BY RowID END RETURN END Two questions: 1. Is there a simple way to move the two identical SELECT statements out of the IF statement so it would only need to appear once in the procedure? I assume I could create a temporary table. Is that the same as using WITH? 2. Does anyone see much reason to do what I'm asking? Would it affect storage or efficiency? Thanks!  Jonathan Wood • SoftCircuits • Developer Blog

update stored procedure for existing values of column(salperyr)

tbl_salary salary           salperyr   hike20      hike20yr10000.0000    NULL      12000.00    144000.0012000.0000    NULL      14400.00    172800.0014000.0000    NULL      16800.00    201600.0015000.0000    NULL      18000.00    216000.0018000.0000    NULL      21600.00    259200.0020000.0000    NULL      24000.00    288000.0022000.0000    NULL      26400.00    316800.00in above table salaryper yr (salperyr) has to be modified after caliculation my null values has to be removed and place (salary*12)in one shot.so plz suggest me update stored procedure for this.

update stored procedure for existing values of column(

tbl_salary salary            salperyr    hike20       hike20yr 10000.0000    NULL      12000.00    144000.00 12000.0000    NULL      14400.00    172800.00 14000.0000    NULL      16800.00    201600.00 15000.0000    NULL      18000.00    216000.00 18000.0000    NULL      21600.00    259200.00 20000.0000    NULL      24000.00    288000.00 22000.0000    NULL      26400.00    316800.00 in above table salaryper yr (salperyr ) has to be modified after caliculation my null values has to be removed and place (salary*12 )in one shot.   so plz suggest me update stored procedure for this

stored procedure: select and update

Is it possible to use a stored procedure to get a value of the database, change it and save it back at ones? Let me show a simple example: In the databse I have an integer value (20). Then I get another number (5), and I would like to subtract it of the  value from database (20 - 5). That means I have to get the value 20 out of the database, of it subtract value 5, and insert (update) new value (15) back to the database. Or is it not even necessary to get the value 20 out of the databse, but only directly subtract value 5 of the value in the database? Is this possible to do with stored procedure?

How can I catch an error from a SqlDataSource stored procedure


I have a stored procedure that creates a dynamic query.  Sometimes the query is not built because there are no elements in the dynamic query.  When the aspx page loads the SqlDataSource throws an error that the query is malformed.  How can I catch this error, and handle it with a default message, before it returns the error to the aspx page?

SQL Stored Procedure - if exists Update else Insert.



I'm trying to create a stored procedure that get a Key and Name (both varchar), the procedure will check if a the key allready exists in the table, if it exists it will update the name, if its not exists it will add it to the table..

I have no clue how to use IF to check if the row exists, please help.

Thanks :)

Update Parameters with SQLDataSource and VB code behind


I have been going around the houses for several days now. I have an sql datasource that uses a stored Proc  to uodate.  I set the updateParameters values from vbcode behind

        Me.SqlAdministration.UpdateParameters("Licence").DefaultValue = Me.TxtLicence.ToString
        Me.SqlAdministration.UpdateParameters("CompanyName").DefaultValue = CType(FormView1.FindControl("CompanyNameTextBox"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("Status").DefaultValue = CType(FormView1.FindControl("status"), DropDownList).SelectedValue.ToString
        Me.SqlAdministration.UpdateParameters("EmailAccounts").DefaultValue = CType(FormView1.FindControl("txtEmailAccounts"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("EmailGeneral").DefaultValue = CType(FormView1.FindControl("txtEmailGeneral"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("TelAccounts").DefaultValue = CType(FormView1.FindControl("txtTelAccounts"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("TelGeneral").DefaultValue = CType(FormView1.FindControl("txtTelGeneral"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("CompanyAddress").DefaultValue = CType(FormView1.Find

update two tables from single grid view using stored procedure PLZ help


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace INV_DN
    public partial class Form1 : Form
        SqlConnection con;
        SqlDataAdapter da1;
        SqlCommand cmd;
        //  SqlCommandBuilder cb;
        DataSet ds;
        public Form1()

        private void Form1_Load(object sender, EventArgs e)
            con = new SqlConnection("User Id=sa;Password=123;database=amar");
            cmd = new SqlCommand("getdata", con);

CRUD Stored Procedure Code/Scripts Generator for SQL SERVER 2005


I need a simple anf functionally CRUD Stored Procedure Code/Scripts Generator.

Anyone have a solution?

Thanks in Advance.

Fire Stored Procedure in Code Behind - nothing happening (vb.net)



I have the following code (vb.net) that I have on a button_click action:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim dbconn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("FrogConnectionString").ConnectionString)

Dim rsInsert As New SqlDataSource

rsInsert.SelectCommand = "Proposals_InsertNew"

rsInsert.SelectCommandType = SqlDataSourceCommandType.StoredProcedure


insert and update from a same stored procedure


in sql server 2005

hi i am creating  a stored procedure for a inserting and updating a table as follows

where id is identity.

my intention is when i supply the id value  then it performs update other wise it goes to insert

my table is as

CREATE TABLE [dbo].[TblAccountType](
    [id] [tinyint] IDENTITY(1,1) NOT NULL,
    [AccType] [varchar](20) NOT NULL,
    [Description] [varchar](max)  NULL)

and stored procedure is



create  proc [dbo].[SppInsertAccType]
(@id as tinyint =null,
@AccType as varchar(20),
@Description as varchar(max))
set nocount on
set transaction isolation level read uncommitted
begin tran
 if exists(select id from TblAccountType where id=@id)
update TblAccountType set AccType=@AccType,Description=@Description where id=@id
insert into TblAccountType(AccType,Description)values(@AccType,@Description)
commit tran

Passing an array from C# code-behind to SQL Server stored procedure



 Via the button1_ click event I retrieve all files in a directory (Example Follows)

    protected void Button1_Click(object sender, EventArgs e)
        DirectoryInfo di = new DirectoryInfo(@"C:\Test");

        foreach (FileInfo filename in di.GetFiles())

I now need to check whether this filename exists as a field in a SQL Server table.  For example, if the directory retrieves a file named ExampleFile.txt which does not exist in the (Table = TableFileStore;s Field = FileRetrievals) then I want to insert the filename. 

How do I construct a stored procedure to pass the filename(s) into a stored procedure as a parameter?  Do I need to also create an array of filenames and pass the array into my stored procedure?  Does the stored procedure belong within the foreach loop?    

How best to achieve the above goal?


SQL timestamp in GridView and manually added update parameter for stored procedure in C#



I have a SQL table containing a timestamp column.  This table is displayed in ASP using GridView control.

The timestamp column is indicated in the DataKeyNames property of the GridView.

I'm using TemplateFields for all columns and a ItemTemplate HiddenField control connected to the timestamp column using Eval.

<asp:TemplateField ItemStyle-Wrap

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend