index > Windows Forms Sample Applications > How to Create a loop and save changes

How to Create a loop and save changes

Hi, I'm trying to do an screen which it has to display a purchase order # and increase the number by one every time you add a new purchase order.

How can i accomplish that? Do i need two tables, one to display the po# save all the changes andanother one to savethe last po# created ?

Do i need to create a primary key and a foreing key?

I'm working with Visual Basic 2008 and SQL 2005 but i have never program in this enviroment.

Please someone would explain this on details to me

GianCarlos Thursday, July 17, 2008 6:52 PM

Hi GianCarlos,

Do you mean you want to display the sequence of each purchase order and get an increased number for a new record.

I think you have many ways to do so.

1. Add an auto increasing column to the purchase order table, you can use the sql script: alter table YourTable add ID int identity(start, increment)

2. Decide the next number by yourself, e.g. create a method called GetNextID()

Code Snippet

int GetNextID()

{

string sql = "select max(id) from tab_purchase";

DataTable dt = new DataTable();

SqlConnection sqlConn = new SqlConnection("Your Connection String");

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConn);

try

{

sqlConn.Open();

sqlDataAdapter.Fill(dt);

sqlConn.Close();

}

catch(Exception exp)

{

MessageBox.Show(exp.Message);

}

int currentMaxID = Convert.ToInt32(dt.Rows[0][0]);

return currentMaxID + 1;

}

After you get the nextID, you can insert into new record with this id.

Either way is okay and I prefer to the first way. It’s the most convenience one.

If you have any more question, please feel free to let me know.

Best Regards,

Kira Qian

Kira Qian Monday, July 21, 2008 5:49 AM

Hi GianCarlos

The GetNextID() method should be the member of your form, neither in the Load event nor in the Button Click event. You can call it when you want to add a row. Here is the structure of the Form:

Code Snippet

publicclass Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void buttonAddRow_Click(object sender, EventArgs e)

{

int nextID = this.GetNextID();

// write insert code here.

}

private int GetNextID()

{

string sql = "select max(id) from tab_purchase";

DataTable dt = new DataTable();

SqlConnection sqlConn = new SqlConnection("Your Connection String");

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConn);

try

{

sqlConn.Open();

sqlDataAdapter.Fill(dt);

sqlConn.Close();

}

catch (Exception exp)

{

MessageBox.Show(exp.Message);

}

int currentMaxID = Convert.ToInt32(dt.Rows[0][0]);

return currentMaxID + 1;

}

If you have any more question, please feel free to let me know.

Best Regards,

Kira Qian

Windows Forms General FAQs
Windows Forms Data Controls and Databinding FAQs

Kira Qian Tuesday, July 22, 2008 8:50 AM

Hi GianCarlos,

Do you mean you want to display the sequence of each purchase order and get an increased number for a new record.

I think you have many ways to do so.

1. Add an auto increasing column to the purchase order table, you can use the sql script: alter table YourTable add ID int identity(start, increment)

2. Decide the next number by yourself, e.g. create a method called GetNextID()

Code Snippet

int GetNextID()

{

string sql = "select max(id) from tab_purchase";

DataTable dt = new DataTable();

SqlConnection sqlConn = new SqlConnection("Your Connection String");

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConn);

try

{

sqlConn.Open();

sqlDataAdapter.Fill(dt);

sqlConn.Close();

}

catch(Exception exp)

{

MessageBox.Show(exp.Message);

}

int currentMaxID = Convert.ToInt32(dt.Rows[0][0]);

return currentMaxID + 1;

}

After you get the nextID, you can insert into new record with this id.

Either way is okay and I prefer to the first way. It’s the most convenience one.

If you have any more question, please feel free to let me know.

Best Regards,

Kira Qian

Kira Qian Monday, July 21, 2008 5:49 AM

Hi thanks

I do have a question

Do i need to paste the method you gave me in the load event handleror in the add botton event handler?

GianCarlos Monday, July 21, 2008 1:12 PM

Hi GianCarlos

The GetNextID() method should be the member of your form, neither in the Load event nor in the Button Click event. You can call it when you want to add a row. Here is the structure of the Form:

Code Snippet

publicclass Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void buttonAddRow_Click(object sender, EventArgs e)

{

int nextID = this.GetNextID();

// write insert code here.

}