how to update table and take value at same time in windows forms

Question

1.00/5 (1 vote)

See more:

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

Posted 31-May-14 10:07am

Member 9671810

Updated 31-May-14 20:33pm

RDBurmon

v2

Add a Solution

Comments

[no name] 31-May-14 17:20pm

Help you with what? You do realize that we cannot see your code, your project, your database, your monitor or read your mind, right?

ZurdoDev 31-May-14 22:22pm

What is your problem?

Member 9671810 31-May-14 22:59pm

The problem is i cannot insert city at the same time i register my user

RDBurmon 1-Jun-14 2:34am

Hope my solution works for you. :)

Member 9671810 3-Jun-14 5:30am

plz read read my code and problem posted above..

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

netfed · Answer 1 · 2014-05-31T19:09:00

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...

RDBurmon · Answer 2 · 2014-05-31T20:28:00

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

sq

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) 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

C#

public static OleDbConnection Conn = new OleDbConnection();
public static string ATCregionId;

4) Add below three methods after that

-- Connect to sql server database

C#

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

C#

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) Add code to save city button

C#

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 :)

how to update table and take value at same time in windows forms

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0