Insert id in place of name into database

[VibhorG]

In my database application, there are three tables:
class : c_id , c_name
section: c_name,sec_id,sec_name
student:c_id,sec_id,roll_no,s_name.

There are 2 comboboxes: One for selecting class name and other for selecting section name .
Now I want to add enteries to student table, such that :
(i) I select class name from combo1 but corresponding c_id should be inserted in student table.
(ii)I select section name from combo2 , but corresponding sec_id should be inserted in student table.
(iii) roll_no and s_name are added through texboxes on the form .

Kindly tell me how will the c_id and sec_id will be added in student table ? :pretty:

[kvprajapati]

You may use databind feature of combobox.

Have a look,

....
            DataTable dt = new DataTable();
            dt.Columns.Add("c_id");
            dt.Columns.Add("c_name");
            dt.Rows.Add("1", "A");
            dt.Rows.Add("2", "B");

            comboBox1.DataSource = dt;
            comboBox1.DisplayMember = "c_name";
            comboBox1.ValueMember = "c_id";
            ...

Use SelectedItem property of combobox to get a row

....
     DataRowView dr = (DataRowView)comboBox1.SelectedItem;
      if (dr != null)
               MessageBox.Show(dr["c_id"] + " " + dr["c_name"]);
      .....

[VibhorG]

Hi adatapost,
I am using following code:

cmdForm.CommandText = "insert into demo(c_id,sec_id,roll_no,s_name) values (?,?,?,?)";
                cmdForm.Parameters.Add("@c_id", OleDbType.Numeric).Value = this.com_cName.SelectedValue;
                cmdForm.Parameters.Add("@sec_id", OleDbType.Numeric).Value = this.com_Section.SelectedValue;
                cmdForm.Parameters.Add("@roll_no", OleDbType.Char).Value = this.txt_Rollno.Text.Trim();
                cmdForm.Parameters.Add("@s_name", OleDbType.Char).Value = this.txt_Sname.Text;

But here I am getting error "no value given for one or more parameter"

[kvprajapati]

Use named-parameters,

cmdForm.CommandText = "insert into demo (c_id,sec_id,roll_no,s_name) values (@c_id,@sec_id,@roll_no,@s_name)";

cmdForm.Parameters.Add("@c_id", OleDbType.Numeric).Value = this.com_cName.SelectedValue;

cmdForm.Parameters.Add("@sec_id", OleDbType.Numeric).Value = this.com_Section.SelectedValue;

cmdForm.Parameters.Add("@roll_no", OleDbType.Char).Value = this.txt_Rollno.Text.Trim();

cmdForm.Parameters.Add("@s_name", OleDbType.Char).Value = this.txt_Sname.Text;

[VibhorG]

Again I am getting same error :
"no value given for one or more parameter"

Kindly provide me some other solution....

[kvprajapati]

Please verify the name of columns and table.

[VibhorG]

Please verify the name of columns and table.

I have following tables:

class -:
c_id (Data type is Number)
c_name (Data type is Text)
(c_id is primary key)

section -:
c_name (Data type is Text)
sec_id (Data type is Number)
sec_name (Data type is text)
(sec_id is primary key)

demo-:
c_id (Data type is Number)
sec_id (Data type is number)
roll_no (Data type is Text)
s_name (Data type is Text)
(roll_no is primary key)

[kvprajapati]

Post complete source code so that the best course of action can be taken.

[Geekitygeek]

Have you checked that each of your controls is returning a valid value? eg, com_cname.SelectedValue is set and correctly formatted, etc.

[VibhorG]

I have attested my project here.
When we run it form1 displays, click on form2 button .
On form2 , the main problem persists.......
Kindly help me in resolving it.......

[kvprajapati]

@VibhorG.

I am not happy with your code. You have been member since Aug-2009 and yet you are struggling with sql statement.

Correction - 1

