.NET Class To Hold Single Disconnected Record? Nothing Smaller Than DataSet?
Jul 9, 2007
What is the most efficient standalone .NET class that can hold a single disconnected record? The class must also retain column names, but other schema is not relevant (.NET data type is sufficient).
If I understand System.Data.Common.DbDataRecord, it provides an interface on a DbDataReader, and has no storage of its own.
I'm familiar with DataSet, is that the only .NET-standard class to do this?
We're constructing a three-tier application. We want the middle tier to extract a recordset from the database, disconnect from the database, then pass the recordset to the front tier. After changes have been made by the front tier, it will pass the recordset back to the middle tier, which will reconnect, and update the database. The problem: Using stored procedures, the recordset is no longer available once the connection has been closed. Is there any way, using stored procedures, to be able to keep the recordset available after the connection has been closed, either by preserving it, copying it, or otherwise?
Hi all:I restored one backup database (7.9 GB mdf) on two diffrent servers. Ishrunk them by clicking "Move pages to beginning of file beforeshrinking".After shrinking, one mdf file is 6.7 GB, and the other is 4.2GB. Ishunk again and again:1. the 6.7GB become 5.9GB, 5.2GB, 4.7GB and 4.2Gb (four times)2. the 4.2Gb become 4.0GB (just one more time)It is wired, I am wondering the mdf will be smaller and smaller if Icontinue to shrink them? What is the reason?ThanksWJ
I created a report using RS with a SQL stored procedure (with date range parameters and a cursor to build the data) as the data source. When I execute the proc in SQL it returns over 300 records. When i go the the data tab on my report view the data, the proc and the preview only returns the first row. What am i doing wrong? I am new to RS.
I'm using strongly typed datasets in my first ASP.NET 2.0 web application. I come from ASP Classic, not an earlier version of .NET, and feel like I'm in another world. I'm slowly getting my head around datasets, but one thing I can't find any information on is how to read the data in a single record?I'm not talking about for next loops, or accessing row information as a repeater or other control is filled. I'm talking about reading the data returned by a method that returns a table containing a single record. This is what I have: Dim BookAdapter As New BooksTableAdapters.BooksBBTableAdapter Dim organizations As Books.BooksBBDataTable Dim organization As Books.BooksBBRow organizations = BookAdapter.GetDataByOneBook(sBookID) Dim sDropOff As String Dim sOrganization As String Dim sContact For Each organization In organizations If organization.DropOff = 1 Then sDropOff = "True" Else sDropOff = "False" End If sOrganization = organization.Organization sContact = organization.ContactName Next sBody = "Books Listing" & Chr(10) & Chr(10) sBody = sBody & "Organization: " & sOrganization & Chr(10) sBody = sBody & "Contact Name: " & sContact & Chr(10) sBody = sBody & "Email: " & organization.Email & Chr(10) sBody = sBody & "Phone Number: " & organization.Phone & Chr(10) I'm using the FOR NEXT, but this is silly since I only have one record. GetDataByOneBook(sBookID) does exactly what is says, it returns a single book with a specific bookID.Not only is this silly, it doesn't work. Using sContact as an example, it's Dim'd as a string. In the FOR NEXT loop, organization.ContactName has the right value, and it appears to be assigned to sContact correctly, but when I try to use sContact in sBody, I get an error saying that sContact has been used before it is assigned a value. Maybe the variables lose their scope outside the loop? Maybe I could get around this by building sBody inside the loop, but there has to be a better way! Diane
I want to display the total no. of Addition, Removals and Relocations during the past 3 months in a paticular Area.
I have written the query for this.
select q.[areaname] as Area,
classCount as TotalCount, ReqType
from ( select a.areano,
a.classCount, ReqType
from ( select areano,
count(*) as classCount, reqdtls as ReqType
from WebSummit
join RequestType
on WebSummit.reqid = RequestType.reqid
where date_created >= dateadd(mm, -3, getdate())
group by areano, reqdtls
) a
join WebSummit b
on a.areano = b.areano
group by a.areano,
a.classCount, ReqType
) p
join Area q
on p.areano = q.areano
The query works fine and has been tested. Now I want to display this in chart format.
The display should be in a chart format, one for each area showing a bar for each RequestType.
I have placed "TotalCount" in the Data Fields section and ReqType in Category fields. How do I use the Filter to set an expression so that it displays the data in a seperate chart for each Area.
I tried using =Fields!Area.Value and set it to Area1 to display only Area1 values, however the preview shows nothing.
hi I have 2 fields in my table by names "usercode" and "pass" and the "usercode" is the primarykey. I want when the user writes the usercode and leave the textbox, check if there is a usercode like the textbox content return true to the user.
hi, i am having 2 columns in a table in a dataset. i want to add those two columns and bind the resultant total as a single column to the datagrid. is it possible. if yes, how o acheive this? please help me. thanks in advance. muppidi.
I'm retreiving data from a SAP BW data source. In the dataset designer I can build a query that returns a single value, the data returns successfully in the designer. When I switch to the layout mode I can place the field of the datset in table / matrix / textbox. However when i switch to preview mode or deploy the report and try to see them in Report Manager the report fails to render with the following message:
Query Execution Failed for dataset 'XXX'
These columns don't currently have unique values.
Its a single cell datset?!?!
I can successfully retreive data from other SAP Infocubes and queries. I also cannot replicate the error using a SQL Server datasource. The stack trace error looks like this:
w3wp!processing!6!5/9/2007-16:57:41:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'. ---> System.ArgumentException: These columns don't currently have unique values. at System.Data.ConstraintCollection.AddUniqueConstraint(UniqueConstraint constraint) at System.Data.ConstraintCollection.Add(Constraint constraint, Boolean addUniqueWhenAddingForeign) at System.Data.DataTable.set_PrimaryKey(DataColumn[] value) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.CreatePrimaryKeys(DataTable table, InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Populate(InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Microsoft.ReportingServices.XmlaClient.IMetadataCache.Populate(InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.ObjectMetadataCache.Microsoft.ReportingServices.XmlaClient.IObjectCache.Populate() at Microsoft.ReportingServices.XmlaClient.CacheBasedFilteredCollection.PopulateCollection() at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollectionInternal.Find(String index) at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollection.Find(String index) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.FindOptionalPropertyName(String internalColumnName, CubeDef cube, Boolean fIsYukonSP1) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.GenerateColumnInformation(CubeDef cube, Boolean fIsYukonSP1) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader..ctor(AdomdDataReader dataReader, CubeDef cube) at Microsoft.ReportingServices.DataExtensions.SapBw.SapBwCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery()
Any ideas why this happening and how I can get around it?
Thanks
Sanjay
ps Why would you want to put a unique constraint on a read only single table dataset?
I want to design a report in which it will contain fields derived from 2 different stored procedures. I understand a 'table' can display data from a single dataset. How can i bind these two stored procedures into a single dataset so as when i click on the table and use its property 'DataSetName', to be able to select the dataset which holds all columns from stored proc 1 and stored proc 2. How can i link multible tables ( multible stored procedures with different column names in each one) into a single dataset to feed the report?
I'm looking to build a report that is basically a series of line charts that is plotting a value over time. I can accomplish this one chart/dataset at a time, but I believe with 'List's this can be achieved in a more automated fashion.If I have some data similar to this:
I want the charts grouped by the Area_ID, and then each Location_Name is a Series in that chart. So in this example, I would end up with 2 charts (Areas 1 & 2), each have 2 series (Area 1: Site1, Site2 and Area 2: Site3, Site4)Is it possible to build this with a list? or do I need to structure the data differently?
I have an issue in generating the report in sql reporting services. I need to display a report in a table format. The datas of the table should be from two different sql tables. I have tried to write a stored procedure that returns two result sets from two different tables. As reporting services takes datas only from the first result set, i tried to write two different stored procedures each displays one result set. Then i have created two datasets with that two different stored procedure. Even then i cannot proceed as i was not able to use two different dataset in a single table because i was setting the datasetname to one dataset, when i try to retrieve the fields from another dataset i was able to retrieve only first and count values. Then i tried using sub reports. As sub reports for a dataregion(table) repeats for every row of the main report i was not able to fetch the correct datas. Atlast i have tried combining the query using join and wrote a single stored procedure. This stored procedure returns a single result set retrieves data from two tables satisfying the conditions. The issue i am facing with this is, the first table has only one row satisfying the condition and the second table has three rows satisfying the same condition, as i am using join query for the three rows returned by the second table the first table datas are getting duplicated for the rest of the rows in the second table. As I found using join query is the only resolution for the output which I need, and also I have to avoid the duplication of the records. Hence let me know for any solutions.
I have shown the sample datas that is duplicating which is indicated as bold. Phonenumber, Attemptdate and calloutcome are from first table and start time and endtime is from second table. As there are three different datas for the second table, first table datas are duplicated
Hi, I have 2 tables in my database PrescriptionHeader and PrescriptionDetails.My PrescriptionHeader table has the following fields:PrescriptionID -identity fieldPatientID PatientfNamePatientlname PrescriptionDetails table has the following fields:PrescriptionDetailID -identityPrescriptionID -from PrescriptionHeader table MedicineDosage The function InsertPrescription inserts values into the table PrescriptionHeader. I want the same function to then insert the value of MedicineDosage into PrescriptionDetails with the same PrescriptionID inserted into PrescriptionHeader. How do I tell the function to insert the PrescriptionID that was automatically inserted into PrescriptionHeader also into table PrescriptionDetails . How do I return the identity before proceeding to insert into PrescriptionDetails table?ThanksFunction InsertPrescription(ByVal PatientID As String, _ByVal PatientFname As String, _ByVal Patientlname As String, ByVal MedicineDosage as String)Dim DBAdapter As SqlDataAdapterDim DBDataSet As DataSetDim SQLString As StringDim DBCommandBuilder As SqlCommandBuilderSQLString = "SELECT * FROM PrescriptionHeader WHERE PrescriptionId = ''"DBAdapter = New SqlDataAdapter(SQLString, DBConnection)DBDataSet = New DataSetDBAdapter.Fill(DBDataSet)Dim AddedRow As DataRow = DBDataSet.Tables(0).NewRow()AddedRow("PatientID") = PatientIDAddedRow("PatientfName") = PatientFnameAddedRow("Patientlname") = PatientlnameDBDataSet.Tables(0).Rows.Add(AddedRow)DBCommandBuilder = New SqlCommandBuilder(DBAdapter)DBAdapter.Update(DBDataSet) End Function
Hai Guys, I have a doubt Regarding SqlDataReader i can able to create object to Sqlconnection,Sqlcomand etc... but i am unable to create object for SqlDataReader ? Logically i understand that SqldataReader a way of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited. sqlDatareader belongs to which class is it sealed or static class? can we create own class like SqldataReader ....... Reply Me ...... if any one know the answer..............
This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:
DELETE FROM Invoices WHERE InvoiceID = 153345
Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.
Can you please assist me on how to get the 2nd record in case there are3 or more records of an employee, the query below gets the MAX and MINBasicSalary. However, my MIN Basic Salary is wrong because I should getthe Basic Salary Prior to the 1st Record (DESC)in case there are 3 ormore records and not the last Basic Salary of the Last Record.How to GET the 2nd Row of Record in Case that There are 3 or morerecords IN A SINGLE ROW ???---------------------------------------------------------------------------*-----This query gets the Max and Min Basic Salary on a certain Date Range.In case there are 5 records of an employee on certain date range howcan I get the record before the Max and would reflect as my OLDBASIC,if I use TOP2 DESC it will display 2 records. I only need one recordwhich should be the Basic Salary before the 1st record on a DESC order.Please add the solution to my 2nd Select Statement which get theOLDBASIC salary Thanks ...SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,E.SectionCode, E.Department, E.DateHired, E.Remarks,(SELECT TOP 1 ([BasicSalary])FROM empsalaries AS T14WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND@ToDateORDER BY startdate DESC) AS NEWBASIC,******************************* BELOW I SHOULD ALWAYS GET THE BASICSALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ???(SELECT TOP 1 ([BasicSalary]) (FROM empsalaries AS T14WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND@ToDateORDER BY startdate ASC) AS OLDBASICFROM dbo.Employees EWHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @FromDate AND@ToDate ORDER BY E.LastName
Can someone please tell me which type of Isolation Levels in the Database Engine to avoid the above side effects respectively.
For Record Locking issue: Example, two editors make an electronic copy of the same document. Each editor changes the copy independently and then saves the changed copy thereby overwriting the original document. The editor who saves the changed copy last overwrites the changes made by the other editor. This problem could be avoided if one editor could not access the file until the other editor had finished and committed the transaction.
For the above example, is it possible to do like this way: Editor 1: SELECT and Lock a record Editor 2: Before SELECT the record, check for the record whether it is locked or not. If it is not lock, then the record can be selected
I am using a drop down list box to select values from an SQL 2000 DB. I have put a record into the table which says"...Select Item"I used the dots so that It will apear at the top of the list and therefore display at startup. I also have a gridview to allow users to edit and delete from the same table.I am worried that a user may accidently delete the "...Select Item" row from the table.Can anyone think of a way round this? Is it possible with SQL Server 2000 to stop a single record from being deleted?Any help would be most appreciated.ThanksPaul
HiIs it possible to return the results of a query so that instead ofhaving say 10 rows its concatenated, egMy query returns 'M' 10 times, can this be returned as 'M M M M M M MM M M'?ThanksLee
a record value instead of aggregated value with GROUP BY?
Assume that I have a PRODUCT_COMMENT table defined as below. It logs the multiple comments for products. A product may have multiple comments logged at different time.
Code Block
CREATE TABLE [dbo].[PRODUCT_COMMENT]( [COMMENT_ID] [int] IDENTITY(1,1) NOT NULL, [PRODUCT_ID] [int] NOT NULL, [COMMENT] [nvarchar](50) NULL, [UPDATED_ON] [datetime] NOT NULL, CONSTRAINT [PK_PRODUCT_COMMENT] PRIMARY KEY CLUSTERED ( [COMMENT_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[PRODUCT_COMMENT] WITH CHECK ADD CONSTRAINT [FK_PRODUCT_COMMENT_PRODUCT] FOREIGN KEY([PRODUCT_ID]) REFERENCES [dbo].[PRODUCT] ([PRODUCT_ID]) GO ALTER TABLE [dbo].[PRODUCT_COMMENT] CHECK CONSTRAINT [FK_PRODUCT_COMMENT_PRODUCT]
I would like to use the following SQL statement to get the latest comments for all products.
Code Block
SELECT PRODUCT_ID, COMMENT, UPDATED_ON FROM PRODUCT_COMMENT GROUP BY PRODUCT_ID HAVING UPDATED_ON = MAX(UPDATED_ON)
But this leads to the following error:
Code Block
Column 'PRODUCT_COMMENT.UPDATED_ON' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like?
Is me again,and now i facing problem to retrieve a single record from the database. here is my code:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim user As String user = TextBox1.Text Dim varpassword Dim mydata As SqlDataSource mydata.SelectCommand = "Select * from tbluser where uLogin = '" & user & "'" varpassword = mydata.SelectCommand.uPassword End SubEnd Class
but i get the error : 'uPassword' is not a member of 'String' i wan to retrieve the password of that user,can anyone help me? thanks
that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client
this does not seem like a very good solution ( or is it?) If posible it would be nice to get something like this
TermID, Term, RelatedTermsInformation 1 test RelatedTermsTwoDimentionalArray
but I am not sure how this idea could be implemented using the capabilities of SQL.
my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.
I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on.
Hi, I have a difficult case that I need to solve. I will try to be the very clear explaining my problem:
I have a sql query which brings me many records. This records have a column in common which have the same value (COL1)
There is a second column (COL2) which has different values bewteen these records. I need to concatenate values from the second column in records with same value in COL1. And I need only one record of the ones that have the same values. If two records have the same COL1 value, only one row should be in my result.
Let me give you an example: COL1 COL2 RECORD1 1-A HHH RECORD2 1-A GGG RECORD3 1-B LLL RECORD4 1-B MMM RECORD4 1-B OOO RECORD5 1-C NNN
Me result should be:
COL1 COL2 RECORD 1-A HHHGGG RECORD 1-B LLLMMMOOO RECORD 1-C NNN
It is clear what I need? I dont know if I can solve it through sql or any function inside SSIS. Thanks for any help you can give me.
I'm trying to pull all records from one table and just a single record from another. I have this join, (see below). It works ok, but the problem is if a blog record doesn't have a corresponding image record it doesn't return. The end result should be the blog record and a single corresponding image record. But always a blog record.
SELECT U.UserFamilyName, F.FamilyName FROM Users U LEFT JOIN User_Friends F ON U.UserID = F.UserID WHERE U.UserName = ‘JOHN’
How do I adjust my query to select just the very first record from Users_friends, I want only the top first one.And if there are no friends how can I return an empty string instead of Null.
Hello, I have problem for insert multiple query for insert in differenr tabels for a single record. I have mail record for candidate and now i wants to insert candiate labour info, candidate passport detail in diff tabel like candidatLabour and candidatePassport, i used two store procedure for it and i write code for it.and it works fine,but i think that if one SP executed and one record inserted but then some problem occure and 2nd SP not executed then........... so plz help me Thanks
Hello, In SQL Server Management Studio (2005), 'Open table' command or a SELECT query displays table rows in a grid (or text).Please tell how to view a single row at a time i.e. all only ONE row is displayed at a time (evenly arranged to cover the screen). This feature (Single record view) is available in other database client like TOAD. Thank You
