Post Transsaction Insert

Hi there.

I have a final sql insert to make in my code. After a succesful transaction I need to the connection to look for my Primary Key (OrderID) make sure it matches the one i have in session then insert in the row last created.

It would then insert "YES" or  " NO" in column "PAID".

I can't quite work out why what I have below isnt working.

 

1     string squery;
2    
3                            String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
4                            SqlConnection con = new SqlConnection(strConnString);
5    
6    
7                            // use current defined connection// note, return the identity generated by the inscert
8                            squery = "INSERT INTO Entries(Paid) VALUES(@Paid); SELECT SCOPE_IDENTITY()";
9                            SqlCommand cmd = new SqlCommand(squery);
10                           cmd.Parameters.AddWithValue("@Paid", "YES");
11                           cmd.CommandType = CommandType.Text;
12                           cmd.Connection = con;
13   
14                           try
15                           {
16                               con.Open();
17                               cmd.ExecuteScalar();
18                               // get the identity
19                           }
20                           finally
21                           {
22                               con.Close();
23                               con.Dispose();
24                           }

 

 

Anyone have any ideaS?

Cheers

0
clownshoes2009
5/4/2009 5:29:10 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

16 Replies
643 Views

Similar Articles

[PageSpeed] 23

 Why don't you use storprocedure with out parameter which return the id of lastly inserted row


Thanks
Pankaj Gohel

Please Mark as Answer if you find the post useful.
0
pankajgohel
5/4/2009 5:48:55 AM

Yes ok!

The problem is I'm not sure of the syntax of the stored proc.

string squery;

                        String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
                        SqlConnection con = new SqlConnection(strConnString);


                        // use current defined connection// note, return the identity generated by the inscert
                        squery = "INSERT INTO Entries(Paid) VALUES(@Paid); SELECT SCOPE_IDENTITY()";
                        SqlCommand cmd = new SqlCommand(squery);
                        cmd.Parameters.AddWithValue("@Paid", "YES");
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = con;

                        try
                        {
                            con.Open();
                            cmd.ExecuteScalar();
                            // get the identity
                        }
                        finally
                        {
                            con.Close();
                            con.Dispose();
                        } 

 

What I'm triyng to do is for the connection to open - lookup the last OrderID entered, then add a value to colum "paid".

At the moment I'm getting the error:

Cannot insert the value NULL into column 'OrderID', table 'redway.dbo.Entries'; column does not allow nulls. INSERT fails.
The statement has been terminated.

 

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: Cannot insert the value NULL into column 'OrderID', table 'redway.dbo.Entries'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Source Error:

Line 590:                        {
Line 591:                            con.Open();
Line 592:                            cmd.ExecuteScalar();
Line 593:                            // get the identity
Line 594:                        }

This is the error I'm getting so far!

Any ideas please?

0
clownshoes2009
5/4/2009 6:09:09 AM

Check you Ebtrues table in the OrderID is Identity Column or not? IF not Set it as IS Identity Yes


Thanks
Pankaj Gohel

Please Mark as Answer if you find the post useful.
0
pankajgohel
5/4/2009 6:15:59 AM

It is my primary key with a foreign relationship. I can't set it as an identity. What should I do?

Should I add another column and make THAT the identity column?

0
clownshoes2009
5/4/2009 6:29:08 AM

 The error is becose in your table OrderID is not null feild and Your are not providing it's value while insertion. The soluetion for this you provider any value while insert.

And let me know why you can't set it as Identity


Thanks
Pankaj Gohel

Please Mark as Answer if you find the post useful.
0
pankajgohel
5/4/2009 6:41:27 AM

Well I have an OrderID in session which I'd like to compare with the value in the table. If they match then insert the new Paid value.

How should I do that please?

0
clownshoes2009
5/4/2009 6:46:29 AM

 That means You need not to insert in Entries table you just need to update it

Use following quiery to update 

Update Entries set Paid = @Paid where orederid = @orderId

And you also need to pass one more parameter of   @orderId


Thanks
Pankaj Gohel

Please Mark as Answer if you find the post useful.
0
pankajgohel
5/4/2009 6:55:03 AM

Thanks alot mate. Here's what I have so far. Looks a bit skinny though!

 

ALTER PROCEDURE dbo.StoredProcedure1

(

@Paid
varchar(3),

@OrderID char(36)

)

AS

UPDATE Entries SET Paid = @Paid

WHERE OrderID = @OrderID

 Do you think this will work?

0
clownshoes2009
5/4/2009 7:18:53 AM

 Yup


Thanks
Pankaj Gohel