private void section_ID()
        {
            try
            {

                int myID;
                con.Open();
                OleDbCommand cmddr = new OleDbCommand("select max(roll_no) as ids from demo  where roll_no='" + this.textBox1.Text + "'", con);
                OleDbDataReader dr = cmddr.ExecuteReader();
......
......

Correction - 2

private void button1_Click(object sender, EventArgs e)
        {
            section_ID();
            try
            {
                con.Open();
                OleDbCommand cmd = new OleDbCommand("insert into demo values(" + this.comboBox1.SelectedIndex   + "," + this.comboBox2.SelectedIndex + ",'" + this.textBox1.Text.Trim() + "','" + this.textBox2.Text + "')", con);
                cmd.ExecuteNonQuery();
                con.Close();
........
........

[VibhorG]

Thanks adatapost for helping me............
But this is my first database project so I am a newbie.

The corrections I have made, and when i am inserting values,it works fine (no error) , but when i see entries in database , new values are not adding.There is only one entry and new value is replacing original value.
Also as I have used comboBox1.SelectedIndex to enter corresponding ids for the names displaying in comboboxes. But it is not taking the same values which are in class and section table.Its is taking some different values.
What can i do for this??????

[kvprajapati]

>but when i see entries in database , new values are not adding.

Visual studio deploy (copy) your .mdb file into bin\debug folder and connectionstring in the project also point that database.

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\School.mdb");

[VibhorG]

>but when i see entries in database , new values are not adding.

Visual studio deploy (copy) your .mdb file into bin\debug folder and connectionstring in the project also point that database.

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\School.mdb");

I am checking the entries in this database only...........
But new entry is overwriting old entry in first row of table demo and other problems persists as it is..............

[kvprajapati]

Set "Copy if newer" value to the Copy to Output Directory property (in properties windows) of School.mdb file.

[Geekitygeek]

Alternatively, you can set the database files 'Copy To Output' property to 'Copy if newer'.

I had a similar problem a few years ago when i started working with databases. I was adding rows to the database file in the bin folder and couldnt figure out why they were overwritten when i ran the program.
Basically, the database file in your project file gets copied to the bin file when you build the program, the copy in the bin file is then used by the app while running within vs. If the file is set to copy always, then any changes you make while the program is running will be overwritten the next time you run it.
If you set it to copy if newer, it will only overwrite the database file if you have made changes to the original copy in your project folder.

ADD: got distracted writing my reply and adatapost pipped me at the post :p

[VibhorG]

When I am selecting "Class 11" , section "Science", Roll No "abc" and Student Name "xyz" .
In this case demo table contains:
c_id = 2
sec_id=0
roll_no=abc
s_name=xyz

But when we see "class" table, in that c_id corresponding to class 11 is 9 and in section table sec_id for "class 11 science" combination is 21.

Also for all roll_no and s_name combinations I am getting same c_id=2 and sec_id=0......

How can i resolve this problem.........

[Geekitygeek]

Wow, I'm not sure where to start with your database file. Have you studied/read anything about database design and normalisation?
You have a sub_id and sec_id in your student table...will a student only ever be part of a single section and study only one subject?
You need to map one-to-many relations, easiest way is to have a table of students, a table of subjects and a link table to match students with their subjects:

Student Table
studentID, <- Primary Key
studentName

Subject Table
subject_id, <- Primary Key
subjectName

StudentSubjects Tables
student_id, <- Composite key
subject_id <- Composite key

Also, when you bind a dataset to a control you can select the DisplayMember and ValueMember fields. The code above has combobox.SelectedIndex inserted into the database. You can't guarantee that the combobox index will match the relevant ID.
For your subject combobox, if you had the above table you would bind it with DisplayMember = "SubjectName" ValueMember = "subject_id".
You need to be using the table ID's in your queries, not the combobox indexes.

At the very least i would suggest a redesign of your database after a read through some normalization info. And maybe even think about starting over and working from the ground up after flicking through some info on databases in c# (looks like you've opted fortableadapters).

[VibhorG]

Thanks a lot for guiding me...........