Dinamic button create insert into sql function?

Goran Bibic

I have created dinamic button from sql items table...I need to create insert into function for them...I create POS system for sale...

private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'bssDataSet.roba_usluge' table. You can move, or remove it, as needed.
this.roba_uslugeTableAdapter.Fill(this.bssDataSet.roba_usluge);
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=bss;Integrated Security=True");
//SqlCommand cmd = new SqlCommand("SELECT Concat(ime),data FROM roba_usluge", con);
// SqlCommand cmd = new SqlCommand("SELECT (redni_broj),data FROM roba_usluge", con);
SqlCommand cmd = new SqlCommand("SELECT (ime), data FROM roba_usluge", con);
// SqlCommand cmd = new SqlCommand("SELECT Concat(ime, cijena_sa_porezom),data FROM roba_usluge", con);
// Getting all the data to ItemTable
var da = new SqlDataAdapter(cmd);
var ItemTable = new DataTable();
da.Fill(ItemTable);
con.Open();
Int32 count = ItemTable.Rows.Count;
con.Close();
int top = 50;
int left = 50;
for (int i = 1; i <= count; i++)
// for (int i = 0; i < count; i++)
{
Button button = new Button();
button.Size = new Size(128,128);
button.BackColor = Color.Transparent;
//button.FlatStyle = FlatStyle.Flat;
button.FlatAppearance.BorderSize = 0;
button.Font = new System.Drawing.Font("Trebuchet MS", 10);
button.TextAlign = ContentAlignment.TopCenter;
button.BackgroundImageLayout = ImageLayout.Zoom;
button.Left = left;
button.Top = top;
button.Text = ItemTable.Rows[i - 1][0].ToString();
//if (ItemTable.Rows[i - 1][1] != null && ItemTable.Rows[i - 1][1] != "")
if (ItemTable.Rows[i - 1][1] != null)
{
try
{
byte[] _byte = (byte[])ItemTable.Rows[i - 1][1];
MemoryStream ms = new MemoryStream(_byte);
button.BackgroundImage = System.Drawing.Image.FromStream(ms);//bilo image
}
catch { }
}
// button.Text = ItemTable.Rows[i][0].ToString();
this.Controls.Add(button);
if (i % 5 == 0)
{
left = 50;
top += button.Height + 2;
}
else
{
left += button.Width + 2;
}
// top += button.Height + 2;
}
}

37 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P8y ago
Accepted answer
try below code

Button btn = (Button)sender;
var itemAndPrice = btn.Text;

int sno = dataGridView1.Rows.Count + 1;
string nazivartikla = itemAndPrice.Split('-')[0].ToString();
double cijena = Convert.ToDouble(itemAndPrice.Split('-')[1].ToString());
string exists="":
foreach (DataGridViewRow g1 in datagrid1.Rows)
{
if(g1.Cells[1].Value.ToString().Trim()== itemAndPrice.Split('-')[0].ToString().Trim())
{
exists="y";
g1.Cells[2].Value = Convert.ToInt32(g1.Cells[2].Value) + 1;
g1.Cells[4].Value = Convert.ToInt32(g1.Cells[2].Value) * Convert.ToInt32(g1.Cells[3].Value);
}
}

if(exists!="y")
{
dataGridView1.Rows.Add(sno, nazivartikla, 1, cijena, cijena);
}
Goran Bibic8y ago
Quantity ++ solution on datagrid item added?
Rafnas T P8y ago
hi,
price multiply by 1 is same as price so you can put price,
so you can put same both places as below
Button btn = (Button)sender;
var itemAndPrice = btn.Text;

int sno = dataGridView1.Rows.Count + 1;
string nazivartikla = itemAndPrice.Split('-')[0].ToString();
double cijena = Convert.ToDouble(itemAndPrice.Split('-')[1].ToString());
dataGridView1.Rows.Add(sno, nazivartikla, 1, cijena, cijena );
Note: here no need to match with your grid column name,matter is only the position
4 th position name(cijena),
5th postion total (ukupno)

position should match not the name of your grid columns
Goran Bibic8y ago
Look
ukupno is total column....total is quantity(kolicina(1)) x price(cijena)
We dont have comand for that

When I put code (sno, itemname, 1, price, price) its ok...work

But must be quantity(kolicina(1)) x price(cijena) to column total

Some like

double cijena = Convert.ToDouble((quantity*price).ToString(0,00));
Button btn = (Button)sender;
           var itemAndPrice = btn.Text;

           int sno = dataGridView1.Rows.Count + 1;
           string nazivartikla = itemAndPrice.Split('-')[0].ToString();
           double cijena = Convert.ToDouble(itemAndPrice.Split('-')[1].ToString());
           dataGridView1.Rows.Add(sno, nazivartikla, 1, cijena, ukupno);
