Multiple table problem
I was hoping someone could take the following data and
show me the steps to make it work. I can do it with a
single table but not with multiple tables
The web will have the following fields:
db NAME "DUDE"
Policy Number (Insurance Table)
LastNAme (Custome Table)
RenewalDate (Insurance Table)
1. User can add/delete new customer all fields
2. User can Update RenewalDate
I have many more fields but I would appreciate which
web controls to use and the codebehind to make this
work.
*************
*************
Here is my other post that has not received any response
I am really stumped at this point. The forum has given
me lots of ideas but nothing to show me how to do this.
How do I input a multi-table DataSet to a Web Forms DataGrid
that can be inserted, updated, and deleted?
Visual Studio 2003, SqlServer DataBase
I am trying to create a datagrid that will have
fields from 4 separate tables.
When I drag in a SqlDataAdapter and
add all four tables to my query I get the following
Messages:
Generate insert statement
The original query has a select list that has
columns from multiple tables. The statement
cannot be generated automatically
for this type of query.
Generate Update statement
The original query has a select list that has
columns from multiple tables. The statement
cannot be generated automatically
for this type of query.
Generate Delete statement
The original query has a select list that has
columns from multiple tables. The statement
cannot be generated automatically
for this type of query.
[1803 byte] By [
kenn_rosie] at [2007-11-19 17:30:26]
# 1 Re: Multiple table problem
Dear kenn_rosie,
Your problem only solution is use Stored Procedure Technique to insert/update/delete from a Multi Table DataSet.
To achieve multi Table DataSet you have to follow following steps:
1. Create Different Stored Procedure in SQL Server for following activity which perform below activity on all tables of Multi Table DataSet:
A. Insert
B. Update
C. Delete
2. Create a command object for different activity i.e. Insert/Update/detete, in Your Dot Net Code i.e in C# Language like below for insert command:
SqlCommand insertCommand = new SqlCommand();
insertCommand.Connection = YourSQLConnection;
insertCommand.CommandText = "yourInsertStoredProcedureName";
insertCommand.CommandType = CommandType.StoredProcedure;
insertCommand.Parameters.Add(new SqlParameter("Parameter1", ParameterdatabaseType, ParameterDatabasefieldsize, directionofParameter, isNullable, precision, scale, sourceColumnoftheDatasetTable, DataRowVersion.Default, null);
In above example DataRowVersion varies depending upon how you want to pass column value to Stored Procedure i.e. If you want to send column value for Insert/Update then use Default and if your column is a primary key and you passing its value to update/delete then use Original.
3. Now create DataAdapter object with following syntax:
SqlDataAdapter da = new SqlDataAdapter();
da.InsertCommand = insertCommand;
da.UpdateCommand = updateCommand;
da.DeleteCommand = deleteCommand;
5. Now fire Update command of DataAdapter like below:
da.Update(yourDataSet);
The above command i.e. Update, of the DataAdapter will perform the required activity as per current state of the Row i.e. Inserted row will be inserted using Insert Stored Procedure, Updated Row will be Updated using Update Stored Procedure and Deleted Row will be delete using Deleted Stored Procedure.
Hope this will help u out. If you still have any problem then u can reach me on my MSN account i.e. gurdarshan70@hotmail.com or gmail account i.e. egurdarshan@gmail.com
ITGURU at 2007-11-10 3:30:33 >
