How to retrieve my last ID from an Insert in a 3trier setup.

I want to retrieve my last ID from an Insert in a 3trier setup.
Every thing works fine Insert, Update, etc
However I just want to know how to bring back the last ID from  my Insert statment. I have the correct sql statment and it return the right number but how do I take that number from my data layer to the user layer.
Please modify the code below.
/////////////////////////////////// User.cs
...
protected void bt_Add_Click(object sender, EventArgs e)
    {
        try
        {
            BOCustomer cus = new BOCustomer();
            cus.cusID = txtID.Text.ToString();
            cus.LName = txtLName.Text.ToString();
            cus.FName = txtFName.Text.ToString();
            cus.Tel = txtTel.Text.ToString();
            cus.Address = txtAddress.Text.ToString();
            cus.Add();
            //lb_error.Text = DACustomer.returnvalue;
        }
        catch (Exception err)
        {
            lb_error.Text = err.Message.ToString();
        }
 
    }
...
 
 
/////////////////////////////////// BOCustomer.cs
using System;
using System.Data;
 
namespace _3tierarchitecture
{
	public class BOCustomer
	{
		//Customer properties
		private String fName;
		private String lName;
		private String cusId;
		private String address;
		private String tel;
 
		private DACustomer cusData;
 
		public BOCustomer()
		{
			cusData = new DACustomer(this);
		}
 
//		~BOCustomer()
//		{
//			cusData = nothing;
//		}
 
		public String FName
		{
	
			get
			{
				return this.fName;
			}
			set
			{
				try
				{
					this.fName = value;
 
					if (this.fName == "")
					{
						throw new Exception("Please provide first name ...");
					}
				}
				catch(Exception e)
				{
					throw new Exception(e.Message.ToString());
				}
			}
		}
 
		public String LName
		{
			get
			{
				return this.lName;
			}
			set
			{
				//could be more checkings here eg revmove ' chars
				//change to proper case
				//blah blah
				this.lName = value;
				if (this.LName == "")
				{
					throw new Exception("Please provide name ...");
				}
 
			}
		}
 
		public String cusID
		{
			get
			{
				return this.cusId;
			}
			set
			{
				this.cusId = value;
				if (this.cusID == "")
				{
					throw new Exception("Please provide ID ...");
				}
 
			}
		}
 
		public String Address
		{
			get
			{
				return this.address;
			}
			set
			{
				this.address = value;
 
				if (this.Address == "")
				{
					throw new Exception("Please provide address ...");
				}
			}
		}
 
		public String Tel
		{
			get
			{
				return this.tel;
			}
			set
			{
				this.tel = value;
				if (this.Tel == "")
				{
					throw new Exception("Please provide Tel ...");
				}
 
			}
		}
 
		public void Add()
		{
			cusData.Add(this);
		}
 
		public void Update()
		{
			cusData.Update();
		}
 
		public DataSet Find(String str)
		{
			DataSet data = null;
			data = cusData.Find(str);
			return data;
		}
 
	}
}
 
 
/////////////////////////////////// DACustomer.cs
using System;
using System.Data.OleDb;
using System.Data;
 
namespace _3tierarchitecture
{
 
	public class DACustomer
	{
		private OleDbConnection cnn;
		private BOCustomer cus;
 
		//local variables
		private String strTable="";
		private String strFields="";
		private String strValues="";
		private String insertStr="";
 
        //public static string returnvalue = autoID;
		
		//this needs to be changed based on customer table fields' Name
		private const String thisTable = "tblCustomer";
		private const String cus_ID = "CUS_ID";
		private const String cus_LName = "CUS_L_NAME";
		private const String cus_FName = "CUS_F_NAME";
		private const String cus_Tel = "CUS_TEL";
		private const String cus_Address = "CUS_ADDRESS";
		
		public DACustomer()
		{
 
		}
 
 
		public DACustomer(BOCustomer customer)
		{
			// A reference of the business object class
			cus = customer;
		}
		