Rafnas T P8y ago
change positions based on your columns

datagrid1.Rows.Add(id, Item, 1, price, price);//id,item,quantity,price,total your grid columns

yellow colored one you can change based on your columns in grid
Goran Bibic8y ago
Everything is ok...just we need in column tal result price*quantity
Goran Bibic8y ago
In column total write

datagridviewtextboxcolumn {Name=ukupno, index=4}
Goran Bibic8y ago
All buttons work...i put in sql price for all items and work...one problem

Column price (in my table name is cijena) write ond datagrid when button click

datagridviewtextboxcolumn {Name=ukupno, index=5}

No have price
Rafnas T P8y ago
i think you do not have all product price, that is why arry does not have index 1,
Rafnas T P8y ago
actually id no use here, you can give column name as serial no(SNO) that will be good,
id is required in sql table and you can make it as auto generate,so that when you insert into sql id will generate automatically, that is no need to show that, and SNO no need to save in sql it is just show purpose.
Goran Bibic8y ago
Have some error line 6...i think becose of picures or I somesthing wrong

{"Index was outside the bounds of the array."}
Button btn = (Button)sender;
           var itemAndPrice = btn.Text;

           int id = dataGridView1.Rows.Count + 1;
           string Item = itemAndPrice.Split('-')[0].ToString();
           double price = Convert.ToDouble(itemAndPrice.Split('-')[1].ToString());
           dataGridView1.Rows.Add(id, nazivartikla, 1, cijena, ukupno);
Rafnas T P8y ago
change your button click event as follows

here quantity I have given 1 so price and total also same

void btn_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
var itemAndPrice = btn.Text;

int id = datagrid1.Rows.Count + 1;
string Item = itemAndPrice.Split('-')[0].ToString();
double price = Convert.ToDouble(itemAndPrice.Split('-')[1].ToString());
datagrid1.Rows.Add(id, Item, 1, price, price);//id,iem,quantity,price,total your grid columns
}
Goran Bibic8y ago
Changed
SqlCommand cmd = new SqlCommand("SELECT ime+'-'+cijena_sa_porezom, data FROM roba_usluge", con);
Goran Bibic8y ago
I am change SELECT statement
Rafnas T P
Rafnas T P8y ago
first you need to change your button text select statement as follows, because we need to get name and price from that by seprating from -
here price is your pice column name
SqlCommand cmd = new SqlCommand("SELECT ime+'-'+price, data FROM roba_usluge", con)
Rafnas T P8y ago
hi,
you can do like this when you click the button you can show the product name and price in right side of product and price text boxes these textboxes are read only and below text box you can have quantity which user can enter, below one more for toatal this is also make read only when you enter quatity the total will calculate after that one button for submit, which goes into database
Goran Bibic8y ago
Yes. Just need insert in datagrid, when user confirm then in databse
Amit Gupta8y ago
Why inserting to the database, you have to insert it in gridview first, right? When user confirms the items and its price then you have to insert it into the databse.

Please take my words in a good spirit.
You really needs to polish your skills, you are just implementing the code without understanding it (what purpose it has been created and what it is doing).

Watch videos, read tutorials, blogs that really helps you to understand code.
Don't learn the code, understand it.
Goran Bibic8y ago
I try this...but wath instead yellow marked staff??

Or without line 17 to line 21 ?
void btn_Click(object sender, EventArgs e)
       {
           Button btn = (Button)sender;
           var itemAndPrice = btn.Text;
           // Item and Price contains your product name and price, you can use this to add this to gridview

           String cs = "Data Source=.\\SQLEXPRESS;Initial Catalog=bss;Integrated Security=True";

           using (SqlConnection openCon = new SqlConnection(cs))
                   {
                       string saveStaff = "INSERT into dbo.postable (id, name, quantity, price, total) VALUES (@id,@name,@quantity,@price,@total)";
                                     //my table is datagrid1...not postable...
                       using (SqlCommand querySaveStaff = new SqlCommand(saveStaff))
                       {

                           querySaveStaff.Connection = openCon;
                           querySaveStaff.Parameters.Add("@id", SqlDbType.VarChar, 255).Value = imeTextBox.Text;
                           querySaveStaff.Parameters.Add("@name", SqlDbType.VarChar, 255).Value = imeTextBox.Text;
                           querySaveStaff.Parameters.Add("@quantity", SqlDbType.VarChar, 255).Value = sifraTextBox.Text;
                           querySaveStaff.Parameters.Add("@price", SqlDbType.VarChar, 255).Value = proizvodjacComboBox.Text;
                           querySaveStaff.Parameters.Add("@total", SqlDbType.VarChar, 255).Value = pakovanjeComboBox.Text;



                           openCon.Open();
                           querySaveStaff.ExecuteNonQuery();
                           openCon.Close();

                       }

                   }
       }