Please Mark as Answer if you find the post useful.
0
pankajgohel
5/4/2009 7:22:02 AM

Of course the Paid column doesn't have a value yet - this is still considered an update?

0
clownshoes2009
5/4/2009 8:03:33 AM

 Yes


Thanks
Pankaj Gohel

Please Mark as Answer if you find the post useful.
0
pankajgohel
5/4/2009 8:06:08 AM

Ok. I'm in the code behind. 

1     String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
2                            SqlConnection con = new SqlConnection(strConnString);
3                            SqlCommand cmd = new SqlCommand("AddPaid", con);
4                            cmd.CommandType = CommandType.StoredProcedure;
5    
6                            cmd.Parameters.AddWithValue("@Paid", "Yes");
7    
8                            try
9                            {
10                               con.Open();
11                               cmd.ExecuteNonQuery();
12                               cmdEntry.ExecuteNonQuery();
13                           }
14                           catch (Exception ex)
15                           {
16                               throw new Exception("Execption adding account. " + ex.Message);
17                           }
18                           finally
19                           {
20                               con.Close();
21                           }
 

This look right?

0
clownshoes2009
5/4/2009 8:11:21 AM

 No Your Sp Should have one more parameter of @orderID


Thanks
Pankaj Gohel

Please Mark as Answer if you find the post useful.
0
pankajgohel
5/4/2009 8:14:59 AM

So I add

cmd.Parameters.AddWithValue("@OrderID"HttpContext.Current.Session["redway_OrderID"].ToString());

Is there no possibility that this will overwrite the wrong orderid?

 

 

0
clownshoes2009
5/4/2009 8:17:59 AM

So finally you got the Answere :)


Thanks
Pankaj Gohel

Please Mark as Answer if you find the post useful.
0
pankajgohel
5/4/2009 10:20:03 AM

Yes thank you!

I've credited you with the answer.

Cheers buddy

0
clownshoes2009
5/4/2009 10:23:56 AM
Reply:

Similar Artilces:

sql datasource insert error
hii,,i am using asp.net 2005 and sql server 2005... i m using sql data source in my form,,i want to insert the details into a table using the insert command thr sqldatasource....one of the column name is service_w/lb, this is where i get an error.i am not able to insert the new data..here is my code:::: ______________________________________ <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:sm123_trackerConnectionString %>"SelectCommand="SELECT * FROM [Agency_Master]" InsertCommand="INSERT...

sql insert .net question
Hello, wrote an insert for a C#.Net web app. The insert works fine, except if there are appostrophies (spelling) for example: this isn't a problem this isnt a problem the second one will work the first will not. The info is going into a sql server database as text(16), the same result occurs with char. I believe you'll need to use an escape character "\", so something like this: "this isn\'t a problem" Also, you should try to use stored procedures or at the very least paramater queries. Both of those would solve your problem as well."I would l...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

post from text box to SQL insert
Hello,I'm trying to update a single field of a record and i want to do it using a standard multi line text box but I'm not sure how to write the c# command to process the sql update.  I would also like the entry to be added into the database with line breaks. Thanks for your help  There is nothing to ask...... you can do it using the same way u r updating the others..... you do not need any c# command..... its the query on which all this depends...you can use query... :     update <table name> set <feild name>= textbox1.text ...

Insert command in SQL with vb.net
Hi I am a new in ASP .NET programming I faced a problem to insert integers with other types of variables in my database from my programs. I have only permission to pass the SQL command to my server. As an example I have one table TestTable with two column one is integer and one is varchar named as ID and Fname I can insert any string that table but when I trying to insert integer and string then it shows an error.Dim strInsertSQL As String = "INSERT INTO TestTable (ID,Fname) VALUES (" & iID & “,’” & strFname & “’)”Dim conStr As String = String.EmptyconStr = Confi...

Variable Insert to SQL server insert satement setting values for the @variable INSIDE sql
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName,  and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example.  this is what I got <%@ Page Language="C#" %><!DOCTYPE html ...

Insert via Entity bean and Insert via SQL Insert
I am inserting a record in Table A using Entity beans create method and then into Table B using SQL Insert Statement. The Table B has a foreign key relationship with Table A. Will there be an issue with transaction failing etc.. There is already a session bean method calling this entity bean and the SQL insert. The session bean method's transactional attribute has been set as "Required". I found that in some cases the session bean method gets executed perfectly but does not insert any data in the table. I am not sure if the issue is because of the above statement....