		public void Add(BOCustomer cus)
		{
			OpenCnn();
 
            strTable = "Insert into " + thisTable;
 
            if (cus.cusID != null)
                strFields = cus_ID + ",";
            if (cus.LName != null)
                strFields = strFields + cus_LName + ",";
            if (cus.FName != null)
                strFields = strFields + cus_FName + ",";
            if (cus.Address != null)
                strFields = strFields + cus_Address + ",";
            if (cus.Tel != null)
                strFields = strFields + cus_Tel + ",";
 
            int strFieldsL = strFields.Length;
            strFields = strFields.Substring(0, strFieldsL - 1);
 
            if (cus.cusID != null)
                strValues = "'" + cus.cusID + "',";
            if (cus.LName != null)
                strValues = strValues + "'" + cus.LName + "',";
            if (cus.FName != null)
                strValues = strValues + "'" + cus.FName + "',";
            if (cus.Address != null)
                strValues = strValues + "'" + cus.Address + "',";
            if (cus.Tel != null)
                strValues = strValues + "'" + cus.Tel + "',";
 
            int strValuesL = strValues.Length;
            strValues = strValues.Substring(0, strValuesL - 1);
 
            String str = strTable + " (" + strFields + ") values (" + strValues + ")";
            
            OleDbCommand cmd = new OleDbCommand(str,cnn);
 
			cmd.ExecuteNonQuery();
 
            cmd.CommandText = "Select @@Identity";
            int autoID = (int)cmd.ExecuteScalar();
 
            CloseCnn();			
		}
 
 
		public void Update()
		{
			OpenCnn();
			
			String fields = "";
            if (cus.LName != null)
                fields = cus_LName + " = '" + cus.LName + "', ";
            if (cus.FName != null)
                fields = fields + cus_FName + " = '" + cus.FName + "', ";
            if (cus.Address != null)
                fields = fields + cus_Address + " = '" + cus.Address + "', ";
            if (cus.Tel != null)
                fields = fields + cus_Tel + " = '" + cus.Tel + "', ";
 
            int fieldsL = fields.Length;
            fields = fields.Substring(0, fieldsL - 2);
 
            String selectStr = "UPDATE " + thisTable + " set " + fields + " where cus_ID = '" + cus.cusID + "'";
 
			OleDbCommand cmd = new OleDbCommand(selectStr,cnn);
 
			cmd.ExecuteNonQuery();
			
			CloseCnn();
		}
 
		public DataSet Find(String argStr)
		{
			DataSet ds=null;
 
			try
			{
				OpenCnn();
			
				String selectStr = "select * from " + thisTable + " where cus_ID = '" + argStr + "'";
				OleDbDataAdapter da = new OleDbDataAdapter(selectStr,cnn);
				ds = new DataSet();
				da.Fill(ds,thisTable);
			
				CloseCnn();
 
			}
			catch(Exception e)
			{
				String Str = e.Message;
			}
 
			return ds;
		}
 
		private void OpenCnn()
		{
			String cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Documents and Settings\\DuncanH\\My Documents\\quest\\3trier\\customer.mdb;";
			cnn = new OleDbConnection(cnnStr);
			cnn.Open();
		}
 
		private void CloseCnn()
		{
			cnn.Close();
		}
 
	}
 
}

Open in new window

quest_capitalAsked:
Who is Participating?
 
Anurag AgarwalPython DeveloperCommented:
As per your requirement i have changed your code.

Enjoy!!!

Anurag
/////////////////////////////////// User.cs
...
protected void bt_Add_Click(object sender, EventArgs e)
    {
        try
        {
            int nextId;
            BOCustomer cus = new BOCustomer();
            cus.cusID = txtID.Text.ToString();
            cus.LName = txtLName.Text.ToString();
            cus.FName = txtFName.Text.ToString();
            cus.Tel = txtTel.Text.ToString();
            cus.Address = txtAddress.Text.ToString();
            cus.Add();
            nextId = cus.NextId; 
            //lb_error.Text = DACustomer.returnvalue;
        }
        catch (Exception err)
        {
            lb_error.Text = err.Message.ToString();
        }
 
    }
...
 
 
/////////////////////////////////// BOCustomer.cs
using System;
using System.Data;
 
namespace _3tierarchitecture
{
	public class BOCustomer
	{
		//Customer properties
		private String fName;
		private String lName;
		private String cusId;
		private String address;
		private String tel;
                  private int nextId;
 
		private DACustomer cusData;
 
		public BOCustomer()
		{
			cusData = new DACustomer(this);
		}
 
//		~BOCustomer()
//		{
//			cusData = nothing;
//		}
 
		public String FName
		{
	
			get
			{
				return this.fName;
			}
			set
			{
				try
				{
					this.fName = value;
 
					if (this.fName == "")
					{
						throw new Exception("Please provide first name ...");
					}
				}
				catch(Exception e)
				{
					throw new Exception(e.Message.ToString());
				}
			}
		}
 
		public String LName
		{
			get
			{
				return this.lName;
			}
			set
			{
				//could be more checkings here eg revmove ' chars
				//change to proper case
				//blah blah
				this.lName = value;
				if (this.LName == "")
				{
					throw new Exception("Please provide name ...");
				}
 
			}
		}
 
		public String cusID
		{
			get
			{
				return this.cusId;
			}
			set
			{
				this.cusId = value;
				if (this.cusID == "")
				{
					throw new Exception("Please provide ID ...");
				}
 
			}
		}
 
public int NextId
		{
			get
			{
				return this.nextId;
			}
			set
			{
				this.nextId = value; 
			}
		}
 
