How to add if else statement to check if data was entered into SQL Server database and display a message

Question

I have a program which enters inputted details into a SQL Server database, the program is working well and data will go into SQL Server. I am sending customerID, customerName, CustomerAddress, CustomerAddedDate to the SQL Server database.

How do I go about creating an if...else statement to display a message box to the user to inform them if data was inserted successfully or not, and if not, why not.

Also, how can I automatically have the next available customerID (which is the integer primary key in the SQL Server database) show up automatically in the customerID textbox?

I imagine a code which reads from the customerID column in SQL Server database and then add +1 to it and setting that to the properties of customerID aka textbox1 but perhaps there is a better idea?

My program:

My code:

public partial class Form1 : Form
{
    SqlConnection con = new SqlConnection("Data Source=LAPTOP\\SQLEXPRESS02;Initial Catalog=Greenwich_Butcher;Integrated Security=True");

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }

    private void button1_Click(object sender, EventArgs e)
    {


        string dat = "Insert into [CustomerDetails](CustomerID, CustomerName, CustomerAddress, CustomerAddedDate) Values('" + Convert.ToInt32(textBox1.Text) + "','" + textBox2.Text + "','" + textBox3.Text + "','" + Convert.ToString(dateTimePicker1.Text) + "')";
        SqlCommand com = new SqlCommand(dat, con);
        con.Open();
        int rowsAffected = com.ExecuteNonQuery();
        if (rowsAffected > 0)
        {

            MessageBox.Show("Record inserted succesfully");
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            dateTimePicker1.Text = "";
        }
        else
        {
            MessageBox.Show("Record not inserted succesfully");
        }
        con.Close();

    }
    private void button2_Click(object sender, EventArgs e)
    {
        Application.Exit();
    }
}

Only one question per question, please. [Edit] and remove the second question, the answer for which you can find here https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row. Also, read this https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection probably not too important where you are now but concatenating user input into a query is very bad. If your record isn't inserted into the database, it'll throw an exception, not return 0. Do a try/catch and... hell, just show them the exception Message. Probably good enough for you. — , Nov 27 '18 at 17:10

score 0 · Answer 1 · answered Nov 27 '18 at 17:17

If you make the Customer and identity column it will auto increment when the record inserts but you won't be able to know which one you just created if let's say 2 person add the data at the same time. There are alot of different ways to do it but looking at your code i assume you are pretty new to this so i'll give you a simple working solution.

You will need couple things:

You need a way to know the user using the software. Anything could be good as long as it's unique. If you don't have a user login you can use things like computer name (if on a domain), windows login name. etc
Add a new column to your table called CreatedBy or something like that.
Now when you insert the record add your unique user value you choose at step 1
After the Insert query do a Select query to get the highest CustomerId where the CreatedBy is the current user. This will give you all the data including the CustomerId you want.

How to add if else statement to check if data was entered into SQL Server database and display a message

1 Answers1