cycledude
asked on
c# msaccess return newly inserted rows id
Hi
I have a networked database, with a number of users
I have a situation where I need to insert a record into a table, and return the autonumber ID generated by access, so that the user can add records to another table using the id from the first table as the foreign key.
I have used the following sql statement
"Select @@Identity from " + table;
which works most times, however on the odd occasion it appears to be returning either nothing or the id from the previous record.
Is there any way to ensure that the query returns the correct value each time?
here is my code to insert data
I have a networked database, with a number of users
I have a situation where I need to insert a record into a table, and return the autonumber ID generated by access, so that the user can add records to another table using the id from the first table as the foreign key.
I have used the following sql statement
"Select @@Identity from " + table;
which works most times, however on the odd occasion it appears to be returning either nothing or the id from the previous record.
Is there any way to ensure that the query returns the correct value each time?
here is my code to insert data
public int InsertData(string table, string[] fields, string[] values)
{
int id = 0;
OleDbConnection con = new OleDbConnection(connectionstring);
string query2 = "Select @@Identity from " + table;
con.Open();
string myFields = "";
string myValues = "";
foreach (string f in fields)
{
myFields += f + ", ";
myValues += "?,";
}
if (myFields.Length > 0)
{
myFields = myFields.Substring(0, myFields.Length - 2);
myValues = myValues.Substring(0, myValues.Length - 1);
}
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("INSERT INTO " + table + " (" + myFields + ") Values (" + myValues + ") ", con);
foreach (string v in values)
{
cmd.Parameters.AddWithValue("?",v);
}
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.CommandText = query2;
id = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
return id;
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the assist
ASKER
Any ideas how I can get that to work with my code?