Sql - Asp.Net VB Or C# How To Combine Lines & Columns Based On Unique Primary Field
Mar 3, 2010
I have been working on a web based report from an existing client/server apps mssql database. I have a working query, that pulls this information together from many tables, and can provide the query and a result set if necessary.
The results currently are similar to this.
ID | Name1 | Date1 | Veh | PO | Stops
_________________________________________
1 | Bob 1 | 12/1 | Car | 1234 | 4
2 | Sam | 12/3 | Car2 | 2245 | 3
2 | Joe | 12/4 | Van1 | 5568 | 2
[Code]....
View 2 Replies
ADVERTISEMENT
Apr 15, 2009
I am having major problems with creating my tables and populating them.The problem for the creating seems to lie when I try to create my table named WeeklyUnitStaffRota. I get the run time error 2147467259(80004005) No unique index found for the referenced field of the primary table. This has only started happening as I have been tinkering with my tables to get them to populate properly.I seem to have a problem when I have two columns with the same info but relating to a different college ie two MSC being run at 2 different colleges.[code]...
View 1 Replies
May 3, 2012
I have a stored procedure (I cannot edit) that I am calling via linq.
The stored procedure returns values (more complex but important data below):
Customer Stock Item Date Price Priority Qty
--------------------------------------------------------
CUST1 TAP 01-04-2012 £30 30 1 - 30
CUST1 TAP 05-04-2012 £33 30 1 - 30
CUST1 TAP 01-04-2012 £29 20 31 - 99
CUST1 TAP 01-04-2012 £28 10 1 - 30
I am trying to limit this list to rows which have unique Dates and unique quantities in LINQ. I want to remove items with the HIGHER priority leaving rows with unique dates and qty's.
I have tried several group by's using Max and order by's but have not been able to get a result.
Is there any way to do this via linq?
EDIT:
Managed to convert brad-rem's answer into VB.net.
Syntax below if anyone needs it:
returnlist = (From p In returnlist
Order By p.Qty Ascending, p.Priority
Group By AllGrp = p.Date, p.Qty Into g = Group
Select g.First).ToList
View 1 Replies
May 8, 2011
Unique constraint - these columns currently doesn't have unique values..?? I have created Unique Constraint with 3 columns. My code works perfectly but once - two users @ same time entered same data and somehow it saved in DB; after that incident. This UniqueConstraint gives me error - These columns currently doesn't have unique values. How can I check if user enters this kind of entry or how can I restrict the Entry?
View 2 Replies
Aug 5, 2009
Primary key must be unique....while inserting data into the database,i need to check whether the data already exist in the primary key column or not.......If not then only data will be inserted in the database else a msgbox will be displayed and data will not get inserted in the database....
View 13 Replies
Apr 28, 2010
I want to combine multiple lists of items into a single list, retaining the overall order requirements. i.e.:
1: A C E
2: D E
3: B A D
result: B A C D E
above, starting with list 1, we have ACE, we then know that D must come before E, and from list 3, we know that B must come before A, and D must come after B and A.If there are conflicting orderings, the first ordering should be used. i.e.
1: A C E
2: B D E
3: F D B
result: A C F B D E 3 conflicts with 2 (B D vs D B), therefore requirements for 2 will be used.
If ordering requirements mean an item must come before or after another, it doesn't matter if it comes immediately before or after, or at the start or end of the list, as long as overall ordering is maintained.
This is being developed using VB.Net, so a LINQy solution (or any .Net solution) would be nice - otherwise pointers for an approach would be good.
Edit: Edited to make example 2 make sense (a last minute change had made it invalid)
View 1 Replies
Nov 4, 2010
I am trying to figure out a way of combining the below Dim statements into one line and maybe also combining Return dblResult into there. currently it is three lines and I have been told that it is possible but kind of lost of how to do it
[Code]...
View 3 Replies
Sep 20, 2009
Havent done this yet, trying to figure it out, I have been reading different post concerning temp tables to use with a report viewer and have been left confused. I have 2 tables that I would like to combine certain columns with to use in a report viewer.
I can itenerate through each table to retrieve what I am wanting, but how do you create a temp table to use in a report viewer? Will this table use an adapter and binding source?
View 3 Replies
Mar 17, 2011
[Code]....
It is returning all the columns by id. I need only the primary key. When I add the following line it is empty: WHERE SIK.indid = 1 Why is that? Is there other ways to do the same thing and working? Since I am in the rush
View 2 Replies
Jul 13, 2009
I am using VB Dot Net 2003, Frame Work Version 1.0. The backend is SQL 2000. In the Backend, there is a table with a Primary Key Column. I created a Windows Application using Dot Net. I added a form using the Wizard, connected to the SQL server, selected the said table. The dot net created the form, data connection, data adapter, data set, many fields (as many columns in the table), and the buttons Add, Update, Delete, Load, Cancel and Cancell All. I ran the form and it worked properly when I hit the Load Button. It retrieved the data from the backend. I unloaded the form and re-run the form. This time, I clicked on Add. The Primary Key field was filled with the value 0. I entered the other values and saved the form. It saved the data. In the backend the PK field (as it had the Auto-seed) was filled with a relevant data. But, the front end was not correspondingly updated.
I checked the Update Command / Insert Command of the data adapter. They use the Select statement at the end with a @@Identity in the Where Clause.
View 2 Replies
Nov 4, 2009
how to fix it : These columns don't currently have unique values.
What should i do with my database. I'm using VB.net 2005 and SQL
View 5 Replies
Aug 5, 2008
I have an access DB with 3 columns, ID#(Primary Key), Minutes, and Profit/Loss. I'm using the Total feature in access which adds a Row 'Total' to the bottom and shows the sums. How would I get the sum from the 'Minutes' column, 'Total' row, and get that to show in a text box (txt002004Min) ?
Imports System.Data.OleDb
Public Class Form13
Dim cn As OleDbConnection
[code]......
View 3 Replies
Mar 8, 2010
Ok i have 3 tables in a dataset
Table1 contains items for sale (referenced)
Table2 contains Stores (Parent)
Table3 Contains items in a particular store (Child)
the problem is the data in this case. Table 2 contains a StoreId field as a primary key. Table1 Contains an ItemId field. My problem is in table 3 this contains 3 fields, StoreId, Name and Quantity.
Now my Table1 contains a field called Name and one called PluralName, I need to determine which field in table one relates to the Name field in table3.
The following 2 constraints exist currently in my app
Table2 - StoreId (one) to Table3 - StoreId (many)
Table1 - Name (one) to Table3 - Name (one)
can i add another constraint :- Table1 - PluralName (one) to Table3 - Name (one) ???
I am presuming if i do i will get a constraint error as the property does not relate on a one to one level if the store has more than one item in stock and vice versa when it has only one in stock.
View 30 Replies
Oct 11, 2009
I have a table with a multiple columns (two) primary key. I get an error on tableadapter.Update .How can I resolve that??
View 3 Replies
Feb 17, 2012
I have a table in an access db that is viewed via a datagrid view in a vb.net front end.The primary keys for this table are PKJobNo & PKProcessNo I need to allow the user to change the order of the processeses which means changing the PKProcessNo field value and then re-ordering the grid by PKProcessNo In the example below
proc 3 becomes proc 2
proc 4 becomes proc 3
and
proc 2 becomes proc 4
I can't just change for instance process 2 to 4 as these are unique values.
PKJobNo ProcessID PKProcessNo Note
X0001 Drill 1
Drill 8mm
[code]....
View 5 Replies
Jun 15, 2011
Possible Duplicate: Retrieve identity value from inserted record's primary key.I have a field with a unique field (that I don't have to update automatically) called ID. I'm using this code to add a field to it:
DB.Snippets.AddSnippetsRow(10, 0, Leaves, Coins, Date.Now, Language, Username, Description)
What I need to be able to do is get the unique field it adds. It +1's to ID every time I add a field there, but how do I get that without hacky database looping?
View 2 Replies
Apr 6, 2011
I am creating a project in VB.NET in which one of the reports require that the name of employees should be displayed as column names and whatever work they have done for a stated period should appear in rows below that particular column.Now as it is clear, the columns will have to be added at runtime. Am using an ODBC Data source to populate the grid. Also since a loop will have to be done to find out the work done by employees individually, so the number of rows under one column might be less or more than the rows in the next column.Is there a way to create an empty data table and then update its contents based on columns and not add data based on addition of new rows.
View 1 Replies
Apr 9, 2009
how to generate unique ID based on combobox selection?
View 5 Replies
Jul 27, 2010
When user inserts a duplicate entry in a unique field, Castle ActiveRecord throws the following exception:
Castle.ActiveRecord.Framework.ActiveRecordException was unhandled
Message="Could not perform Save for SerieNotaFiscal"
Source="Castle.ActiveRecord"
StackTrace:
[Code] .....
View 1 Replies
Jun 22, 2009
Basically i have made an application that loads a text file and loads it into a rich text box
now what i want is at a push of a button i want to replace everything from line 3 onwards and between col 56 and col 113 with the same string for each line
At the min i have no code at all underneath this button part from loading the Rich Text Box by another button[code]...
View 1 Replies
Mar 8, 2011
I'm using vb.net / winforms.How can I convert 10 lines with three columns into a DataSet/DataTable?
View 1 Replies
May 19, 2010
I have multiple datasets that I would like to combine into one. There is a common ID field that can be associated to each row. Calling Merge on the dataset will add additional rows to the dataset, but I would like to combine the additional columns. There are too many fields to do this in one query and therefore would make it unmanageable. Each individual query would be able to handle ordering to ensure the data is placed in the correct row.
For Example lets say I have two queries resulting in two datasets:
SELECT ID, colA, colB
SELECT colC, colD
The resulting dataset would look like
ID colA colB colC colD
1 a b c d
2 e f g h
View 1 Replies
Jul 13, 2010
I have made several posts on here lately regarding master/detail or parent/child data setups but no one has been able to help. It seems like a subject people don't like to tackle BUT I really need help.
I'm having a real hard with the DataView.RowFilter functionality and it is driving me insane.
For the most part, I have the basic master/detail functionality working. When you select a record in the first grid, it shows the correct data in the second grid. When you enter a record in the first grid, you can enter related data in the second grid. I accomplish this by using a dataview and row filter bound to the 2nd grid. When you change the record in the first grid, the rowfilter for the 2nd grid is restated and reapplied.
The problem arises when I overwrite the primary field value in the first grid and the detail records become orphaned. You would think all I have to do is change the ID in the 2nd grid to match the new ID in the 1st grid but this is where I am having problems. I wrote a loop to change the ID of each record currently loaded in the 2nd grid to match the newly changed ID in the first grid. The code is here:
Code:
Dim I As Integer
For I = AuthorizationGroupsDGV.Rows.Count - 2 To 0 Step -1
AuthorizationGroupsDGV.Rows(I).Cells(0).Value = StrEmployeeID
Next
I have used this code before and it works great. It has to step backwards through the grid because when the ID's are changed, they no longer match the RowFilter criteria and they disappear from the grid. They still exist in the dataset. The problem is, while the code "works" the intended effect does not. Some of the records completely disappear from the dataset and do not reload when I restate the row filter against the new ID number.
View 2 Replies
Jan 4, 2012
Is it possible to show vertical columns lines for datagridview, also for empty area, as VFP grid,as in image?
View 2 Replies
Nov 3, 2009
I have the following code that replaces an item in a richtextbox with a new one. Reg-replace works fine but only when the pattern matches only one value. When there are two similar values in the richtextbox it matches them both. How can I replace based on specific lines?
[Code]....
View 7 Replies
Jul 25, 2011
I have a Dataset with a Bunch of Columns used in a Report (DevExpress XtraReports) (DataSet being the DataSource). The Dataset has many columns, and i need to read a column (based on the row type), and decide which column value to read for the row, and apply formatting based on the row type.
[Code]...
Is there a suggested way to handle this in the Report or at DataSet Level (excepting the formatting part)?
View 1 Replies
Jan 24, 2011
What i am trying to achieve is that there are 7 columns in my db table. one is primary key and rest 6 are integer columns. Now my application will allow user to input 6 values. I want to compare 6 values entered by user to those 6 integer columns and if in any row 3 or more columns match user's input Primary key of that table is returned..I have tried using queries, cursor, nested loops and i am still unable to achieve my results..
UPDATED :DB Table Structure.. I am inputting 2,3,7,11,45,65. Number of returned rows should be 5. eg..
UID A1 A2 A3 A4 A5 A6
-----------------------
1 2 3 4 5 6 7
2 2 3 4 55 56 57
3 65 11 45 66 67 68
[code]....
IDs returned should be 1,3,4,9,10
View 5 Replies
Jun 1, 2010
I have a monthly forecast showing expense accounts by cost centre so columns headings look like:
Cost Centre, Account Number, Jan, Feb, Mar etc
Using VBA code to automate the process, I need to calculate the total for each month of each account number where the 2 left characters of the cost centre are the same. I have a formula which will calculate this (for one cost centre grouping/account) which looks like this:
=SUMPRODUCT((LEFT('Sheet2'!A6:A5136,2)="BE")*('Sheet2'!C6:C5136=AccountNumber)*('Sheet2'!D6:D5136))
I am not sure whether there is a more efficient way than using a formula like that (at the moment this would be used for 12 months on about 6 cost centre groupings with about 500 accounts each - though this would vary). Its not massive, but could be slow to calculate. I don't think the subtotals command is powerful enough, and I haven't used arrays for a long time (especially for what would appear to need a dynamic, mulch-dimensional array).
View 2 Replies
Jun 2, 2011
What is wrong with this, it throws a debug error.[code]...
View 3 Replies
Jan 27, 2012
I want to change the row color of every row in my DataGridView where a particular column value = "RBF3". I am currently using the DataBindingComplete event which seems to work fine, but it fires every time I change a cells value. I'd like for this bit of code to only fire when the datagridview is first displayed, sorted, or filtered. Is this possible?
[Code]...
View 3 Replies
