Solved

Please Help ExecuteReader CommandBehavior.KeyInfo

Posted on 2004-10-18
9
788 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:Robinsonx6
  • 6
  • 3
9 Comments
 
LVL 2

Expert Comment

by:Dom_Cotton
ID: 12356848
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
 
LVL 2

Author Comment

by:Robinsonx6
ID: 12357061
how do i obtain the number from the recently entered row
thanks im pulling my hair out with a tight deadline!
0
 
LVL 2

Author Comment

by:Robinsonx6
ID: 12357346
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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 2

Author Comment

by:Robinsonx6
ID: 12357353
if you agree with this then i will asign yo the points
Thanks
0
 
LVL 2

Author Comment

by:Robinsonx6
ID: 12357376
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
 
LVL 2

Accepted Solution

by:
Dom_Cotton earned 500 total points
ID: 12357534
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
 
LVL 2

Author Comment

by:Robinsonx6
ID: 12357570
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
 
LVL 2

Expert Comment

by:Dom_Cotton
ID: 12357613
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
 
LVL 2

Author Comment

by:Robinsonx6
ID: 12357642
thanks DJ, Bye
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question