The current solution i implemented is awful!
I use a for... loop for inserting records from an ADO.NET data-table into an SQL table.
I would like to insert at once the data-table into the SQL table, without iterating...
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).
View 3 RepliesI am trying to insert data into two table at the same time , but i'm successding in inserting data into only one table at a time on button click .
[Code]....
When i run the code, data gets inserted into table "implantDetails" only. Are multiple INSERT statments allowed?
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
[code]...
how can i select a record from A table and insert into B table using linq?
View 2 RepliesBasically 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.)
Here is my source code for the page:
[Code]....
I have two tables - Student and Advert.
I want to do the following;
1. Student is logged into their account (session is authenticated)
2. StudentID is primary key for that table, it is a foreign key for table Advert
3. When student is logged in, they create advert
What I want is their student Id to be input into the advert table when this new advert is created. How is this possible?
im new to trigger concept
when i insert data in a table [DSR_OUT_PRO] data get enter in another table ie [trg_Insert_DSR_OUT_PRO] but 2 times..
Is it ok, or something is wrong..
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.
[Code]....
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.
View 1 RepliesI have a text file coming from another system that is in fixed format with header and details information regarding a request of material, looks like:
HDR 1000 HeaderDescription
DTL ProductId Productdescription qty ...........
DTL ProductId Productdescription qty ...........
DTL ProductId Productdescription qty ...........
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 have created a datatable with three fields namely TimeTask, TaskItem and Count (count of user) eg; {"12:30AM-01:00AM" , T1 , 3}.
dataTable.Columns.Add("Task Time", typeof(string));
dataTable.Columns.Add("Task", typeof(string));
dataTable.Columns.Add("Count", typeof(int));
dataTable.Rows.Add("12:00AM-12:15AM", "T1", 6);
dataTable.Rows.Add("12:45AM-01:00AM", "T1", 5);
dataTable.Rows.Add("01:00AM-01:15AM", "T1", 1);
dataTable.Rows.Add("01:15AM-01:30AM", "T2", 4);
dataTable.Rows.Add("01:30AM-01:45AM", "T2", 9);
GridView1.DataSource = dataTable;
GridView1.DataBind();
In this there is no task for the TimeTask "12:15AM-12:30AM" and "12:30AM-12:45AM" yet the TimeTask should be inserted as,
TimeTask TaskItem Count
12:00AM-12:15AM T1 6
12:15AM-12:30AM - -
12:30AM-12:45AM - -
12:45AM-01:00AM T1 5
01:00AM-01:15AM T1 1
01:15AM-01:30AM T2 4
01:30AM-01:45AM T2 9
How to chk for the missing rows? Is it possible to dynamically insert rows in an existing DataTable (No DataSource used) in this scenario.
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
tblRoute.routeID(1), tblRoute.allHaltes(halteID1)
tblRoute.routeID(1), tblRoute.allHaltes(halteID2)
tblRoute.routeID(1), tblRoute.allHaltes(halteID3)
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
DataTable changedRecordsTable = dataTable_old.GetChanges();
How do I compare it with all the records in the old datatable and pass them to the save query ?
I have AspTable which is full of data
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 a datatable with a,b,e,f as cols My table is having a,b,c,d,e,f cols for this i am using :
[Code]....
I have upload the data from datatable to sql table with exact cols.
I have a textbox and a search button i trying to search file names in a database table and display them in a datatable...
private void GetSearchResults(string machineID, string searchFileName)
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection();
connection.ConnectionString = ConfigurationManager.ConnectionStrings["SumooHAgentDBConnectionString"].ConnectionString;
connection.Open();
SqlCommand sqlCmd = new SqlCommand("SELECT FileID, BuFileName FROM BackedUpFiles WHERE BuFileName Like '%@searchFileName%' AND MachineID=@machineID", connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlCmd.Parameters.AddWithValue("@machineID", machineID);
sqlCmd.Parameters.AddWithValue("@searchFileName", searchFileName);
sqlDa.Fill(dt);
}
everything is working fine except that the searchFileName is not working in the query...
I tried putting just a value to check like
SELECT FileID, BuFileName FROM BackedUpFiles WHERE BuFileName Like '%b%' AND MachineID=@machineID
i have table called templates, when user ordered a new template then the All the values are needed to be inserted into the User Template Table
How can we do this?
SQL For this? ..........
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 have a table say, Student.
I have a class 'Student'.
I want to populate IList<Student> with objects of Student class from Student table in database.
Is it possible to implement this?
If yes then how to implement this.
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?
View 2 RepliesI 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?
View 1 RepliesI'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?