Selecting from the Table by specifying the row number

[khalid Ahmed]

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

string insertStmnt = @"INSERT INTO Student (stName) VALUES (@stName)";
OleDbCommand comm = new OleDbCommand(insertStmnt, myconn);
OleDbParameter param = new OleDbParameter("@stName",stNameVal);
comm.Parameters.Add(param);
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 ....

[Plater]

With a second SQL query.

I recently have started moving towards stored procedures that do both queries at once.

[khalid Ahmed]

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.

[Plater]

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.

[dip_developer]

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

string insertStmnt = @"INSERT INTO Student (stName) VALUES (@stName)";
OleDbCommand comm = new OleDbCommand(insertStmnt, myconn);
OleDbParameter param = new OleDbParameter("@stName",stNameVal);
comm.Parameters.Add(param);
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_i d) 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.Activ e := False;
datResult.Comma ndText := 'select @@IDENTITY as [ID]';
datResult.Activ e := True;
Result := datResult.Field ByName('id').As Integer;
datResult.Activ e := 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.Comm andText := 'insert into [test] ( [name] ) values ( "Test" )';
datCommand.Exec ute;
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(for mat('lastinsert id : %d',[GetLastInsertID]));
end;

[Shashi Sadasivan]

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.

[khalid Ahmed]

I have written a second sql-statement to get the last auto-number field entered
after the insert method like the following method

      private int LastID()
        {
            //Find highest ID in the  table
            OleDbCommand comm = new OleDbCommand("SELECT MAX(ID) FROM Student",myconn);
            return (int)comm.ExecuteScalar();
        }

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,

[dip_developer]

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 procProductsLis t AS SELECT * FROM Products"

The statement: "CREATE PROC procCustomerLis t" 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??

[CODE=vbnet]

Dim sSQL As String

sSQL = "CREATE PROC procProductsLis t AS SELECT * FROM Products;"

Dim con As OleDbConnection
Dim cmd As OleDbCommand = New OleDbCommand()
Dim da As OleDbDataAdapte r
' Change Data Source to the location of Northwind.mdb on your local
' system.
Dim sConStr As String = "PROVIDER=Micro soft.Jet.OLEDB. 4.0;Data " _
& "Source=C:\Prog ram Files\Microsoft " _
& "Office\Office1 0\Samples\North wind.mdb"
con = New OleDbConnection (sConStr)
cmd.Connection = con
cmd.CommandText = sSQL
con.Open()
cmd.ExecuteNonQ uery()
con.Close()
[/CODE]

[Plater]

I did recently learn (in MS SQL Server) that
[code=sql]
return SCOPE_IDENTITY( );
[/code]
can be used instead of a second select statement