Help! Getting values from SqlDataSource command parameters

 I'm trying to get the value from a sql command in a sqldatasource and assign it to a session variable for use on another form.  Here is the SQL insert command I'm using:


BEGIN TRANSACTION; INSERT INTO DEV.ENTITY (CompanyName, FirstName, MiddleName, LastName, EntityType) VALUES (@CompanyName, @FirstName, @MiddleName, @LastName, 'Owner'); SET @nextEntityId=(SELECT MAX(EntityId) FROM DEV.ENTITY); COMMIT;


Here is the code for the SqlDataSource called EntityDataSource:

 <asp:SqlDataSource ID="EntityDataSource" runat="server"
            ConnectionString="<%$ ConnectionStrings:TcoConnString %>"
            InsertCommand="BEGIN TRANSACTION; INSERT INTO DEV.ENTITY (CompanyName, FirstName, MiddleName, LastName, EntityType) VALUES (@CompanyName, @FirstName, @MiddleName, @LastName, 'Owner'); SET @nextEntityId=(SELECT MAX(EntityId) FROM DEV.ENTITY); COMMIT;"
            SelectCommand="SELECT CompanyName, FirstName, MiddleName, LastName FROM DEV.ENTITY;">
                <asp:Parameter Name="CompanyName" />
                <asp:Parameter Name="FirstName" />
                <asp:Parameter Name="MiddleName" />
                <asp:Parameter Name="LastName" />
                <asp:Parameter Name="nextEntityId" />


Do I need to set the Direction property of the parameter to extract the value (direction="Return Value" or direction="output")?  After that, how do I access the value in the code behind.  What I want to do is get the id of the entry that was just created, then assign it to a session variable for use on another form.  This is what I've been doing so far:


        for (int i = 0; i < EntityDataSource.InsertParameters.Count; i++)
            if (EntityDataSource.InsertParameters[i].Name == "nextEntityId")
                Parameter p = EntityDataSource.InsertParameters[i];


But once I have the parameter, p, there isn't even a value property for it.  So I tried using an SqlDataAdapter:


        SqlDataAdapter da = new SqlDataAdapter();
        SqlConnection dc = new SqlConnection();
        dc.ConnectionString = ConfigurationManager.ConnectionStrings["TcoConnString"].ConnectionString;
        SqlCommand cmd = new SqlCommand(EntityDataSource.InsertCommand, dc);
        da.InsertCommand = cmd;


The problem is that it doesn't recognize any of the parameters that have been declared in the sqldatasource.  I could just use the dataadapter and a dataset, but where do I declare it in my code so that it initiates when the page loads, but that I can get the value for the next id when the event is fired to insert the data into the DB?  Any advice or suggestions would be greatly appreciated.




12/12/2008 9:37:03 PM 29906 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 56

Looks like you can use the OnInserting and OnInserted events of the SqlDataSource to capture that data then add it to a session.


Robert Lindley | Sr. Software Engineer - MCP, MCAD
ASTRA Innovations, Ltd. - Better Solutions by Design.


I like points just as much as the next person. If I helped you out please help me out by marking my response as the ANSWER if or when it is.
12/13/2008 4:37:36 AM

Similar Artilces:

