CODEWITHSUNDEEP
c#ms-access
ansie23
ansie23

Reputation: 11

How can I add auto numbering in visual c# 2010? using ms Access?

I'm using autonumber but it doesn't work for me. I want auto numbering in my StudentID number.

OleDbCommand system = new OleDbCommand();
system.CommandType = CommandType.Text;
system.CommandText = 
"insert into Student(ID, Lastname, Middlename, Firstname, Address, DateofBirth, Birthplace, Contact_number, emailaddress, guardian_name, Guardian_contact) values ('" + txtStudentIDnumber.Text + "','" + txtlastname.Text + "','" + txtfirstname.Text + "','" + 
txtmiddlename.Text + "','" + txtaddress.Text + "','" + txtdateofbirth.Text + "','" + txtbirthplace.Text + "','" + txtcontactnumber.Text + "','" + txtemailaddress.Text + "','" + txtGuardianname.Text + "','" + txtguardiancontact.Text + "')";
system.Connection = mydatabase;



if (MessageBox.Show("Save data?", "Confirm Save", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

Upvotes: 0

Views: 1496

Answers (3)

Rohan Büchner
Rohan Büchner

Reputation: 5393

Your ID column should be setup to be an identity (in the database), then you should omit it from the insert.

http://forums.asp.net/t/1492834.aspx/1

UPDATE

I suspect your StudentIdNumber is an actual stateissued ID number, and what you're looking for is an identity field.

You'll need to add an identity column to your table, either using the table designer you used to create the table, or using a script

CREATE TABLE Student(
   ID int identity, 
   StudentIdNo varchar(10), 
   Lastname varchar(10), 
   Firstname varchar(10), 
   Middlename varchar(10), 
   CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID)
)

This will be the format of your insert statement, notice there is no ID field

"INSERT INTO Student (StudentIdNo, Lastname, Firstname, Middlename) VALUES (?)"

...in your case, after adding a identity field

   OleDbCommand comm = new OleDbCommand();
   comm.CommandType = CommandType.Text;

   comm.CommandText = 
       @"insert into Student(StudentIdNo, Lastname, Firstname, Middlename) 
                     values (@StudentIdNo, @Lastname, @Firstname, @Middlename)"; 

   comm.Parameters.AddWithValue("@StudentIdNo", txtStudentIdNo.Text);
   comm.Parameters.AddWithValue("@Lastname", txtlastname.Text);
   comm.Parameters.AddWithValue("@Firstname", txtfirstname.Text);
   comm.Parameters.AddWithValue("@Middlename", txtmiddlename.Text);

   comm.Connection = mydatabase;

Upvotes: 0

Sirwan Afifi
Sirwan Afifi

Reputation: 10824

first you should specify identity Column like this :

enter image description here

then your code :

 OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\db.accdb");
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = string.Format("insert into Student(LastName,...) values('{0}',...)",txtLastName.Text.Trim(),...);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

Upvotes: 2

akton
akton

Reputation: 14376

It is difficult to determine the issue without the database schema or the error message. However, the issue is probably because you are trying to insert a value into the ID column when it may have auto numbering (also known as a counter) enabled. Change:

system.CommandText = "insert into Student(ID, Lastname, ..."; // And so on

to 

system.CommandText = "insert into Student(Lastname, ..."; // And so on

Also consider changing the query to be a parameterized query (such as that mentioned in incorrect syntax near 's'. unclosed quotation mark after the character string ')') rather than using concatenation to avoid SQL injection and escaping issues.

Upvotes: 2

Related Questions