Insert, Update and record exist not working

Question

1.00/5 (1 vote)

See more:

I have a web form that a user can save their progress and come back to edit or add to it later. When a user enters their data and wants to come back they can save it to a database table where it stays until the user comes back. When the user comes back the data is populated back into the textboxes and the user can finish their data entry or edit the data that is there and finish their entry and click save. The data is getting Inserted into the database but not updated. What did I do wrong in my code for this not work?

C#

protected void ButtonSubmit2_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString);
            con.Open();
            string cmdStr = "Select count(*) from Table88 where User_ID='" + TextBoxUser_ID.Text + "'";
            SqlCommand userExist = new SqlCommand(cmdStr, con);
            SqlCommand cmd = new SqlCommand("select User_ID from Table88", con);
            int temp = Convert.ToInt32(userExist.ExecuteScalar().ToString());

            if (temp == 0)
            {
                const string commandText = @"INSERT INTO Table99
            (User_ID, FT_UNDERGR, DATE, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b, FTEYR, THCAS, FTE40, HC50, FTE4050)
             SELECT User_ID, FT_UNDERGR, DATE, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b,FTEYR, THCAS, FTE40, HC50, FTE4050 FROM Table88 WHERE User_ID = @User_ID;";

                using (SqlCommand cmd2 = new SqlCommand(commandText, con))
                    cmd.Parameters.AddWithValue("@User_ID", TextBoxUser_ID.Text);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            else if (temp == 1)
            {
                const string commandText = @"UPDATE INTO Table99
            (User_ID, FT_UNDERGR, DATE, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b, FTEYR, THCAS, FTE40, HC50, FTE4050)
             SELECT User_ID, FT_UNDERGR, DATE, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b,FTEYR, THCAS, FTE40, HC50, FTE4050 FROM Table88 WHERE User_ID = @User_ID;";
            }

Posted 12-Nov-14 15:20pm

Computer Wiz99

Add a Solution

Comments

PIEBALDconsult 12-Nov-14 22:30pm

If SQL Server, look into the MERGE statement.

/\jmot 14-Nov-14 2:13am

debug the code and find the Error, and if you can't solve the error then post here.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kannan Karthikeyan · Answer 1 · 2014-11-12T16:03:00

Your update statement is wrong. Here is the example of UPDATE INTO SELECT

UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO

Refer the following link for more details:-

http://blog.sqlauthority.com/2013/04/30/sql-server-update-from-select-statement-using-join-in-update-statement-multiple-tables-in-update-statement

Member 10757849 · Answer 2 · 2014-11-13T18:26:00

SQL

change your update to - UPDATE INTO Table99
            set User_ID=User_ID, FT_UNDERGR=FT_UNDERGR,
DATE=DATE,  FT_GRAD=FT_GRAD, FTE_UNDERG=FTE_UNDERG,
FTE_GRAD=FTE_GRAD, NON_CREDIT=NON_CREDIT,
TOTAL_FTE=TOTAL_FTE, FCFTUHC = FCFTUHC
FCFTPBHC=FCFTPBHC, FCPTUHC=FCPTUHC,
FCPTPBHC=FCPTPBHC, NCHC=NCHC,
UnderG12= UnderG12, Postb9=Postb9,
Total123b4b=Total123b4b, FTEYR=FTEYR,
THCAS=THCAS,FTE40=FTE40,
H1C50=H1C50, FTE4050=FTE4050
WHERE User_ID = @UserId

Dusara Maulik · Answer 3 · 2014-11-12T17:17:00

Hi,

You need to user Stored Procedure instead of writing SQL on code side. Kindly review below stored procedure which will help you to achieve your result as expected.

To understand and work with stored procedure, review below few links.

Sql Server - How to write a Stored procedure in Sql server[^]
SQL Server Stored Procedure[^]
Select SQL Server Stored Procedures using ASP.Net Example[^]

SQL

CREATE PROCEDURE AddInsertUser
    @UserId VARCHAR(30)
AS
    IF EXISTS(SELECT 1 FROM Table88 where User_ID= @UserId)
        BEGIN
            UPDATE INTO Table99
            (User_ID, FT_UNDERGR, DATE, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b, FTEYR, THCAS, FTE40, HC50, FTE4050)
             SELECT User_ID, FT_UNDERGR, DATE, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b,FTEYR, THCAS, FTE40, HC50, FTE4050 FROM Table88 WHERE User_ID = @UserId
        END
    ELSE
        BEGIN
            INSERT INTO Table99
            (User_ID, FT_UNDERGR, DATE, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b, FTEYR, THCAS, FTE40, HC50, FTE4050)
             SELECT User_ID, FT_UNDERGR, DATE, FT_GRAD, FTE_UNDERG, FTE_GRAD, NON_CREDIT, TOTAL_FTE, FCFTUHC, FCFTPBHC, FCPTUHC, FCPTPBHC, NCHC, UnderG12, Postb9, Total123b4b,FTEYR, THCAS, FTE40, HC50, FTE4050 FROM Table88 WHERE User_ID = @UserId;
        END

    SELECT * FROM Table88 WHERE User_ID = @UserId /* To return updated or newly inserted record to code side */

Insert, Update and record exist not working

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0