I am doing a data warehouse project.I have two tables tblA (id, type) and tblB(city, no_crimes, type).I want to create (insert) a number of rows based on the value of no_crimes.For Example, in tblB(Leeds, 2000, murder). SO, I need to insert 2000 rows into tblA by a single INSERT statement (not 2000 statements).
I got the following query to group the table. I am wondering how to add a subtotal record with anonymous type.
string prevOriginator = string.Empty; var oOriginator = (from I in dtIssue.AsEnumerable() group I by new {RaisedBy = I.Field<string>("RaisedBy"), ItemType = I.Field<string>("ItemType")} into grp
Basically i deal with two tables.i have a table T1 of the following format:id company_name i need to read this data and create a table T2 as followsi wrote the following code. it worked fine for retrieving data and modification but fails to insert the modified data into the 2nd table.the error is, it does not recognise the value for @token
ArrayList dynarr=new ArrayList(); // global variable protected void Button2_Click(object sender, EventArgs e) { int row = 1; int i; string strcmd = null; string Connection = "Data Source=...."; SqlConnection conn = new SqlConnection(Connection); string str = "select company_name from T1 where ID=@ID"; [code]...
i just use this code to insert value in datatable and pass this table in method look code---
public class RoomBO { DataTable dt; RoomDal objRoom = new RoomDal(); public int insert(int room,string bedType, int bed, int charge) { //adds the details of the new room dt = new DataTable(); dt.Columns.Add(new DataColumn("Column1")); dt.Columns.Add(new DataColumn("Column2")); dt.Columns.Add(new DataColumn("Column3")); dt.Columns.Add(new DataColumn("Column4")); DataRow dr; dr = dt.NewRow(); dr["Column1"] = room; dr["Column2"] = bedType; dr["Column3"] = bed; dr["Column4"] = charge; objRoom.Save(dt); return 1; }
*on data access layer*
public int Save(DataTable dataTable) //adds the details of the new room { ada = new SqlDataAdapter("room_insert", con); //DataRow r= dataTable.NewRow(); ada.SelectCommand.CommandType = CommandType.StoredProcedure; ada.SelectCommand.Parameters.AddWithValue("@room_no", Convert.ToInt32(dataTable.Rows[0][0])); ada.SelectCommand.Parameters.AddWithValue("@room_type", Convert.ToString(dataTable.Rows[0][1].ToString())); ada.SelectCommand.Parameters.AddWithValue("@no_bed", Convert.ToInt32(dataTable.Rows[0][2])); ada.SelectCommand.Parameters.AddWithValue("@charge", Convert.ToInt32(dataTable.Rows[0][3])); ds = new DataSet(); ada.Fill(ds); return -1; }
i am not getting any error but value is not insert to my database
I am working on a web app for an online photo album. It is the last project in Scott Mitchell's book: "ASP.Net 2.0 in 24 hours".
I am creating a page where users can enter new photo images. The functionality for uploading an image will come later. I am working on just inserting a new row into the Pictures table with an optional category, a required title and a required description.
I am getting an error when I try to insert a new row.
Here is a screenshot of the page with a shot of the dropdown list.
The Categories are user-specific. I am getting the correct values retrieved.
[URL]
There are two pretty simple database tables involved here.
NOTE: The CategoryID and PIctureID are both autoincrement integer columns.
[URL]
The page uses a DetailsView that uses a SqlDataSource that uses the Pictures table. (The DetailsView's Default Mode property is set to "Insert". The "Enable Inserting" checkbox is also checked.)
The dropdown list uses a dropdown control that uses a SqlDataSource that uses the Categories table.
When the "Insert" button is clicked the CategoryID value associated with selected (Category) Name on the dropdown list will be used along with the Title and Description values to insert a row in to the Pictures table. (If no Category value is selected, then a null value will be used for the CategoryID. This is OK because the CategoryID column in the Pictures table allows nulls.)
My problem is that I am getting this error:
Cannot insert explicit value for identity column in table 'Pictures' when IDENTITY_INSERT is set to OFF.
(FYI: I have the full version of both Visual Studio and SQL Server.)
What I am trying to achieve here is, I am supplying xml as string (varchar) to my stored procedure, In stored procedure I want to parse this xml and fetch each Promotion record and insert into my table. Also once each record is inserted, I need to fetch the Identity of that record alongwith the AreaID attribute from the Parent node "Promotion" of each Promotion record and insert into second association table. how would I go about doing it, my generated xml is given as under.
i have to insert values in two tables on save button click.ne sipmle insert and return a order_id.that id is used for inserting order details information.i am using a datatable to store this information,then insert to table.i want use Transaction for both insert on save button click.
I can read the file using streamreader on a "continous way" inside a do while loop, and parse it to obtain th different fileds required.i want to read the file, identify header line and fill a datatable, then identify details lines and fill another datatable to be able to insert both tables on the same transaction scope, if something fails rollback the insert.Im using a access 2003 database as backend and custom DAL and Business objects.
I'm trying to build an asp.net application, where I have three tables.
tblRoute tblHalte tblHalteRoute
I want to insert a routeID into tblHalteRoute and for every routeID multiple halteID's
TblRoute has the needed fields (routeID(1) & allHaltes[halteID1,halteID2,halteID3]) --> so Every route has multiple Haltes that I can access by parsing the allHaltes field and using the IDS.
What I want to do is update tblHalteRoute like this
inserting these values into the tblHalteRoute .. I'm really not sure on where to look or how to start, I tried using SUBquerys with an Insert before a select, but no success.
Existing records can be updatedNew records can be insertedAll existing records can be deleted & new ones can be insertedExisting records have a primary key field New ones are just blank inserts
All this detail is stored in a data table. On form update I only want to update existing (only modified rows) & insert new rows. I have gone about loading the changed records in a new data table
Table TableDDl = new Table(); and I have DataTable
DataTable DataTableDDl = new DataTable();
who can I put the same data from AspTable in the same place in DataTable ?
Ex...If I have "Hello" in (third row and first cell) in AspTable....Who can I put "Hello" in (Third Row and First Colum in DataTable) >>>But for add data...
I try
DataTable1.Load(Table1);
But it didn't work because it want a reader....I want the same thing but With AspTable
I have two tables in a database and I want to select one value from one of that table. For that I want to pass one value and if that value stored in the table I want pick the id representing the value in the next table.
That means the operation is that first I select a row of data from first table by using a user control in that row there is a value (example "apple") and I want pass that value("apple") to the second table. In the second table the value ("apple") having a id (example "australian") and I want that the query search for that id ("australian") and show that in the text box.
[code]....
Imagine that this is the two tables using a usercontrol I select first a row from first table and I want to pass that value "apple" to second table and find out the id of "australian" (that is equivalent id for apple in the second tabl ) from the second table and show that in a text box.
I'm making the leap from using all the 'wizards' that are built into VS, and have been doing more with code-behinds. But, I'm curious as to when/why I should use a reader, versus a databable, table adaptor to "manage" the data returned from a stored procedure. Typically, the data is to be displayed on a webpage, and not written back to the database. What's the difference? When should I use one over another?
I have a DataTable for which i do not know how many rows are there in it.. How to get the rows/columns one by one and save it in the desired SQL Table?
I'm returning one table. My understanding is that a DataSet can be made of more than one DataTable. I also understand that I can use either of these to return my one table.
Is it better to use the DataTable when I have one table to return instead of a DataSet? Is there any advantage of not using the DataSet in a situation like this? Is there a disadvantage to using the DataSet when only one table is being returned? Does this even matter?