How do I display an item in a day? This is a featured product of the day. This will change everyday. I can do this in classic ASP but not in stored procedure.
Table:
This is just to give you an idea of the table
ID | ProductName | DatePosted
----------------------------------------
1 | item1 | 5/1/2008
2 | item2 | 1/2/2008
3 | item3 | 6/2/2007
I want to know how to display only each of it where in my database for example the ProductItem got a lot of value of '1000' in it. i only want it to display once.
ProductItem | Name 1000 | ABC 1000 | DEF 1000 | HIJ 2000 | KLM 3000 | NOP
I want in my dropdownlistbox only display 1000, 2000, 3000. I using the 'Select ProductItem from Product' for sure it will display 1000,1000,1000,2000,3000. So to filter it.
I have view with Patients name and Appointment table where I save those patients. In form I have 2 comboboxes. For PatientComboBox source is store procedure based on Patient view. For PatientAppintmentComboBox I would like to create store procedure.
How to create store procedure to display only one PatierntName record in PatientAppointmentComboBox if Patient_Id selected from PatientComboBox exist or not exist in Appointment?
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
This is my case. I want in my datagrid to display first record from table A and second record from table B. This is because i need to display a price in the column where it will have current price and history price, therefore the first row will be current price and second row will be history price.
This is the output that i trying to do.
Item | Price -------|--------- A | 2.50 --------> Table A A | 1.50 --------> Table B
Please let me know if my explanation is not details enough.
I would like to show when leads updated last their records in database. An automated report that tells me when the last date was that the leads updated an entry, only 1 entry per lead.
select Lead,LastUpdated from dbo.KPITbl
I have a table with data that looks like this:
Lead LastUpdated ----------- ----------------------- JOHN SMITH 2008-03-26 08:45:00 JOHN SMITH 2008-03-20 09:33:00 MEG RYAN 2008-02-21 16:16:00 JOHN SMITH 2008-02-21 16:19:00 MEG RYAN 2008-02-21 16:22:00 JOHN SMITH 2008-03-28 16:10:00 JOHN SMITH 2008-03-28 08:49:00 JOHN SMITH 2008-03-23 19:23:00 MARK MCRAE 2008-03-27 03:12:00 MARK MCRAE 2008-03-26 08:48:00 MARK MCRAE 2008-03-26 08:46:00 JOHN SMITH 2008-03-26 08:47:00 JOHN SMITH 2008-03-26 08:48:00 ALLAN WHITE 2008-03-26 08:43:00 ALLAN WHITE 2008-03-26 08:40:00 JOHN SMITH 2008-03-26 08:48:00
Do anybody know how can I find or display to the current SQL server 2005 record number (eg. 10 of 1600) on a VB 2005 form label. The BindingNavigator on the form has been deleted. Thanks.
I am duplicating a record from my asp.net page in to the database. When i click on save I am getting the following error message Violation of PRIMARY KEY constraint 'PK_clientinfo'. Cannot insert duplicate key in object 'clientinfo'. The statement has been terminated. The above message i am getting since i have tried to duplicate the clientname field of my table which is set as the primary key. What i want is instead of this message in the browser i need to display an alert saying "the clientname entered already exists" by checking the value from the database. Here is my code. pls modify it to achieve the said problem if(Page.IsValid==true) { conn.Open(); SqlCommand cmd = new SqlCommand("insert into clientinfo (client_name, address) values ('"+txtclientname.Text+"', '"+txtaddress.Text+"')", conn); cmd.ExecuteNonQuery(); conn.Close(); BindData(); txtclear(); System.Web.HttpContext.Current.Response.Write("<script>alert('New Record Added!');</script>"); Response.Redirect("Clientinfo.aspx"); }
For my report I am trying to display images that are pulled from a database. Images are from separate records. I have the records I need in a dataset and now I want to set a Table Cell value to the field corresponding to the image. However, the next images/(records) are displayed on the next row (as expected i guess). How do I set the the records/values to be displayed across a single row?
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row? thanks
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
i have a matrix report and i want to switch the record background color with each record in the value column in that matrix report e.g 1st record background color is gray and next record background color is white and then the next record background color is gray ... and so on
I've been looking for examples online to write a SPROC to get some data. Here are the tables.
Album_Category AlbumCategoryID (PK, int, not null) Caption (nvarchar(max), not null) IsPublic (bit, not null)
Albums AlbumID (PK, int, not null) AlbumCategoryID (int, null) Caption (nvarchar(max), not null) IsPublic (bit, not null)
I need to return: -[Album_Category].[AlbumCategoryID] -[Album_Category].[Caption] -[Albums].[Single AlubmID for each AlbumCategoryID] -[Count of Albums in each AlbumCategory]
I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.
I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.
Hello. I have a database with a record that has two columns locked. descrip1 and descrip2. they are both nvarchar(max) columns. These are the only two columns of the record that remain locked. I am certain no user is accessing the record. I have even moved a backup of the database to my testing computer and the lock still exists. How do I remove the lock from these two columns on that particular record.
I can edit these two columns on other records. I have researched "Unlock" on MSDN but it doesn't seem to apply to t-sql. Any help would be greatly appreciated.
I have not yet succeeded in getting an aswer to this in a previous post, so I'll try again and rephrase the question
I have 2 fields, 1 called 'Created' the other 'Updated'
I would like to create a function, user procedure ,whatever in SQL something along the lines of...
FUNCTION myUID() RETURN Date(Today)+Time(Now)+USERNAME END FUNCTION
so that the value returned by the above pseudo code is something like '20080526T21:01:05.620SamL' where the date part is in yyyy0m0d format
I have got this working fine for the default 'Created' field but the calculated (and persisted) field comes up with something about being non-deterministic and refuses to play
Clearly something along these lines is a very straightforward requirement (I have it working fine in my 20+ year old database) , but have spent a week trying to get an answer (even from this forum) without success, so would be grateful for any help that you can provide me with to get round this barrier
I am trying to create package something like that..
1- New Customer table as OleDB source component 2- Lookup component - checks customer id with Dimension_Customer table 3- And if same customer exist : I have to update couple fields on Dimension_Customer table 4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination but How can I update the existing ones? I have tried to use oledb command but somehow it didnt work my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
How can we insert multiple records in a OLEDB destination table for each entry from the source table. To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record. how is this achieved.
I tried checking to see if the point at which the reader was, that if it was the record I am looking for to go ahead and add the table data to a label. But for some reason it's only taking the first record in the database and not the one I thought I was at.[CODE] public void UpdateMaleHistLbl() { SqlConnection conn = new SqlConnection("Server=localhost\SqlExpress;Database=MyFamTree;" + "Integrated Security=True"); SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = @usrnmeLbl ", conn); comm.Parameters.AddWithValue("@usrnmeLbl", usrnmeLbl.Text); conn.Open(); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { string usr = reader["username"].ToString(); usr = usr.TrimEnd(); string pss = reader["password"].ToString(); pss = pss.TrimEnd(); if (usrnmeLbl.Text == usr) { if (hiddenpassLbl.Text == pss) { maleHistLbl.Text = reader["GG_Grandfather"] + " > "; maleHistLbl.Text += reader["G_Grandfather"] + " > "; maleHistLbl.Text += reader["Grandfather"] + " > "; maleHistLbl.Text += reader["Father"] + " > "; maleHistLbl.Text += reader["Son"] + " > "; maleHistLbl.Text += reader["Grandson"] + " > "; maleHistLbl.Text += reader["G_Grandson"] + " > "; maleHistLbl.Text += reader["GG_Grandson"] + "<br /><br />"; } } break; //exit out of the loop since user found } reader.Close(); conn.Close(); }}[/CODE]Thanks in advance
Consider this scenario. I have two database in the sql server and consider that i have a query which has 4 tables inner joined. When i execute the query in the database1 , the query is returning rows, But when i execute the same query in the database2, the query is not retuning rows . I know that the no rows are returned because of missing data in the database2. But have no idea how to trace what values are missing in the database2. Please note the tables is having a huge list of records by which manually comparison is painfull. Please consider i dont have any background idea of the values in the tables but just using it. Any help would be appericated.
I have 2 tables they are identical what i need to do is when i make a update on one table the other table needs to reflect that same table. so lets say i insert into table A i need to look in table B if the record exist if it doesnt insert it if it does update it and if it exist in table B and doesnt exist in table A delete the record but i have to do this record by record..can anyone help me with this
