MS ACCESS QUery
-
Hi I am writing the following SQL command in the MS Access and it is working in access. When I add it to the command string in C# to execute it with the OleDbSqlDataAdapter I get a syntax error. Can someone help please?
SELECT USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = \"I\" AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));I used \" only in the C# part -
Hi I am writing the following SQL command in the MS Access and it is working in access. When I add it to the command string in C# to execute it with the OleDbSqlDataAdapter I get a syntax error. Can someone help please?
SELECT USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = \"I\" AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));I used \" only in the C# partjonhbt wrote:
SELECT USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = \"I\" AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));
try this for your string: "USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = 'I' AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));"
I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!
-
Hi I am writing the following SQL command in the MS Access and it is working in access. When I add it to the command string in C# to execute it with the OleDbSqlDataAdapter I get a syntax error. Can someone help please?
SELECT USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = \"I\" AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));I used \" only in the C# partReplace CHECKTYPE = \"I\" with CHECKTYPE = 'I' :)
Niladri Biswas
-
Replace CHECKTYPE = \"I\" with CHECKTYPE = 'I' :)
Niladri Biswas
-
jonhbt wrote:
SELECT USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = \"I\" AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));
try this for your string: "USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) INNER JOIN CHECKINOUT as main ON USERINFO.USERID = main.USERID WHERE (main.CHECKTYPE = 'I' AND main.LOGID = (SELECT MAX(LOGID) FROM CHECKINOUT as last WHERE last.USERID = main.USERID));"
I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!
Nothing happened U tried it that way before and still the same error. The error is in second select statement because when i removed that part it worked. So if there is a way I can replace that sql part it would solve it. The funny thing is that when running that in the access queries it works
-
Nothing happened U tried it that way before and still the same error. The error is in second select statement because when i removed that part it worked. So if there is a way I can replace that sql part it would solve it. The funny thing is that when running that in the access queries it works
LOL... that would have been pertinent information beforehand... The second query (or more correctly - subquery) is failing because you are referencing a table that doesn't exist in the query... Consider saving the subquery as a view/query then referencing the saved view/query instead.
I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!
-
LOL... that would have been pertinent information beforehand... The second query (or more correctly - subquery) is failing because you are referencing a table that doesn't exist in the query... Consider saving the subquery as a view/query then referencing the saved view/query instead.
I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!
-
Save this as a query, call it something like qryMaxLogID
SELECT MAX(LOGID) as MaxLogID, UserID FROM CHECKINOUT
group by UserID
where CHECKTYPE = 'I'
Order by UserID;Then, reference it in your existing query like this:
"select USERINFO.Name,DEPARTMENTS.DEPTNAME, main.CHECKTIME FROM (DEPARTMENTS RIGHT JOIN USERINFO ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID) inner join qryMaxLogID on userinfo.UserID = qryMaxLogID.UserID inner join INNER JOIN CHECKINOUT as main ON qryMaxLogID.MaxLogID = main.LogID;"
I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!