I have a vb app that accesses a data base of participants who are in the db in primarily two tables, a "Roster" table and a "grades" table. This is designed to be able to track their grades from year-to-year. The "Roster" table has their first name, last name, and gender and the "Grade" table has a grade for them for each year that they have been in the database. Here is the problem that I have having:
If I have two different people with the same name and gender on the same team and I call them up to list them in a list box it shows the correct name(s) and grades (even if their grades are different) but shows the same unique id for both of them. It basically shows the id that is associated with the first of the two participants.
For instance, this code:
sql = "SELECT r.RosterID, r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g "
sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName"
Set rs = conn.Execute(sql)
Do While Not rs.EOF
lstRoster.AddItem rs(0).Value & "-" & Replace(rs(2).Value, "''", "'") & ", " & rs(1).Value & " (" & rs(3).Value & ", " & rs(4).Value & ")"
rs.MoveNext
Loop
Set rs = Nothing
could show the following:
2441-John Doe (10)
2441-John Doe (11)
if there were two John Doe's on this team where one was in grade 11 and the other in grade 10. I assume there is something wrong with my join but it puzzles me that it lists both of them with the correct ages but only the first RosterID.
Can anyone please tell me what is wrong with this query: rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''") In DreamWeaver the bit in bold is greyed out, why? rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''") Thanks Joe
I am attempting to count distinct orders and display by client for the preceding month. Below is my current query. It is providing inaccurate results. Could someone with a fresh look point out my error(s)?
SELECT DISTINCT MtgeBroker, COUNT(CreatedDate) AS [Title Orders for Current Month]
FROM RECalendar
WHERE ( RECalendar.FileType IN ('COM','CONSTR','ConstrPerm','Constr Refi Table Fund','Conv Refi','FHA Refi','HELOC','Purchase 0 Loan','Purchase Loan','Purchase Cash','0 Is Not Closing','Witness') AND RECalendar.ModuleID IN ('594','603','675','814','815','816','817','818','819','820','821','822','823','824','825','826','827','828','829','830','831','832','833','834','887','888','889','890','891','892','893','894','895','896','897','898','899','900','901','902','903','904','905','906','907','908','909','910','911','912','913','914','915','974') ) AND ( MONTH(EventDateBegin)=MONTH(DATEADD(MM,-1,GETDATE())) AND YEAR(EventDateBegin)= YEAR(DATEADD(MM,-1,GETDATE())) ) GROUP BY MtgeBroker
string ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["blogg"].ConnectionString; SqlConnection con = new SqlConnection(ConnectionString);
Finally, the database has a table called poll, including the fields int lagID Pk string lagnamn int votes
What happens if I push the Rösta(Vote)-button is that it properly displays the text in it's label, but it won't update the database. I hope someone will see where I am going wrong here.
select DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS , SUM(inv_numb, freight_val) FROM OPCSAHH WHERE DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' AND LESS THEN = 2003 AND PREPAID COLLECT FLAG = 'C'
What could be wron with this query. I must be overlooking something. I keep getting errors for the count of records, sum, and Less Then.
THe following query generates, Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'if'. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ',' errors.
Can anyone tell me what I am missing?
EXECUTE sp_MSforeachtable ' select if(COL_LENGTH(''?'',''rn_create_user'') > 0, rn_create_user, rn_create_user) from ? as o where not exists(select * from users u where (u.users_id = o.rn_create_user) )';
Hi. I have a SP named, for instance, SP1.I need to execute something likeSELECT Sum([Field1]) FROM SP1 WHERE [SP1].[Field1]='0'and I get the message:Server: Msg 208, Level 16, State 3, Line 1Invalid object name 'SP1'.However, SP1 *IS THERE* and runs fine !!!ThanksAlex
For a given table, I want to know all the columns that are included inan index. I have looked on the web and come up with this, which seemsto work, but just wanted some verification. Are there any reasons whyI should be using the metadata functions like OBJECT_NAME?ThanksBruceSELECTDISTINCT c.namefrom sysusers u,sysobjects o,syscolumns c,sysindexes i,sysindexkeys kWHERE o.uid = u.uidAND u.name = userAND o.name = 'ing_customer'AND o.id = i.idAND i.indid = k.indidAND OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0AND 1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) ,INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) )AND i.indid BETWEEN 1 And 250AND k.id = o.idand k.colid = c.colidand c.id = o.idORDER BY c.name
SELECT T1.lnumber AS LOT_Number,(T2.time_out - T1.time_in) AS Duration, ((DATEPART (hour, (T2.time_out-T1.time_in)) *60) + DATEPART (minute, (T2.time_out-T1.time_in))) AS Minutes, COUNT(DISTINCT T2.test_desc) AS Number_of_Process
FROM Results AS T1 JOIN Results AS T2 ON T1.lnumber = T2.lnumber
WHERE T2.test_desc = 'Shipping' AND T1.test_desc = 'Receiving'
I have an error message that says:
Msg 8120, Level 16, State 1, Line 3 Column 'Results.lnumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
If I take this line out: COUNT(DISTINCT T2.test_desc) AS Number_of_Process The query runs ok.
Also if I run [COUNT(DISTINCT T2.test_desc) AS Number_of_Process] in:
SELECT COUNT(DISTINCT T2.test_desc) AS Number_of_Process, T2.lnumber AS Lot_number FROM Results AS T1 JOIN Results AS T2 ON T1.lnumber = T2.lnumber GROUP BY T2.lnumber
It will also give me result. Please help.... Thanks for your time
select a.service_num, a.cust_num from table1 a, table2 b where a.service_num = b.service_num and b.cust_num = b.cust_num and datediff(day, a.compl_date, b.compl_date) between case when month(getdate()+7) - month(getdate()) != 0 then -7 and 7 else -30 and 30 end and a.qty+b.qty=0
it gives me an error message as follow:
Server: Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'and'.
actually what i am trying to do is if the date of executing this query is the last week of each month, then the datediff between a.compl_date and b.compl_date is 30 days, if not then the datdiff is 7 days.
the error message shows it's now allow " =�!=�<�<=�>�>=" after sub query..
Select * From ZT_MediaImportLog Where isNumeric(ImportFileTime) = 1 And ImportFileTime < Convert(varchar(10),DateAdd(Month,-CAST (( select keepmonth from ZT_MediaImportLog, ZT_BillerChain a,ZT_BillerInfo b,ZT_Biller c,ZT_databackup d where a.BillerInfoCode = b.BillerInfoCode AND c.CompanyCode = d.Companycode AND ZT_MediaImportLog.Importsource = a.ChainCode ) AS int),GetDate()),112)
I keep getting the error "Invalid attempt to read when no data is present" when trying to query a table in my SQL DB. I have checked and rechecked and the table name and column names within it are spelled correctly. There are only three records in the database, they all have data in them, and the code in Country.Text precisely matches the data in the Country field in one of the records.
It's worth mentioning that when I use Visual Studio 2005's little direct SQL query tool to build and run the following SQL statement that it works properly:
I am perplexed. Any ideas anybody...here is the code...?
Dim SelectSQL_Countries As String SelectSQL_Countries = "SELECT * FROM data_Countries " SelectSQL_Countries &= "WHERE Country='" & Country.Text & "'"Dim con_Countries As New SqlConnection(ConfigurationManager.ConnectionStrings("MySiteMainDB").ConnectionString) Dim cmd_Countries As New SqlCommand(SelectSQL_Countries, con_Countries)Dim reader_Countries As SqlDataReader Try con_Countries.Open() reader_Countries = cmd_Countries.ExecuteReader()StateID.Text = reader_Countries("WordForState") reader_Countries.Close()Catch err As Exception lblResults.Text = err.Message Finally con_Countries.Close() End Try
1 SELECT 2 RowNumber, 3 'Source.Dbf, Plan.Dbf', 4 'Source Name is missing for Source Number "' + IsNull(RTrim(f.SOURCE_NUM),'Unknown') + '" in Plan.Dbf table.' 5 FROM 6 SourceDbf f 7 JOIN 8 ( 9 SELECT DISTINCT 10 SOURCE_NUM, 11 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME 12 WHEN 2 Then SRC2NAME 13 WHEN 3 THEN SRC3NAME 14 WHEN 4 THEN SRC4NAME 15 WHEN 5 THEN SRC5NAME 16 WHEN 6 THEN SRC6NAME 17 WHEN 7 THEN SRC7NAME 18 WHEN 8 THEN SRC8NAME 19 WHEN 9 THEN SRC9NAME 20 WHEN 10 THEN SRC10NAME 21 WHEN 11 THEN SRC11NAME 22 WHEN 12 THEN SRC12NAME 23 WHEN 13 THEN SRC13NAME 24 WHEN 14 THEN SRC14NAME 25 WHEN 15 THEN SRC15NAME 26 END 27 FROM 28 PlanDBF p 29 Where 30 p.PLAN_NUM = s.PLAN_NUM 31 ) as SourceName 32 FROM 33 SourceDBF s ) c on f.PLAN_NUM = c.PLAN_NUM
i am getting an error on Line 33 and this what the error says... Msg 207, Level 16, State 1, Line 33Invalid column name 'PLAN_NUM'.
I have tried running this query multiple times with no success I get the following errorIncorrect syntax near '('.I tried with quotes and without quotes around the 10 and also without the brackets around variable. It runs when an integer in entered in the variables place but that is not what I want. What am I doing wrong DECLARE @p AS intSET @p='10'SELECT TOP (@p)* FROM my_tbl order by newid()
strCommand = "SELECT * FROM tblevents WHERE startingDate=#"&startDate &"# AND eventtitle like '%"&criteria &"%' ORDER BY " &sSortSt I want to find any records that match a certain keyword on a specific date... But nothing comes up even though there is an event matching that criteria on the given date. Do I need brackets around my query or something?
What is the reason everything here executes except the very last "INSERT INTO forums_UsersInRoles VALUES ((@@identity),11) "? IF I change the order of the last two queries it is the other one (now last) that is not executed. Is there a limit to how many you can run at a time?
strSQL = " BEGIN INSERT INTO forums_Users (UserName, Password, Email) VALUES ('" & request.form("UserName") & "','" & Request("UserPassword") & "','" & Request("UserEmail") & "'); INSERT INTO forums_UserProfile (UserID) VALUES (@@identity) ;INSERT INTO forums_UsersInRoles VALUES ((@@identity),11) END;"
Hi all, As my user runs a query for her data, the query shows up with someone else's data. Can somebody tell me what happened and how o fix the problem. Thanks!
Since its so long, I've shown it as multiple lines, but in my code it is one long statement:
CurrentDb.Execute "Insert into Speechfiles(...) values (...)"
The table is called Speechfiles and the fields are as follows (types are in brackets)
FileName (text) SpeakerID (number) SamplingRate (number) DirectoryPathID (number) Text (text) CreationDate (text) <- this isn't date format, so not the prob Duration (number) Range (number)
and the values I'm trying to insert are all of the same type as the fields are.... SOOOOOOOOOOOOOooooo, can anyone spot anything? I've spent way to long on this. All my other CurrentDb.Execute "Insert Into...." statements are working fine, it's just this one!
From Traveler_X3 trv Join Recipe_X3 rcp On (trv.RecipeID = rcp.RecipeID)
Where trv.Oper = rtsA.oper And trv.RouteGroupID = ( Select rgp.routeGroupID
From Route_Group_X3 rgp
Where rgp.routeGroupName = ( Select rtl.RouteGroup
From RouteList_X3 rtl
WHERE rtl.route = rtsA.route ) ) ))
FROM Routes_X3 rtsA
where rtsA.route=( SELECT lhm.route
FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o
WHERE lhm.lot = 'S6D0IQ002A' AND lhm.oper = x3o.oper AND lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o WHERE lhm.lot = 'S6D0IQ002A' AND lhm.oper = x3o.oper) )
and rtsA.seq BETWEEN ( SELECT seq
FROM Routes_X3
WHERE oper =( SELECT lhm.oper
FROM brettb.pdash2.dbo.lothistorymoves lhm
WHERE lhm.lot ='S6D0IQ002A' AND lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm WHERE lhm.lot = 'S6D0IQ002A') )
and route=( select top 1 lhm.route
FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o
WHERE lhm.lot = 'S6D0IQ002A' AND lhm.oper = x3o.oper AND lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o WHERE lhm.lot ='S6D0IQ002A' AND lhm.oper = x3o.oper) ) )+1 and 219;
What i got working was to put the query in a temp table called #temp1 and then run this: select sum(theoreticalTime) from #temp1;
SET @EventName = 'DIVIDEND' SET @Ticker = 'IBM' SET @Cusip = ''
SET @Filter = 'Name like ''' + @EventName + '%'' AND Ticker Like ''' + @Ticker + '%'' AND Cusip like ''' + @Cusip + '%'''
Select * from eventdatadetails where @Filter
I want to execute the above SELECT statement and the filters in the where clause should come from the @Fitler variable... but query analyzer gives an error. If i replace the data in the @Filter with the variable in the query, everything works fine ...
By Database is heavily used , we use Transaction log backups ever 10 minuts, now someone fired a wrong query , that updated a particular table , the DB is still in use, there are some users working on it , I cant get the DB offline , what can I do to correct the mistake , please advice.
99pShop writes "I am trying to create a query to select all record for a specific 'PersonnelID'that have vacation booked in 2006 from a database.
I have written this query but it returns all PersonnelID that fit into the date block
requrirements
PersonnelID select if DateStart falls within 2006 select if DateEnd falls within 2006
query i built:
Select * From tblResourceList Where PersonnelID=72 And tblResourceList.DateStart Between '01 January 2006' And '31 December 2006' Or tblResourceList.DateEnd Between '01 January 2006' And '31 December 2006' Or (tblResourceList.DateStart < '01 January 2006' And tblResourceList.DateEnd > '31 December 2006')"
EMPNO int Checked ENAME nchar(10) Checked JOB nchar(10) Checked MGR varchar(50) Checked HIREDATE nvarchar(50) Checked SAL int Checked COMM varchar(50) Checked DEPTNO int Unchecked Unchecked
Code Snippet
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-80 800
20
7499 ALLEN SALEMAN 7698 20-Feb-81 1600 300 30
7521 WARD SALEMAN 7698 22-Feb-81 1250 500 30
7566 JONES MANAGER 7839 2-Apr-81 2975
20
7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-81 2850
30
7782 CLARK MANAGER 7839 9-Dec-82 2450
10
7788 SCOTT ANALYST 7566 9-Dec-82 3000
20
7839 KING PRESIDENT
17-Nov-81 5000
10
7844 TURNER SALESMAN 7698 8-Sep-81 1500 0 30
7876 ADAMS CLERK 7788 12-Jan-83 1100
20
7900 JAMES CLERK 7698 3-Dec-81 950
30
7902 FORD ANALYST 7566 3-Dec-81 3000
20
7934 MILLER CLERK 7782 23-Jan-82 1300
10
After I execute the query
Code Snippet select * from emp where( deptno=10 or comm is not null or sal <= 2000 ) and deptno=20
I got
Code Snippet EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-Dec-80 800 20 7566 JONES MANAGER 7839 2-Apr-81 2975 20 7788 SCOTT ANALYST 7566 9-Dec-82 3000 20 7876 ADAMS CLERK 7788 12-Jan-83 1100 20 7902 FORD ANALYST 7566 3-Dec-81 3 000 20
FROM dbo.v_Report_SHW_ReportRecords_Users_Companies AS v INNER JOIN
dbo.v_Company AS c ON v.CompanyID = c.CompanyID
WHERE (CONVERT(datetime, CONVERT(varchar, v.DateCreated, 101), 101) BETWEEN '2008-01-01' AND '2008-03-31') GROUP BY v.FirstName, v.LastName, v.Email, v.CompanyID, c.CompanyName, c.ParentCompanyName, v.ProgramID, v.ProgramName
This is the error I get.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ' '.
I know this is simple but for the life of me I can not see the problem. Any help is great.
UPDATE CART SET TOTAL_COUNT = TOTAL_COUNT + 1, SET TOTAL_COUNT1 = TOTAL_COUNT1 + 1, SET DATE1 = 10/30/2006 6:17:02 PM, SET PERSON = luke WHERE CART_ID = 1
I have wacked my head over this query I dont see anything wrong with it
Ive got the following SQL query and i want to return only distinct [ReviewID]'s, however SQL Server is complaining about it. This is the query:
SELECT DISTINCT Top 5 [ReviewID],[ReviewType],[ReviewTypeName],[LoginID],[LoginForename],[LoginSurname],[Approved],[ReviewDate] ,[Stars],[RelatedProductID],[Title],[Copy],[Rating], [Image1], [Image1Width], [Image1Height], [Image1AltText], [Image2], [Image2Width], [Image2Height], [Image2AltText], [CategoryL4] FROM [feManagedReview]
This is whats its complaining about:
Msg 421, Level 16, State 1, Line 1 The text data type cannot be selected as DISTINCT because it is not comparable. Msg 421, Level 16, State 1, Line 1 The text data type cannot be selected as DISTINCT because it is not comparable. Msg 421, Level 16, State 1, Line 1 The text data type cannot be selected as DISTINCT because it is not comparable.
select t.name as TriggerName, ta.name as TableName, o.parent_obj into GLPDemo.dbo.Temp_TablesAndTriggers from sysobjects o inner join sys.triggers t on t.object_id = o.id inner join syscomments c on c.id = t.object_id inner join sys.tables ta on ta.object_id = o.parent_obj where xtype = 'tr' and c.text like '%Audit%'
DECLARE @DBTrigger as varchar(100), @DBTable as varchar(100), @exestr as varchar(100)
DECLARE TCursor CURSOR for
SELECT TriggerName, TableName from Temp_TablesAndTriggers
