ADO.NET :: SqlCommand.Transaction Object "lost" During SqlException?
Aug 16, 2010
I've been stuck with this for days, and posted elsewhere online without success. If anyone can provide me with an answer to this problem I will arrange for beer to be sent to your chosen address....I have a DataTable containing multiple rows of data that I want to insert into a SQL 2005 table. The code works perfectly when the DataTable contains values that meet the SQL table schema design (e.g. all rows have a username, password, firstname & surname). However, when a value is missing or incorrectly entered (e.g. no firstname value), this causes a SqlException. I need to handle this in ASP.NET to provide a more user-friendly error message than what SQL gives, then rollback the whole transaction to cancel the transaction.The code attached is an imitation of how my data layer works. The error occurs on the
RollBack() method. The error is: 'Object reference not set to an instance of an object'Can anyone explain why this happens? The SqlCommand object seems okay when I debug (it exists), but the
Transaction object is lost as soon as the SqlException occurs. Although the transaction never commits when a SqlException is thrown, its a pretty ugly exit.My code design might be poor, in which I would welcome advice, but most important I need to figure out how to trap SqlExceptions without this error occuring, and Rollback as needed.The SqlException Numbers thrown are 2601 or 2627, both of which carry a severity level of
14, which is not high enough for the SqlCommand object to close the connection according to MSDN.The code looks lengthy but should be simple to follow...
I need to see an errorcode produced by a SqlException - however, I can't get one to fire. I use NHibernate and have a SQL UNIQUE CONSTRAINT setup on my table. When that constraint is violated, I need to catch the error code and produce a user-friendly message based off of that. Here is a sample of my try/catch:
I've got all of my ASP.NET requests wrapped in a Session and a Transaction that gets commited only at the very end of the request. At some point during execution of the request, I would like to insert an object and make it visible to other potential threads - i.e. split the insertion into a new transaction, commit that transaction, and move on. The reason is that the request in question hits an API that then chain hits another one of my pages (near-synchronously) to let me know that it processed, and thus double submits a transaction record, because the original request had not yet finished, and thus not committed the transaction record.
So I've tried wrapping the insertion code with a new SessionScope, TransactionScope(TransactionMode.New), combination of both, flushing everything manually, etc. However, when I call Refresh on the object I'm still getting the old object state. Here's some code sample for what I'm seeing:
Post outsidePost = Post.Find(id); // status of this post is Status.Old using (TransactionScope transaction = new TransactionScope(TransactionMode.New)) { Post p = Post.Find(id); p.Status = Status.New; // new status set here p.Update(); SessionScope.Current.Flush(); transaction.Flush(); transaction.VoteCommit(); } outsidePost.Refresh(); // refresh doesn't get the new status, status is still Status.Old
I'm using visual studio 2008 and sql server 2005 and everything is working just fine under normal use. However if a user is on a page for a while several minutes with no activity then clicks a button on occassion the site throws the following exception ...
Procedure or Function "sp_name" parameter '@SomeParameterName', which was not supplied
I'm also encountering this error in Visual Studio while debugging the application, in otherwords run the site from visual studio then make some change to the html in VS save the changes and refresh the page.
The error is not consistent nor is the time the page has to stay idle in order for it to occur....
The current sql command object timeout is 30 secs and the website timeout is 30 minutes.
If I use local SQL database, everything working fine, but when I change database link to Web SQL server this error pop out, and I have no idea. Anyone know what happen? My error as below:
Server Error in '/' Application.
Invalid object name '訂單'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code
Exception Details: System.Data.SqlClient.SqlException: Invalid object name '訂單'.
I am in a situation where I have to perform a transaction master detail record (Drop prev details, insert new details, Update Master status) Master Business Object has 20 fields Details Business Object has 4 fields only. Now I have to update only 1 field in master table and 4 fields in details table for insert.
If I initialize a new master object, 19 fields are being wasted for a simple update. What do I do to efficiently handle this situation? Can I make a new object and inherit only one field from my master business object?
We have a Static class that 's called CData in our asp.net c# app.It handles all the data layer CRUD functionality on our pages.
Problem is, were are losing the intellisense reference in our pages to it.For example: we would expect to type CData.(dot) and have a list of methods and properties available to us.
Nope. Not there.
I kind of thought it was because the file was getting too long at 3000 lines of code, since some other static classes were working ok.
So then I thought that I'd try wrapping the CData Class in a namespace. That worked, but of course I had to put an include statement of the top of each page that's using CDATA, also, now some of the other developers are getting errors in there pages complaining about missing methods that are in CDATA, but their pages are not seeing.
I am using hidden fields to save some preset data, but upon postback they appear to disappear.
Here's what my controller actions look like:
[Code]....
The view is coded like this:
[Code]....
But when the POST action receives the object back, some of the fields have become null. The FormCollection, however, contains all values. I realize I could just take everything from the formcollection but it's probably better practice to use the object, right?
I have a GridView and a LinkButton control. I click the LinkButton control and loose the object reference to both objects after post back. I want to be able to get the command arguments from the link button and then be able to get a reference to the gridview control to determine which page index to set the gridview. I have been able to get the __EventTarget property to reference the object that was called but Im not able to reference that object.
I want to execute a SQL query inside EF [Code]....but I got an error
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'orderby'.
I try running my query in the SQL Server, such as :
select * from Airplane A inner join Flight f on A.ID = f.Airplane_ID where FromSrc = 1 and ToDest = 3 order by Price, Comfort, Timeliness, [Safety] desc
I have a gridview, where I have made autocode using the wizard. However I need a dynamic SQLcommand from server side. I have tried this code, but it doesn't work, it works if I use this code from HTML (setting the sqlcommand in the SQLDataSource. I have deleted the sqlcommand from the HTML and instead I have implemented the code in the page_load event:
Code:
Dim str As String = SqlDataSource1.SelectCommand = "SELECT DISTINCT Beregning.bId, Informationer.Dato,Beregning.bType, Informationer.pNavn, Informationer.pNr, Informationer.Beskr, Beregning.uId FROM Beregning CROSS JOIN Informationer WHERE (Beregning.UserId = @UserId) AND Beregning.uId = Informationer.uId AND (Beregning.bType=@berType2) ORDER BY Beregning.bId DESC" SqlDataSource1.SelectCommand = str But this code works, when I apply the SQLCommand in HTML:
I have another class that creates the connection (see refined class half way down page) and I believe it connects or at lest I do not hit any of the exceptions in run time.
I would now like to insert data into my database but nothing is updated.
I am trying to follow this example but it is for reading.
I did run "querystring" on the show SQL Pane and it does execute correctly. It inserts data properly as a new row.
a) SqlCommand.ExecuteNonQuery is used for update, insert and delete operations.
Besides the fact that by using ExecuteNonQuery instead of ExecuteReader we automatically know there won't be any query results returned, are there some other benefits/reasons why ExecuteNonQuery should be used?
b) Similarly, if we want a database operation to return a single value, we should use ExecuteScalar instead of ExecuteNonquery ,where with the latter result would be returned via SqlParameter. Is there any particular reason why we should prefer ExecuteScalar over ExecuteNonQuery?
Suppose I have a user table that creates strong relationships (Enforce Foreign Key Constraint) with many additional tables. Such orders table ..If we try to delete a user with some orders then SqlException will arise.. How can I catch this exception and treat it properly?Is this strategy at all?1) first try the delete action if an exception Occur handel it?2) Or maybe before the delete action using code adapted to ensure that offspring records throughout the database and alert according to .. This piece of work So how to do it?
--Edit:The goal is not to delete the records from the db! the goal is to inform the user that this record has referencing records. do i need to let sql to execute the delete command and try to catch SqlException? And if so, how to detect that is REFERENCE constraint SqlException?Or - should I need to write some code that will detect if there are referencing records before the delete command. The last approach give me more but its a lot of pain to implement this kind of verification to each entity..
Dim conn As SqlConnection = New SqlConnection("server='h'; user id='w'; password='w'; database='w'; pooling='false'") conn.Open() Dim query As New SqlCommand("DECLARE @investor varchar(10), @sql varchar(1000) Select @investor = 69836 select @sql = 'SELECT * FROM OPENQUERY(db,''SELECT * FROM table WHERE investor = ''''' + @investor + ''''''')' EXEC(@sql)", conn) dgBookings.DataSource = query.ExecuteReader dgBookings.DataBind()
I need to add in an If statement basically saying if the SQLCommand query returns no rows then show a specific label Can this be done?
I want to add array list in sqlcommand.but i don't no how to pass it. My code is.
ArrayList ProductId = new ArrayList(); for (int i = 0; i < ShipmentPackage.Items.Count; i++) { ProductId.Add(ShipmentPackage.Items[i].ProductVariant.ProductID); } select * from TableName where id= ProductId(ProductId is my Arraylist)
I use a dbml for my Data Access Layer to provide the data that i need in my app. When i connect from the server explorer everything seems fine. I choose to use my windows authentication and the connection test shows everything works just fine. When i Build my solution and run it on my IIS it says that i'm using a login that is not working.
What I'm trying to do is load data into a dataset using Repeater in Asp.net Here what i've done in the code behind
[Code]....
and my HTML code
[Code]....
Is this the correct way for me to use Repeater?ie can we use stored procedure to call it There's an error "The IlistSource does not contain any data sources" I'm trying to retrieve data from the stored procedure only just a simple loading
i want to ask how to i set variable in asp:sqldatasource in sqlcommand in asp.net c#.... This is my code
asp.net <dx:ASPxListBox ID="lsAssignToko" runat="server" DataSourceID="SqlDataSource6" TextField="NAMA" ValueField="ID" AutoPostBack="true" EnableCallbackMode="True" SelectionMode="CheckColumn" OnSelectedIndexChanged="lsAssignToko_SelectedIndexChanged"> <Columns> <dx:ListBoxColumn FieldName="ID"/> <dx:ListBoxColumn FieldName="NAMA" /> </Columns> </dx:ASPxListBox> <asp:SqlDataSource ID="SqlDataSource6" runat="server" ConnectionString="<%$ ConnectionStrings:Ora2010 %>" ProviderName="<%$ ConnectionStrings:Ora2010.ProviderName %>" SelectCommand="SELECT ID, NAMA FROM REF_TOKO WHERE ID IN ( :inTOKO ) ORDER BY NAMA"> <SelectParameters> <asp:SessionParameter Name="inTOKO" SessionField="inTOKO" Size="200" Type="String" /> </SelectParameters> </asp:SqlDataSource> c# string dummy = ""; string data = ""; string inTK = ""; string inTOKO = ""; //string inTOKO1 = ""; int pot; for (int i = lsToko.SelectedItems.Count - 1; i >= 0; i--) { string tok = lsToko.SelectedItem.Text.ToString(); data = lsToko.SelectedItem.Value.ToString(); dummy = dummy + "" + data + ","; int index = lsToko.SelectedItem.Index; lsToko.Items.RemoveAt(index); } if (lsToko.SelectedItems.Count <= 1) { inTK = dummy.Length.ToString(); pot = int.Parse(dummy.Length.ToString()) - 1; inTOKO = dummy.Substring(0, pot); } Session["inTOKO"] = inTOKO;
the problem is where in my variable inTOKO consist of couple of value... Example while variable inTOKO = one value success but while value inTOKO = two value error inTOKO = 1101111 => success inTOKO = 1101111,1211321 => not success
