Adding new object to Typed Data Set and return the primary key

N

Nic

Hi,

I have started using Typed Data Sets with VS2005 and so far so good,
however I am having a problem with adding a row to a data table and
returning the new Primary Key value.

My data set has been auto generated from a DB table and has an auto
generated Primary Key for the ID field. I create the row using
ProjectsDataTable.NewProjectsRow() However the code that returns this
Project Row does not persist the data table as it is a method on my
singleton object.

Once the new row has been populated with data I call an update method
on the singleton which then creates a new table adapter. My problem is
how to add this orphaned row to the database and return the Primary Key
that the database creates for it.

I have tried many of the different methods that are created by default
in the TypedDataSet however so far the only one that works is the
TableAdapter.Insert() method and this does not return the primary key.

Method 1:
DataAdapter.Update(myProjectRow) // fails I guess because the row has
no connected DataTable

Method 2:
newDataTable = DataAdapter.GetData(); // returns eg a data table
containing 4 rows.
newDataTable.ImportRow(myRow); // data table still contains 4 rows no
error generated.
DataAdapter.Update(newDataTable); // does nothing as there is nothing
to update

Please does anyone have any insight into this or do i have to go back
to the good old way of manually executing the SQL or dumping my data
singleton and manually creating and persisting the data table for the
form that creates the new project ?

Another strange thing is that the if i return a project from the
singleton (which does not persist the data table) then call the same
update method ( on the singleton which creates a new data adapter) the
updates are commited. Is something strange with the primary key going
on ?

Sudo Code Snippet:

public class DataObject {

private static DataObject this_Data;

public static DataObject getInstance() {
if (this_Data == null)
this_Data = new Data();

return this_Data;
}

public TypedDataObjectProject newProject() {

Projects.ProjectsDataTable db_ProjectsTable;
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_TableAdapter = new
com.acet4data.DataPro.DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

Projects.ProjectsDataTable db_ProjectsTable2 =
db_TableAdapter.GetData();

return db_ProjectsTable2.NewProjectsRow();

}

public TypedDataObjectProject getProject() {

Projects.ProjectsRow db_Project;
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_ProjectAdapter = new
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

Projects.ProjectsDataTable db_ProjectTable2 =
db_ProjectAdapter.GetData();
db_ProjectAdapter.Dispose();

return db_ProjectTable2[0];

}

public int updateProject(Projects.ProjectsRow Project)
{

int int_Return = 0;

DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_ProjectAdapter = new
com.acet4data.DataPro.DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

db_ProjectAdapter.Update(Project);
db_ProjectAdapter.Dispose();


}


}

This works:
ob_Prog = DataObject.getInstance().getProject();
ob_Prog.Name = "new value";
DataObject.getInstance().updateProject(ob_Prog);

This Fails not with error but no new row is added:
ob_Prog = DataObject.getInstance().newProject();
ob_Prog.Name = "new value";
DataObject.getInstance().updateProject(ob_Prog);

Many thanks in advance for you help.

Nic
 
N

Nic

I have figured this out eventually when I call the newProject function
its not sufficent to just create the new row and return it I need to
add it to the table that created it and set the primary key to a bogus
value. When the update statement is called after the object has been
populated the insert method on the data adapter sets the ID field to
that returned from the database.

public TypedDataObjectProject newProject() {

Projects.ProjectsRow obj_Row;
Projects.ProjectsDataTable db_ProjectsTable;
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_TableAdapter = new

com.acet4data.DataPro.DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

Projects.ProjectsDataTable db_ProjectsTable2 =
db_TableAdapter.GetData();

obj_Row = db_ProjectsTable2.NewProjectsRow();
obj_Row.ID = -1;

return obj_Row;

}
Hi,

I have started using Typed Data Sets with VS2005 and so far so good,
however I am having a problem with adding a row to a data table and
returning the new Primary Key value.

My data set has been auto generated from a DB table and has an auto
generated Primary Key for the ID field. I create the row using
ProjectsDataTable.NewProjectsRow() However the code that returns this
Project Row does not persist the data table as it is a method on my
singleton object.

