Incorrect syntax 1

[hsingh1981]

Hi all i have created an insert SQL statement while back but it doesn't seem to be working. I get the following error msg when my code catches the msg and displays the error as:

Line 1 :Incorrect syntax near ';'
Line 1 :Incorrect syntax near R_Equipment
'Date' is not a recognised function name

my sql statement is this. This did work in access 2003 but i have migrated to SQL server 2000 and slowly filtering the quirks out.

    sql = "INSERT INTO EquipTraining ( StaffID, EquipID, Status, StatusDate ) "
        sql = sql + "SELECT " & id & " AS StaffID, R_Equipment.ID, 'E' AS Expr1, Date() as Expr2 "
        sql = sql + "FROM R_Equipment INNER JOIN R_EquipmentArea ON R_Equipment.ID = R_EquipmentArea.EquipID "
        sql = sql + "WHERE (((R_Equipment.ID) Not In (SELECT EquipTraining.EquipID FROM EquipTraining WHERE (((EquipTraining.StaffID)=" & id & "));)) AND ((R_EquipmentArea.AreaID)='" & cboArea.SelectedValue & "') AND ((R_Equipment.Used)='Y'));"

If someone could point out where abouts am going wrong?

many thanks

 

[r937]

this question really belongs in the SQL Server forum, doesn't it

tip: print the sql variable instead of executing it, to let you see the actual sql, which helps in debugging

okay, you will have to replace Date() with GETDATE()

also, remove the semi-colon inside the query



r937.com | rudy.ca

 

[hsingh1981]

many thanks mate...that did the trick.