Here is my situation: I use the SqlCommond to update some records in the SQL Server in a ASP.NET web site. Users can choose which records they want to update. Sometimes they may choose 40 or 60 records to update at a time.Is there any good way to do it? I do not want to do like it
foreach(string ID in List)
{
Update here
}
I have a table line (ID, LineNo, LineName) having values as :
1, 1, <blank>
2, 2, <blank>
3, 3, <blank>
ID is PK with Auto-Increment and LinNo(I am manually incrementing through ASP.Net Page while record insertion) Now i have to write a query such that it updates LineName column from line table which is blank(as shown above) with values from table linet(linenamet) having values as :
a
b
c
My trial query is :
update line set LineName = (select linenamet from linet);
It gives ERROR as : Subquery returns more than 1 row
Expected Result : After Updation it should show line table as :
1, 1, a
2, 2, b
3, 3, c
[Code]....
Update multiple records with subquery
I get the fol error: An exception of type 'System.Data.SqlClient.SqlException' Occurred in System.Data.dll but was not handled in user code..Subquery returned more than 1 value. This is not permitted When the subquery Follows =,! =, <, <=,>,> = Or When the subquery is used as an expression.
IF EXISTS (SELECT CustomerId FROM Customers where CustomerId= (SELECT Id FROM @tblCustomers ))
UPDATE Customers
SET Name=(SELECT Name FROM @tblCustomers ), Country=(SELECT Country FROM @tblCustomers )
WHERE CustomerId=(SELECT Id FROM @tblCustomers );
Else
[code]...
Is it ok to do multiple records update with the following requirements and be safe that the operation will not get timed out? A user requiring me to do the following using a web form:
I. Display a list of accounts in a GridView for multiple records insertion with no client side validation. Each account will accept new reading and posted date values.
II. Once the user presses the submit button, the following steps will be performed for each record at server side:
1. Check if new input data is for new or old period. A checkbox is to be selected in the form.
2. Check if new data has existing related records. Else this is first time insertion; get consumption result and save to db.
3. If records found, get previous reading and date.
4. Check if new reading and date is greater than previous reading/date else should inform the user.
5. Get new consumption = new reading - previous reading and some other operation.
6. A join select statement will be used to get the lowest and highest values (consumption) for current account.
7. Compare the new consumption result with previous consumption to give a status indication for new reading as either high, low or normal to be saved into the db. (# 6 values will be used here)
8. If everything goes ok, save new data into 3 different tables.
9. Display the GridView with new data.
The SQL server and the hosted page are in two different servers.
using ASP.NET 2.0 SqlDataAdapter update returns 0 records. I have a simple application that stores Passenger's ticket info in a sql database.
when a user clicks "Create Reservation" I add a Passenger Information (name, destination, PNR) to dataset
and save dataset in ViewState.
Then when user clicks update, I pull the dataset from viewState and try to update database. The update is returning 0. what i am doing incorrectly?
protected void btn_CreateReservation_Click(object sender, EventArgs e)
{
DataRow dr ;
dr = dt.NewRow();
dr["Name"] = txtBox_Name.Text; dr["Destination"] = txtBox_Destination.Text;
dr["PNR"] = Convert.ToInt32(txtbox_PNR.Text); dt.Rows.Add(dr);
dt.AcceptChanges();
ViewState["MyTable"] = dsPassengers;
[Code]....
I have to insert a multiple records under the single table.So i have to execute the command in the frontend for every records.
Inorder to avoid i write the query like this
insert into table1(field1,field2) values('1','Test1'),('2','Test2');
and also i tried
insert into table1(field1,field2)
SELECT "1","Test1"
Union all
SELECT "2","Test2"
But I am getting syntax error.
How to insert a multiple records under the single query?
I am running an Update statement against an SQL database on server 2008. The update runs but does not update the record, but returns no errors.The database is part of a commercial help desk package called TrackIt 8.5 which was just a fresh install on a new web server and a new sql server. The update is running from a web form we use to automatically create active directory accounts. After the account is created the web form closes out the work order in the Trackit db. This was working fine when the system was running on Server 2003 and SQL server 2005. Running Trackit version 7.02.The login use to access the database has full permissions to read and write to the database, just as it did on the old server which never had an issue updating. Odd thing is, if I log onto the SQL server and make a change to any single field in that record. I can then run the web form and it updates the record normally.
View 3 RepliesI have a web portal designed in ASP.NET in which we ask customers to enter the data. On the click of submit button all i have done is, just read the data and called a stored procedure which inserts that into a table. There seems to be a problem in SQL Server 2005 while inserting the data from multiple computers at the same time. We have tested in our lab with three computers, result is that we get the data inserted successfully in only one machine and on the other two machines we get error on page. I have used transactions in the stored procedure and also tried setting the isolation levels to READ_UNCOMMITTED, SERIALIZABLE and SNAPSHOT. Nothing seems to work properly.
View 4 RepliesCDC or Change data capture is a new feature in SQL Server 2008, which is an ability to record changes to table data into another table without writing triggers or some other mechanism, Change data capture records the changes like insert, update, and delete to a table in SQL server.I have sql 2005 and I have created triggers and tables CDC to capture the data. everything functions good and the data is being updated in the _CDC tables. all i need to do now is to generate the updated data as a report (excel/html)- what should I do ......?i need to show only the updated columns when user selects the date periods ( Range between dates ) and the person name - i need to display any updated info about that person during that period. So this updated columns about this person should be displayed in excel formatted column wise.
View 2 RepliesThe goal is to update multiple row in the table. In my table there is 3 col, at here i will name it col1, col2 and col 3. The number of row in the table is, let say 10. Each row will have a different value in col1 and col2, where col3 value is static.
Something like the below;
|col1 |col2 |col3
Row1 |a |b |c
Row2 |aa |bb |c
Row3 |aaa |bb |c
My update statement;
UPDATE table1 SET Col2 = CASE Col1
WHEN 'a' THEN '1'
WHEN'aa' THEN '2'
End where col3 IN (c)
In plain, i just wanna update col2, but had to base on col1 and col3 value. After I run the statement above, it will empty Row3's col2, but in fact, i don't want to update Row3's col2.
On satisfying the If condition, I need to run 2 MS SQL update queries. How can I accomplish this? I am using C# as the server side language.
View 6 RepliesI have two tables, Customer and CustomerPhone.
Single record in Customer can have multiple CustomerPhone records. As you see in the image below, Phone and Fax resides in CustomerPhone table whereas the rest of the fields resides in Customer table. If user want to edits a customer record, obviously i will have to update the record in Customer table and at least two records from CustomerPhone (Phone and Fax).
I could write two update statements, one Update customerPhone and the second update Customer table. Is there a better solution?
I have an inventory page with a gridview that shows 1 item per record, each item starts with a checkbox in the gridview. Currently users can select multiple records and hit the order button and everything has worked well. Now I have multiple pages in my gridview, and if a user selects a few records from the first page, and then goes to the second page, all the records (checked checkboxes) disappear from the first page.
[Code]....
I am trying to design my logic to do this but I want to create a dataSet which will have records from several databases, The records all have the same layout.
I am reagin the connection string paths from a table in a database.
I am asking for your help in trying figure out my logic.
Should I use the connectionString builder in conjunction with a loop to Connect, read a record into a dataset Until therer are no more records to be read from my databse table with the database name/paths tables ?
Here is my beginning code which deals with one database:
[Code]....
I have several rows in my database like this
ID Status Type Count Event
291 0 0 2 1
523 1 2 0 4
and so on. When my user performs some basic actions on my website, some values are passed through JSON to the server and are then converted into custom objects with 5 int properties(so they match the table). These objects that the server receives are the rows to be updated(based on ID) and the values for each column. How can I do this in one SQL statement without the need to loop through each object and update them all seperately? Is there a way? Preferably compatible with SQLServer 2005.
The website is over half way done and the functionality for the blog is (except for adding posts) is already implemented and working correctlyI have a SQLExpress 2008 backendBlog posts are rendered on the page with full HTML markup within a label control.
All of the above is done and working. Though I am essentially new to creating websites with ASP.NET, CSS and SQL, I am sure that I could simply carry on and make a login page with some controls that would allow me to add records (blog posts) directly to the database on the host server. However, I am fearful of doing this because I know that malicious code can be passed in this way. Also, because of my lack of knowledge, the only way that I know of to pass the code from a control to the database is to disable validation for the page the control is on. Without a doubt I do not want to do that.
So what are my options for getting blog posts into the database? Is it safest for me to fully create the post in html and update a copy of the database that resides on my local machine? If I do it this way, how can I merge the records from the database on the local machine with the records on the remote server?
I want to know how to Update / delete the table records in ASPNETDB.MDF in single update / delete query ?
View 1 Repliesi want to build a datagrid bound to a datatable with several columns. One column has only a dropdownlist with the number of the rows in the datatable.
Here a samplescreenshot:
[URL]
I know how to bind the columns to a grid and its no problem to create such a dropdown list with the content for me, too. But i dont know how to sort the complete datatabl after changing one value of one dropdown listbox.
So, how can i resort the values of a datatable column based on a given value and the datarow id?
im having a gridview in which im displaying records and status.....this gridview shows employee applied leave waiting for approvals..
so in gridview i have chabged the status button as radio button list and giving two choices as approve/reject ....so HOD seect relevant button and update the grid...
how to do this?
1) do i need to place a button on footer and do some write code behind?
2) or any other easier way?
I've got a single table, with project information and schedule information. To make the data easier to present, I created a tab control with two panels (a detailsview in each). The first tab is for project info, the second tab is for schedule info (again, all fields in the same table). The problem is, if i change info in both tabs, and then hit the update button, only the data from one tab is added to the table. I think this problem is simple to fix, and boils down to the way I have the page organized, but I can't quite figure it out.
I've boiled my code down to the following conceptualized structure:
[code]....
When you click the update button, you return to the listview, but with only the information from your current tab saved. How can I get it to update from both detailsviews?
I want to update multiple rows of gridview (only price field. for that i have added textbox) on click of update button which is outside of gridview.I have done following way
<asp:LinkButton ID="lnkUpdate" CssClass="BlueButton" runat="server" OnClick="lnkUpdate_Click">Update</asp:LinkButton>
[Code]....
Up to this its working fine but when no textbox updated then no need to go in for loop so i am looking for confiramtion before updating rows please help me how to do that becausei am less aware with javascript. another problem is textbox value disappear when page index changed how i can retain that values.
I have a user control, that has an update panel and update progress control in it.
I use this user control in more than 1 location on the same page.... problem is, when ucA posts back, I see the update progress control for both ucA and ucB. I assume this is because it is a user control and the update panel and progress are named the same?
Either way - how do I make it so that the update progress only displays for the proper user control?
I have an update panel in a user control and I am adding multiple instances of it for example 5.
When that update panel refreshes the page load occurs 5 times.
How can I prevent it only for once?
The question: Is it possible to configure multiple SMTP servers or update the SMTP object server information at runtime?
Description of problem: The site I'm working on has a few pages which send emails. Our site is using Gmail so even though it's a custom domain our email is sent out though Gmail SMTP servers.
Some emails come from the system and some emails come from users in certain groups. I have an SMTP server configured with the admin email for the system e.g. admin@mysite.com This works fine and when I get the email it shows from admin@mysite.comOne of our user groups let's call it "groupA" has an email configured on our domain i.e. groupA@mysite.com When I send the email and specify a from address for the MailMessage object as groupA@mysite.com it still shows up in my outlook as coming from admin@mysite.com I did a little further testing and if I specify both the from address and the ReplyTo as groupA@mysite.com it will show as From=admin@mysite.com but when I choose reply in outlook it shows groupA@mysite.com My best guess is that Gmail is overwriting the From address with the SMTP user to prevent email spoofing.
I found this: http://www.systemnetmail.com/faq/4.1.aspx but it won't work for me since group A, B, and C all need to send emails from the same page.