Amit Gupta8y ago
Yes, thats what I had commented on the code.
Goran Bibic8y ago
?
void btn_Click(object sender, EventArgs e)
    {
           Button btn = (Button)sender;
           var itemAndPrice = btn.Text;
           // Item and Price contains your product name and price, you can use this to add this to gridview

           //now here continue with insert into...etc????
    }
Amit Gupta8y ago
Try like this
private void Form1_Load(object sender, EventArgs e)
{
    // TODO: This line of code loads data into the 'bssDataSet.roba_usluge' table. You can move, or remove it, as needed.
    this.roba_uslugeTableAdapter.Fill(this.bssDataSet.roba_usluge);



    SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=bss;Integrated Security=True");
    //SqlCommand cmd = new SqlCommand("SELECT Concat(ime),data FROM roba_usluge", con);
    // SqlCommand cmd = new SqlCommand("SELECT (redni_broj),data FROM roba_usluge", con);
    SqlCommand cmd = new SqlCommand("SELECT (ime), data FROM roba_usluge", con);
    // SqlCommand cmd = new SqlCommand("SELECT Concat(ime, cijena_sa_porezom),data FROM roba_usluge", con);

    // Getting all the data to ItemTable
    var da = new SqlDataAdapter(cmd);
    var ItemTable = new DataTable();
    da.Fill(ItemTable);

    con.Open();
    Int32 count = ItemTable.Rows.Count;
    con.Close();

    int top = 50;
    int left = 50;
    for (int i = 1; i <= count; i++)
    // for (int i = 0; i < count; i++)
    {
        Button button = new Button();
        button.Size = new Size(128, 128);
        button.BackColor = Color.Transparent;
        //button.FlatStyle = FlatStyle.Flat;
        button.FlatAppearance.BorderSize = 0;
        button.Font = new System.Drawing.Font("Trebuchet MS", 10);
        button.TextAlign = ContentAlignment.TopCenter;
        button.BackgroundImageLayout = ImageLayout.Zoom;

        button.Left = left;
        button.Top = top;
        button.Text = ItemTable.Rows[i - 1][0].ToString();
        //if (ItemTable.Rows[i - 1][1] != null && ItemTable.Rows[i - 1][1] != "")
        if (ItemTable.Rows[i - 1][1] != null)
        {
            try
            {
                byte[] _byte = (byte[])ItemTable.Rows[i - 1][1];
                MemoryStream ms = new MemoryStream(_byte);
                button.BackgroundImage = System.Drawing.Image.FromStream(ms);//bilo image
            }
            catch { }
        }
        // button.Text = ItemTable.Rows[i][0].ToString();
        button.Click += new EventHandler(this.btn_Click);

        this.Controls.Add(button);
        if (i % 5 == 0)
        {
            left = 50;
            top += button.Height + 2;
        }
        else
        {
            left += button.Width + 2;
        }
        // top += button.Height + 2;


    }
}
void btn_Click(object sender, EventArgs e)
    {
           Button btn = (Button)sender;
           var itemAndPrice = btn.Text;
           // Item and Price contains your product name and price, you can use this to add this to gridview
    }
Goran Bibic8y ago
I need when click on DINAMICAL button to work insert into....have some instruction?
Is that complicated to explain?
Goran Bibic8y ago
This statement fill buttons
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=bss;Integrated Security=True");

         SqlCommand cmd = new SqlCommand("SELECT Concat(ime, cijena_sa_porezom),data FROM roba_usluge", con);

          // Getting all the data to ItemTable
          var da = new SqlDataAdapter(cmd);
          var ItemTable = new DataTable();
          da.Fill(ItemTable);
Goran Bibic8y ago
Amit, you not understan me...I now how to do insert into, update and delete in datagrid, in sql etc...I need help Where and how to put insert into code in my app....where? Becose are buttons are dinamics.....no have clasic butto to click and put code inside to work insert into or somethnig...undestand?

How to DINAMIC button click insert into code?

How to find dinamic button specs or somesting...?

