Syntax error in FROM clause
the code:
rs.open "Select * from Transaction",db,adopenDynamic, adlockOptimistic
and
rs.open "Select count(TransactionID)as total from Transaction",db,adopenDynamic, adlockOptimistic
it seems like you´re dealing with a Database in VB. in that case it is very important to check what you´re doing as most of selects commands are done using a single string and that can be very confuse sometimes. since you said its a Syntax error i think the only error in the text i can see is this:
you wrote in the second open:
Select count(TransactionID)as total from Transaction",db,adopenDynamic, adlockOptimistic
between
"Select count(TransactionID)" and
"as" must be a empty space as you´re declaring the count of the TransactionID as "Total" so it should be separate like this:
SELECT count(TransactionID)
AS total
FROM Transaction",db,adopenDynamic, adlockOptimistic
other than that make sure your connection string is correct and your database contains the columns you´re calling there. a tip from me... don´t use "SELECT * FROM..." as you´re selecting all the columns in the table and that´s not a good thing specially if you´re dealing with huge databases.. i recommend you to do this:
SELECT nameOfMyColumn1, nameOfMyColumn2
FROM MyTable
or if you´re going to join to another table then do this:
SELECT T.nameOfMyColumn1, T.nameOfMyColumn2, TB.nameOfMyColumnFromTable2
FROM MyTable T
LEFT JOIN MyTable2 TB
ON TB.nameOfMyColumn1FromTable2 = nameOfMyColumn1FromTable1
in that way you can select the columns you need and so get a nice performance gain with this small optimization... one more thing... don´t write "select and from" and other commands in lowercase as it could make the string really hard to read sometimes.. but instead use uppercase like: "SELECT, FROM, LEFT JOIN, INNER JOIN" etc.. or just use stored procedures to do the same job but to have 1 call
Regards
@ruantec
