I am trying to return the identity value of a inserted record for the first time in ASP.NET but it always returns 0, I can see in the database that a record is being inserted and the identity value is not 0.
Stored Proc:
[Code]....
Code Behind:
[Code]....
I have a Stored Proceedure which DID return the Scope_Identity when this was executed in SQLServer 2000.
Now I am porting this over to SQLServer 2008, and this always returns 0, and not the Scope_Identity which it should.
Here is the Stored Proceedure which will pass null for the parameter @pProjId for an insert:
[Code]....
Here is the script which creates the hip_Project Table:
[Code]....
What do I need to change for SQLServer 2008?
Again, this executed fine in SQLServer 2000 and always returned the Scope_Identity as expected. This problem has come up with other stored proceedures as well for which I need the Scope_Identity returned.
This is completely frustrating and probably embarrassing as well. A while back I posted a similar question and resolved the issue. [URL]...scope_identity The program has been working ever since, until yesterday when as it turns out SCOPE_IDENTITY() started returning an empty string even though a record is being inserted. I am not sure what has changed since the fateful day.
[Code]...
I am hoping that there is something I am just not seeing here. A lot of the information I have seen thus far indicates that there are problems with SCOPE_IDENTITY() but most of those are related to inserting multiple records. As you can see this should be fairly straight forward. The thing that really has got my goat is that it used to work and now it doesn't. If someone could take a look at the above code and comment on any anomalies it would be great. Also I have heard suggestions for switching to ADO.NET and it is beginning to look a lot more attractive every day. But I have a ways to go before making the switch so it is SqlDataSources for now
I guess it is too late and I'm too tired to see what I'm doing wrong. Here is what I'm trying:
int imageId = imageDal.AddImage(new SqlParameter[]
{
new SqlParameter("@IMAGE_ID",
SqlDbType.Int, Int32.MaxValue, ParameterDirection.Output,
true, 0, 0,"IMAGE_ID", DataRowVersion.Current,DBNull.Value),
new SqlParameter("@IMAGE",
SqlDbType.Image, 11, ParameterDirection.Input,
true, 0, 0,"IMAGE", DataRowVersion.Current,image)
});
public int AddImage(SqlParameter[] spParams)
{
SqlHelper.ExecuteNonQuery(BaseDAL.ConnectionStringImages, INSERT_IMAGE_SQL, spParams);
return Convert.ToInt32(spParams[0].Value);
}
Stored Procedure:
[dbo].[sp_insert_image]
-- Add the parameters for the stored procedure here
@IMAGE_ID int OUT,
@IMAGE image
AS
BEGIN
INSERT INTO images
(IMAGE)
VALUES
(@IMAGE)
SELECT @IMAGE_ID = SCOPE_IDENTITY();
END
GO
I get DBNull as spParams[0].Value. I've tried setting value of @IMAGE_ID to a constant in my stored procedure yet it didn't change anything so the problem isn't with my stored procedure (that is what I think). When I execute the procedure from sql management studio, I see the inserted_id returning..
I am using DetailsView attached to a ObjectDataSource to add a new record to a database. When it is complete I want to display the ID of the row that was added. I have a column in my database called ID that is set as the primary key and has Identity Specification enabled.
My Insert statement is:
{
i have the following stored proc that inserts data into a database based on a stauts field. the first stored proc inserts into the first table and i am trying to get the identity field out as i need this value for the next stored proc. the first stored proc works fine and inserts the data but the secnd one doesnt, even though there is data there.
[Code]....
I've used a sql data source to execute an insert stored procedure into my sql database. This time though, I want to execute the sp from my code behind. BUT, I'm stuck on the parameters and how to pass in the ones for the insert, and return one parameter, which is the identity of the row just inserted. The error I'm getting is. I'm missing something, but I don't see what... I think it's with the input/output parameters...
Procedure or function 'sp_CloneIT' expects parameter '@ClonedMatterID', which was not supplied. My stored proc
[Code]....
I am trying to add "Select Scope_Identity()" to my table adapter but vs2010 respond with "Unable to parse query text." error message.
Code for Insert SQL is -
INSERT INTO orders
(creditAutorizarionNumber, OrderName, OrderPersonPhone, OrderPersonEmail)
VALUES (@creditAutorizarionNumber,@OrderName,@OrderPersonPhone,@OrderPersonEmail)
I tried to add -
;Select Scope_Identity()
How can I use the Scope_Identity() withing a BLL table adapter ?
I have a webapplication based on MonoRail, where we utilize the routing feature from MonoRail. On the first request after the application has started, the routing isn't initialized. To circumvent this, I have the following code in Application_OnError():
public virtual void Application_OnError()
{
if ( // identified as routing error )
Server.TransferRequest( Context.Request.RawUrl, false );
return;
}
Problem beeing that on our development server (which runs server 2008 R2, with IIS 7.5 and .NET 3.5) returns a blank page without headers, but on my workstation (which runs win7, IIS 7.5 and .NET 3.5) it works fine.
What could be the cause of this? If the code in Application_OnError() throws an exception, what would be the expected output?
I have verified the following:
The access-log shows one entry, I'm not sure if a TransferRequest would show up as a second entry when invoked successfullyThe application actually do some work according to my internal logs, and it doesn't die since a subsequent requests works flawlessly (because routing will be active)
I have a WCF based test harness client for a set of web services. The test client allows me to see raw requests and responses going to and from the services. A Message Inspector endpoint behavior is used to "pick off" the raw requests and response messages and save them for later display in the UI.
This works great, except for the use case where invalid credentials are passed. The server returns an HTTP 401 along with a SOAP fault containing details of what happened. This hurts me in a couple ways:
On the client this shows up as a MessageSecurityException not a FaultException, so I can't get the details from the fault. This exception appears to prevent the AfterReceiveReply event handler on my message inspector from firing, so I have no access to the raw response.
Is there any way I can handle this case so that the SOAP fault comes through as a FaultException and allow my message inspector to handle responses regardless of the HTTP status code that is returned?
using ASP.NET 2.0 SqlDataAdapter update returns 0 records. I have a simple application that stores Passenger's ticket info in a sql database.
when a user clicks "Create Reservation" I add a Passenger Information (name, destination, PNR) to dataset
and save dataset in ViewState.
Then when user clicks update, I pull the dataset from viewState and try to update database. The update is returning 0. what i am doing incorrectly?
protected void btn_CreateReservation_Click(object sender, EventArgs e)
{
DataRow dr ;
dr = dt.NewRow();
dr["Name"] = txtBox_Name.Text; dr["Destination"] = txtBox_Destination.Text;
dr["PNR"] = Convert.ToInt32(txtbox_PNR.Text); dt.Rows.Add(dr);
dt.AcceptChanges();
ViewState["MyTable"] = dsPassengers;
[Code]....
I've built a form for entering some data into a table in my database, now I want to INSERT that data, and get back the ID (which is an autonumber) of the record that has just been added. I've got the INSERT part working just fine, and I've tacked "SELECT SCOPE_IDENTITY();" onto the end of that, but now I need to retrieve the returned number. At the moment all of this is done with an SqlDataSource (with the "INSERT" statement being put together and fired by some C# in the codebehind file, which takes data from a form on the page). Is there a quick way to grab that number and put it in a variable?
View 2 RepliesI have a routine that submits a SOAP request using HttpWebRequest and WebResponse. If the SOAP Request fails the server sends back HTTP/1.1 500 Internal Server Error. When I trap the error I have yet to find a way to view the body of the reply which contains the fault code. Is there a way to retrieve the message body when the server returns a 500 internal Server Error? In body of the reply which I am not able to retrieve.
faultstring xml:lang="en-US" Specified argument was out of the range of valid values.
I have a WCF client test harness where I'm using a "client message inspector" endpoint behavior to "pick off" raw requests and response messages going to and from a service and save them for later display in the UI.
This works great, except for the use case where invalid credentials are passed (Basic Authentication). The server returns an HTTP 401 along with a SOAP fault in the body containing details of what happened. I have no control over the service, so this behavior cannot be changed.
Because the HTTP 401 is returned as a WebException, my message inspector never fires and I can't get to the raw response. This exception eventually gets to the UI in the form a MessageSecurityException.
Is there any way to suppress this so the message inspector's AfterReceiveReply still fires? or is there somewhere else I can access the entire raw response?
Inner Join:- Returns a row when there is matching table in both tables. we can use comparison operator like =,<,>,<>
I created two tables
TableA
ID OID Type
1 1 A
2 6 B
3 7 C
4 10 D
TableB
ID Value
1 10
2 30
3 40
5 60
6 70
Basic Inner Join select Type From TableB as a inner join TableA as b on a.ID=b.OID Result Type
A
B
If i want only the type which has not match in tableB then i can use not in subquery i get the result if i m using select Type From TableB as a inner join TableA as b on a.ID<>b.OID then i get
Type
A
A
A
A
B
B
B
B
C
C
C
C
C
D
D
D
D
D
18 rows affetected i m suprise
I have a table line (ID, LineNo, LineName) having values as :
1, 1, <blank>
2, 2, <blank>
3, 3, <blank>
ID is PK with Auto-Increment and LinNo(I am manually incrementing through ASP.Net Page while record insertion) Now i have to write a query such that it updates LineName column from line table which is blank(as shown above) with values from table linet(linenamet) having values as :
a
b
c
My trial query is :
update line set LineName = (select linenamet from linet);
It gives ERROR as : Subquery returns more than 1 row
Expected Result : After Updation it should show line table as :
1, 1, a
2, 2, b
3, 3, c
My ASP.NET MVC web app has a weird issue. In VS debugging mode it works as expected, but when I publish it to dedicated web server (windows 2003, IIS6) a sql query returns previous data even though underlying data was already updated through the same connection. It looks like as if a query returns cached data. What might be the problem?
View 2 RepliesI'm trying to mock Server.HtmlEncode(), but I keep having a null reference exception.I'm pretty new to the Moq framework, here is my code:
var context = new Mock<HttpContextBase>();
var request = new Mock<HttpRequestBase>();
var response = new Mock<HttpResponseBase>();
var session = new Mock<HttpSessionStateBase>();
var server = new Mock<HttpServerUtilityBase>();
server.Setup(svr => svr.HtmlEncode(It.IsAny<string>())).Returns((string s) => s);
context.Setup(ctx => ctx.Request).Returns(request.Object);
context.Setup(ctx => ctx.Response).Returns(response.Object);
context.Setup(ctx => ctx.Session).Returns(session.Object);
context.Setup(ctx => ctx.Server).Returns(server.Object);
I've also tried the following:
context.Setup(ctx => ctx.Server.HtmlEncode(It.IsAny<string>())).Returns((string s) => s);
I've found a solution but it seems an outdated solution as expect is replaced with setup.
I need to get the id of a record created so that I can store that ifnormation in another SQL table. A snipet of my c#code is as follows:
string insertSql = "INSERT INTO [Group] ([userid], [groupname]) VALUES (@userid, @groupname); SET @groupid = SCOPE_IDENTITY()";
using (SqlConnection myConnection = new SqlConnection(connectionString))
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
myCommand.Parameters.AddWithValue("@userid", currentUserId);
myCommand.Parameters.AddWithValue("@groupname", groupTextBox1.Text);
SqlParameter groupidParameter = new SqlParameter("@groupid", SqlDbType.Int);
groupidParameter.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(groupidParameter);
myCommand.ExecuteNonQuery();
myCommand.ExecuteNonQuery();
gid = (int)groupidParameter.Value;
myConnection.Close();
}
The problem is that I am not getting the @groupid from the SET @groupid = SCOPE_IDENTITY() that is appended to the INSERT statement. The groupid in the SQL table is the Key of type uniqueidentifier
I insert a new user to my SQL database
but when I try to retrieve my Identity PK using select SCOPE_IDENTITY()
I cant use it in my page
here is my code and my stored procedure
_sqlConnection = new SqlConnection(ConnectionString.connectionString());
I am using a EntityDataModel. I have one table that has fields id, first, last
I have a stored procedure for inserting that is mapped to this table in the entydatamodel.
[Code]....
If I execute this in the Code Editor the Output window would look like this
[Code]....
Here is the sample code for a button click event on a simple page that only has a button. This is a bit of bad example because I am actually setting the field id. But assuming that it was an Identity field that was incrementing itself by 1....what would be the code after SaveChanges() that would capture the reutrn value from expId in the stored procedure.
[Code]....
I am using this code to get th ProcessID of newly Created Excel.
Code:
Imports Microsoft.VisualBasic
Imports Microsoft.Office.Interop
Public Class Class1
[code]....
This code returns the Process ID Correctly with Virtual Server. Now I deployed it using a Website, and the ProcessID is 0
I am running an Update statement against an SQL database on server 2008. The update runs but does not update the record, but returns no errors.The database is part of a commercial help desk package called TrackIt 8.5 which was just a fresh install on a new web server and a new sql server. The update is running from a web form we use to automatically create active directory accounts. After the account is created the web form closes out the work order in the Trackit db. This was working fine when the system was running on Server 2003 and SQL server 2005. Running Trackit version 7.02.The login use to access the database has full permissions to read and write to the database, just as it did on the old server which never had an issue updating. Odd thing is, if I log onto the SQL server and make a change to any single field in that record. I can then run the web form and it updates the record normally.
View 3 RepliesI have a web application in ASP.NET 4.0. I've added an asmx service, primarily as a source for the autocomplete extender's lookup values.
When I debug on my machine locally, everything works fine. However, when I deploy the web application to IIS 7.5, I get a HTTP 404 response when trying to send data to the service.
I am able to browse to the service definition, see the available operations. Tellingly, however, when I use the test pages to test the service using POST, I receive an HTTP 404 again.
I'm not sure what is going on. I did create the asmx file within my web application and it is deployed in the virtual directory of my otherwise working production application. Is there an issue with the .asmx file being deployed in the same virtual directory, perhaps?
I'm new to .net, and I've noticed that when viewing my HTML source code generated by a .net application the carriage returns are removed from the head tag when it has runat="server" attribute on it.
I remove the runat="server" and the returns... return.
This really looks nasty when you have a few javascript and css files in your header because it ends up making the entire contents of the head tag 1 big line.
Just wondering if there's a way to control this or tell .net through configuration not to mangle the output?