Once the new row has been populated with data I call an update method
on the singleton which then creates a new table adapter. My problem is
how to add this orphaned row to the database and return the Primary Key
that the database creates for it.

I have tried many of the different methods that are created by default
in the TypedDataSet however so far the only one that works is the
TableAdapter.Insert() method and this does not return the primary key.

Method 1:
DataAdapter.Update(myProjectRow) // fails I guess because the row has
no connected DataTable

Method 2:
newDataTable = DataAdapter.GetData(); // returns eg a data table
containing 4 rows.
newDataTable.ImportRow(myRow); // data table still contains 4 rows no
error generated.
DataAdapter.Update(newDataTable); // does nothing as there is nothing
to update

Please does anyone have any insight into this or do i have to go back
to the good old way of manually executing the SQL or dumping my data
singleton and manually creating and persisting the data table for the
form that creates the new project ?

Another strange thing is that the if i return a project from the
singleton (which does not persist the data table) then call the same
update method ( on the singleton which creates a new data adapter) the
updates are commited. Is something strange with the primary key going
on ?

Sudo Code Snippet:

public class DataObject {

private static DataObject this_Data;

public static DataObject getInstance() {
if (this_Data == null)
this_Data = new Data();

return this_Data;
}

public TypedDataObjectProject newProject() {

Projects.ProjectsDataTable db_ProjectsTable;
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_TableAdapter = new
com.acet4data.DataPro.DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

Projects.ProjectsDataTable db_ProjectsTable2 =
db_TableAdapter.GetData();

return db_ProjectsTable2.NewProjectsRow();

}

public TypedDataObjectProject getProject() {

Projects.ProjectsRow db_Project;
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_ProjectAdapter = new
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

Projects.ProjectsDataTable db_ProjectTable2 =
db_ProjectAdapter.GetData();
db_ProjectAdapter.Dispose();

return db_ProjectTable2[0];

}

public int updateProject(Projects.ProjectsRow Project)
{

int int_Return = 0;

DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_ProjectAdapter = new
com.acet4data.DataPro.DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

db_ProjectAdapter.Update(Project);
db_ProjectAdapter.Dispose();


}


}

This works:
ob_Prog = DataObject.getInstance().getProject();
ob_Prog.Name = "new value";
DataObject.getInstance().updateProject(ob_Prog);

This Fails not with error but no new row is added:
ob_Prog = DataObject.getInstance().newProject();
ob_Prog.Name = "new value";
DataObject.getInstance().updateProject(ob_Prog);

Many thanks in advance for you help.

Nic
Click to expand...
 
J

Jim Rand

this.sqlInsertOffice.CommandText = "INSERT INTO AgencyNET.Office
(AREndUserID,City,CompanyIDNmbr,CompanyLicenseNumber,CompanyLicensePrefix,CompanyName,Cty,DateJoined,Dropped,Email,ExpulTerminDate,FaxNumber,FirmType,Idx,IdxAddDate,IdxDropDate,IdxOptOut,LastUpdatedBy,LicenseNumber,LicensePrefix,MailingAddress,MREISID,MRERCAdd,MRERCDrop,Note,OfficeTypeID,ParentOfficeID,PhoneNumber,SiteAddress,SiteCity,SiteState,SiteZipCode,State,StatusLookupID,SuspensionDate,Vow,Vow2,VowAdd,WebSiteUrl,ZipCode)
" +

"VALUES
(@AREndUserID,@City,@CompanyIDNmbr,@CompanyLicenseNumber,@CompanyLicensePrefix,@CompanyName,@Cty,@DateJoined,@Dropped,@Email,@ExpulTerminDate,@FaxNumber,@FirmType,@Idx,@IdxAddDate,@IdxDropDate,@IdxOptOut,@LastUpdatedBy,@LicenseNumber,@LicensePrefix,@MailingAddress,@MREISID,@MRERCAdd,@MRERCDrop,@Note,@OfficeTypeID,@ParentOfficeID,@PhoneNumber,@SiteAddress,@SiteCity,@SiteState,@SiteZipCode,@State,@StatusLookupID,@SuspensionDate,@Vow,@Vow2,@VowAdd,@WebSiteUrl,@ZipCode);
" +