I now clasic insert into and update
Goran Bibic8y ago
I can create sql table with same specification if neded?
Amit Gupta8y ago
It is not possible to do the whole code. catch that video (or you can search for youtube and enhance your skill). To polish your coding skills you must understand what code is doing and for what purpose it has been implemented.

Try to implement that from your end, if you stuck somewhere, then post your issue. We will try to clarify it.

Your code needs much more enhancement and optimization so that it can be object oriented and easy to maintain in future, some of the optimization requires

1. Use config file to store connection string instead of hard coding.
2. Centralized database connectivity for fetching data
3. Using SOLID principles for code implementation

Hope you got my point.
Goran Bibic8y ago
Column names

id
Item
quantiy
price
total

dataGridView1(or some name)
Amit Gupta8y ago
Okay, Got it

That means you had added columns in gridview mannually. Please avoid doing that.

Bind that with the value that came from database. What columns do you want to have in gridview and what columns are available in database. Share the column names

Edit:
Binding with gridview is having like below code
dataGridView1.DataSource = ItemTable; // where ItemTable is your tablename (datatable)

You can learn from the below video
https://www.youtube.com/watch?time_continue=2&v=Sm5mxkytfWk
Goran Bibic8y ago
How to give you more information? Table down and on pictures is datagrid items...and textboxes...but no have funciton... I use that for test...Anotehr datagrig this bigger is for insert into from dinamic button
Goran Bibic8y ago
Same code two times...I am post...
I dont understand question? Binding code for wath? From dinamic button to empty datagrid..

Binding code for buttons are in statemnt on top...?

What to to? Wher is binding code? for wath?
Amit Gupta8y ago
The same code has already been posted by you in question.

I am asking to for the binding code (that you had binded with gridview from database)
Goran Bibic8y ago
Amit
Line 8,9 and 10 bind data from sql table(that is items)
Code for dinamic button, picture I am alredy post
private void Form1_Load(object sender, EventArgs e)
       {
           // TODO: This line of code loads data into the 'bssDataSet.roba_usluge' table. You can move, or remove it, as needed.
           this.roba_uslugeTableAdapter.Fill(this.bssDataSet.roba_usluge);



           SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=bss;Integrated Security=True");

          SqlCommand cmd = new SqlCommand("SELECT Concat(ime, cijena_sa_porezom),data FROM roba_usluge", con);

           // Getting all the data to ItemTable
           var da = new SqlDataAdapter(cmd);
           var ItemTable = new DataTable();
           da.Fill(ItemTable);

           con.Open();
           Int32 count = ItemTable.Rows.Count;
           con.Close();

           int top = 50;
           int left = 50;
           for (int i = 1; i <= count; i++)
           // for (int i = 0; i < count; i++)
           {
               Button button = new Button();
               button.Size = new Size(128,128);
               button.BackColor = Color.Transparent;
               //button.FlatStyle = FlatStyle.Flat;
               button.FlatAppearance.BorderSize = 0;
               button.Font = new System.Drawing.Font("Trebuchet MS", 10);
               button.TextAlign = ContentAlignment.TopCenter;
               button.BackgroundImageLayout = ImageLayout.Zoom;

               button.Left = left;
               button.Top = top;
               button.Text = ItemTable.Rows[i - 1][0].ToString();

                   if (ItemTable.Rows[i - 1][1] != null)
                   {
                   try
                   {
                       byte[] _byte = (byte[])ItemTable.Rows[i - 1][1];
                       MemoryStream ms = new MemoryStream(_byte);
                       button.BackgroundImage = System.Drawing.Image.FromStream(ms);//bilo image
                   }
                   catch { }
               }

               this.Controls.Add(button);
               if (i % 5 == 0)
               {
                   left = 50;
                   top += button.Height + 2;
               }
               else
               {
                   left += button.Width + 2;
               }



           }
       }
Amit Gupta8y ago
Not a problem, show that code, so that it can be observed, how you are binding that from gridview so that it would be easy for you and me to get the solution.
Goran Bibic8y ago
Corect Amit.

I am post code up...when ask question...I need to create insert into code....but I dint now HOW,WHERE...etc...

I now clasic insert into...update delete...but I dont now from dynamical button..

I need help
Amit Gupta8y ago
It seems you are creating an application for Restaurant (something like) and you want to add the item into the gridview and increase its quantity (if that particular item is already added).

Am I Right? Is that you want to do?

If yes, then what is the problem in adding, please show that code, what you had done to achieve this till now.
Amit Gupta8y ago
What do you mean by insert into function for them?

Are you asking for a click event for each button?

Please always give much details as one can understand it.

Dinamic button create insert into sql function?

Rafnas T P