i have a table in MS SQL Server,i need to show two results at a time,and on click of a button nxt two results have to b shown,how do i accomplish this (i mean retrieving the results two at a time)thanx in advance
Hi I'am practically new in C#, so I want to ask you guys some question.Let say I have this query:"Select EmployeeID, EmpName from PI_Employee"How can I retrieve the result from EmployeeID column and EmpName column and put it into collections of array, so I can call the array and use it again in another function. Can I possibly do that in C# ? if so, how ? please help me guys, I'm on the edge of nervous wreck in here so I could use a little help, any kinds of help. Thanks. Best Regards.
HI y'all, I have a problem in my query I hope you dudes can help. Ya see I have this query:SELECT CustId,CustName,CustAddress,CreditLimit,dateStart,DateUpdated From AR_Customer And this is the resultsCustId CustName CustAddress CreditLimit dateStart DateUpdated-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------CS0201 Reena Mayfield Str. 300000.000 2006-08-01 00:00:00.000 2006-08-01 00:00:00.000 CS0202 Bryant Bringfield 202 50000.000 2006-08-01 00:00:00.000 2006-08-01 00:00:00.000CS0203 Jack Marjory 22 3000000.000 2006-08-01 00:00:00.000 2007-05-18 17:30:23.000CS0204 Alan Orchard 25 4500000.000 2006-10-02 00:00:00.000 2006-08-01 00:00:00.000Then I add Where clause to the query, like this SELECT CustId,CustName,CustAddress,CreditLimit,dateStart,DateUpdated From AR_Customer WHERE DateUpdated = '08/01/2006'And the result is just as I expected:CustId CustName CustAddress CreditLimit dateStart DateUpdated -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------CS0201 Reena Mayfield Str. 300000.000 2006-08-01 00:00:00.000 2006-08-01 00:00:00.000 CS0202 Bryant Bringfield 202 50000.000 2006-08-01 00:00:00.000 2006-08-01 00:00:00.000CS0204 Alan Orchard 25 4500000.000 2006-10-02 00:00:00.000 2006-08-01 00:00:00.000But when I changed the date parameter to 05/18/2007 for example like this SELECT CustId,CustName,CustAddress,CreditLimit,dateStart,DateUpdated From AR_Customer WHERE DateUpdated = '05/18/2007'It doesn't show any results at all, it just showing the header CustId CustName CustAddress CreditLimit dateStart DateUpdated -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------What I want to ask is why is that happen, is it because the date contains hours--according to the database is the date contain hours like this 2007-05-18 17:30:23.000. I already use this operator <=,>= but it just showing the wrong result. Can you guys tell me the correct query, please. I appreciate any help. Thanks. FYI: I use SQL Server 2000Best Regards.
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
I am trying to get my query to give me the results I want by date in a list. I want in one column to get the date and the next column to show me the minutes of use (as reported in my database). I tried a Group by statement and asked it to group by the CallDate column. It gives me the date, but the minute totals in the next column are not right. Please help.
I have the following query and I am wanting to get the results by date. Please advise. SELECT COUNT(UniqueCallID), VoiceCallDetailRecord.[Type], SUM(PaidBalCost), SUM(BonusBalCost), SUM(FreeMsgCost), SUM(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE VoiceCallDetailRecord.[Type] IN ('MOSMS','FreeMTSMS') AND CallDate Between '01/01/2007' and '02/01/2007' GROUP BY VoiceCallDetailRecord.[Type] and GROUP BY dateadd(day, datediff(day, 0, CallDate), 0)
UNION SELECT COUNT(UniqueCallID), ZeroChargeVCDRecord.[Type], SUM(PaidBalCost), SUM(BonusBalCost), SUM(FreeMsgCost), SUM(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE ZeroChargeVCDRecord.[Type] IN ('MOSMS','FreeMTSMS') AND CallDate Between '01/01/2007' and '02/01/2007' GROUP BY ZeroChargeVCDRecord.[Type]
I tried looking for assistance but I cant get my formatting to work. I was pretty sure yyyy=2007 and yy=07 but its not working right.
What I am doing is using Business Intelligence to read the previous days IIS logs. The logs are labeled as ex070602.log (exyymmdd.log) The code I use to get the previous day is dateadd("d",-1,getdate())
now I want to format the yy/mm/dd but I am getting single digits for the month and day and 4 digits for the year. Can someone tell me the expression I would need to use for this. Thank you kindly guys.
hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this:
oct 2 oct 3 sep 13 sep 21 sep 22 sep 30 aug 3 aug 5 aug 16
the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help.
also, can you not search here any more? i keep getting timeout errors.
I really hope that someone can help me or at least point me in the right direction. I am selecting a set of data and using the date values across the X axis. However the needs exists to group these by week, but these weeks are not the normal weeks, for - they exist as follows the month starts on the first Monday of a month, for example December 2007 starts on Monday the 3rd and the week ends on the 6th of December a so on till the fact that the last week of the month December 2007 starts on Monday the 31st and ends on January the 6th is there any way that I can create a group that could group the datetime values together in this way,
This is not best achieved in SSRS where should I be creating these groups. Any help would really be appreciated.
how to hard code mention date range in my SP to get expected results in my query 01/01/2012 to 12/31/2012
DECLARE @ACCOUNT AS INT DECLARE @POSTING_DATE AS DATETIME DECLARE @FIRST_POSTING_DATE AS DATETIME SET @POSTING_DATE = {?POSTING_DATE} SET @ACCOUNT = {?ACCOUNT}
I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100 SELECT * FROM Customer WHERE CountyId IN ( SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId) THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
Hi All, I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
Hi, I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number(). I would like to give my users to option of removing individual people from this list but cannot find a way to do this. I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message. Is there any way to do this? I am still new to stored procedures so any advice would be helpful. Thanks
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
How can I efficiently retrieve the top x managers within the top y regions within the top z states within the top q countries? The only way I have been able to do this is to first find the top q countries, and store the list in an IN clause. Then for each of those, find the top z states and create another IN clause that contains the country+state concatenated. Then for each of those, find the top y regions and concatenate country+state+region. Then finally find the top x managers within this IN clause. This works fine for a few hundred records, but once it reaches the thousands, it takes much too long. The final IN clause contains thousands of entries. Isn't there a simpler way to approach this, especially with SQL Server 2005?
i am using a stored procedure, lets say spTemp, that calls sp_executesql within it. in the sp_executesql procedure i am passing parameters from the stored procedure 'spTemp'. i want to retrieve the result of the sp_executesql statement so as to perform further calculations in spTemp. is there a way of doing this?
ex:
create procedure spTemp ( @abc int ) as declare @temp int exec sp_executesql 'select @temp = Id from Product where @abc > 10' ...
I am now retrieving the PDF from the database and I am getting an error: Error 1 'GetImages.GetImage(int)': not all code paths return a value I have: int imageid = Convert.ToInt32(Request.QueryString["ACTUAL_IMAGE_PDF"]); And... private SqlDataReader GetImage(int imageid) { Sql Statement... } Could someone help please??
I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.
Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.
"I meant to delete just one assignment, doing which was giving me difficulty.
I deleted a whole category by accident which had most of my daily grades in it for a course with quite a few of them. I was negligent in backing them up, so I cannot restore them. I have an XLS file from the midterm, but am missing about 6 grades after that.
Is there any way to recover the grades I had in the deleted category. It has been a couple of days since I entered any new grades. HELP!"
My current backup strategy is to do a full backup 5 pm, differentials every 2 hours, logs every 30 min, all so they arenot simultaneous. Backing these all to same backup device via three different jobs.
Then the server is backed up to tape shortly after the 5 pm full backup.
Obviously, I can't restore from the backup for this situation because it would hurt other data.
But - is there a way I can take last night's backup from tape and restore it to a different database than our production database? (Such a database doesn't at this time exist. Would I detach, copy over the files, and then reattach on prod and attach on the devl side, and then restore from the backup device to the devl copy?)
I've never had to perform this kind of rescue and am wondering if my backup strategy is flawed since it isn't immediately evident how I can easily do this.
I needed a similar thing from our Oracle DBA yesterday, and he went to tape, copied out a table and put it on the Oracle database under a different owner, and I easily fixed the few records that I had botched.
That's exactly what I need to do now with MS SQL 2000.
I was hoping someone would be able to help me, I trying to go through several databases, 111 to be exact, and get the data out of a particular table which is present in all the databases on the same server.
I know how to get the names of the databases from the INFORMATION_SCHEMA, but how can use the name for it to cycle through all the databases and get data out of the one particular table.
I have a giant SQL query that retrieves a bunch of information from a couple of tables for use on a page, and I would like some help on constructing the SQL to get exactly what I have in mind. I have a table - called scContacts - that contains a list of contacts with detailed information. The contacts in the table are regular sales contacts, sales reps, and sales managers. Another table - called scCompany - contains a list of companies with detailed information.
For each company, we have a certain sales manager (or user) pertaining to that company as well as potentially a certain rep. The problem is that the contact tied to the company, the sales manager tied to the company, and the rep tied to the company all come from the same table. Here is the base SQL code I've used to get everything but the sales rep's name and sales manager's name:
scCompany.ID AS companyID, scCompany.companyName, scCompany.companyURL, scCompany.billToAddress1 AS companyAddress1, scCompany.billToAddress2 AS companyAddress2, scCompany.billToCity AS companyCity, scCompany.billToState AS companyState, scCompany.billToZip AS companyZip, scCompany.billToCounty AS companyCounty, scCompany.billToCountry AS companyCountry, scCompany.businessType, scCompany.phoneExt, scCompany.phoneNum, scCompany.faxNum, scCompany.minEmployees, scCompany.maxEmployees, scCompany.actionTypeMAX, scCompany.actionDateMAX, scCompany.actionTimeMAX, scCompany.statusID, scCompany.userID, scCompany.repID
FROM scCompany, scContacts
INNER JOIN scStatus ON scStatus.ID = scCompany.statusID
WHERE scgroupContacts.contactID = [insert cookie value] AND scgroups.ID = [insert cookie value] AND scCompany.ID = scContacts.companyID AND scCompany.groupID = scgroups.ID ----- END SQL CODE -----
As indicated right now, this SQL code will retrieve all of the contact and company information for a certain contact ID which comes from a cookie value (inserted in ASP). I want to get the [fName] and [lName] fields from scContacts table where the scContacts.ID = scCompany.userID, and I also want those same fields from scContacts table again where the scContacts.ID = scCompany.repID. It would be simplest and most efficient if I could do this all at once (and I'm sure it's possible). How would I change the SQL to bring in that information from the same table two more times, tying their ID's to ID's in the company table?
bobby writes "I have a Table from which i get the ID of customers(Customers are people who have written an online exam).The date of completion of the exams are stored in the corresponding table with the name of the exam.There are more than one exams and the names of the exams are stored in an other table.Now my question is how to get the date of cpletion with the given Id from the first table?"
