如何在Windows窗体中同时更新表和获取价值 [英] how to update table and take value at same time in windows forms
问题描述
我有一个表格，其中有两个用于州和城市的组合框.我现在已插入所有州，我希望用户通过选择适当的州来插入城市.但是问题是，当我单击插入"按钮时，city-id在城市表中获取空值，而city-id是主要的，因此它不能为空
所以请帮助.
1)如何在具有自动分配的城市ID的表格中插入城市，并且每个城市的城市应该唯一
2)如何在组合框中显示最近添加的城市
I have form where i have 2 combo boxes for state and city . I have inserted all states now i want user to insert city by selecting appropriate state. But the thing is when i click insert button city-id gets null value in city table and city-id is primary so it can''t be null
So plz hepl..
1) How to insert city in table with autoassigned city id and it should be unique for each city
2) How to show recently added city into combo box
推荐答案
按钮city-id会获得null值，因为它是主要值，您不会插入null. br/>
我认为您要执行的操作是在表单上的文本框中插入用户输入的城市名称.
您应该做的是将其写入数据库，如下所示:
1)确保您的数据库表字段城市ID主键没有自动递增(在数据库中执行此操作)
2)每当您在数据库表中写入新城市名称时，都使用如下查询增加主键值:
插入城市(id，cityname)值((从城市中选择max(id)+1)，``Sidney'');
也许您调用数据库表的其他名称，但是这里是城市，主键名为id，城市名称的字段为.. cityname ...
the button city-id gets null value as it is primary it does not insert null, you say.
I think what you are trying to do is to insert a city name that a user has written in a textBox on a form.
What you should do is to Write it to the database as follows:
1) Make sure your database table field City ID primary key is not automatically incremented (this you do in the database)
2) Whenever you write a New city name into the database table, increment the primary key value With a query like this:
insert into cities (id,cityname) values ( (select max(id)+1 from cities), ''Sidney'');
maybe you Call Your database table something else but here it is cities, and the primary key is named id, the Field for the city name is.. cityname...
这是您的解决方案
数据库设计
在我在以下示例中考虑过的sql server数据库上运行以下查询
请注意，您可能必须在以下代码中更新服务器名称和数据库名称的连接字符串
Here is your solution
Database design
Run below query on sql server database which I have considered in below example
Please note that you may have to update connection string in below code for server name and database name
CREATE TABLE tblStateMaster (Id INT IDENTITY(1,1),State NVARCHAR(50))
CREATE TABLE tblCityMaster (Id INT IDENTITY(1,1),StateId Int, City NVARCHAR(50))
--
INSERT INTO tblStateMaster(State)
SELECT 'State1'
UNION
SELECT 'State2'
1)创建一个Windows窗体并按照此处的说明添加控件:
1.1)添加一个组合框-cmbState
1.2)添加一个组合框-cmbCity
1.3)添加一个按钮-btnAddCity
2)双击表单中的任意位置以打开CS代码窗口
3)在表单加载后添加以下三个声明
1) Create one windows form and add controls as per instruction here:
1.1) Add one combbox - cmbState
1.2) Add one combobox - cmbCity
1.3) Add one button - btnAddCity
2) Double click anywhere in the form to open cs code window
3) Add below three declarations after form load
public static OleDbConnection Conn = new OleDbConnection();
public static string ATCregionId;
4)之后添加以下三种方法
-连接到SQL Server数据库
4) Add below three methods after that
-- Connect to sql server database
public static bool Connect()
{
string conn = @"Provider=SQLNCLI;Server=sqlservertest;Database=testdb;Uid=sa;Pwd=sa123;";
if (Conn.State == System.Data.ConnectionState.Open)
Conn.Close();
Conn.ConnectionString = conn;
Conn.Open();
return true;
}
-刷新状态组合框，城市组合框
-- Refresh state combo box , City combo box
public void RefreshState()
{
cmbstate.Items.Clear();
OleDbDataReader reader;
int dbRecords;
dbRecords = 0;
OleDbCommand command = new OleDbCommand();
command.CommandText = "SELECT State FROM tblStateMaster";
try
{
Connect();
command.Connection = Conn;
reader = command.ExecuteReader();
int index = 0;
while (reader.Read())
{
string result = reader.GetString(0);
cmbstate.Items.Add(result.ToString());
index++;
}
reader.Close();
Conn.Close();
}
catch (Exception ex)
{
}
}
public void RefreshCity()
{
cmbcity.Items.Clear();
OleDbDataReader reader;
int dbRecords;
dbRecords = 0;
OleDbCommand command = new OleDbCommand();
command.CommandText = "SELECT City FROM tblCityMaster";
try
{
Connect();
command.Connection = Conn;
reader = command.ExecuteReader();
int index = 0;
while (reader.Read())
{
string result = reader.GetString(0);
cmbcity.Items.Add(result.ToString());
index++;
}
reader.Close();
Conn.Close();
}
catch (Exception ex)
{
}
}
5)添加代码以保存城市按钮
5) Add code to save city button
private void button1_Click(object sender, EventArgs e)
{
Connect();
OleDbCommand selectCommand = new OleDbCommand();
OleDbDataReader reader;
int dbRecords;
dbRecords = 0;
selectCommand.CommandText = "SELECT City FROM tblCityMaster WHERE City='" + cmbcity + "'";
selectCommand.Connection = Conn;
reader = selectCommand.ExecuteReader();
if (reader.Read())
MessageBox.Show("City already added");
else
{
OleDbCommand insertCommand = new OleDbCommand("INSERT INTO tblCityMaster( StateId,City) SELECT sm.Id, '"+cmbcity.Text+"' FROM tblStateMaster sm WHERE State='" + cmbstate.Text + "'", Conn);
try
{
int count = insertCommand.ExecuteNonQuery();
Conn.Close();
RefreshCity();
}
catch (Exception ex)
{
}
}
}
希望这可以帮助.如果是，则投票并接受答案:)
Hope this helps. If yes then vote and accept the answer :)
这篇关于如何在Windows窗体中同时更新表和获取价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
