Insert data in AutoNumber DataType (Access Database).

Hi to all.

Normally we insert data in table.but when we need some unique identity we use primary key and make AutoNumber datatype in access database.

so when we insert data in database not add column in query of insert.

Like table name is Test and columns are Id=>AutoNumber,Name=>Text

Query for insert is ="Insert into Test (Name) Values ('TestName')"

Hear we not mention Id because Id is AutoNumber. It automatically set last id increment 1.and if we want the inserted id in database after insert query make one code "Select @@Identity";
to execute.

        public int InsertData(string sQuary)
        {
            try
            {
                //for get last inserted id
                string sQryIdentity = "Select @@Identity";
                int Id = 0;
               
               //check connection
                if (dbConnection.State != ConnectionState.Open)
                {
                    dbConnection.Open();
                }
                //create command
                OleDbCommand cmd = new OleDbCommand(sQuary);
                //set connection
                cmd.Connection = dbConnection;
                try
                {
                    //execute query
                    cmd.ExecuteNonQuery();
                    //set new command text for get identity
                    cmd.CommandText = sQryIdentity;
                    //get identity
                    Id = (int)cmd.ExecuteScalar();

                    //close connection
                    dbConnection.Close();
                    return Id;
                }
                catch (OleDbException exo)
                {
                    dbConnection.Close();
                    throw exo;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
This function return Id of inserted data in database.

Just send query string in function and return Id of data.

Thanks for read.

Comments

Post a Comment

Popular posts from this blog

Populate combo box for month and year in c#

Image
Hi to all. In this post we populate combo box for month and year. First take two combo box one for month and second for year.Name them  CmbMonth and  CmbYear . we create function for fill data in combo box. For month combo box         private void Fill_CmbMonth()         {             try             {                              //clear all data from combobox                 CmbMonth.Items.Clear();                 //add default item                 CmbMonth.Items.Add("Select");                 //fill array from System.Globalization.DateTimeFormatInfo.InvariantInfo                 var ...
Read more

How to get month days count from date or month and year

Hi all . Window form application need some calculation of date and days,so require no of days in current month or any date of month. So for that we use on c# in build function to get days count. System.DateTime.DaysInMonth(Year(int), Month(int)); This function output number of days in integer. And we need to input year and month number in integer value. If you have date then we get month and year from it. DateTime NowDate = new DateTime(); int iMonth=NowDate.Month; int iYear=NowDate.Year; If you have month name so need to get number of month int iMonth=DateTime.ParseExact("MonthName", "MMMM", CultureInfo.InvariantCulture).Month For this code need to include using System.Globalization; library. Thanks to read this post. comment if any problem in code.
Read more

CRUD Operations in win form application with access database

Hi to all, In first post we connect with access database now in this post we make operations in database. First we create one method that help to execute sql query in database. public bool Executquary (string sQuary)         {             try             { //check connection                 if (dbConnection.State != ConnectionState.Open)                 {                     dbConnection.Open();                 } //create command                 OleDbCommand cmd = new OleDbCommand(sQuary);                 cmd.Connection = dbConnection;                 try                 { /...
Read more