		public String Address
		{
			get
			{
				return this.address;
			}
			set
			{
				this.address = value;
 
				if (this.Address == "")
				{
					throw new Exception("Please provide address ...");
				}
			}
		}
 
		public String Tel
		{
			get
			{
				return this.tel;
			}
			set
			{
				this.tel = value;
				if (this.Tel == "")
				{
					throw new Exception("Please provide Tel ...");
				}
 
			}
		}
 
		public void Add()
		{
		    nextId = cusData.Add(this);
		}
 
		public void Update()
		{
			cusData.Update();
		}
 
		public DataSet Find(String str)
		{
			DataSet data = null;
			data = cusData.Find(str);
			return data;
		}
 
	}
}
 
 
/////////////////////////////////// DACustomer.cs
using System;
using System.Data.OleDb;
using System.Data;
 
namespace _3tierarchitecture
{
 
	public class DACustomer
	{
		private OleDbConnection cnn;
		private BOCustomer cus;
 
		//local variables
		private String strTable="";
		private String strFields="";
		private String strValues="";
		private String insertStr="";
 
        //public static string returnvalue = autoID;
		
		//this needs to be changed based on customer table fields' Name
		private const String thisTable = "tblCustomer";
		private const String cus_ID = "CUS_ID";
		private const String cus_LName = "CUS_L_NAME";
		private const String cus_FName = "CUS_F_NAME";
		private const String cus_Tel = "CUS_TEL";
		private const String cus_Address = "CUS_ADDRESS";
		
		public DACustomer()
		{
 
		}
 
 
		public DACustomer(BOCustomer customer)
		{
			// A reference of the business object class
			cus = customer;
		}
		
		public int Add(BOCustomer cus)
		{
			OpenCnn();
 
            strTable = "Insert into " + thisTable;
 
            if (cus.cusID != null)
                strFields = cus_ID + ",";
            if (cus.LName != null)
                strFields = strFields + cus_LName + ",";
            if (cus.FName != null)
                strFields = strFields + cus_FName + ",";
            if (cus.Address != null)
                strFields = strFields + cus_Address + ",";
            if (cus.Tel != null)
                strFields = strFields + cus_Tel + ",";
 
            int strFieldsL = strFields.Length;
            strFields = strFields.Substring(0, strFieldsL - 1);
 
            if (cus.cusID != null)
                strValues = "'" + cus.cusID + "',";
            if (cus.LName != null)
                strValues = strValues + "'" + cus.LName + "',";
            if (cus.FName != null)
                strValues = strValues + "'" + cus.FName + "',";
            if (cus.Address != null)
                strValues = strValues + "'" + cus.Address + "',";
            if (cus.Tel != null)
                strValues = strValues + "'" + cus.Tel + "',";
 
            int strValuesL = strValues.Length;
            strValues = strValues.Substring(0, strValuesL - 1);
 
            String str = strTable + " (" + strFields + ") values (" + strValues + ")";
            
            OleDbCommand cmd = new OleDbCommand(str,cnn);
 
			cmd.ExecuteNonQuery();
 
            cmd.CommandText = "Select @@Identity";
            int autoID = (int)cmd.ExecuteScalar();
 
            CloseCnn();	
              return autoid;		
		}
 
 
		public void Update()
		{
			OpenCnn();
			
			String fields = "";
            if (cus.LName != null)
                fields = cus_LName + " = '" + cus.LName + "', ";
            if (cus.FName != null)
                fields = fields + cus_FName + " = '" + cus.FName + "', ";
            if (cus.Address != null)
                fields = fields + cus_Address + " = '" + cus.Address + "', ";
            if (cus.Tel != null)
                fields = fields + cus_Tel + " = '" + cus.Tel + "', ";
 
            int fieldsL = fields.Length;
            fields = fields.Substring(0, fieldsL - 2);
 
            String selectStr = "UPDATE " + thisTable + " set " + fields + " where cus_ID = '" + cus.cusID + "'";
 
			OleDbCommand cmd = new OleDbCommand(selectStr,cnn);
 
			cmd.ExecuteNonQuery();
			
			CloseCnn();
		}
 
		public DataSet Find(String argStr)
		{
			DataSet ds=null;
 
			try
			{
				OpenCnn();
			
				String selectStr = "select * from " + thisTable + " where cus_ID = '" + argStr + "'";
				OleDbDataAdapter da = new OleDbDataAdapter(selectStr,cnn);
				ds = new DataSet();
				da.Fill(ds,thisTable);
			
				CloseCnn();
 
			}
			catch(Exception e)
			{
				String Str = e.Message;
			}
 
			return ds;
		}
 
		private void OpenCnn()
		{
			String cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Documents and Settings\\DuncanH\\My Documents\\quest\\3trier\\customer.mdb;";
			cnn = new OleDbConnection(cnnStr);
			cnn.Open();
		}
 
