473,322 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Selecting from the Table by specifying the row number

Hi all,
Can any one help me how to get a field (auto-number field)
after I add a new row to the table using ExecuteNonQuery() method

Expand|Select|Wrap|Line Numbers
  1. string insertStmnt = @"INSERT INTO Student (stName) VALUES (@stName)";
  2. OleDbCommand comm = new OleDbCommand(insertStmnt, myconn);
  3. OleDbParameter param = new OleDbParameter("@stName",stNameVal);
  4. comm.Parameters.Add(param);
  5. int rowNum = comm.ExecuteNonQuery();
so, how do we select the rowNum row from a table?
I'm using MS Access

plz help me...

thanks in advanced ....
Aug 30 '07 #1
8 1850
Plater
7,872 Expert 4TB
With a second SQL query.

I recently have started moving towards stored procedures that do both queries at once.
Aug 30 '07 #2
With a second SQL query.

I recently have started moving towards stored procedures that do both queries at once.
thanks Plater Can you show me how I can do it.
Aug 30 '07 #3
Plater
7,872 Expert 4TB
Well I don't know is MS Access has stored procedures or not.

But what you would do is write a second SELECT sql statement that would grab the field from what you just inserted.
Aug 30 '07 #4
dip_developer
648 Expert 512MB
Hi all,
Can any one help me how to get a field (auto-number field)
after I add a new row to the table using ExecuteNonQuery() method

Expand|Select|Wrap|Line Numbers
  1. string insertStmnt = @"INSERT INTO Student (stName) VALUES (@stName)";
  2. OleDbCommand comm = new OleDbCommand(insertStmnt, myconn);
  3. OleDbParameter param = new OleDbParameter("@stName",stNameVal);
  4. comm.Parameters.Add(param);
  5. int rowNum = comm.ExecuteNonQuery();
so, how do we select the rowNum row from a table?
I'm using MS Access

plz help me...

thanks in advanced ....
in any time you can use a second sql-query....if your auto number are ascending then you can easily retrieve it with
SELECT MAX(myAutonum_id) FROM mytable

but like sql-server Access also support @@IDENTITY

read this.....................I have found it in another site....I have no link for that site......but i have the functions saved


We have a table in MsAccess like :
Test, Fields (id=autoinc, name=text);
First we have to have a function like the one below :
function GetLastInsertID : integer;
begin
// datResult = TADODataSet
datResult.Active := False;
datResult.CommandText := 'select @@IDENTITY as [ID]';
datResult.Active := True;
Result := datResult.FieldByName('id').AsInteger;
datResult.Active := False;
end;
Now before getting the last inserted record record id = autoincrement field, in other words calling the above function. You have to do a SQL insert like the following
procedure InsertRec;
begin

// datCommand = TADOCommand
datCommand.CommandText := 'insert into [test] ( [name] ) values ( "Test" )';
datCommand.Execute;
end;
Now if we like to know which is the last autoinc value ( notice that the getlastinsertid proc. only works after the insertrec proc)

procedure Test;
begin
InsertRec;
Showmessage(format('lastinsertid : %d',[GetLastInsertID]));
end;
Aug 31 '07 #5
Shashi Sadasivan
1,435 Expert 1GB
you dont want to use a seperate method to get the idendity...
write a seperate method to get the row_no for the field you entered.

If you use the idendity approach, you may get wrong values due to concurrency.

Moreover if u get the row_no for the field you entered, u may use the code later on for other things.

MS Access i dont think supports stored procedures.
Aug 31 '07 #6
I have written a second sql-statement to get the last auto-number field entered
after the insert method like the following method

Expand|Select|Wrap|Line Numbers
  1.       private int LastID()
  2.         {
  3.             //Find highest ID in the  table
  4.             OleDbCommand comm = new OleDbCommand("SELECT MAX(ID) FROM Student",myconn);
  5.             return (int)comm.ExecuteScalar();
  6.         }
but I think this is not a good idea, because I can't benefited from the returned row number from the ExecuteNonQuery() method .

Thanks alot guys, I appreciate ur help

Regards,
Aug 31 '07 #7
dip_developer
648 Expert 512MB
you dont want to use a seperate method to get the idendity...
write a seperate method to get the row_no for the field you entered.

