ironwill96
asked on
Easy way to obtain new Autonumber ID Field from newly created row (ADO.Net in C#)
Hi all,
I have the code below which adds a new record to my database and then updates it. The database is Microsoft Access and i'm using the OleDbAdapters in ADO.Net with a disconnected dataset. I want to grab the "ProjectID" field which is an Autonumber field that isn't created until you "update" the dataset. Is there an easy way to get it without reopening the whole database and doing an SQL search for the record I just created using the Project Title? Maybe some sort of "newid" method or some such?
Here is the code I have now:
OleDbDataAdapter daOurAdapter;
DataSet dsResults;
//do a search
this.mstrSQL = "SELECT * FROM Projects";
this.instantiateDatabase() ;
try
{
//create an adapter to interact with the database
daOurAdapter = new OleDbDataAdapter(this.mstr SQL, this.dbConnect);
//make our dataset object an actual dataset
dsResults = new DataSet();
//now fill our dataset object with data (if any)
daOurAdapter.Fill(dsResult s, "Projects");
//we now have a DataSet object that contains any results that have been returned by our query
//create a DataTable object representing the table returned
DataTable dtProjects = dsResults.Tables["Projects "];
DataRow drProjectResults = dtProjects.NewRow();
drProjectResults["Customer ID"] = cboCustomers.SelectedValue ;
drProjectResults["ProjectT itle"] = (string)txtProjectTitle.Te xt;
drProjectResults["ProjectN otes"] = (string)txtProjectNotes.Te xt;
if (txtRender1Loc.Text != "")
drProjectResults["RenderLo cation1"] = (string)txtRender1Loc.Text ;
if (txtRender1Desc.Text != "")
drProjectResults["RenderDe scription1 "] = (string)txtRender1Desc.Tex t;
if (txtRender2Loc.Text != "")
drProjectResults["RenderLo cation2"] = (string)txtRender2Loc.Text ;
if (txtRender2Desc.Text != "")
drProjectResults["RenderDe scription2 "] = (string)txtRender2Desc.Tex t;
if (txtRender3Loc.Text != "")
drProjectResults["RenderLo cation3"] = (string)txtRender3Loc.Text ;
if (txtRender3Desc.Text != "")
drProjectResults["RenderDe scription3 "] = (string)txtRender3Desc.Tex t;
if (txtRender4Loc.Text != "")
drProjectResults["RenderLo cation4"] = (string)txtRender4Loc.Text ;
if (txtRender4Desc.Text != "")
drProjectResults["RenderDe scription4 "] = (string)txtRender4Desc.Tex t;
if (txtRender5Loc.Text != "")
drProjectResults["RenderLo cation5"] = (string)txtRender5Loc.Text ;
if (txtRender5Desc.Text != "")
drProjectResults["RenderDe scription5 "] = (string)txtRender5Desc.Tex t;
drProjectResults["Status"] = 0;
drProjectResults["StartDat e"] = (string)txtStartDate.Text;
if (txtDueDate.Text != "")
drProjectResults["DueDate" ] = (string)txtDueDate.Text;
//push all changes to the database
dtProjects.Rows.Add(drProj ectResults );
//FIGURE OUT THE NEW PROJECT ID HERE AND CREATE THE DIRECTORY WITH THE CUSTOMER ID, THEN
//CREATE A DIRECTORY WITH THE PROJECT ID
// MessageBox.Show("New project id = " + drProjectResults["ProjectI D"]); <-- this doesn't work
if (!dsResults.HasErrors)
{
// Set up insert, update, and delete commands
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(daOurA dapter);
//setup escape characters
cmdBuilder.QuotePrefix = "[";
cmdBuilder.QuoteSuffix = "]";
daOurAdapter.Update(dsResu lts, "Projects");
dsResults.AcceptChanges();
}
}
catch (Exception ex)
{
string strMsg = "Error Message = " + ex.Message;
}
You can see in the comments where I want to be able to figure out the new project ID at. Any help would be appreciated, even if it is to tell me to just subsequently search for the Project based on its title which is what i'm implementing temporarily.
Thanks,
Nathan
I have the code below which adds a new record to my database and then updates it. The database is Microsoft Access and i'm using the OleDbAdapters in ADO.Net with a disconnected dataset. I want to grab the "ProjectID" field which is an Autonumber field that isn't created until you "update" the dataset. Is there an easy way to get it without reopening the whole database and doing an SQL search for the record I just created using the Project Title? Maybe some sort of "newid" method or some such?
Here is the code I have now:
OleDbDataAdapter daOurAdapter;
DataSet dsResults;
//do a search
this.mstrSQL = "SELECT * FROM Projects";
this.instantiateDatabase()
try
{
//create an adapter to interact with the database
daOurAdapter = new OleDbDataAdapter(this.mstr
//make our dataset object an actual dataset
dsResults = new DataSet();
//now fill our dataset object with data (if any)
daOurAdapter.Fill(dsResult
//we now have a DataSet object that contains any results that have been returned by our query
//create a DataTable object representing the table returned
DataTable dtProjects = dsResults.Tables["Projects
DataRow drProjectResults = dtProjects.NewRow();
drProjectResults["Customer
drProjectResults["ProjectT
drProjectResults["ProjectN
if (txtRender1Loc.Text != "")
drProjectResults["RenderLo
if (txtRender1Desc.Text != "")
drProjectResults["RenderDe
if (txtRender2Loc.Text != "")
drProjectResults["RenderLo
if (txtRender2Desc.Text != "")
drProjectResults["RenderDe
if (txtRender3Loc.Text != "")
drProjectResults["RenderLo
if (txtRender3Desc.Text != "")
drProjectResults["RenderDe
if (txtRender4Loc.Text != "")
drProjectResults["RenderLo
if (txtRender4Desc.Text != "")
drProjectResults["RenderDe
if (txtRender5Loc.Text != "")
drProjectResults["RenderLo
if (txtRender5Desc.Text != "")
drProjectResults["RenderDe
drProjectResults["Status"]
drProjectResults["StartDat
if (txtDueDate.Text != "")
drProjectResults["DueDate"
//push all changes to the database
dtProjects.Rows.Add(drProj
//FIGURE OUT THE NEW PROJECT ID HERE AND CREATE THE DIRECTORY WITH THE CUSTOMER ID, THEN
//CREATE A DIRECTORY WITH THE PROJECT ID
// MessageBox.Show("New project id = " + drProjectResults["ProjectI
if (!dsResults.HasErrors)
{
// Set up insert, update, and delete commands
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(daOurA
//setup escape characters
cmdBuilder.QuotePrefix = "[";
cmdBuilder.QuoteSuffix = "]";
daOurAdapter.Update(dsResu
dsResults.AcceptChanges();
}
}
catch (Exception ex)
{
string strMsg = "Error Message = " + ex.Message;
}
You can see in the comments where I want to be able to figure out the new project ID at. Any help would be appreciated, even if it is to tell me to just subsequently search for the Project based on its title which is what i'm implementing temporarily.
Thanks,
Nathan
http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx
ASKER
That ScopeID stuff didn't work for me and is also using SQL-based instead of OleDB-based solutions. Here is how I am now getting the ProjectID, I just want maybe a faster way, but perhaps this is the best solution possible.
(Add this code right after what I have in my first post):
try
{
//this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID = SCOPE_IDENTITY())"; <-- that doesnt work, OleDB doesnt understand the SCOPE_IDENTITY() operator
this.mstrSQL = "SELECT * FROM Projects WHERE CustomerID = " + cboCustomers.SelectedValue + " AND ProjectTitle = '" + txtProjectTitle.Text + "'";
this.openDatabase();
OleDbCommand myCmd = new OleDbCommand(this.mstrSQL, this.dbConnect);
OleDbDataReader myReader = myCmd.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
//we found a value, grab the ProjectID
string temp = (string)myReader["ProjectT itle"];
this.mintProjectID = (int)myReader["ProjectID"] ;
break;
}
}
else
{
//we didnt find any values, this is an issue, abort
MessageBox.Show("Unable to locate ProjectID from newly created record, aborting.", "Data Value Error");
myReader.Close();
return;
}
myReader.Close();
this.closeDatabase();
}
catch (Exception exp)
{
//something went wrong, abort
this.closeDatabase();
MessageBox.Show("Exception thrown during attempt to obtain ProjectID, aborting.", "Fatal Data Error");
return;
}
Thanks,
Nathan
(Add this code right after what I have in my first post):
try
{
//this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID = SCOPE_IDENTITY())"; <-- that doesnt work, OleDB doesnt understand the SCOPE_IDENTITY() operator
this.mstrSQL = "SELECT * FROM Projects WHERE CustomerID = " + cboCustomers.SelectedValue
this.openDatabase();
OleDbCommand myCmd = new OleDbCommand(this.mstrSQL,
OleDbDataReader myReader = myCmd.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
//we found a value, grab the ProjectID
string temp = (string)myReader["ProjectT
this.mintProjectID = (int)myReader["ProjectID"]
break;
}
}
else
{
//we didnt find any values, this is an issue, abort
MessageBox.Show("Unable to locate ProjectID from newly created record, aborting.", "Data Value Error");
myReader.Close();
return;
}
myReader.Close();
this.closeDatabase();
}
catch (Exception exp)
{
//something went wrong, abort
this.closeDatabase();
MessageBox.Show("Exception
return;
}
Thanks,
Nathan
//this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID = SCOPE_IDENTITY())"; <-- that doesnt work, OleDB doesnt understand the SCOPE_IDENTITY() operator
Scope_Identity is purely MSSQL Server. in Access, Mysql and SQL Server the @@IDENTITY should work however:
this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID =@@IDENTITY)"
Scope_Identity is purely MSSQL Server. in Access, Mysql and SQL Server the @@IDENTITY should work however:
this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID =@@IDENTITY)"
ASKER
Angel,
When I do it that way:
try
{
this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID =@@IDENTITY)";
//this.mstrSQL = "SELECT * FROM Projects WHERE CustomerID = " + cboCustomers.SelectedValue + " AND ProjectTitle = '" + txtProjectTitle.Text + "'";
this.openDatabase();
OleDbCommand myCmd = new OleDbCommand(this.mstrSQL, this.dbConnect);
OleDbDataReader myReader = myCmd.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
//we found a value, grab the ProjectID
string temp = (string)myReader["ProjectT itle"];
this.mintProjectID = (int)myReader["ProjectID"] ;
break;
}
}
else
{
//we didnt find any values, this is an issue, abort
MessageBox.Show("Unable to locate ProjectID from newly created record, aborting.", "Data Value Error");
myReader.Close();
return;
}
myReader.Close();
this.closeDatabase();
}
catch (Exception exp)
{
//something went wrong, abort
string tempmsg = exp.Message;
this.closeDatabase();
MessageBox.Show("Exception thrown during attempt to obtain ProjectID, aborting.", "Fatal Data Error");
return;
}
It says "Syntax error (missing operator) in query expression '(ProjectID =@@IDENTITY)'".
Any idea why it doesnt like the syntax of that SQL?
Nathan
When I do it that way:
try
{
this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID =@@IDENTITY)";
//this.mstrSQL = "SELECT * FROM Projects WHERE CustomerID = " + cboCustomers.SelectedValue
this.openDatabase();
OleDbCommand myCmd = new OleDbCommand(this.mstrSQL,
OleDbDataReader myReader = myCmd.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
//we found a value, grab the ProjectID
string temp = (string)myReader["ProjectT
this.mintProjectID = (int)myReader["ProjectID"]
break;
}
}
else
{
//we didnt find any values, this is an issue, abort
MessageBox.Show("Unable to locate ProjectID from newly created record, aborting.", "Data Value Error");
myReader.Close();
return;
}
myReader.Close();
this.closeDatabase();
}
catch (Exception exp)
{
//something went wrong, abort
string tempmsg = exp.Message;
this.closeDatabase();
MessageBox.Show("Exception
return;
}
It says "Syntax error (missing operator) in query expression '(ProjectID =@@IDENTITY)'".
Any idea why it doesnt like the syntax of that SQL?
Nathan
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, that should work ok for me.
Nathan
Nathan