out parameters workaround for Ms Access [solved] [modified]

[Dan Mos]

Hy, I've got a really unpleasant task. To create a personnel tracking app using MS Access as the "DB provider". Here's my problem: I have a table named Person Person ========= ID - AutoNumber Name - Text(40) Adress - Text(50) . . . I have some "stored procs"/querys.

//this one adds a person(actually not this one but similar with extra fields)
PARAMETERS inName Text ( 255 ), inAdress Text ( 255 );//more but let's simplify
INSERT INTO Person( Name, Adress )
VALUES(inName, inAdress);

//something like this gets the ID
PARAMETERS inName Text ( 255 ), inAdress Text ( 255 );
SELECT Person.ID
FROM Person
WHERE (Person.Name= [inName]) AND (Person.Adress= [inAdress]);

And here is a mini version of one of the calsses:

public class Person : IComparable<Person>
{
public int ID { get; set; }
public string Name { get; set; }
public string Adress { get; set; }

    public int CompareTo(Person other)
    {
        int equality = this.Name.CompareTo(other.Name);
        if (equality < 0) return -1;
        else if (equality == 0)
        {
            return this.Adress.CompareTo(other.Adress);
        }
        else
        {
            return 1;
        }
    }
}

//and here is the code that inserts the Person and then gets the ID

int id = 0;
try
{
con.Open();
//insert the person
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "EXEC procAddPerson";
int result = cmd.ExecuteNonQuery();

            if (result == 1)
            {
                //get the ID by changing the comand text to execute the GetID query
                cmd.CommandText = "EXECUTE procGetPersonID";
                id = Convert.ToInt32(cmd.ExecuteScalar());
            }
        }
        catch
        {
            id = -1;
            //log and stuff
        }
        finally
        {
            if (con.State != ConnectionState.Closed)
            {
                con.Close();
            }
            con = null;
        }

        return id;

And here is my question: I know that Access/JET does not support out params but is there a better/faster way of getting the ID or any auto generated numbers? Thanks in advance.

[Mycroft Holmes]

Assuming there is an @@IDENTITY equivalent in Access (if not kludge one) then change the last line of your insert proc to

select @@IDENTITY as PersonID

Change you command from executenonquery to executescalar and put the returned value int a variable, oh you already do that but on a nonquery? Instead of relying on the RETURN as in SQL Server you need to explicitly return a result set (PersonID)

Never underestimate the power of human stupidity RAH

[Pranay Rana]

check below code Dim cnDatabase As ADODB.Connection Dim rsNewAutoIncrement As ADODB.Recordset Dim strConn As String Dim strSQL As String Dim strPathToMDB As String strPathToMDB = "C:\NewJet4.MDB" strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPathToMDB & ";" Set cnDatabase = New ADODB.Connection cnDatabase.Open strConn strSQL = "CREATE TABLE AutoIncrementTest " & _ "(ID int identity, Description varchar(40), " & _ "CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))" cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords strSQL = "INSERT INTO AutoIncrementTest " & _ "(Description) VALUES ('AutoIncrement Test')" cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords strSQL = "SELECT @@Identity" Set rsNewAutoIncrement = New ADODB.Recordset rsNewAutoIncrement.Open strSQL, cnDatabase, adOpenForwardOnly, _ adLockReadOnly, adCmdText MsgBox "New Auto-increment value is: " & rsNewAutoIncrement(0).Value rsNewAutoIncrement.Close Set rsNewAutoIncrement = Nothing strSQL = "DROP TABLE AutoIncrementTest" cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords cnDatabase.Close Set cnDatabase = Nothing or go to followin link http://support.microsoft.com/kb/232144/EN-US/[^] for more info will provide info that you want

[Dan Mos]

Thanks it works:)

[Dan Mos]

thanks. I do that (assign to the result variable the value of executeNonquery) to see if it succeeded. Thanks again.