Solved

Please Help ExecuteReader CommandBehavior.KeyInfo

Posted on 2004-10-18
9
793 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

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

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

728 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