		private void CloseCnn()
		{
			cnn.Close();
		}
 
	}
 
}

Open in new window

0
 
ororioleCommented:
Normally if you are using an identity statement you do not want the user to be able to enter an ID. Trying to insert a ID in a column defined with identity will result in an error. Let the identity provide you with the ID.
So I would:
      try
        {
            BOCustomer cus = new BOCustomer();
            cus.cusID = txtID.Text.ToString(); <-----remove this line.
            cus.LName = txtLName.Text.ToString();
---------------------------------------------------
in DACustomer: public void Add(BOCustomer cus)
      ...      
int autoID = (int)cmd.ExecuteScalar();
cus.cusID = autoID.ToString();  <--this returns it to your user layer
------------------------------------------------

There are other things I would recommend changing, but its a bit fragile so I dont want to break what you have working. I would recommend you change your sql command to a stored proc and return the @@Identity as a return value from the stored procedure. By making two commands you run the risk of losing the @@identity value if anything is executed by someone in between your commands.


 
0
 
quest_capitalAuthor Commented:
anuragal:

Do you mean for
return autoid;
to be
return nextId;
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Anurag AgarwalPython DeveloperCommented:
From function
public int Add(BOCustomer cus)
we will return
return autoid

And in function

public void Add()
            {
                nextId = cusData.Add(this);
            }

the return value will be will be assigned to nextid

Try to debug the application & see what happens :-)

Anurag
0
 
quest_capitalAuthor Commented:
anuragal:

Sweet that worked well and you get the points, but since we are on topic can you show me the syntax on how to get back mulitple fields of data like if I wanted to get back lets say ID and First Name from a stored procedure.

thx
again
0
 
Anurag AgarwalPython DeveloperCommented:
Yeah Sure...

Here is the code for retreiving selective fields from database. through Stored Procedure

This SP will return you only the ID & NAME then in C# code you can read them through datareader.

Anurag

(Request->Can you specify ZONE while giving away the points if yes then please select the "Programming for ASP.NET" zone. Thanks)
SqlCommand myCMD = new SqlCommand("MyStoredProc", nwindConn);
myCMD.CommandType = CommandType.StoredProcedure;
nwindConn.Open();
SqlDataReader myReader = myCMD.ExecuteReader();
BOCustomer customer = new BOCustomer();
while (myReader.Read())
{
    customer.cusID = myReader["CUS_ID"].ToString();
    customer.FName = myReader["CUS_F_NAME"].ToString();
}
 
myReader.Close();
nwindConn.Close();
 
 
**********************SP************************
Create a Stored Procedure with a query like
**********************************************
Select CUS_ID, CUS_F_NAME from tblCustomer

Open in new window

0
 
quest_capitalAuthor Commented:
anuragal:

I guess I was not clear I understand how to get data back from a stored procedure, the problem is geting that data back from a 3tier enviornment.
Lets say I had the code below how do I setup the syntax for the  Data(the return values) , Busniss and Presentation layer.
/////////////////////////////////////// DACustomer
public int Add(BOCustomer cus)
		{
.......
cmd.CommandType = CommandType.StoredProcedure;
            OleDbParameter oParam1 = cmd.Parameters.AddWithValue("@autoid", 0);
            OleDbParameter oParam2 = cmd.Parameters.AddWithValue("@lname", 0);
            oParam1.Direction = ParameterDirection.Output;
            oParam2.Direction = ParameterDirection.Output;
            OpenCnn();
 
			cmd.ExecuteNonQuery();
........
}

Open in new window

0
 
Anurag AgarwalPython DeveloperCommented:
for getting back the data from Data access layer to business & presentation you have to create a function that will return a customer type of object

like

public BOCustomer FetchCustomer()
{
   //Here you will fetch the data from database & then iterate the datareader & fill the customer object then return that object back
}

same type of function in Business layer and from this function call Data access layer function. And same type of code in presentation layer.

Anurag
0
 
quest_capitalAuthor Commented:
anuragal:

I figured it out  see below:
Thx for all you help
////////////////////// DACustomer
...
cmd.CommandText = "Select @@Identity";
            int autoID = (int)cmd.ExecuteScalar();
            string ver = "mydesktop";
 
            object[] mydata = { autoID, ver };
 
            CloseCnn();
 
            return mydata;
...
//////////////// BOCustomer
...
private object[] outParam;
...
public object[] OutParam
        {
            get
            {
                return this.outParam;
            }
            set
            {
                this.outParam = value;
            }
        }
...
public void Add()
		{
            outParam = cusData.Add(this);
		}
////////////////////////// Output
lb_error.Text = Convert.ToString(cus.OutParam[0]) + " : " + Convert.ToString(cus.OutParam[1]);

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.