Please Help ExecuteReader CommandBehavior.KeyInfo

Hi i am inserting a record into the company access database and the record is being inserted correctly, but i am trying to return the Identity for the new row which is a column called 'CustomerID' and is the key column, can someone help me as it says there is no value in the dataview!

                  System.Data.OleDb.OleDbDataAdapter DACustomer = new System.Data.OleDb.OleDbDataAdapter();
                  System.Data.OleDb.OleDbCommand InsertCmd = new System.Data.OleDb.OleDbCommand();
                  InsertCmd.Connection=new System.Data.OleDb.OleDbConnection(Admin.OleDataBase.connString());
                  InsertCmd.CommandType=System.Data.CommandType.Text;
                  InsertCmd.CommandText = "INSERT INTO CustomerList (Business_Name)VALUES(@Business_Name)";
                  
                  DACustomer.InsertCommand = InsertCmd;
                  InsertCmd.Parameters.Add("@Business_Name",System.Data.OleDb.OleDbType.VarWChar);
                  InsertCmd.Parameters["@Business_Name"].Value=Customer.BusinessName.Trim();
                  InsertCmd.Connection.Open();
                  System.Data.OleDb.OleDbDataReader dr;
                  dr= InsertCmd.ExecuteReader(System.Data.CommandBehavior.KeyInfo);
                  System.Web.HttpContext.Current.Response.Write(dr.GetValue(0).ToString());InsertCmd.Connection.Close();

thanks in advance
LVL 2
Robinsonx6Asked:
Who is Participating?
 
Dom_CottonConnect With a Mentor Commented:
ok so the first one... your select statement only needs to be like this:

SelectCmd.CommandText = "select customerid from customerlist where ( Business_Name = '" + Customer.BusinessName.Trim() + "')";

Then, run that - so long as you don't have duplicates, then you should return the row that has only just been inserted.  Also, on your insert the parameter thing will not be helping. You can build the insert string into one command to save memory allocation and time and make the code shorter - never a bad thing!  Thus:

InsertCmd.CommandText = "INSERT INTO CustomerList (Business_Name)VALUES'" + Customer.BusinessName.Trim() + "')";

Secondly, the if statement in c# is as follows:

if (x == y || x == d) {
}

remember the double equals... if you are comparing strings you should use x.equals(y) - otherwise you are checking to see if the objects are the same - and they normally won't be!

Hope this is helpful,
DJ
0
 
Dom_CottonCommented:
Hi,

In your second block of code, you are creating a datareader to execute an insert statement.  As an Insert statement will return nothing (well maybe the number of rows affected), you don't actually need to use a data reader.  You should be able to just execute the code at the database.  There is no need to wait for a response.  Then, after this has happened, create another OleDbCommand object with a "select" statement obtaining the customer number from the recently entered row.

For info, once you have populated your "InsertCmd" object with enough information to run the Insert, just call "InsertCmd.ExecuteNonQuery()" - this will just run the insert and finish.... no need to return anything!

Hope this helps,
DJ
0
 
Robinsonx6Author Commented:
how do i obtain the number from the recently entered row
thanks im pulling my hair out with a tight deadline!
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.

 
Robinsonx6Author Commented:
Well i managed to sort it out thanks ive included the code below

                  SelectCmd.CommandText = "SELECT  TOP 1 PERCENT CustomerID FROM CustomerList ORDER BY ClientID DESC;";

                  InsertCmd.Connection=new System.Data.OleDb.OleDbConnection(Admin.OleDataBase.connString());
                  InsertCmd.CommandType=System.Data.CommandType.Text;
                  InsertCmd.CommandText = "INSERT INTO CustomerList (Business_Name)VALUES(@Business_Name)";

                  DACustomer.SelectCommand = SelectCmd;            
                  DACustomer.InsertCommand = InsertCmd;
                  InsertCmd.Parameters.Add("@Business_Name",System.Data.OleDb.OleDbType.VarWChar);
                  InsertCmd.Parameters["@Business_Name"].Value=Customer.BusinessName.Trim();
                  InsertCmd.Connection.Open();
                  SelectCmd.Connection.Open();
                  
                  InsertCmd.ExecuteReader();
                  int st = (int)SelectCmd.ExecuteScalar();
0
 
Robinsonx6Author Commented:
if you agree with this then i will asign yo the points
Thanks
0
 
Robinsonx6Author Commented:
sorry to be a pain but 1 that has got me for a long time is in vb you could use the or statement in if blocks how do you do this in c#

vb code     if x=y or x=d then

c# equivelent ?
0
 
Robinsonx6Author Commented:
thanks DJ
points comming over,

I have been told repeatedly by ado.net specialists that it is good code practice to allways use parameters, just thought id mention it!!

Please comment
0
 
Dom_CottonCommented:
hi...

I am by no means a professional - just do what is required...... the only thing that I say to specialists is ... while the code could be easier to read, the performance could take a dive.... (memory allocation, etc...) while memory is not as important as it once was, i always think it is nice to reduce the work wherever possible and a carefully placed comment or two would show exactly what the code is doing.

And just as we are on the subject, stored procedures and functions have to take parameters in that way...

i hope you get your project finished anyway... all the best!
DJ
0
 
Robinsonx6Author Commented:
thanks DJ, Bye
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.