Best Practice For Updating Single Field In N Rows

Jan 16, 2006

Hi

I haven't included DDL etc as this is theoretical at this time. I can rustle up an illustrative example if required.
Following applies to disconnected environment.

In general, most edits are broad (i.e. n fields affected) against a single record. However, there are certain circumstances where a single field will be edited against a deep (n) set of records.

So business request:
User needs to access and edit n records at a time (for arguments sake n is unlikely to be enourmous - say 50 max) but only editing one single field, and always the same field. The values for this edited field will differ for each record. Initially retrieving data for the app is no problem, nor is identifying those records that have been edited. What is the best means of updating the table?

There are, to my mind, three ways of dealing with the latter senario -
1) Client calls the server n times editing a record at the time.
2) Client creates a csv string and passes to sproc. Sproc parses string using some UDF split function (probably chucking into table variable) and updates using a single set set based operation.
3) Client creates some flat file or other that is BULK inserted (or similar) by SQL Server.

3 - I think would only be an option if n was a very large number and or having the changes immediately reflected in the data is not priority.
1 - would create a chatty app and presumably put the most load on the server.

So I am left thinking 2 is (depending on circumstances) the best method. Is this fair? Are there any particular considerations gotchas I should be aware of? I know where to get hold of as many TSQL split functions as I could want so I'm not looking for code just opinions.
OR - am I just plain wrong? Is there a better alternative or am I dismissing the other two methods prematurely?

Thanks in advance

View 5 Replies


ADVERTISEMENT

Updating N Rows At Once - Best Practice (again)

Aug 14, 2006

I have put this thread on as a follow up discussion to some concepts that surfaced here:http://www.dbforums.com/showthread.php?t=1208316here:http://www.dbforums.com/showthread.php?t=1604936and also here:http://www.dbforums.com/showthread.php?t=1606555 The gist is: Is it ever correct excusable advisable to pass a non-normalised csv list of data to a stored procedure? When I first asked this I thought it was simply an implementation issue. Pat thinks it is but he also holds an ideological objection too. If I seem a little obsessed with this topic it is simply thata) I don't want to hijack yet another threadb) I am Anyway - I PMed Pat about this and he (quite rightly) suggested I post publicly. So here we go: I think I have struggled a little getting my head round the concepts you are using. And here (I think) is why: Normalisation starts with a logical model and ends with the physical implementation of the database (as I see it...). Relational theory pervades these two but extends further to SQL and database objects (views, sprocs etc). I work exclusively (with the exception of some legacy I stuff I simply must update) with the sproc database-API methodology. As such, my databases are abstracted from the other tiers. Now - I think this is the crux of it for me - I typically think of the sproc API as another tier (perhaps tier 1.5). Although the database itself and the objects that manipulate it are housed by a single application I don't actually think of the sprocs as part of the database. They are dependant upon it and inextricably intertwined with it but they are not a part of it in my eyes. As such, I don't worry about producing denormalised output and I have never (up until you got me thinking about it) worried about accepting non-normalised input. The sproc is not the database. The sproc is just a code procedure that acts upon the database. As such - if it accepts a typless, denormalised input but subsequently types (validates) it and normalises it before it makes any changes to the data within the database then I struggle to see the scale of the problem. I am writing as something of a stickler for normalisation myself - I think that rather than querying how closely one should adhere to normalisation principles I am pondering at what point these principles come into play. As a follow up question - if SQL Server sprocs were to accept (for example) a table variable as an argument would this meet your requirement (since the data is verified and normalised before it gets to the sproc) or would you require a solution like this to be an ANSI Standard too? So - that's the nub of it - comments welcome ona) The practicality of passing csv lists to sprocs and subsequently parsing them and b) How "correct" or "incorrect" this is. Blindman and Pat both object on both counts (I think).Thrasmachus didn't have a problem with it (however to be fair that was a very early response to a specific question I asked). I don't hold a particularly strong opinion either way – in fact the “loop through the changes at the client and fire the sproc n times” route is probably much easier to code in any case - I'm just interested in the idea that it violates relational theory in some way.

View 13 Replies View Related

Multiple Rows Into A Single Field

Oct 12, 2005

Hi
I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this..

CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner
(
@Crime_No varchar(20),
@Unit_ID int
)
as
begin
DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int
DECLARE @Defect_ID varchar(100)

select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID

