I need to update a empty column in our SQL database with the login ID for employees of our company.The table is called SY01200 and were I need to put the login ID is column INET5, and the login ID is just me stripping off the company's email address(removing the @company.com), and I need to update the INET5 column only where Master_Type = 'EMP'
And here is the Query that I am using to strip the email select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp'And here is what I thought the Query would be to update however I got and error saying more than 1 arguement returned
UPDATE sy01200
SET INET5 = (select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp')
WHERE Master_Type = 'EMP'
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
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
So to the above table I have added a new field/column named "ProdLongDescr(varchar, Null)"
So, I need to populate this newly added column with specific values for each row depending on "ProductCode" which is different forevery row. The problem is that I have 25 rows.So instead of Writing 25 individual update scripts, is there a way in which single query will do the same job instead of writing one update query for each row ?. If so can some one guide me how to achieve that OR point to me a good resource.
Below are a couple of Individual update scripts I Wrote. "ProductCode" is different for all 25 rows.
Update tblValAdPackageElement SET ProdLongDescr = 'Slideshows' WHERE ProductCode = 'SLID' And szElementDescr='Slideshow' if @@error <> 0 begin goto ErrPos end
Update tblValAdPackageElement SET ProdLongDescr = 'CategorySlideshows' WHERE ProductCode = 'SLDC' And szElementDescr='CategorySlideshow' if @@error <> 0 begin goto ErrPos end
First of all Thanks for all the help, I received over the years from MSDN..
Here is my new problem...
I have a SQL table like following table..
State
City
StartDt
EndDt
1
AK
ANCHORAGE
4/1/2007
12/31/2049
2
AK
ANCHORAGE
4/1/2007
12/31/2049
3
AK
ANCHORAGE
5/1/2006
3/31/2007
4
AK
ANCHORAGE
5/1/2006
3/31/2007
5
AK
ANCHORAGE
6/1/2004
4/30/2006
6
AK
ANCHORAGE
6/1/2004
4/30/2006
7
AK
COLDFOOT
10/1/2006
12/31/2049
8
AK
COLDFOOT
10/1/1999
12/31/2049
Now here is what I want to do€¦ 1> Sort the table based on Start Date (the picture shown is already sorted..) for example first 6 rows for AK - Anchorage 2> select the rows with same city (rows 1-6) 3> Select the rows with distinct start date (rows 1-3-5) 4> Change the End Date of the second selected row (row 3 in this case) to I day below the start date of 1 selected row. (4/1/2007 €“ 1 day = 3/31/2007) 5> proceed till end of selected rows.. 1-3-5 6> do the same thing for rows 2 and 4. 7> follow the same procedure for rest of the file.
The selected file should look like this when done..
State
City
StartDt
EndDt
1
AK
ANCHORAGE
4/1/2007
12/31/2049
2
AK
ANCHORAGE
4/1/2007
12/31/2049
3
AK
ANCHORAGE
5/1/2006
3/31/2007
4
AK
ANCHORAGE
5/1/2006
3/31/2007
5
AK
ANCHORAGE
6/1/2004
4/30/2006
6
AK
ANCHORAGE
6/1/2004
4/30/2006
7
AK
COLDFOOT
10/1/2006
12/31/2049
8
AK
COLDFOOT
10/1/1999
9/30/2006
Is there way to do this in SSIS? any recommened appprach>?
Any help with this is highly appreciated.. Thank You..
So to the above table I have added a new field/column named "ProdLongDescr(varchar, Null)"
So, I need to populate this newly added column with specific values for each row depending on "ProductCode" which is different forevery row. The problem is that I have 25 rows.So instead of Writing 25 individual update scripts, is there a way in which single query will do the same job instead of writing one update query for each row ?. If so can some one guide me how to achieve that OR point to me a good resource.
Below are a couple of Individual update scripts I Wrote. "ProductCode" is different for all 25 rows.
Update tblValAdPackageElement SET ProdLongDescr = 'Slideshows' WHERE ProductCode = 'SLID' And szElementDescr='Slideshow' if @@error <> 0 begin goto ErrPos end
Update tblValAdPackageElement SET ProdLongDescr = 'CategorySlideshows' WHERE ProductCode = 'SLDC' And szElementDescr='CategorySlideshow' if @@error <> 0 begin goto ErrPos end
I'm using a stored proceedure which should update a number of rows in atable depending on a key value supplied (in this case 'JobID'). Butwhat's happening is when I call the proc from within the program, onlyone row gets updated.SoWhen I call the proc from Query Analyser, all rows get updated.When I call the proc from within the program, only one row gets updatedAny ideas as to why this is happening??JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 073412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0^^^^Only one record gets updated, so the table ends up being...JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 473412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0Public Sub UpdateAllItems() As BooleanDim objCnn As ADODB.ConnectionDim objCmd As ADODB.CommandSet objCnn = New ADODB.ConnectionWith objCnn.ConnectionString = cnConn.CursorLocation = adUseClient.OpenEnd WithSet objCmd = New ADODB.CommandSet objCmd.ActiveConnection = objCnnWith objCmd.CommandText = "sp_UpdateJobItem".CommandType = adCmdStoredProc.Parameters.Append .CreateParameter("@Status", adInteger,adParamInput, 4, Me.Status).Parameters.Append .CreateParameter("@JobID", adInteger,adParamInput, 4, Me.iJobID).ExecuteEnd WithSet objCnn = NothingSet objCmd = NothingEnd Sub-----------------------------------------------------------------SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE dbo.sp_UpdateJobItem@JobID As int, @Status As intAS--================================================== ===========================================SET XACT_ABORT OFF -- Allow procedure to continue aftererrorDECLARE @error integer -- Local variable to capture theerror OnHoldAction.--================================================== ===========================================BEGIN TRANSACTIONUPDATE tbl_JobItemsSET Status = @statusWHERE JobID = @JobID--================================================== ===========================================-- Check for errors--================================================== ===========================================SELECT @error = @ERRORIf @error > 0BEGINROLLBACK TRANSACTIONENDElseBEGINCOMMIT TRANSACTIONENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with.
I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER PROCEDURE rptArFLSalesByCustItemized_sp @custId pCustID, @dateFrom datetime, @dateThru datetime, @itemIdFrom pItemId, @itemIdThru pItemId as set nocount on
-- define some variables for previous year declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime
-- set defaults SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem)) SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem)) SET @LYfrom=DATEADD(YEAR,-1,@dateFrom) SET @LYthru=DATEADD(YEAR, -1, @dateThru)
-- create small temp table to hold customer info Create Table #tmpArCustInfo ( custId pCustID, custName VARCHAR (30), ) -- populate customer temp table with info Insert into #tmpArCustInfo select custId, custName from tblArCust WHERE custId = @custId
-- create a temp table to hold the Data for each Item Create Table #tmpArSalesItemized ( itemId pItemId, productLine VARCHAR (12), pLineDesc VARCHAR (35), descr VARCHAR (35), LYQtySold int, LYTDQtySold int, QtySold int, LYTDsales money, totalSales money, LastInvDate datetime, )
-- populate the temp table with all of the inventory items insert into #tmpArSalesItemized select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL from tblInItem ii, tblInProductLine ip where ip.productLine = ii.productLine AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru
-- update table with this years quantities update #tmpArSalesItemized SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd where TransId IN (select TransId from tblArHistHeader where custId = @custId) AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru) AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru GROUP BY hd.partId )
-- Return the temp tables results select * from #tmpArSalesItemized, #tmpArCustInfo
drop table #tmpArSalesItemized, #tmpArCustInfo
return
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables.
Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things.
I am working with some old code that we are trying to clean up and perform some performance enhancements. The performance is now, so Very much better. From over 3 minutes to under 2 seconds.
But I am still trying to get the multiple rows into a single row. I would like to place this into a CTE to get the multiples into a single row. I just cannot get my head around how is the best, most efficient way to write the query.
This is a small example of what the rows look like in the resultset, and what I want to single to be.
DECLARE @BillingCorrect TABLE ( ContractNumber char(10) , pc1 int , pb int , om int , vp int
[Code] ....
I am not sure how to write the query to have all the data in a single row.
I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.
Sample Data:
WITH SampleData (PERSON, [DATA], [FIELD]) AS ( SELECT 1234,'04/02/2014','Date' UNION ALL SELECT 1234,'123','Department' UNION ALL SELECT 1234,80.0,'Rate' ) SELECT * FROM SampleData;
The results from the above are as follows:
PERSONDATA FIELD 123404/02/2014Date 1234123 Department 123480.0 Rate
The desired results would be:
PERSONDate Department Rate 123404/02/2014 123 80.0
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb GO CREATE TABLE ProdCodes (Prefix char(8), Code char(5)
<DetailRows> <DetailRow> <MonthNumber></MonthNumber> <Amount></Amount> </DetailRow> </DetailRows>If my variable contains following xml document as un-typed xml
[Code] ....
However, if I use a typed xml variable that is based on above schema, I cannot use OPENXML. What is the correct way of achieving same result with a typed xml doc? I am using SS2K5.
SELECT 'Type'[Type] ,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END FROM Table1 WHERE (Code BETWEEN '09' AND '12') GROUP BY Code
and the output
Column 1 Column 2 Column 3 Column 4 Type 14022731.60 0.00 0.00 0.00 Type 0.00 4749072.19 0.00 0.00 Type 0.00 0.00 149214.04 0.00 Type 0.00 0.00 0.00 792210.10
How can I modify the query to come up with output below,
Summary * The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats. * As far as I know a fetch-next-statement always returns a single row? * Using a static cursor works as aspected. * Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function create partition function fnTestPartition01( smallint ) as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
[Code]....
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.
Below is sample of data:
create table #Temp(depID int, Name varchar(50),LocationHead varchar(50)) insert into #temp values(1,'test','head1') insert into #temp values(1,'test','head2') insert into #temp values(1,'test','head3') insert into #temp values(2,'test1','head1') insert into #temp values(2,'test1','head2')
Required output
depID Name LocationHead 1test *head1,*head2,*head3 2test1 *head1,*head2
I have a job below, which takes the results and send to the users in email.But I have a question, how can I send only one email with all rows, not to send the for every row on table separated email.
;WITH ctePreAgg AS ( select top 500 act_reference "ActivityRef", row_number() over (partition by act_reference order by act_reference) as rowno, t3.s_initials "Initials" from mytablestuff order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
JobRequirements (A) JobID int QualificationTypeID int
EmployeeQualifications (B) EmployeeID int QualificationTypeID int
Employee (C) EmployeeID int EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
I need to build an update query for all my article beginning with '0.%' for my varchar field refkey, but depending on some conditions.
For example. Case st.base <> '' and st.qttbase <> 0 Theo refkey = B Case st.cos <>'' and st.qttcos <> 0 Then refkey = C Case st.refo <> '' and st.qttrefo <> 0 Then refkey = R
But my problem are That i can have Many combination on refkey, for example:
B,BC,BCR,C,CB,CBR,R...and so on.
Also i need to separate the letters with comma, like:
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
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 ?
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?
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
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.
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.
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.
