How Can I Report On Entered And Updated Records
My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table.
Maybe a tool someone knows of?
View Complete Forum Thread with Replies
Related Forum Messages:
Linked Reports Not Being Updated When Master Report Is Updated
Since updating to SQL Server 2005 SP2 I've noticed two things about Linked Reports. 1. I do a lot of 'Snapshot' reports. With SP-1 if I updated a master report and made any changes to the Parameter List - it undid all my custom parameter changes on linked versions (restored to the Master Reports Defaults). While this is no longer happening with SP2 - it is still 'unhiding' the parameters. 2. With SP-1 if I added/deleted columns or made other changes to the report structure - the linked reports would pick up on the changes with their next refresh. With SP-2 I'm finding that I have to 'Re-link' the linked report back to the master report before the changes are refreshed. This is very time consuming especially with each report having 8 or more Snapshot reports pre-set up. Am I missing something - or is this a 'bug'... Any help would be appreciated...
View Replies !
Get Last Updated Records?
If I update a recordset a group of records using dynamic SQL where I update the TOP n records, is it possible to get the set of records that was updated? CREATE PROCEDURE usp_Structural_ScheduleComponent @cProject char(7), @cComponentID char(10), @iPour int, @iQuantity int, @iAvailable int OUTPUT, @dtCast datetime OUTPUT AS SET @dtCast = convert(char(10), getdate(), 120) DECLARE @cSql varchar(500) SET @cSql = 'UPDATE tbStructuralComponentSchedule SET PourNumber = ' + CAST (@iPour AS VARCHAR) + ', ScheduledDate = ' + '''' + CAST(@dtCast AS VARCHAR) + '''' + ' WHERE EntryID IN ( SELECT TOP ' + CAST(@iQuantity AS VARCHAR) + ' FROM tbStructuralComponentSchedule ' + ' WHERE fkProjectNumber = ' + '''' + @cProject + '''' + ' AND fkComponentID = ' + '''' + @cComponentID + '''' + ' AND IssueDate IS NOT NULL' + ' AND ScheduledDate IS NULL' + ' ORDER BY EntryID DESC)' EXEC(@cSql) IF(@@ERROR <> 0 OR @@ROWCOUNT < = 0) RAISERROR('Failed to add components to pour!',16,1) SELECT @iAvailable = SUM(CASE WHEN IssueDate IS NOT NULL AND ScheduledDate IS NULL THEN 1 ELSE 0 END) FROM tbStructuralComponentSchedule WHERE fkProjectNumber = @cProject AND fkComponentID = @cComponentID GO -- Is there a way to return the recordset that were modified in the update? Mike B
View Replies !
Big Records Not Always Updated
In our database we have a memo field that sometimes coud be over 9 MBytes. We are using the following code (VB) to update it lReg = Len(fielText) lOffset = 0 Do While lOffset < lReg sChunk = Left(Right(fieldText, lReg - lOffset), 32768) rs("document").AppendChunk sChunk lOffset = lOffset + 32768 DoEvents Loop rs.Update The problem is that sometimes the record is NOT updated and there is NO errors returned by SQLServer. Do you have any suggestion? Is there another way to update records with big fields?
View Replies !
Need To Track Updated Records
Hi all, I have a data of applicants. Everyday we dump this data into SQL server. Now I need to generate reports everyday so that we can track how many records get updated everyday. Now the thing is that the applicants are in various stages. So my reports need to track the how many applicants changed from stage "abc" to "pqr" and how many changed from "pqr" to "xyz". Now it is not necessary that all the records change stages everyday. thanks in advance, Rohit
View Replies !
Protecting Records From Being Updated And Deleted
Hi I am using sql server 2005 express and would like to keep all my fields from being both updated and deleted. In other words, once I create a new record, I would like to have it protected from being deleted and I dont want the field values to be updated/changed from the values initially entered. Is there a way to this without running triggers or changing database permissions and user roles? I tried making the database read-only, but then of course i cant add new records. Thanks
View Replies !
Updated Records From A Table Insert Into Another
Hi: I need to create a trigger to save every record which has been updated in a table (e.g. "Contacts") into another (e.g. "Shadow_contacts). So basically something like a versioning on database level. I have no experience with it and any precise hints/solutions would be very appreciated selmahan
View Replies !
UPDATE Stored Proc -- Updated Over All Of My Records
I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD.. IF anyone could shead some light on what I'm doing wrong that would be great. Here is the syntax for my stored proc. CREATE PROC updateResults2 @id int, @address1 nvarchar (50), @address2 nvarchar (50), @CITY nvarchar (33), @ST nvarchar (10), @ZIP_OUT nvarchar (5), @ZIP4_OUT nvarchar (4), @home_phone nvarchar (22), @NEWPhone nvarchar (20) AS UPDATE Results SET address1 = @address1, address2 = @address2, CITY = @CITY, ST = @ST, ZIP_OUT = @ZIP_OUT, ZIP4_OUT = @ZIP4_OUT, home_phone = @home_phone, NEWPhone = @NEWPhone GO As said previously it ran but it updated the WHOLE DATABASE with the same change (WHICH I DIDNT WANT IT TO DO)!! Thanks in advance. RB
View Replies !
Linked Tables - After Path Updated Can't Add Records
Hello-I created a MS Access 2002 database with linked tables on a SQL Serverdatabase by way of a File DSN, A. I have created a form which points toone of the linked tables. After I finished testing it in thedevelopment environment I updated the path for all the linked tables toa new File DSN, B. After this action, I opened the form to test addinga new record and the "add new record" navigation button has beendisabled. Can anyone help? I need to be able to add new recordsthrough the form and can't figure out why I am now NOT able to add arecord. PLEASE HELP!!Thank you,nosenia--noseniaPosted via http://dbforums.com
View Replies !
Modifying Inserted Or Updated Records Without Recursion?
I am looking for a way to update the information in the "inserted" logical record without having to call something like this: UPDATE tblX SET ValueY = ValueA/100 FROM tblX INNER JOIN inserted ON tblX.ID = inserted.ID because this may result in the update trigger firing (again). I'd like to avoid this. As a better example, if I have a table of transactions with cost and price info, plus a flag indicating whether the transaction has been merged to AR or GL, I want to be able to update cost or price directly, which will clear the flag and indicate that I have a batch out of balance. This I can do easily with Update(Cost) Or Update(Price). But, I also need to be able to change the supplier in the transaction record. If I do this, I want the trigger to fetch the new cost and price for me. If the cost or price change, I still want to update the flag. To get there I can call UPDATE tbl SET Cost = @EffectiveCost, Price = @EffectivePrice FROM tbl INNER JOIN inserted ON tbl.ID = inserted.ID which is recursive. I can't say "UPDATE inserted" because it's a logical table. Is there any way to avoid this and to set values in the middle of an insert/update?
View Replies !
Default Non-queried Report Parameter Not Updated When Project Is Deployed
Adding a value to a non-queried default report parameter value does not update on the target server after deployment. To recreate 1. Create a report in Visual Studio and add a report parameter with the following properties: Multi-value is checked Available values = "From Query" Dataset = [create a dataset that returns a table w/ a Id and Description column] Value field = [the Id field from the table] Label field=[the Description column from the table] Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report) 2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter. 3. Go back to Visual Studio and add a value to the Non-queried Default values. 4. Build and deploy the report again. View the report. The newly added item is not selected. Notes I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter. Work-arounds 1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this: SELECT '4' AS SelectedId UNION SELECT '5' AS SelectedId UNION SELECT '6' AS SelectedId 2. Delete the report in Management Studio, then redeploy. I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.
View Replies !
Save Updated Date When Row Is Updated
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View Replies !
How To Save &&"New&&" / &&"Updated&&" Records Only In To Destination Tables
Hi I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records. So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records. How can i do that in SSIS 2005 using different data flow tasks ? Thanks Kumaran
View Replies !
Getting Key Just Entered
************* Edited by moderator Adec *************** Inserted missing < code></ code> tags (without the spaces inside). Always include such tags when including code in your postings. Don't force the moderators to do this for you. Many readers disregard postings without the code tags. ************************************************** Hi Probably a dumb question but I'm doing an insert into a table with an identity field. How do I get the key back straight after I add it? This is how I do the addition SQLString = "Insert into users (username,password,status,campus, ulevel ) values ( @username, @password, @status, @campus, @ulevel)" cmdInsert = new SQLCommand(SQLString, conn) cmdInsert.Parameters.Add( "@username", _username) cmdInsert.Parameters.Add( "@password", md5Hasher.ComputeHash(encoder.GetBytes(_password))) cmdInsert.Parameters.Add( "@status", _status) cmdInsert.Parameters.Add( "@campus", _campus) cmdInsert.Parameters.Add( "@ulevel", _ulevel ) conn.Open() cmdInsert.ExecuteNonQuery() conn.close() but about now I need the primary key just generated. Any ideas? Thanks ABold
View Replies !
Sum All Record Except Last One Entered
im trying to summarize all records except the last one entered. ex -column001- 1 2 3 4 5 6 the output should be "15" (1+2+3+4+5) is this possible? One thing i tryed is to put in autodate and time and then count them and leaveout the newest one. Well i can't make it work... Thx for all help
View Replies !
What Is The Way To Receive The Data As We Entered
Hai ,I created a table with primary key clustered. I have entered the datathru E.Manager . If a close the table and open it again , Ii shows therows with the (default) ascending order. Is, there any way to get therows in the user entered order(neither asc or dec order)With ThanksRaghu
View Replies !
Run Report With Specific Records.
I am not sure where i should post this question since it falls both in Report Server and T-Sql but here goes... I currently need to run a Report that has only specified records that the client/user wants by clicking the check in the check box next to the record they want. They can pick as many or a few of the records that want then run a report only with the records they indicated they wanted... i am thinking they will need some kind of t-sql statement either a function or temp table but i am not sure if even that... if anyone has any ideas please reply... Thanks, WoFe EXAMPLE: Instead of running a report on records 1, 2, 3, 4, 5, 6, 7, 8, 9 they would run the report on records: 2, 5, 6, 9
View Replies !
Capture The ID Of The Last Record Entered And Use In An Update
I'm entering a Selection record for a partiuclar lotID, Once entered, I need to obtain its SelectionID then use it to update a another field within that record. Here's what I've been doing... --insert values into a testchangeorders table INSERT INTO testchangeorders VALUES (2,3,3,3,1,'red',0,5) --Find the SelectionsID of the last record created for that partiuclar LotID SELECT MAX (SelectionsID) FROM testchangeorders WHERE LotID = 2 --Once located, I was trying to update a field called uniqueID with a contantination of '3-' & the record's SelectionsID UPDATE testchangeorders SET UniqueID = ('3-' & SelectionID WHERE SelectionsID = SELECT MAX (SelectionsID) AND LotID = 2)
View Replies !
Parameter Entered Has The Wrong Format.. You Must Be ....me!
Hi I have the problem that the below defined paramter gets entered in the database as a interger. the Field in the DB is a nvarchar(5) and the controll that suplies the value is a TextBox this is the parameter definition:<asp:ControlParameter ControlID="tbComment" Name="Comment" PropertyName="Text" Type="String" /> Why do I get this error, why does ASP to whant to make an integerfrom this text field? When putting a interger value in the textbox all works well and the data gets posted to the database. I use a SqlDataSource with automatic generated script. look forwart to a solution walter
View Replies !
Joining Table On On Last Entered Record
Dear All, What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B. I'd like to select every record in table A but only joining the last relevant record from table B. So: Table A: A1 Prj1 A2 Prj2 Table B: B1 A1 23/12/2005 B2 A1 26/12/2005 B3 A1 2/1/2007 B4 A2 25/12/2006 B5 A2 1/1/2007 So I'd like to list using the most efficient way this: A1 Prj1 B3 2/1/2007 A2 Prj2 B5 1/1/2007 I'm assuming this is NOT the most efficient way: select A, (select top 1 date from B orderBy ...) Any suggestions?
View Replies !
Prevent Invalid Characters From Being Entered
Hi, I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle. In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data. If this is not possible, is it viable to remove the character after insert and still send the email with the required info? Any one any ideas? Thanks
View Replies !
Preventing Invalid Data From Being Entered
Hi, I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle. In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data. If this is not possible, is it viable to remove the character after insert and still send the email withe the required info? Any one any ideas on the cleanest way to achieve this? Thanks
View Replies !
Displaying User Entered Data
I have a procedure that allows a user to enter two dates to run a query. I would like to display those dates on the generated report underneath the title. "5-01-07 to 5-31-07" How would I do this?
View Replies !
Detect No Time Entered For Datetime
I have a VB.NET program that displays the time extracted from a SQL Server database datetime datatype by way of a User-defined scalar function I created. However, sometimes information is entered into the system through the program that does not have a time-- only a date. SQL Server automatically assigns a time of 12:00 AM to these values (since they're a datetime). Is there any way to detect when this happens in my user-defined scalar function so that when I try to extract time values, I can instead return a message/time of my choice? I would rather not assume that all 12:00 AM values are automatically inserted by SQL Server since this might not actually be the case.
View Replies !
User Entered Data Error
Hi Guys, I'm having a problem with some data in our database, basically a web app is storing data into the DB and then recalling it to display to a user. The problem I am having is that for one particular function the DB is causing the app to fail, the app code works for 95% of the data inserted into the DB by the users but it is failing on a few records.. I'v gone through the data manually checking for funny characters or spaces or anything else which is different from the other records, but everything seems to be in order. I doubt very much that this is a system app code problem as the code is working perfectly for all the other records... Can anyone advise me on what else I can check.. really stuck on this one guys Thanks Gurj
View Replies !
Records Missing For End Date In Report
hello friends.. i aleays facing this problem while reporting.... if i want to show report for date range then i am not getting records for end date...why??? my report query was select distinct DwnDate,isnull(D.FileName,'No File Found'),isnull(H.File_ID,0), isnull(C.DownLoadCatname,'No Category Found'),count(H.File_ID) AS TotalCount from DownLoadHistory H inner join DownLoad D on H.File_ID = D.File_ID inner join DownLoadCat C on D.File_Cat = C.DownLoad_CatID where File_DwnDate between '10/01/2006' and '10/31/2006' group by D.File_Name,C.DownLoad_Catname,H.File_ID,File_DwnDate order by 3 i am getting rows 15 here but when i fired this select distinct DwnDate,isnull(D.FileName,'No File Found'),isnull(H.File_ID,0), isnull(C.DownLoadCatname,'No Category Found'),count(H.File_ID) AS TotalCount from DownLoadHistory H inner join DownLoad D on H.File_ID = D.File_ID inner join DownLoadCat C on D.File_Cat = C.DownLoad_CatID where File_DwnDate between '10/01/2006' and '11/01/2006' group by D.File_Name,C.DownLoad_Catname,H.File_ID,File_DwnDate order by 3 then i am getting rows 16 previous one i always missed records on 10/31/2006...is there any solution or i always add one day to end date and then get values?? please help me out Thanks
View Replies !
Roll Date If Time Entered Is After Midnight
Hi again, In ASP.net, is there any elegant way to handle a set of time inserts from a form when the 2nd time is past midnight? Specifically, I have a form with 2 textboxes on it (startTime and endTime) that are set up to accept time values (using AJAX MaskedEditExtender for formatting/validation - pretty cool). This data is posted to a sub that enters the data into a table (T_Details). However, I've noticed that the data inserted as part of the record (SQL field is smalldatetime) doesn't take into account the fact that a time value past 23:59:59 in the "endTime" textbox is a time on the next day - it simply rolls to an A.M. date for the same day as the date for the pre-midnight value from the "startTime" textbox. I'm sure that I can simply do some conditional coding and modify the date if necessary but is there a better way to do it? Thanks as always...this forum is a great resource
View Replies !
Inserting A New Record Into Sql Db Using User-entered Information
Im trying to add a new rcord to my db on a button click usign the following code 'data adapter Dim dAdapt1 As New SqlClient.SqlDataAdapter 'create a command object Dim objCommand As New SqlClient.SqlCommand 'command builder Dim builderT As SqlClient.SqlCommandBuilder 'connection string Dim cnStr As String = "Data Source=ELEARN-FRM-BETA;Initial Catalog=StudentPlayGround;Integrated Security=True" 'dataset Dim dsT As DataSet Private Sub connect() 'connection objCommand.Connection = New SqlClient.SqlConnection(cnStr) 'associating the builder with the data adapter builderT = New SqlClient.SqlCommandBuilder(dAdapt1) 'opening the connection objCommand.Connection.Open() 'query string Dim query As String = "SELECT * from StudentPlayground..Employees" 'setting the select command dAdapt1.SelectCommand = New SqlClient.SqlCommand(query, objCommand.Connection) 'dataset dsT = New DataSet("Trainee Listings") dAdapt1.Fill(dsT, "Employees") End Sub Private Sub BindData() connect() DataBind() End Sub Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click Dim empID As Integer = CType(FindControl("TextBox8"), TextBox).Text BindData() Dim firstName As String = CType(FindControl("TextBox1"), TextBox).Text BindData() Dim lastName As String = CType(FindControl("TextBox2"), TextBox).Text BindData() Dim location As String = CType(FindControl("TextBox3"), TextBox).Text BindData() Dim termDate As Date = CType(FindControl("TextBox4"), TextBox).Text BindData() Dim hireDate As Date = CType(FindControl("TextBox7"), TextBox).Text BindData() Dim dept As String = CType(FindControl("TextBox5"), TextBox).Text BindData() Dim super As String = CType(FindControl("TextBox6"), TextBox).Text BindData() Dim newRow As DataRow = dsT.Tables("Employees").NewRow newRow.BeginEdit() newRow.Item(0) = empID newRow.Item(1) = firstName newRow.Item(2) = lastName newRow.Item(3) = location newRow.Item(4) = hireDate newRow.Item(5) = termDate newRow.Item(6) = dept newRow.Item(7) = super newRow.EndEdit() 'do the update Dim insertStr As String = "INSERT INTO Employees" + _ "(EmployeeID,FirstName,LatName,Location,HireDate,TerminationDate,Supervisor)" + _ "VALUES (empID,firstName,lastName,location,hireDate,termDate,dept,super)" Dim insertCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(insertStr, objCommand.Connection) dAdapt1.InsertCommand() = insertCmd dAdapt1.Update(dsT, "Employees") 'Dim insertCmd As new SqlClient.SqlCommand = (builderT.GetInsertCommand()).ToString()) 'dAdapt1.InsertCommand = New SqlClient.SqlCommand(insertCmd.ToString(), objCommand.Connection) BindData() objCommand.Connection.Close() objCommand.Connection.Dispose() End Sub im not sure wats going wrong because the record is not being added. Please help!!
View Replies !
Values Entered In Db Has Trailing White Spaces
Hi, I'm inserting a few columns into my db (they all have a nvarchar(50) ).. but i noticed when i retrieve them out of the db, the length of the string always have some trailing white spaces behind them and such when I try to do stuff like dropdownlist.items.findbyvalue(), it normally fails.I did trace and before the string get into the db, they were teh right length. so I'm not sure where did I do things wrong? thanks
View Replies !
How To See If A Value Entered In A Textbox Is Present In An SQL Database Field?
Hello! and Help if possible!!!I am creating a booking system in ASP and C#. I have a database that stores the calendar info on some employees, including start and end dates of their current meetings.I am stuck! i am a newbie at this! ive got a pop up calendar that populates a text box with the start date selected and the same for the end date. What i need to do is pass this value to the start date field column in my sql database and iterate through the rows to see if that date already exists for that employee! if anyone has any ideas in how to do this, any help would be extremely gratefully appreciated!!!!! Thanks,
View Replies !
The Value You Entered Is Not Consistent With The Data Type Of The Column.
Hi.. I am trying to add new record to any table in my database. But I can't. I am tryting to add a value (1500 character) and it gives the message below "The value you entered is not consistent with the data type of the column." I have tried alot. The result is my columns in my table accept maximum 900 character. But the type of my column I try to add a value is text.. I am too angry with SQL Please help
View Replies !
DatetimePicker Control Retains The Previously Entered Value
Hello all, I have a peculiar problem. We are using VS-2005,SSRS-2005,SqlServer-2005. Our front end is Windows forms and we are communicating with SSRS through WebServices protocol. Through WebServices we are generating dynamic parameters based on the report and were able to show them on the Win form screen. We have a Custom Nullable DatetimePicker control, which can accept NULL values. My problem is: once a date is entered and deleted the DatetimePicker control retains the old value. Please throw some light on this issue...Any kind of help would be appriciated. --Deepak
View Replies !
Bug? Report Builder Returning Only Unique Records!
Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report. Example: TransID Type TransQuantity 1 CS 4 2 CS 4 3 CP 2 4 CN 3 Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows: Type TransQuantity CS 4 CP 2 CN 3 Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain. Thanks, Taurkon
View Replies !
2 Records Not Showing In Report But They Can Be Retrieved From DB Using Same SQL Statement
Hi, I have a report with 5 filters which can be applied to it. The records are grouped by the Rotation programme there are on, with a subtotal for each unique programme name. The report seems to work fine, but upon closer inspection - we noticed that 2 of the records are not being displayed. As a result, the total count is out by 2. We tracked down the missing records so I ran the SQL query with a Where clause, and it was able to find the two records. What could possibly cause this behaviour? Please see included SQL statement : Code Block SELECT Posts.PostNumber,COUNT(Posts.PostNumber) AS RPCount, Incumbents.Name AS IncumbentName, Grades.GradeTitle, Specialties.SpecialtyTitle, Hospitals.Name AS Hospital, Genders.Gender, [Incumbent History].YearGraduated, COUNT([University Origins].Origin) AS OriginCount, Incumbents.Nationality AS NationalityID, Countries.[Country Name] AS Nationality, [Rotation Programmes].[Programme Name], Posts.[Post Approved for Training], [University Origins].Origin, Posts.OldPostNumber, [Rotation Programmes].[Programme ID] FROM Posts INNER JOIN Incumbents ON Posts.PostNumber = Incumbents.PostNumber INNER JOIN [Incumbent History] ON Incumbents.[Incumbent ID] = [Incumbent History].IncumbentID INNER JOIN Grades ON Incumbents.[Official Grade] = Grades.GradeID INNER JOIN Specialties ON Posts.Specialty = Specialties.SpecialtyID INNER JOIN Hospitals ON Posts.HospitalID = Hospitals.[Hospital ID] INNER JOIN Genders ON Incumbents.GenderID = Genders.GenderID INNER JOIN Countries ON Incumbents.[Country Of Birth] = Countries.[Country ID] INNER JOIN [Rotation Programmes] ON Posts.[Rotation Programme] = [Rotation Programmes].[Programme ID] INNER JOIN [University Origins] ON [University Origins].[Uni Origin ID] = Incumbents.[University Origin] GROUP BY [Rotation Programmes].[Programme Name], Posts.PostNumber, Incumbents.Name, Grades.GradeTitle, Hospitals.Name, Genders.Gender, [Incumbent History].YearGraduated, [University Origins].Origin, Incumbents.Nationality, Countries.[Country Name], [University Origins].Origin, Posts.[Post Approved for Training], Posts.OldPostNumber, Specialties.SpecialtyTitle, [Rotation Programmes].[Programme ID]
View Replies !
Unable To Load Report With Large No. Of Records
Hi, We need to generate a report using SQL Server 2005 Reporting Services which can have 1 million plus records. So we have created a report with a simple select query. This query when run against our database returned 1110018 records. We deployed the report onto our server and tried accessing the report through its URL. What we observed was that the report ran for about 15 minutes and then it prompted for user id and password (Windows authentication). On giving the user ID and password it continued running for another 15 minutes and at the end of it, the browser again prompted for user id and password. This cycle continued twice and at the end of it we got the message €œThe page cannot be displayed€? (i.e. the usual message displayed by the browser when it cannot load a web page). We were not able to load the report at all. Are there any specific considerations when loading reports with such high no. of records. Any suggestion, solutions are welcome. Thanks, CodeKracker.
View Replies !
Not Able To INSERT The Values Into The Database Entered In The Textbox-Please Solve It
using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Configuration; namespace Balatest { /// <summary> /// Summary description for WebForm2. /// </summary> public class WebForm2 : System.Web.UI.Page { protected System.Web.UI.WebControls.Label Label1; protected System.Web.UI.WebControls.Label Label2; protected System.Web.UI.WebControls.Label Label3; protected System.Web.UI.WebControls.Label Label4; protected System.Web.UI.WebControls.TextBox TextBox2; protected System.Web.UI.WebControls.TextBox TextBox3; protected System.Web.UI.WebControls.TextBox TextBox4; protected System.Web.UI.WebControls.TextBox TextBox5; protected System.Web.UI.WebControls.TextBox TextBox1; protected System.Web.UI.WebControls.Button Button1; protected System.Web.UI.WebControls.Label Label5; private void InitializeComponent() { } private void Button1_Click(object sender, System.EventArgs e) { try { string con,fname,age,city,companyname,designation; fname= Server.HtmlEncode(TextBox1.Text); age= Server.HtmlEncode(TextBox5.Text); city= Server.HtmlEncode(TextBox4.Text); companyname=Server.HtmlEncode(TextBox3.Text); designation=Server.HtmlEncode(TextBox2.Text); con= ConfigurationSettings.AppSettings["connection"].ToString(); SqlConnection obcon= new SqlConnection(con); string sql = "insert into home (fname,age,city,companyname,designation) values(@fname,@age,@city,@companyname,@designation)"; obcon.Open(); SqlCommand com = new SqlCommand(sql, obcon); com.CommandType = CommandType.Text; com.Parameters.Add(new SqlParameter("@fname", fname)); com.Parameters.Add(new SqlParameter("@age", age)); com.Parameters.Add(new SqlParameter("@city", city)); com.Parameters.Add(new SqlParameter("@companyname",companyname)); com.Parameters.Add(new SqlParameter("@designation",designation)); com.ExecuteNonQuery(); obcon.Close(); } catch(System.Exception ex) { } } } } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { //CODEGEN: This call is required by the ASP.NET Web Form Designer. InitializeComponent(); base.OnInit(e); } // <summary> // Required method for Designer support - do not modify // the contents of this method with the code editor. // </summary> private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } #endregion
View Replies !
Verify Data Entered In An Access Form With A Dialog Box
hi.. i just made a form to collect information and add it to a databse.. i was wondering if tehre was any way i cud put some code in my "submit button" that would tell the user.. "this is wat you have entered.. are you sure u want to add it to myTable? cust. name : jon last name : smith add : 100 main st. ........." and w/2 buttons, "Yes" and "No I made a mistake, let me go back and edit".. whihc server their respective purposes.. any ideas anyone..?? cheers..
View Replies !
What Is The Optimal Solution To Find, If All The Data Is Entered On A Predefined Condition?
Hi,I want to know the optimal solution, to find if all the data was entered. Lets say, Table A (date field) and for a given month, i need that all the days in the given month are present in the Table A. Right now i have different solutions, 1) a stored procedure which loops through all the days in the given month against a select statement on Table A2) a stored procedure, create a temp table which contains all the dates in the given month, and a single select statement using where condition (select * from.... where datefield not in (select * from...))I want to know what is the best solution of these two or any other solution.Thanks
View Replies !
Combine Tables From 2 SQL Servers With Different Schemas And Update As New Data Is Entered
I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.
View Replies !
Question-Advice Needed: Creating A System To Synch Handheld Entered Data With Main Database.
Greetings! I would like to create a database for keeping track of payroll data for employees where the supervisors (job coaches) on our workshop floor can use a Pocket PC device to record the hourly employee data on the fly. Then at the end of the day, the supervisor can place the device in a cradle of some sort and synch the newly entered data into the main database. I'm guessing that SQL Server Compact edition would be perfect for this type of task? Is that correct? Can someone give me recommendations on how to go about setting this up? What should I use as the main database? SQL Server? Access? Any advice is appreciated!
View Replies !
Form Criteria Based On An Entered Date And Date Today
Hi I am very new to SQL so please excuse me if my question seems too easy to answer. Basically I need to populate a form based with records based on the criteria that the next mot date and todays are +/- 10 days. i.e if todays date is 13/05/07 and the next mot date is 3/05/07 or later OR 23/05/07 or less then various fields will be shown in the form. Can you please help. Thanks Paul
View Replies !
How To Display &&"Other&&" Records From TopN Report
I was wondering if there was a way to display the "other" total that does not appear in a TopN report. I.e. I display the Top 10 records I would then like a row saying "All Others" and the subtotal of the non Top 10 records I would also like to display a Total of the TopN records and what % of the overall that amounts to Simple example..... Top 3 Report 1. AAA 10 2. BBB 9 3. CCC 8 4. Others 20 Top N Total 27 (% of total = 57%) Overall Total 47 Lines in italics are the values I can't seem to get my head around
View Replies !
|
TRACKER