SELECT
TBL_TR_Person_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1,
dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id,
dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone,
dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age,
dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type,
TBL_TR_ACCIDENT_VEHICLE.Registration_Number,
TBL_TR_ACCIDENT_VEHICLE.Crime_No,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre,
TBL_TR_ACCIDENT_VEHICLE.vehicle_Make,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model,
TBL_TR_ACCIDENT_VEHICLE.Unit_ID,
TBL_TR_ACCIDENT_VEHICLE.RowID,
TBL_TR_ACCIDENT_VEHICLE.UserID,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color,
TBL_TR_ACCIDENT_VEHICLE.HP,
TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity,
TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle,
TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight,
TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight,
TBL_TR_ACCIDENT_VEHICLE.Skid_Length,

(select TBL_TR_Person_OutsideDetails.OutSide_state from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state],

(select TBL_TR_Person_OutsideDetails.OutSide_City from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City]


---here I faced the problem-
/*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/

(SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5))
FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE
WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number)
select @Defect_ID



FROM
tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE
where
tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID
AND
TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No
AND
TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner

end
GO

View 2 Replies View Related

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.

Nov 18, 2007

Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department:                                            Sells:
1                                                              Meat
1                                                              Rice
1                                                              Orange
2                                                              Orange
2                                                              Apple
3                                                             Pears
The Data I would like read separated by Semi-colon:
Department:                                            Sells:
1                                                             Meat;Rice;Orange
2                                                             Orange;Apple
3                                                             Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
 
 

View 2 Replies View Related

Discarding Rows - Best Practice?

Jan 9, 2007

I have a need to filter out certain rows from my data stream. I cannot apply the filter against the source data using my DataReader component, due to some constraints in the source system. Therefore, I must filter the data out after it enters my datastream (trust me on this part).

I have created a data flow that uses the Conditional Split transformation to do this. I created one condition that matches the rows I want to discard. I then connected the Default output stream to my target table. I have simply left the "discard" output disconnected. This appear to do what I want.

My question is: is it OK to leave outputs disconnected in this fashion? It isn't really apparent when viewing the package that the conditional split is discarding rows. Is there a better way to handle this situation? For now I've just added an annotation to the package that describes what is happening.

Thanks for any help

View 8 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

How To Encrypt A Single Field Like A Password Field

May 25, 2006

without writing code in my application? Does SQL Server have stored procedure to do it?

Any help is appreciated.

Thanks.

View 14 Replies View Related

How To Encrypt A Single Field Like Password Field

May 25, 2006

without writing code in my application? Does SQL Server have stored procedure to do it?

Any help is appreciated.

Thanks.

View 7 Replies View Related

Inserting/ Updating More Than One Table From A Single Web Form

Apr 22, 2008

 i wanted to ask how to insert values from a single web form into two sql tables, i have been looking and the visual web developer i use doesnt seam to allow me to even atempt it i've tried selecting all the values from two different tables and then adding those two tables to an insert function but it doesnt work likewise the update functioni have values in a table currently a reference number and i want to use this reference number to update the address values in this table so update this field.table1 and thisfield.table2 when ref number = @ refnumber the reference number is present in both tables and is linked PK to FK  <asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:Back End DataConnectionString %>"
SelectCommand="SELECT StartDetails.StartDetailsID, StartDetails.ContractIDNo, StartDetails.ContractName, StartDetails.NINO, StartDetails.AnticipatedStartDate, StartDetails.StartDateTime, StartDetails.StartDateLetterSent, StartDetails.StartDate, StartDetails.AnticipatedEndDate, StartDetails.ActualEndDate, StartDetails.ReasonForLeaving, StartDetails.Provider, StartDetails.AdviserReferrer, StartDetails.ProvisionCat, StartDetails.Provision, ClientDetails.NINO AS Expr1, ClientDetails.CentreNo, ClientDetails.FirstName, ClientDetails.SecondName, ClientDetails.AddressLine1, ClientDetails.AddressLine2, ClientDetails.PostCode, ClientDetails.ContactTelephoneNumber, ClientDetails.MobileNo, ClientDetails.Email, ClientDetails.DateOfBirth, ClientDetails.Gender, ClientDetails.PWD, ClientDetails.Ethnicity, ClientDetails.ClientGroup, ClientDetails.RepeatStartDate, ClientDetails.CaseworkerName, ClientDetails.ClientStatus, ClientDetails.PlacementDates, ClientDetails.JobsearchDay, ClientDetails.AchievedILP, ClientDetails.JobDate, ClientDetails.JobDate2, ClientDetails.JobDate3, ClientDetails.EligibleForRolledUpWeeks, ClientDetails.NoOfWeeksClaimed, ClientDetails.MarketingWhere, ClientDetails.Notes, ClientDetails.JobCentre, ClientDetails.JobCentreRep FROM StartDetails INNER JOIN ClientDetails ON StartDetails.NINO = ClientDetails.NINO WHERE (StartDetails.StartDetailsID = @StartDetailsID) AND (StartDetails.NINO = @NINO)"
InsertCommand="INSERT INTO [StartDetails] ([NINO], [StartDate], [AnticipatedEndDate]) VALUES (@NINO, @StartDate, @AnticipatedEndDate)"

UpdateCommand="UPDATE [StartDetails] SET [StartDate] = @StartDate, [AnticipatedEndDate] = @AnticipatedEndDate WHERE [StartDetailsID] = @StartDetailsID,[NINO] = @NINO">
<SelectParameters>
<asp:QueryStringParameter Direction="InputOutput" Name="StartDetailsID"
QueryStringField="StartDetailsID" />
<asp:QueryStringParameter Direction="InputOutput" Name="NINO"
QueryStringField="NINO" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="StartDate" Type="DateTime" />
<asp:Parameter Name="AnticipatedEndDate" Type="DateTime" />
<asp:Parameter Name="StartDetailsID" Type="Int32" />
<asp:Parameter Name="NINO" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="NINO" />
<asp:Parameter Name="StartDate" Type="DateTime" />
<asp:Parameter Name="AnticipatedEndDate" Type="DateTime" />
</InsertParameters>
</asp:SqlDataSource>   your coinsideration is appreciatedChris  

View 4 Replies View Related

Updating Multiple Records In A Single Table?

Sep 3, 2014

I'm trying to update a checkbox from "False" to "True" within a single table for multiple records. I can update a single record using the script below. However, I'm having trouble applying additional Id's to the string.

(Works) - Update Name_Demo set KEY_CONTACT = 'true' where ID = 225249

(doesn't work) - Update Name_Demo set KEY_CONTACT = 'true' where ID = '225249, 210014, 216543'

It says query executes successfully but returned no rows.

View 3 Replies View Related

Transact SQL :: Updating Multiple Tables In A Single Query?

Apr 27, 2015

Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.

View 8 Replies View Related

Updating A Single Column On A Table SQL Server 2005 (45 Records), The Session Hangs...

May 12, 2007

Hello, I am pretty new with SQL Server 2005.

I have installed SQL Server Express Edition. I have migrated a set of tables from Oracl10g (by using Microsoft's Migration Tool Kit).While I am trying the following simple update command, the session hangs and it never finishes !!!!!!!!!!!!

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

select pos_key from pos_station where staff_key = 1105

POS_KEY
=======
NULL


update pos_station set pos_key = 1 where staff_key = 1105

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

The table has a few constraints and a couple of indices in place.

Then I create another table (but no contraints or indices), just copy the data from the problematic one and the update WORKS (in msecs) :



update pos_station_new set pos_key = 1 where staff_key = 1105

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Is there any way to tell if the table (any table in SQL Server) is corrupted or not ?

How can I tell if a session is waiting for something and what is that something ?

Thank you very much for your help.

Tom

View 7 Replies View Related

Updating Fact Table Field From Source Table Field

Apr 11, 2008




Hi,


I have source table , fact table and four dim. tables , I have to update a field in fact table from source table.

How can I do it?

thanks...

View 6 Replies View Related

Updating A Field Of Type Bit

Aug 4, 2004

Hi,
I want to update a field in my table whose value is a 0, to a value 1. This field is of type bit and here is the SP that I wrote to achieve this. Somhow, its giving some error when I tried executing it in the Query Analyzer. What am I doing wrong here??


CREATE PROCEDURE PublishSchedule
(
@SiteCode smallint,
@YearMonth int
)
AS

DECLARE @Active bit
IF ( (SELECT COUNT(*) FROM CabsSchedule WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode) > 0 )
BEGIN
UPDATE CabsSchedule
SET Active=1
WHERE SiteCode=@SiteCode AND YearMonth=@YearMonth
END
GO



Thanks

View 4 Replies View Related

Updating Date Field

Aug 7, 2005

All other fields are updating ok and I'm not getting an error.I am trying to update a date and time (smalldatetime) using a stored procedure.First, the info to be updated comes from a datagrid.
Dim sDate As DateTime
sDate = CType(e.Item.FindControl("tDate"), TextBox).TextThen, passed to the SQLDal class and then to the stored procedure.....
Public Function updateData(ByVal sDate As DateTime, ByVal sp As String) 'Some items snipped for easier read
Dim command As SqlCommand = New SqlCommand(sp, conn) 'Where sp is the stored procdure name
command.Parameters.Add("@date", sDate)'And so on.....And then the stored procedure....@num    VARCHAR(256),@date    SMALLDATETIME,@contact    VARCHAR(256),@notes    VARCHAR(8000),@media    VARCHAR(256) ASBEGIN DECLARE @errCode   INT
BEGIN TRAN
  -- UPDATE THE RECORDS  UPDATE dbo.tblData  SET    fldDate = @date, fldContact = @contact, fldNotes = @notes, fldMedia = @media  WHERE fldNum = @num     
<sniped>Like I said, all other fields are updated with no problems, but not the date.The date format being passed into the sp is {0:MMM dd, yyyy hh:mm tt} or Aug 05, 2005 04:39 PM Is it the format of the date? Or something else I'm not seeing...Thanks all,Zath

View 4 Replies View Related

Updating 3 Characters In A Field

Mar 5, 2002

I have a sql database that includes a table of customer contact information. The area code for many of my customers is about to change. Is there a way to mass update the phone number field so that all phone numbers that currently start with 111 change to 222 ? Ex 1115554444 to 2225554444 ?

Thanks in advance.

View 2 Replies View Related

Updating A Text Field

Apr 27, 2000

I try to update a field of text datatype using WRITETEXT statement. The information that I try to change has both the single(') and double (") quotes in it.
How can I get it done ?

Thanks
Pete

View 1 Replies View Related

Need Help Updating Part Of A Field.

Aug 28, 1999

Trying to update part of a field. Currently using ColdFusion 4.0 and SQL Server 7.0.
My field looks something like this: ABC.DEF.GHI and I just want to update the last 3 characters, GHI. The length of the field may change so it's not going to be 11 characters long.
Any help would be appreciated.

View 1 Replies View Related

Updating A Field With A Triggers

Mar 25, 2004

How would i update a field with a triggers to set the column password to something else if the password column = <NULL> on a update.

thank you very much.

View 3 Replies View Related

Updating Field But Not Filename

Jan 22, 2007

I want to change the following field in the database - text field.

Want it to be like this,

C:ProgramFilesHEATHEATSelfServiceattachmentsWinter019997.htm

want it to be H:2007Winter019997.htm

My aim is to move the files from C: to H: drive and update the database to reflect this. Thanks

Thsi is my code;

declare @str varchar(1000)
UPDATE heatgen
set @str = 'c:Program FilesHEATHEATSelfServiceattachmentsWinter019997.htm'
select reverse(@str)
select charindex('', reverse(@str))
select right(@str, charindex('', reverse(@str)))
select 'H:2007' + right(@str, charindex('', reverse(@str)))

SET gdetail = 'H:2007' + RIGHT(gdetail, CHARINDEX('', REVERSE(gdetail)))

The field is text I get error

Line 9: Incorrect syntax near '='.

Thanks.

View 19 Replies View Related

Updating A Text Field

Feb 2, 2007

I have a complex issue that has several steps.

Question 1.
I need to be able to update the following path in the database, a text field

[Info] NumAttachments=1 [Attachments] Attachment1=65513|C:Program FilesHEATHEATSelfServiceattachmentsWinter019997.htm

I need to update the path only to H:2007|( Winter019997.htm)Filename as in database.

Question 2
This needs to be done automatically so when attachments are being added this updates. How can I do this?

Question 3
I have the attachments saved in this location C:Program Files etc… I need them moved to another location on the network. How can I do this?

View 4 Replies View Related

Updating Field In Table

Apr 24, 2008

I have 3 tables and 1 view. Which are:
TOWNLAND_GEOREFERENCE_POLYGON
PlanningPointLocation
paflarea
VIEW_paapplic

The View paaplic has 100 records and I have to do the below for all 1000 records individually.
I have to update the field TP_Total in the TOWNLAND_GEOREFERENCE_POLYGON table depending on what is in the fields in the tables.
I am writing the below code but am unsure if this is going to achieve what I want.

BEGIN
Select file_number, land_use_code, pluse1_code
From VIEW_paapplic
END

BEGIN
If pluse1_code = 'A' Then
Select TP_Total From TOWNLAND_GEOREFERENCE_POLYGON
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number

Update TOWNLAND_GEOREFERENCE_POLYGON SET TP_Total As TP_Total + 1
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number

Else If pluse1_code = 'C' Then
Select Count(*) As TempCount From table paflarea
Where fk_paapplicfile_nu = file_number

Select TP_Total From TOWNLAND_GEOREFERENCE_POLYGON
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number

Update TOWNLAND_GEOREFERENCE_POLYGON SET TP_Total As TP_Total + TempCount
WHERE PlanningPointLocations.Townland = TOWNLAND_GEOREFERENCE_POLYGON.Townland
AND PlanningPointLocations.File_Number = File_Number
END

Anyone any ideas?
Jmccon

View 11 Replies View Related

Need Help Updating One Field In Every Table

Sep 14, 2007

ok, i am trying to update a database at work for a product we are developing.

i need to run this command
update <tablenamehere> set value = Replace(value, 'GeoLynxAO_Henrico', 'GeoLynx')
on every tabe in the database

is there a simple way to do this while pulling the table names out of information_schema.tables?

i have searched using google and been unable to find anything so far. the db server is running sql server express 2005 and i'm doing this from sql server management studio express

i really don't want to have to type the update statement by hand for 90+ tables................

View 4 Replies View Related

Updating Multiple Rows

Mar 17, 2008

How would I update a table where id = a list of ids?Do I need to parse the string idList? I am being passed a comma seperated string of int values from a flex application.example: 1,4,7,8  Any help much appreciatedBarry  1 [WebMethod]
2 public int updateFirstName(String toUpdate, String idList)
3 {
4 SqlConnection con = new SqlConnection(connString);
5
6 try
7 {
8 con.Open();
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = con;
11 cmd.CommandText = "UPDATE tb_staff SET firstName = @firstName, WHERE id = @listOfIDs";
12
13
14
15 SqlParameter firstName = new SqlParameter("@firstName", toUpdate);
16 SqlParameter listOfIDs= new SqlParameter("@listOfIDs", idList);
17
18
19
20 cmd.Parameters.Add(firstName);
21 cmd.Parameters.Add(listOfIDs);
22
23 int i = cmd.ExecuteNonQuery();
24 con.Close();
25 return 1;
26
27 }
28 catch
29 {
30 return 0;
31 }
32 }
33
34
  

View 3 Replies View Related

Updating 2 Or More Rows At The Same Time

Jul 23, 2004

Hi,

I am working on a SQL Server table designed by a partner company and cannot change the data structures. There is a table with a list of people available for calling out to a security system (keyholders).

From a web form, I need to allow my users to change the telephone calling order of the keyholders in the table.

The two important fields are AccountCode nVarChar and CallOrder nVarChar - where AccountCode + CallOrder must be unique.

As an example, the table may contain records with the following data..

1234, 1, Fred
1234, 2, Bert
1234, 3, Bob

If the user wants to make Bob the number 1 keyholder, Fred number 2 and Bert number 3 - what is the best practice for me to approach the update ?

Is this a job for ADO.Net or T-SQL ?

Thanks in advance.

Steve.

View 3 Replies View Related

Updating/Inserting Rows

Jul 16, 2002

Does anybody have a sample SQL script that will select table A and compare it to table B. If a row exists in both table A and table B, it will update the columns in table B with the columns in table A. If the row does not exist in table B, it will insert a row in table B using the row in table A. Is this possible?

Thanks Mitch

View 2 Replies View Related

Updating Grouped Rows...

Jul 9, 2004

I am new here, and I am sure this is a simple query, but im being forcefed database chores from my job, so i have to teach this stuff to myself/get help from places like this,
I need help with a query,
lets say that there are columns a,b,c,d,e,f,g
if columns c,d,e are the same, than I want the info in column g changed to the info in column b in the first record of that group
the reason I am doing this is,
I have like items (sku's) grouped in my database, and i want to create a blanket part number for skus that have matching descriptions which is the information in colums c,d,e,
I want to link them to the part number of the first product with that description, and add that part number in a new column at the end of the grouped SKU's record

this is what i start out with

a b c d e f g
2 4 5 6 9 8
2 5 5 6 9 9
2 7 5 6 0 5
1 2 3 4 5 6
1 3 3 4 5 7
1 4 3 4 5 8
1 5 3 4 5 9

i want to end up with
a b c d e f g
2 4 5 6 9 8 4
2 5 5 6 9 9 4
2 7 5 6 0 5 7
1 2 3 4 5 6 2
1 3 3 4 5 7 2
1 4 3 4 5 8 2
1 5 3 4 5 9 2

View 2 Replies View Related

Updating Rows Of A Column

Apr 10, 2008

Hi, I need to update column week14 in table PastWeeks with data from Eng_Goal and then result of some calculation from table AverageEngTime in the row Goal and Used respectively. I used the following and was not successful. Please advice. Thank you.

Update Pastweeks
set
week14 = (SELECT Eng_Goal,((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average')
where Weeks = ('Goal','Used' )

View 8 Replies View Related

Need Help In Updating Multiple Rows

Jul 23, 2005

Hi,New to writing sql scriptI get this error in my sql scriptServer: Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated.I want to write a single sql script which will update column 1 (FK)in table A with column 1 (PK) in table BHere's an example of what I need to doTable DATA_A-------------column C1 (Foreign Key)111111222222333333334455Table DATA_B------------column C1 (Primary Key) Column C2 Column C311 ABC NULL12 ABC 2004-12-1222 EFG NULL23 EFG 2003-12-1233 HIJ NULL34 HIJ 2003-12-1244 KLM 2005-02-0255 JJJ NULLI need to update Table DATA_A set column C1 with 11 data to point toTable DATA_B column C1 with 12 data. Currently, the problem is theTable DATA_A Column C1 is pointing to the wrong primary key which hasNULL data in COLUMN C3. I need to point to the correct Primary Key withDate filled in Column 3. The two primary key is tied together bycolumn C3.Here's my SQl scriptUPDATE DATA_A SET C1 =(SELECT C1 FROM DATA_BWHERE C2 in(SELECT B1.C2 FROM DATA_B B1WHERE EXISTS(SELECT * FROM TABLE_B B2 WHERE B2.C3 is NOT NULL)AND EXISTS(SELECT * from TABLE_B B2 WHERE B2.C3 is NULL)AND B2.C2 = B1.C2GROUP BY B1.C2HAVING COUNT(B1.C2) = 2)AND C3 IS NOT NULL)WHERE(SELECT C1 FROM DATA_BWHERE C2 in(SELECT B1.C2 FROM DATA_B B1WHERE EXISTS(SELECT * FROM TABLE_B B2 WHERE B2.C3 is NOT NULL)AND EXISTS(SELECT * from TABLE_B B2 WHERE B2.C3 is NULL)AND B2.C2 = B1.C2GROUP BY B1.C2HAVING COUNT(B1.C2) = 2)AND C3 IS NULL)Thanks - Been struggle at this for a whileMLR

View 1 Replies View Related

Updating Rows Problem

Jan 28, 2008

Hi,
I am preparing a project that contains multiple tables, because of huge operations to be performed, so i split these into two forms. I used one form for "Inserting Data in the multiple tables" and another form used for "Updating and Deleting Data from the multiple tables". I used a common identification number in all the tables are "License_Number" for inserting, retreving, updating and deleting the data. I used tabcontrol to place lot of controls on it.

The form-1 which is used for Inserting, works fine.
The problem occuring in the second form which i used for updating.

The following code fill the data into the Textboxes and as well as in the datagridview. Below two tables "SetBack_Details" and "Far_Details" are fill the data in datagridview.

Private Sub FillDataFromAllTables(ByVal LicenseNumber As Long)

Try
'Fill Owner Personal Details Data
CheckOwnerID = RetrieveOwnerIDFromPD(LicenseNumber)
Me.Owner_Personal_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Owner_Personal_Details, CheckOwnerID)

'-------------------------------------------------------------------
'Fill Project Details Data
Me.ProjectDetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.ProjectDetails, LicenseNumber)

'-------------------------------------------------------------------
'Fill Consultant Details Data
Me.Consultant_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Consultant_Details, LicenseNumber)
'Fill Applicant Details Data
Me.Applicant_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Applicant_Details, LicenseNumber)
'-------------------------------------------------------------------
'Fill Property Details Data
Me.Property_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Property_Details, LicenseNumber)
'-------------------------------------------------------------------
Me.SetBack_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.SetBack_Details, LicenseNumber)
'-------------------------------------------------------------------
Me.FAR_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.FAR_Details, LicenseNumber)
Catch ex As Exception
MessageBox.Show(ex.Message.ToString, "Project Details")
End Try
End

The data which i retrieves in a single record easily updated but problem occurs in DataGridView.

The data which binds to textboxes is easily updating because of a single record. Example is below
UPDATE PROJECTdETAILS SET PNAME=@PNAME, ZONE=@ZONE, ZONEAREA=@ZONEAREA WHERE LicenseNumber=@LicenseNumber

The data which binds to datagridview is problem. I know the following statement is not appropriate to update the data rows in datagridview.
UPDATE SETBACK_DETAILS SET DIRECTION=@DIRECTION, BORDERS=@BORDERS, DIMENSION=@DIMENSION WHERE LICENSENUMBER=@LICENSENUMBER
What happening to the above statement is , all rows with this LICENSENUMBER contains same data. Before updating data contains in different information but after updating which ever last row is updating, it contains the last row values to all records.

Example :
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
.
.
The following code i used to save the data
Private Sub SaveData()
Me.ValidateChildren()
Me.ProjectDetailsBindingSource.EndEdit() 'EndEdit for Project Details
Me.Consultant_DetailsBindingSource.EndEdit() 'EndEdit for Consultant Details
Me.Applicant_DetailsBindingSource.EndEdit() 'EndEdit for Applicant Details
Me.Property_DetailsBindingSource.EndEdit() 'EndEdit for Property Details
'Me.SetBack_DetailsBindingSource.EndEdit() 'Endedit for SetBack_Details

Me.ProjectDetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.ProjectDetails) 'Update Project Details
Me.Consultant_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Consultant_Details) 'Update Consultant Details
Me.Applicant_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Applicant_Details) 'Update Applicant Details
Me.Property_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Property_Details)
'Me.SetBack_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.SetBack_Details)
End Sub


Please help me in the regard how to update the data in DataGridView which contains multiple recrods with different information for a single LicenseNumber. I try so many times with different sort of techniques but i fails, sometimes i get concurrency violation error and many more.
Hope anybody understand my problem. Please help me in this regard.

Thanks.

Best Regards,
Kashif Chotu


View 12 Replies View Related

Need Help With Sqldatasource And Updating A Field In Sql 2005

Nov 19, 2007

I am experiencing some wacky errors here. While trying to update a field that does not allow nulls and the default value is set to '', I keep receiving an exception error that:
 Cannot insert the value NULL into column 'image_name', table 'DB_123871.dbo.tWebBlogs'; column does not allow nulls. UPDATE fails. The statement has been terminated.
 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlGetBlogs" DataKeyNames="article_id" AutoGenerateColumns="False" CssClass="GridView" CellPadding="4" HorizontalAlign="Center" Width="875px">
<Columns>
<asp:CommandField CancelImageUrl="~/images/Cancel.gif" EditImageUrl="~/images/Edit.gif"
UpdateImageUrl="~/images/Update.gif" ButtonType="Image" HeaderText="Edit" ShowEditButton="True">
</asp:CommandField>
<asp:BoundField DataField="article_id" HeaderText="ID" ReadOnly="True" />
<asp:TemplateField HeaderText="Artilce Header">
<EditItemTemplate>
<asp:TextBox ID="ArticleHeaderTxt" runat="server" Text='<%# Bind("article_header") %>' Width="250"></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleHeaderTxtReq" runat="server" ControlToValidate="ArticleHeaderTxt" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Header Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ArticleHeaderLbl" runat="server" Text='<%# Eval("article_header") %>' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Article Description">
<EditItemTemplate>
<asp:TextBox ID="ArticleDescriptionTxt" runat="server" Text='<%# Bind("article_description") %>' Width="325" Rows="8" TextMode="MultiLine"></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleDescTxtReq" runat="server" ControlToValidate="ArticleDescriptionTxt" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Description Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ArticleDescriptionLbl" runat="server" Text='<%# Eval("article_description") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Link Short Text">
<EditItemTemplate>
<asp:TextBox ID="ArticleLinkTxt" runat="server" Text='<%# Bind("short_link_text") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleLinkTxtReq" runat="server" ControlToValidate="ArticleLinkTxt" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Link Text Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ArticleLinkLbl" runat="server" Text='<%# Eval("short_link_text") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Article Link">
<EditItemTemplate>
<asp:TextBox ID="ArticleLink" runat="server" Text='<%# Bind("article_link") %>' Width="250"></asp:TextBox>
<asp:RequiredFieldValidator ID="ArticleLinkReq" runat="server" ControlToValidate="ArticleLink" Display="Dynamic" EnableClientScript="true" ErrorMessage="Article Link URL Required" SetFocusOnError="true"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:HyperLink ID="ArticleLinkLnk" runat="server" CssClass="LinkNormal" NavigateUrl='<%# Eval("article_link") %>'
Target="_blank" Text="View Link"></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Image Name">
<EditItemTemplate>
<asp:TextBox ID="Image1Txt" runat="server" Text='<%# Bind("image_name") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Image ID="Image1Img" runat="server" AlternateText='<%# Eval("image_name") %>' ImageUrl='<%# Eval("image_name", "~/Blogs/Images/Thumbs/{0}") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date Entered">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("article_date_entered", "{0:d}") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("article_date_entered", "{0:d}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Active?">
<EditItemTemplate>
<asp:CheckBox ID="ActiveChk" runat="server" Checked='<%# Bind("active") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:CheckBox ID="ActiveChk" runat="server" Checked='<%# Eval("active") %>' Enabled="false" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete?">
<ItemTemplate>
<asp:ImageButton ID="DeleteBtn" runat="server" AlternateText="Delete Record" CommandName="Delete"
ImageUrl="~/images/Delete.gif" OnClientClick="return confirm('Are you sure you want to delete this blog?');" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle HorizontalAlign="Left"></RowStyle>
<EditRowStyle Font-Bold="False"></EditRowStyle>
<HeaderStyle CssClass="GridViewHeader" HorizontalAlign="Left"></HeaderStyle>
<AlternatingRowStyle CssClass="GridViewAltRow"></AlternatingRowStyle>
</asp:GridView>
</td>
</tr>
<tr>
<td>
<div style="text-align: center;"><asp:Label ID="recordset_lbl" runat="server" CssClass="HelpTextNormal"></asp:Label></div>
</td>
</tr>
</table>
</div>

<asp:SqlDataSource ID="SqlGetBlogs" runat="server" ConnectionString="<%$ connectionStrings:dbconn1 %>"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT article_id, article_header, article_description, image_name, short_link_text, article_link, article_date_entered, active FROM tWebBlogs ORDER BY article_date_entered DESC"
SelectCommandType="Text"
UpdateCommand="UPDATE tWebBlogs SET article_header=@article_header, article_description=@article_description, image_name=@image_name, short_link_text=@short_link_text, article_link=@article_link, active=@active WHERE article_id=@article_id"
UpdateCommandType="Text"
DeleteCommandType="Text"
DeleteCommand="DELETE tWebBlogs WHERE article_id=@article_id">
<DeleteParameters>
<asp:Parameter Name="article_id" />
</DeleteParameters>
</asp:SqlDataSource> 
 Please help! I am stumped!

View 1 Replies View Related

Gridview Date Field Not Updating

Jan 31, 2008

Hi - Once again I've been looking at this forever and not able to see the problem.  Have a grid table everything updates except the training date field.  That get's wiped out each time - no matter if something is in it or not.  Everything else updates correctly.
Here's the code: 
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><Columns><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True"><HeaderStyle Font-Bold="True" Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Names="Verdana" Font-Size="Small" /></asp:BoundField><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate><HeaderStyle Font-Bold="True" Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Names="Verdana" Font-Size="Small" /></asp:TemplateField>
<asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>" ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"UpdateCommand="UPDATE [trainingLog] SET  [trainingDate] = @trainingDate WHERE [eventID] = ?" >
<UpdateParameters><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>

View 2 Replies View Related

Error Updating A DateTime Field

Oct 11, 2005

Hi, I'm having trouble updating a DateTime field in my SQL database.  Here is what I'm trying to do....I retrieve the existing value in the DateTime field (usually a bum date like 1/1/1900 00:00:00:00), then put it in a variable.  Later, depending on some conditions, I'll either update the DateTime field to today's date (which works great) or set it back equal to the existing value from the variable (this one messes up and says "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. ").  There is a ton more than this but here are the relevant snippets:<code>Dim CompDate As DateTimeDim aComm As SQLCommand Dim aReader As SQLDataReader Dim bSQL,bConn As String bSQL= "SELECT CompleteDate,StatusOfMarkout FROM Tickets WHERE TicketName=" _ & CHR(39) & Trim(Ticket.Text) & CHR(39) bConn = serverStuff aConn = New SQLConnection(bConn) aComm = New SQLCommand(bSQL,aConn) aConn.Open() result = aComm.ExecuteReader() 'fills controls with data While result.Read()    CompDate = result("CompleteDate")    PreviousMarkoutStatus.Text = result("StatusOfMarkout") End While result.Close() aConn.Close()sSqlCmd ="Update OneCallTickets CompleteDate=@CompleteDate, StatusOfMarkout=@StatusOfMarkout WHERE TicketFileName=@TicketFileName" dim SqlCon as New SqlConnection(serverStuff) dim SqlCmd as new SqlCommand(sSqlCmd, SqlCon) If Flag1List.SelectedItem.Value = "No Change" Then    SqlCmd.Parameters.Add(new SqlParameter("@Flag1", SqlDbType.NVarChar,35))    SqlCmd.Parameters("@Flag1").Value = PreviousMarkoutStatus.Text    SqlCmd.Parameters.Add(new SqlParameter("@CompleteDate", SqlDbType.DateTime, 8))    SqlCmd.Parameters("@CompleteDate").Value = CompDateElse   SqlCmd.Parameters.Add(new SqlParameter("@Flag1", SqlDbType.NVarChar,35))    SqlCmd.Parameters("@Flag1").Value = CurrentStatus.Text    SqlCmd.Parameters.Add(new SqlParameter("@CompleteDate", SqlDbType.DateTime, 8))    SqlCmd.Parameters("@CompleteDate").Value = Today()End IfSqlCon.Open() SqlCmd.ExecuteNonQuery() SqlCon.Close()</code>Can anybody help me with this?  Thanks a bunch

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved