Visual C# SQL statement won't insert into database

Question

I am trying to simply insert values into an SQL table. The ID in the database cannot be AUTO_INCREMENT so I use MAX and +1. Not only will this code not make a new ID, it simply isn't inserting anything into the table.

Even in the debugger there are no errors or warnings, it just isn't showing up in the database itself..

Here is my code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Windows;
using System.ComponentModel;
using System.Drawing;
using System.Text;


namespace WebApplication2
{
public partial class WebForm1 : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void ButtonClick(object sender, EventArgs e){



        using (var sqlConnection1 = new SqlConnection("Data 
Source=SERVER; Initial Catalog = Metal; Integrated 
Security = True"))
        {
            SqlDataAdapter cmd = new SqlDataAdapter();

            using (var insertData = new SqlCommand("INSERT INTO ApptIn 
(CONTROLNUMBER, CARRIERNAME, EXPECTEDNUMOFPIECES, EXPECTEDWEIGHT) VALUES 
(@carrierSelectInput, 
@pieceCountInput, @weightinput)")
            {



                SqlCommand generateApptNum = new SqlCommand();
                View appNumView = new View();

                insertData.Connection = sqlConnection1;

                string carrierSelectInput = DropDownList1.Text;
                string pieceCountInput = TextBox1.Text;
                string weightInput = TextBox2.Text;

                insertData.Parameters.Add("@carrierSelectInput", 
carrierSelectInput.VarChar);
                insertData.Parameters.Add("@pieceCountInput", 
pieceCountInput.Int);
                insertData.Parameters.Add("@weightInput", 
weightInput.Int);

                cmd.InsertCommand = insertData;

                sqlConnection1.Open();
                insertData.ExecuteNonQuery();
                generateApptNum.ExecuteNonQuery();
                sqlConnection1.Close();
            }
        }

        }
    }
}

EDIT: I have tried running the SQL into the DB and it gave an error, so I changed it(updated in code) but it puts in at ID=0...

the max may be null when the subquery returns null. try COALESCE((SELECT MAX(CONTROLNUMBER) FROM ApptIn),0)+1. also, I doubt that a subquery in the VALUES clause of an INSERT is valid syntax? what does that second command, generateApptNum, do? — Cee McSharpface
– Cee McSharpface, Commented Jul 28, 2017 at 16:44
Select the ID column and make <Is Identity=yes> for auto incrementing. — Musab
– Musab, Commented Jul 28, 2017 at 16:56
I tried COALESCE but no change. generateApptNum is something I was going to add when I get this problem sorted out. It will just return the new CONTROLNUMBER for the user to see. — Pandasai
– Pandasai, Commented Jul 28, 2017 at 17:07
My company blocks the ability to change Identity settings in the SQL DB — Pandasai
– Pandasai, Commented Jul 28, 2017 at 17:09
If you want an incrementing value like this identity or a sequence is the only reliable way of generating the number. MAX + 1 is fraught with concurrency problems. If you have multiple inserts at the same time it is a nightmare. And be careful using AddWithValue when you have all of your queries as pass through like this. It will sometimes get the datatype wrong. blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… — Sean Lange
– Sean Lange, Commented Jul 28, 2017 at 17:26

Deadron · Accepted Answer · 2017-07-28 16:52:52Z

2

I know you have already committed to your plan, but, I feel that I have to point out that, due to the sub select for the Max id value in your query, the insert statement has the potential to be much slower than a normal insert.

If you are planning on inserting a large number of rows or creating an API for use throughout the code I highly recommend either adjusting the column definition to be an identity column or to consider using a a sequence to generate the ids.

answered Jul 28, 2017 at 16:52

Deadron

5,2891 gold badge18 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Tsai · Accepted Answer · 2017-07-28 21:06:09Z

1

The issue could be that you need to specify the CommandType to be CommandType.Text on the insertData command. There is a lot going on in the original code with multiple sqlcommands being declared. I think the code could be simplified as such:

protected void ButtonClick(object sender, EventArgs e)
{
    using (var sqlConnection1 = new SqlConnection("data source=testServer;initial catalog=testCat;integrated security=True;"))
    using (var insertData = new SqlCommand("insert into tempExample (id, code, description) values ((select max(coalesce(id, 1)) from tempExample)+1, @code, @description)", sqlConnection1))
    {
        insertData.CommandType = CommandType.Text;
        insertData.Parameters.AddWithValue("@code", "Testing4");
        insertData.Parameters.AddWithValue("@description", "Testing3");

        sqlConnection1.Open();
        insertData.ExecuteNonQuery();
        sqlConnection1.Close();
    }
}

Update - I changed the code above to reflect a working test on my local machine. Note that the connection string format is different (lack of spaces).

edited Jul 28, 2017 at 21:06

answered Jul 28, 2017 at 16:58

Paul Tsai

9136 silver badges16 bronze badges

3 Comments

Paul Tsai Over a year ago

Have you checked the connection string to ensure that it's hitting the correct server?

Pandasai Over a year ago

Yes, I've gotten it to give results back from each row in the db with this

Paul Tsai Over a year ago

@Pandasai I've updated the code to reflect a working example from my local machine. If the c# code was wrong, the database should be throwing an error. If the db is not throwing an error, there could be something else preventing the update from happening. Have you tried executing the sql from a prompt?

Collectives™ on Stack Overflow

Visual C# SQL statement won't insert into database

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related