Checking text box value with SQL Database before insert

[GilbertB]

I am building a program which generates a random number in a textbox everytime the form loads.

I have this code :

 public partial class Form1 : Form
    {

        SqlConnection c = new SqlConnection(@"Data Source=GILBERTB-PC\SQLEXPRESS;Initial Catalog=DVDandGameBooking;Integrated Security=True");
        SqlDataAdapter da = new SqlDataAdapter();

        public Form1()
        {
            InitializeComponent();
            random();
        }

        //private void button1_Click(object sender, EventArgs e)
        //{

        //}

        public void random()
        {

            textBox1.Text = randomnumbers(1, 20000).ToString();

        }

        public int randomnumbers(int min, int max)
        {
            Random random = new Random();
            return random.Next(min, max);
        }
    }
}

This number generated than will be inserted in a table ShopType (in my SQL Server) in the column ShopID.

How can the program first check if there is the same number (being generated ) in the table ShopType ?

[Mitja Bonca]

Create a DataTable, do the SELECT statement of that number (column name) from database, and do the filtering, if the number already exists in dataTable. I would suggest using Linq - its simple and fast.

It should be like:

            int textBoxNumber = int.Parse(textBox1.Text);
            DataTable table = new DataTable("Numbers");
            using (SqlConnection conn = new SqlConnection("connString"))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(@"SELECT ShopID FROM ShopType", conn))
                    da.Fill(table);
            }
            if (table.Rows.Count > 0)
            {
                IEnumerable<DataRow> query = table.AsEnumerable().Where(w => (int)w["ShopID"].ToString() == textBoxNumber);
                if (query.Count() == 0)
                {
                    //no number yet
                }
                else
                {
                    //number already exists!
                }
            }
            else
            {
                //show message there is no number in db yet
            }

IMPORTANT: my code assumes your ID is a number (integer). If your field in dataBase is an integer there will be no problem, but if its a varchar (string), then change this code to:

.Where(w => (string)w["ShopID"] == textBox1.Text); //must be a string

Hope it helps,
bye

[bharat_4g]

Why do you generate Ids in a table? why can't you use Identity column to generate the Id by the sqlserver, use SCOPE_IDENTITY() to get the newly created id