"SELECT OfficeID, CAST(TS AS INT) AS TS FROM AgencyNET.Office " +

"WHERE OfficeID = SCOPE_IDENTITY()";

Nic said:
I have figured this out eventually when I call the newProject function
its not sufficent to just create the new row and return it I need to
add it to the table that created it and set the primary key to a bogus
value. When the update statement is called after the object has been
populated the insert method on the data adapter sets the ID field to
that returned from the database.

public TypedDataObjectProject newProject() {

Projects.ProjectsRow obj_Row;
Projects.ProjectsDataTable db_ProjectsTable;
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_TableAdapter = new

com.acet4data.DataPro.DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

Projects.ProjectsDataTable db_ProjectsTable2 =
db_TableAdapter.GetData();

obj_Row = db_ProjectsTable2.NewProjectsRow();
obj_Row.ID = -1;

return obj_Row;

}
Hi,

I have started using Typed Data Sets with VS2005 and so far so good,
however I am having a problem with adding a row to a data table and
returning the new Primary Key value.

My data set has been auto generated from a DB table and has an auto
generated Primary Key for the ID field. I create the row using
ProjectsDataTable.NewProjectsRow() However the code that returns this
Project Row does not persist the data table as it is a method on my
singleton object.

Once the new row has been populated with data I call an update method
on the singleton which then creates a new table adapter. My problem is
how to add this orphaned row to the database and return the Primary Key
that the database creates for it.

I have tried many of the different methods that are created by default
in the TypedDataSet however so far the only one that works is the
TableAdapter.Insert() method and this does not return the primary key.

Method 1:
DataAdapter.Update(myProjectRow) // fails I guess because the row has
no connected DataTable

Method 2:
newDataTable = DataAdapter.GetData(); // returns eg a data table
containing 4 rows.
newDataTable.ImportRow(myRow); // data table still contains 4 rows no
error generated.
DataAdapter.Update(newDataTable); // does nothing as there is nothing
to update

Please does anyone have any insight into this or do i have to go back
to the good old way of manually executing the SQL or dumping my data
singleton and manually creating and persisting the data table for the
form that creates the new project ?

Another strange thing is that the if i return a project from the
singleton (which does not persist the data table) then call the same
update method ( on the singleton which creates a new data adapter) the
updates are commited. Is something strange with the primary key going
on ?

Sudo Code Snippet:

public class DataObject {

private static DataObject this_Data;

public static DataObject getInstance() {
if (this_Data == null)
this_Data = new Data();

return this_Data;
}

public TypedDataObjectProject newProject() {

Projects.ProjectsDataTable db_ProjectsTable;
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_TableAdapter = new
com.acet4data.DataPro.DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

Projects.ProjectsDataTable db_ProjectsTable2 =
db_TableAdapter.GetData();

return db_ProjectsTable2.NewProjectsRow();

}

public TypedDataObjectProject getProject() {

Projects.ProjectsRow db_Project;
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_ProjectAdapter = new
DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

Projects.ProjectsDataTable db_ProjectTable2 =
db_ProjectAdapter.GetData();
db_ProjectAdapter.Dispose();

return db_ProjectTable2[0];

}

public int updateProject(Projects.ProjectsRow Project)
{

int int_Return = 0;

DataLayer.ProjectsTableAdapters.ProjectsTableAdapter
db_ProjectAdapter = new
com.acet4data.DataPro.DataLayer.ProjectsTableAdapters.ProjectsTableAdapter();

db_ProjectAdapter.Update(Project);
db_ProjectAdapter.Dispose();


}


}

This works:
ob_Prog = DataObject.getInstance().getProject();
ob_Prog.Name = "new value";
DataObject.getInstance().updateProject(ob_Prog);

This Fails not with error but no new row is added:
ob_Prog = DataObject.getInstance().newProject();
ob_Prog.Name = "new value";
DataObject.getInstance().updateProject(ob_Prog);

Many thanks in advance for you help.

Nic
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Primary Key, Identity Column - Problem adding row to strongly typed data table - "Column 'ShipmentID 0
add record to access db and then get primary key has error 2
to add data continuously and the chart adding new lines automaticly 1
Download the JAVA , .NET and SQL Server interview with answers 2

Top