If you use the idendity approach, you may get wrong values due to concurrency.

Moreover if u get the row_no for the field you entered, u may use the code later on for other things.

MS Access i dont think supports stored procedures.
Hi Shashi Sadasivan,
are you sure that ms access dont support stored procedure??? I have to say something for you.........
did you use Access 2000 ??? Hopefully not.....
Now here is something which will be help ful for you..

Stored procedures in Access have been available since Access 2000 and are native to the Jet 4 Database Engine. If you're accustomed to using stored procedures in SQL Server, then you'll be right at home with how they're used in Access.

How do stored procedures work in Access?

Unlike other objects in Access, stored procedures have no interface and cannot be created or run through the Access User Interface. The way to get them into your database is to simply code them.
When a stored procedure is added to an Access Database, the Jet Engine reworks the stored procedure syntax into a query object.

How to creat stored procedure?

"CREATE PROC procProductsList AS SELECT * FROM Products"

The statement: "CREATE PROC procCustomerList" is the part that actually creates the stored procedure. The part following "AS" can be any valid SQL Statement.

Limitations:

There are some limitations you may encounter here, especially if you're used to the power of SQL Server.
1.3Output parameters cannot be used.
2.Don't use the @ character. The @ character is often used in Transact SQL (SQL Server), where it represents a local variable. Access doesn't always convert this character and will sometimes leave it out. T
3.Temporary tables are not available in Access.
4.I suspect many of the options available in Transact SQL are not available in Access as it's not Transact SQL compatible.

How to write in code??

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Dim sSQL As String 
  4.  
  5. sSQL = "CREATE PROC procProductsList AS SELECT * FROM Products;"
  6.  
  7. Dim con As OleDbConnection
  8. Dim cmd As OleDbCommand = New OleDbCommand()
  9. Dim da As OleDbDataAdapter
  10. ' Change Data Source to the location of Northwind.mdb on your local 
  11. ' system.
  12. Dim sConStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data " _
  13. & "Source=C:\Program Files\Microsoft " _
  14. & "Office\Office10\Samples\Northwind.mdb"
  15. con = New OleDbConnection(sConStr)
  16. cmd.Connection = con
  17. cmd.CommandText = sSQL
  18. con.Open()
  19. cmd.ExecuteNonQuery()
  20. con.Close()
  21.  
Aug 31 '07 #8
Plater
7,872 Expert 4TB
I did recently learn (in MS SQL Server) that
Expand|Select|Wrap|Line Numbers
  1. return SCOPE_IDENTITY();
  2.  
can be used instead of a second select statement
Aug 31 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Kevin Crosbie | last post by:
Hi all, (Sorry for the cross-post, there doesn't appear to be much activity on comp.database.oracle)I'm trying to get the last 300 rows from the difference between 2 large tables and my queries...
5
by: Axial | last post by:
Question: How to select columns from Excel-generated XML when some cells are empty. I've found examples where rows are to be selected, but I can't seem to extrapolate from that to selecting...
4
by: Gordon Burditt | last post by:
What's the easiest way to copy a row from one table to another (with the same schema), without knowing in advance the number and types of the columns? The problem: I have several sets of tables...
4
by: Doslil | last post by:
I have a form which has a subform.In the main form I have only one field (this is a drop down list and has a query attached to it) which selects empno,Name from the EmployeeInformation table. ...
1
by: Ramesh | last post by:
hi, I am selecting fields from three table for manupulating data and i want to display total number of records selected. But i am always getting -1 value, eventhough 1000 of records are selected....
6
by: aaj | last post by:
Hi all I use a data adapter to read numerous tables in to a dataset. The dataset holds tables which in turn holds full details of the records i.e. keys, extra colums etc.. In some cases I...
10
by: Steve Atkins | last post by:
I have a large table (potentially tens or hundreds of millions of rows) and I need to extract some number of these rows, defined by an integer primary key. So, the obvious answer is select *...
2
by: Catch_22 | last post by:
Hi, I have a stored procedure that has to extract the child records for particular parent records. The issue is that in some cases I do not want to extract all the child records only a...
4
by: Jeffrey Davis | last post by:
I'm hoping that someone here can give me some assistance with a database I'm trying to set up. My skills in Access are fairly basic, and I'm trying to skill up, but some of the stuff is a little...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.