Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pluto
Starting Member
2 Posts |
 Posted - 2005-04-10 : 09:16:18
|
| Hi there,I am currently transferring from MS Access to MS SQL, I have imported all the data successfuly from my MS Access database to my MS SQL database. I have updated my connection details with the SQL server configuration, and the files seem to feeding most of the data through correctly, however I am experiencing some problems and could really do with some advice on what I need to watch out for... The things I do know about is dates in access are between # and in MS Sql it should be between ' and I know some of my boolean fields, rather than searching for... where field = true, this should be a 1.2 areas which are now not working and I do not have a clue as to why are...A. The following code just does not seem to work anymore for a login page, and I have checked to ensure the MasterPanel table has been imported correctly... but it just does not seem to work anymoresqlLogin = "SELECT * FROM MasterPanel WHERE User = '" & session("user") & "' AND Password = '" & session("password") & "'"set LoginRs = connMain.execute(sqlLogin)B. The following code, again, for some reason just no longer works... it hits an error saying : Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'. sqlCheckCurrency = "SELECT COUNT(*) FROM (SELECT DISTINCT Moneda FROM Bookings WHERE ADvertiserID = " & Cint(PanelRs("ID")) & ")"set checkCurrencyRs = connMain.execute(sqlCheckCurrency)Any help would be SO SO SO appreciated as I am really lost and worried that all my pages are not going to work and I just do not know why Many thanks |
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2005-04-10 : 10:24:13
|
User is a reserved word in SQL. Rename the column.Derived tables must be have a name in SQL.SELECT COUNT(*) FROM (SELECT DISTINCT Moneda FROM Bookings WHERE ADvertiserID = " & Cint(PanelRs("ID")) & ") as dt"As Tsql supports count (distinct expression) you don't actually need a derived table for that query. |
 |
|
|
Pluto
Starting Member
2 Posts |
Posted - 2005-04-10 : 19:55:00
|
| Hi there,Thank you ever so much for replying, changed the User column name and it works great, but I am having a little difficulty with doing the shortened sql statement, I have done this but it doesn't work, could you let me know where I am going wrong :)sqlCheckCurrency = "SELECT COUNT (DISTINCT Moneda FROM Bookings WHERE ADvertiserID = " & Cint(PanelRs("ID")) & ") as currencyCount"And one last thing, all my dates seem to be feeding in wrong now, basically I have experienced issues before even in MS Access where when you write a short date to the database it defaults it to American, so therefore 02/04/2005, in England would be 2nd April 2005, it wrote it to the databse as 4th Feb 2005... anyhow I got around this by making sure ALL dates I wrote to the database went in long format, ie 4 jun 2005.But now, all the dates which have been imported from my MS Access database into my MS SQL database are now all showing as American dates, essentially 02/03/2005 before via the MS Access db was 2nd March, now from the MS SQL feeds out to the page as 3rd Feb ... Could you offer me any advice on how I can get this problem resolved?I am very new to this and I must say your help is a real life saver!! :o)Thanks again |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-04-11 : 04:45:15
|
| Data display issues are jsut that DISPLAY issues....your dates are still stored properly.You may need to include a SET DATEFORMAT xxx at the start of your SQL....or change/affect your regional settings. Date manipulation using ccyymmdd seems to be impervious to problems.RE your SQL issue....it's a problem to do with the IMPROPER location of the ")" after the DISTINCT. Have a look at BOL for the syntax of this statement. |
|
|
|
|
|
|
|
|
Problems transferring from Ms Access to MS SQL