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

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

ExecuteNonQuery trimming return parameter value

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :ASP.Net
Hi all,I am observing a rather strange behavior. I have a procedure which selects a field from a matching record and returns it. The procedure works as expected returning proper values, when I execute the procedure from Visual Studio Server Explorer tab. The procedure isALTER PROCEDURE dbo.p_UpdNinjaName ( @ninja_name nvarchar(50), @user_id int, @ReturnMessage nvarchar(250) output ) AS /* SET NOCOUNT ON */ select ninja_name from users where ninja_name =@ninja_name if (@@ROWCOUNT> 0) begin SET @ReturnMessage = 'EAE'; /*Error : Already Exists*/ end else begin update users set ninja_name = @ninja_name where user_id = @user_id set @ReturnMessage = (Select ninja_name from users where user_id = @user_id) endThis is being called from the DAL as follows:SqlCommand cmd = new SqlCommand(storedProc, this._connection, this._transaction); cmd.CommandType = CommandType.StoredProcedure; DBParameterMap.Enumerator mapEnum = dbParameterMap.GetEnumerator(); while (mapEnum.MoveNext()) cmd.Parameters.AddWithValue(mapEnum.Current.Key, mapEnum.Current.Value); mapEnum = outputParameters.GetEnumerator(); while (mapEnum.MoveNext()) cmd.Parameters.AddWithValue(mapEnum.Current.Key, mapEnum.Current.Value); foreach (st

View Complete Post

More Related Resource Links

How many out parameter we can return by Store procedure?


How many out parameter we can return by Store procedure?

Waiting for reply.

BDC: Cannot create default instance on a Parameter with DircetionType 'Out', 'Return' or 'SqlRe



I created a ADF with the Application Definition Designer. Some things are allready working, some don't. One thing I want to achieve is the filtering on wildcard filters. I defined a method for a "Customers" table and added two "in"-parameters for "Name" and "FirstName". I also added an instance of type "Finder". Now I want to receive some data from this query with help of the "Execute" feature of the Application Definition Designer. But this return only an error "Cannot create default instance on a Parameter with DircetionType 'Out', 'Return' or 'SqlReturn'". What am I doing wrong?

Thanks for any help!



Oracle Store Procedure does not return any thing in the out parameter


I am goig to execute an oracle store procedure using C# through through ODP.NET. The store procedure executes succsfully but it does not return any value in the out parameter.

When I execute the same store procedure with the same input parameteres from within SQL*Plus. It does return values.

This is to be clearify here that in the oracle store procedure the data type for the out parameter is number and in the c# the variavle that will hold its value is Integer. I also tried using Long on the C# side but it is still not working. After the successfull execution of the store procedure the C# variable contains nulll value.  

Service Reference: return type converted to a "ref" parameter



I created a WCF service using Message Contracts. One of the operations in the service takes in an entity (embedded in a message contract) and returns an updated version of the entity:

EWADemo.MessageContracts.CustomersService.SaveCustomersResponseMessage SaveCustomers(EWADemo.MessageContracts.CustomersService.SaveCustomersRequestMessage request);

[MessageContract(WrapperName = "SaveCustomers")]
public partial class SaveCustomersRequestMessage
 public EWADemo.DataContracts.Customers Customers

[MessageContract(WrapperName = "SaveCustomersResponse")]
public partial class SaveCustomersResponseMessage
 public EWADemo.DataContracts.Customers Customers

When I tried creating a s

getting return value from executenonquery



I am using executeNonQuery and passing in a complete stored procedure in my c# code...now i wanted that if the stored procedure fails then it should give me a return value back saying that it failed...how can i do this..i am using sql server 2008 r2.

Let me know if i have to give some more explanation.



SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Enterprize library 4.1 getting output parameter after adding record in db, plz guide



I need to get output parameter (flag) from db after saving record in database. I am using Microsoft Enterprise Library 4.1 for DB. I am inserting record using I Data reader.

Please guide


ListView DataPager with SQL-Statements that already return only paged data.



As far as I understand the ListView-Control and the DataPager-Control, they work like this: The ListView gets its datarows from its data source, then the DataPager causes the ListView to display only a subset of those rows.

So what, when I want to use custom databinding for the ListView, that *already* gets the certain subset from SQL-Server (for better performance). Can I and how can I use the DataPager then?

Thanx, S.

Use a column as parameter for a sds in another column; gridview


Hola, I have a gridview which is bound to a sds which returns 7 columns and I added two columns from the GUI.  What I want is to fill up the 9th column with DDLs and these  DDLS will get their data from a different sds which is using a stored procedure with a single parameter.  I created the 9th row, used template field, made the sds for 9th row, the stored_procedure, threw a DDL in Item template and all  I need now is to pass a parameter in the first column to the sds in the 9th column.  I can code this but i'm getting tired of aspx.cs :) and would like to do it from the GUI.

Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />

Need Syntax To Make Results of LINQ Union Return Non-Generic Type



I have the below SQL which works just fine:

SELECT     Message, CreateDate, AccountId, AlertTypeId
FROM       dbo.Alerts
SELECT     TOP (100) PERCENT Status, CreateDate, AccountId,
                          (SELECT     10 AS Expr1) AS AlertTypeId
FROM         dbo.StatusUpdates
WHERE AccountId = PassedInParameter

I have been going about trying to convert the above into a LINQ method and am having problems with return types.
As the above SQL illustrates, I am querying two distinct LINQ tables - Alerts and StatusUpdates -
and then attempting to perform a Union on them. In order to do the union in LINQ, I have to have agreeing types. So, I
am using "var" to make the results of "alerts" and "updates" generic before the union.  

public IList GetStatusUpdatesAsAlertsByAccountId<T>(Int32 accountId)
            using (WorkbookDataContext dc = _conn.GetCont

type object parameter



I have a problem I can't figure out.

I've been trying to add a listbox with multi select enabled as a parameter to my select statement in sqldatasource.

So i created a function like this :

string strItemTypes = "";
foreach (ListItem li in lbItemType.Items)
   if (li.Selected)
      strItemTypes += "," + li.Value;
strItemTypes = strItemTypes.Substring(1);

which gives me the selected items like this for example : "1,2,3"...
Then I've been trying to add it to my sql select statement :

SELECT tblOfficesItems.idOfficesItems, tblStockItems.Name, tblStockItems.Description, tblStockItemTypes.TypeItem, tblStockItems.Notes FROM tblOfficesItems INNER JOIN tblStockItems ON tblOfficesItems.fkItem = tblStockItems.idItem INNER JOIN tblStockItemTypes ON tblStockItems.fkTypeItem = tblStockItemTypes.idTypeItem WHERE tblStockItems.fkTypeItem IN ( @fkTypeItems )

using a hiddenfield with a controlparameter of type=object
but it just don't seem to work, seems like the data just won't bind or something and I don't get any errors so I'm confused!!

then i tried this :

SELECT tblOfficesItems.idOfficesItems, tblStockItems.Name

XMLHTTP.ResponseText return unexecuted code?


I have a simple asp with the following code.  When I access this page directly (via browser) everything works fine:  the xml object shows up fine.  When I call this page via VBA (excel), the ResponseText method of the 

XMLHTTP object returns the below code unexecuted.  Anybody knows what's going on?  thanks.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <% Response.Buffer = True Response.ContentType = "text/xml" Dim oDataXML As Object 'Custom Data XML passed in by caller. On Error Resume Next 'Load the XML passed into the request. oDataXML = Server.CreateObject("Microsoft.XMLDOM") oDataXML.Load(Request) Dim sResult As String sResult = "<?xml version=""1.0""?>" sResult = sResult & "<OrderProcessed>" sResult = sResult & "<Status>test</Status>" sResult = sResult & "<OrderID>111</OrderID>" sResult = sResult & "</OrderProcessed>" Response.Write(sResult) Response.End() %>

Posting data to ASMX and return Object for jQuery


I am using fluent NHibernate with WebForms and I am trying to build a page where I allow a user to post a status update on their profile page and I a using a .asmx WebService to post the data to the database and then returnig a StatusUpdate instance to the page to be used by jQuery. I have a couple problems.

1) First off when I return a string from the WebService(i was testing) the textbox where the user enters their status doesnt empty the contents. And since the page doesnt refresh even if I manally clear out the textbox and put in something else, it still posts the previous status to the database again. How do I fix this?

2) Secondly, when I return the StatusUpdate Object from the Webservice I cant the results to display anything. Like I sad, Im using jQuery to make an AJX call to the WebService.

Here is my code:

User Profile Page:

Status: <asp:Label ID="status" runat="server"></asp:Label><span style="font-size:.7em;"><asp:Label ID="statustime" runat="server"></asp:Label></span><br />
<textarea id="statusBox" rows="3" cols="40"></textarea><br />
<input type="button" value="Update Status" onclick="SetStatus(); return false;

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Get ServerReport selected Parameter value to use in vb query


I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.

Crystal Report parameter value window.


 Hi friends,

   I am using Crystal report which displays the result of stroed procedure which have 2 paramater.

  When i run report it shows two window for getting each parameter.

  My need is, can i have one window for getting all the paramter value for stored procedure.


  With Advanced thanks,






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