?
Solved

Please Help ExecuteReader CommandBehavior.KeyInfo

Posted on 2004-10-18
9
Medium Priority
?
794 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

777 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