HELP! Need a SIMPLE way to update a SQL record, and GET a SQL value.
 I am new to, but I have to believe there is a simple way to write a function that will set a value in a sql database to a variable in the code (Im writing in c#) and also GET a value and set it to a variable.How can I do something as simple as String tempString = 'Fred'; UPDATE test1SET fname =temStringWHERE lname='Flintstone'; I also want to do somtheing equally as simple after that is done: Set a variable to the result of the update.For example String tempFName;tempFName = (SELECT fname FROM Users WHERE lname = 'Flintstone') I get t...

Can I use a Formula Field or Report Parameter in my SQL Command when using a Command as my datasource?
Instead of having the database tables as my datasource, I'm creating a command (double clicking the Add Command Node under my server connection in the database expert) and using that for the fields on my report. I need to be able to use a formula field (or even a report parameter) in that command SQL so that I can pass dates that will change at runtime to my SELECT statement. Is that possible? Yeah it is possible but you have to preset the filters in your report and then pass the information (i.e. dates) as parameters. To learn how to do this there is an article on

SqlDataSource: Getting parameter value from ViewState
Hello all, This may be a simple question, but it is causing me some grief at the moment.I put a SqlDataSource control on my form, and pointed it to the stored procedures I had written to insert/update/delete rows in my database.The DataSource control notices that I am using parameters in my queries, and asks me to select where the parameters will be assigned from (Control, Form, Session, etc.)I am keeping the primary key value (in this case an ID number for a real estate listing) persisted in ViewState.Is there any way to access ViewState from the SqlDataSource control, or do I need to ...

How do I get the parameter for the delete command when I get the parameter for the select in another way?
I'm working with VS 2008.  I've got a data grid control, which I populate through a DropDownList that is data bound using a stored procedure.  When the DDL changes it updates the contents in the DataView control.  The DataView control is also data bound, and is populated using its own stored procedure and the code returned from the DDL once the user selects something.  I've got another stored procedure assigned to the DeleteCommand of the SqlDataSource, which I intend to use is with a delete CommnadField of the DataView control.  However, in this case I w...

Query for substring to get some particular value in a string (Get querystring value by sql )
I have a column name URL in Table1 with data like  <a href="/Folder1/view_media_news.cfm?news_media_i=1">August 2002 Factsheet</a>            <a href="/Folder1/view_media_news.cfm?news_media_i=149">March 2002 Newsletter </a>  i need to grab the news_media_i value by sql query  Please any one can help me to get that particular value from string using substring or any other suggestion Thank you in advance SurendraIf this helps mark it as answer surendra_kla:i nee...

HOW DO YOU get return value from SQL Server command
I need a return value from a SQL Server 2005 command "EXECUTE AS USER = 'user' WITH COOKIE INTO @COOKIE". I'm using Powerbuilder 10 - DBMS=OLEDB PROVIDER='SQLOLEDB', IntegratedSecurity='SSPI' Does anyone have a suggestion to steer me in the right direction? TF You'll have to pardon my ignorance, I've never seen this command before. What return value are you expecting? Is it a success/failure code? How are you running the command? Brad tflooder wrote: > I need a return value from a SQL Server 2005 command > "EXEC...

Questions about data type of SQL command parameter values
I have a couple of questions about parameters in SQL commands. According to the documentation the data type of a parameter should match that of the database field with which it's associated. This implies that single and double precision floating point parameters will be in the binary format for single and double floating point numbers. However a TSQLDataset object is designed for client-server databases, where the client program and server program may reside on different computers having different operati ng systems. The SQLDataset command text and parameters presumably get sent from the ...

How to pass parameter value from one sql datasource to another?
Hi everybody, Here is the problem, how to pass the values from one datasource to another one on the same aspx page, without coding?  I have a page with FormView control to present data for one selected record from a search page, with edit item and insert item templates created. The main sql datasource keeps control on everything with select, update, delete and insert methods. For insert I am using existing stored procedure, for which the wizard generated all the required parameters. In Command and Parameter Editor I can see them, and I need to define all of th...

.NET Add Command
I added two Parameter Fields to my Report in the Field Explorer. I also can create a SQL Command using the Add Command option in the Database Expert window. However, I would like to have parameters in my Command Query... when I try to add parameters to my command query i get the error "Failed to open a rowset.  Details: 07001:[IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001" my query is: SELECT TL.PICK_UP_PUNIT,SUM(Case when Master_Order=0 then 1 ELSE 0 END) + COUNT(DISTINCT(MASTER_ORDER)) - (CASE when SUM(CASE when MASTER_ORDER=0 THEN 1 ELSE 0 END)...

Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL. For example: SELECT name FROM employee WHERE id = @id I would like to retreive from SQLDataSource SELECT name FROM employee WHERE id = 1   Thank youPeace in ChristMarco Napoli Hello, If my understanding is wright, try this <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWor...

How can I execute SQL command and get the record value?
I connected Sql Server 2000 with SqlDataSourceControl, I want to know how can I execute the SQL command such as movefirst, movenext movelast and so on. And how can I get the value of a record. eg: I have the table in database. No.   Name   Position 1      Tom      Manager 2      Peter      Administrator I create a variable "Name" and do as follow: Name = SqlDataSourceControl1.dataset("Name") But it's wrong, how can I do? Pls help me...

Get a dump of the sql command SqlDataSource sends to SqlServer
I'd like to get a dump of the actual string that gets sent to the Sql Server when an SqlDataSource Command is executed, like an update command for example.  (Note: the correct answer is not SqlDataSource.UpdateCommand)  Thanks!-Brandon Is this what you are looking for? protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { Label1.Text = SqlDataSource1.SelectCommand.ToString(); } Limno If u sql server installed in ur machine, u can use "sql profiler". It will give d details of stored prcedures and sql ...

Help on how to ensure that cells on datagrid get values when used sql?
 I am  developing web application using Csharp. I have a datagrid and I am doing some checks on datagrid.I have an sql that is under DataGrid_ItemDataBound the sql is below. The sql is using e.Item.Cells[1].Text to pick or get value from datagrid, BUT it is not getting the value. It is showing that it is blank.How can I ensure that e.Item.Cells[1].Text get the value? is there any other way of getting the values from datagrid? strfinal = "SELECT proName  FROM Schhoolwork WHERE name ='"+ e.Item.Cells[1].Text +"' AND curddate BETWEEN '" + seedate.T...

Need help getting a return value in SQL Query Analyzer
This has got to be a simple one, but I could not find out how I have this simple proc for test purposes only...   ALTER    PROCEDURE dbo.RotoTest( @strSSN VARCHAR(11), @blnUseACHDate BIT = 0,  @intInvestorId int = 0)ASreturn 5   Thats right it does nothing but return 5 and thats because I removed all the real code to simplify my question. When I hit this proc in SQL Query Analyzer.. exec RotoTest '123-45-6789', 0, 1     No return value is displayed in Query Analyzer. When I try something like this exec RotoTest ...

Web resources about - Help! Getting values from SqlDataSource command parameters -

Latest Updates
Latest Updates - Free source code and tutorials for Software developers and Architects.; Updated: 15 Feb 2013

Using The LINQDataSource
-*+There is a new datasource control available in Visual Studio that works in much the same way as the SQLDataSource and the ObjectDataSource. ...

M ore d otnet .NET Interview Questions, ASP.NET, C#, VB.NET, XML, SQL Check Out √ .NET NOTES OOPS in .NET .NET ...

Architectural Overview of the Windows Presentation Foundation Beta 1 Release
Introduction Windows Presentation Foundation Beta 1 Windows Integration New Features WinFX SDK Exploring the New Features Introducing "Express" ...

About Mikesdotnetting
I started this site as somewhere to store answers to bits and pieces that I see asked frequently in the forums at . Rather than type ...

Using MiniProfiler with SqlDataSource ASP.NET WebForms Control
public class ProfiledSqlDataSource : SqlDataSource{ protected override DbProviderFactory GetDbProviderFactory() { // get the "base" DbProviderFactory ...

ASP.NET Pivot Table - RadPivotGrid for ASP.NET AJAX
... reports. RadPivotGrid allows easy customization of all data reports. Try now! Use any declarative data source control (AccessDataSource, SqlDataSource, ...

Sign on
Sign On • Join • Forums Home User Name * Password * Remember me next time ( I forgot my password ) SUBMIT YOUR WTF Content Random Article All ...

ComboBox Sample
ComboBox is an ASP.NET AJAX control that, like the AutoCompleteExtender , combines the flexibility of a TextBox with a list of options that users ...

Latest Updates
Latest Updates - Free source code and tutorials for Software developers and Architects.; Updated: 15 Feb 2013

Resources last updated: 11/27/2015 6:35:44 AM