Insert SQL on Sql Command
i'm using an sql command for insert query it looks like this cmd=new sqlcommand("insert into tblname values('" & textbox1.text & "')",cn) cmd.executereader the problem is if i type a singlequote or double quote an error is throwing,invalid syntax .... how can i s'rt out it with out using a sql stored procedure thanks in advance PrasantHI think therefore i'mvPras© Hi, try replacing the single quotes present in your input with double single quotes ... example below: string sqlInput = textbox1.text.Replace("'","''...

Submit Form to insert data into SQL and display with a gridview (edit as well) below how do i insert data into sql table?
I have an asp.net page that has a simple form where a user can submit customers information. Below that I have a gridview which displays customer information submited by the user who is logged in. I have the gridview displaying, sorting and updating as I want however I can't figure out how to bind a button click to insert data into the customer table and then have it refresh the gridview when they submit. Here is the basic code; (I am using AJAX for a calendar when a user wants to edit an appointment date) &lt;div class="leftBar"&gt; <div align=center&...

SQL Server insert query
When I am inserting into a table, I have some values which I am finding are "N/A" which obviously come from a source which allows it ie nvarchar column)  I need to check the values before entering into the column, and if it is not an integer, converting it to the value '00000000' ie a similar thing would be isnull(columnname,00000000), but in this case, not checking for if it is null, but rather if it is an integer. Does anyone have any ideas? Take a look at http://berezniker.com/content/pages/sql/microsoft-sql-server/isnumericex-udf-data-type-awareBeware of ...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

how to insert sql stored procedure into vb.net code??
I am trying to create a paged web site from my database file. for that i found an appropriate sql code. the only thing i don't know is how to declare this sql inside the vb. is it supposed to be in the same .aspx file or in another one?? pls help if you know. thank you so much. here's the sql stored procedure: ------------------------------------------------------------ CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Cr...

SQL Query String insert a null with ADO.NET
 I have an SQL Update query with four string variables that I get from a textBox.Text to pass to DB. When data is present, no problem, but when There is NO data to Update, I want to pass a null to the database. I tried:        string box1 = txtCustMachSN.Text;        if( box1 == "" || box1 == "0" ) { box1 = System.DBNull.Value; }       string box2......        string strCmd = "UPDATE Equip2 SET CustomerSerialNo='" + box1 + "&...

Inserting data in batch mode in a SQL Table using .NET
Hi, I have an ASP.NET Web Service that accepts a DataSet object passed to it. This DataSet will contain a large number of records in it's table. What I want to do (if possible) is insert all records in a SQL table in a batch mode (one go). Is this doable? Thanks,--Mazen depends on the size of the dataset.Kay LeeMySpace.com - http://www.myspace.com/kragieInfrastructure GroupMySpace.com- Code to live, but Live to code....

Web resources about - Post Transsaction Insert - asp.net.sql-datasource

Transaction cost - Wikipedia, the free encyclopedia
Bargaining costs are the costs required to come to an acceptable agreement with the other party to the transaction, drawing up an appropriate ...

Sheryl Sandberg, Peter Thiel, Mail.ru Group Involved In Notable Facebook Stock Transactions
Facebook shares were involved in some large transactions in recent days, with moves by Chief Operating Officer Sheryl Sandberg , board member ...

Unilever’s charitable app uses Timeline and subscription billing; Facebook agrees to take only 5 percent ...
... this week, including subscription payments and the new Open Graph-enabled Like action . Betapond says Facebook agreed to take a 5 percent transaction ...

Transaction Network Services - LinkedIn
Learn about working at Transaction Network Services. Join LinkedIn today for free. See who you know at Transaction Network Services, leverage ...

DocuSign Transaction Rooms (formerly Cartavi) on the App Store on iTunes
Get DocuSign Transaction Rooms (formerly Cartavi) on the App Store. See screenshots and ratings, and read customer reviews.

The Largest Bitcoin Transaction Ever = $210M at today’s exchange rate
... a photo: I am so glad that my son demanded that his allowance and related monetary exchanges switch to bitcoin last year. The fixed transaction ...


Real estate transactions Oct. 19, 2015
Real estate transactions Oct. 19, 2015 Bradenton Herald $388,000 Jack Jeffrey C, Jack Mary C to Reeves Danny Gerald, Reeves Karen Marlene, ...

FTC Bans Wire Transfers, Reloadable Cash Cards, And Payment Orders In Phone Transactions
We’ve shared warnings about many different types of telemarketing scams, but they all have one thing in common: they extract money from their ...

Check your credit card transactions: 54 Starwood hotels hit by malware
Third-party forensic experts have discovered that the point-of-sale systems in 54 Starwood hotels across the US and Canada were infected with ...

Resources last updated: 11/29/2015 9:13:07 PM