C#-检索最大的MySQL列值,增量为1,并在INSERT语句中使用
本文关键字:并在 INSERT 语句 检索 MySQL C#- 列值 | 更新日期: 2023-09-27 18:27:42
我使用的是c#、MySQL和visual studio 2015。
我有一个:
- 名为"cp_users"的数据库
- 名为"users"的表
- 名为"account_no"的列
account_no列是int(11)和主键。
我只是想得到最高帐号的值,加1,然后在我的语句中使用新值插入新的用户数据,从而确保帐号永远不会冲突。
信息:
public partial class Form1 : Form
{
MySqlConnection mcon = new MySqlConnection();
public Form1()
{
InitializeComponent();
mcon.ConnectionString = "datasource=166.XXX.XXX.XXX;port=3306;initial catalog=cp_users; username=XXXXXX;password=XXXXXX";
}
我遇到问题的代码:
if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && textBox6.Text != "" && listBox1.SelectedItem != null)
{
mcon.Open();
//Upload New User Information to 'users' Database
try
{
//CODE TO RECALL LARGEST ACCOUNT NUMBER AS reader3 VALUE,
//INCREASE IT BY 1, ASSIGN IT AS A STRING
//FOR USE BELOW (NOT WORKING)
MySqlCommand mda3 = new MySqlCommand();
mda3.Connection = mcon;
mda3.CommandText = "SELECT max(account_no) value FROM users";
MySqlDataReader reader3 = mda3.ExecuteReader();
reader3 = reader3++;
//UPLOAD NEW USER DATA INCLUDING 'reader3' VALUE FOR 'account_no'
MySqlCommand mda2 = new MySqlCommand();
mda2.Connection = mcon;
mda2.CommandText = ("insert into cp_users.users(account_no, first_name, last_name, email_1, company_industry, user_password) values('" + reader3 + "','" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + listBox1.Text + "', '" + textBox5.Text + "');");
MySqlDataReader reader2 = mda2.ExecuteReader();
mcon.Close();
this.Hide();
Form2 frm4 = new Form2();
frm4.ShowDialog();
MessageBox.Show("Registration Successful - You can now login to our Desktop, Web and App Interfaces", "Welcome to ConnectPlanet");
}
catch
{
MessageBox.Show("New Registration Failed - We're Sorry, Please Contact Customer Support", "Oops!");
mcon.Close();
}
任何建议都很感激,我想这对更有经验的人来说很简单,但我似乎在网上或我的(小)藏书中找不到答案。
您正在寻找一个AUTO_INCREMENT
列。请参阅:使用AUTO_INCREMENT
CREATE TABLE users
(
account_no INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email_1 VARCHAR(100) NOT NULL,
company_industry VARCHAR(100) NOT NULL,
user_password VARCHAR(128) NOT NUL
);
因此,在插入数据时甚至不需要指定id列:
insert into cp_users.users(first_name, last_name, email_1, company_industry, user_password) values( ....