CODEWITHSUNDEEP
c#sql-servert-sql
Pandasai
Pandasai

Reputation: 35

Visual C# SQL statement won't insert into database

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...

Upvotes: 1

Views: 844

Answers (2)

Paul Tsai
Paul Tsai

Reputation: 903

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).

Upvotes: 1

Deadron
Deadron

Reputation: 5289

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.

Upvotes: 2

Related Questions