?
Solved

Please Help ExecuteReader CommandBehavior.KeyInfo

Posted on 2004-10-18
9
Medium Priority
?
797 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

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!

Question has a verified solution.

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

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
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.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

839 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