Problem in INSERT [RESOLVED]

[usamaalam]

Hello everybody,

I am facing a problem in inserting record in Access database. Here is the short code snippet, which gives exception "Syntax error in INSERT INTO statement", however if I print the query by uncommenting the Response.Write line and copy the query and paste it in access and run, it runs well.

' Insert
        Set Rs = Conn.Execute("SELECT [User].[UserName] FROM [User] WHERE [User].[UserName] = '" & UserName & "' AND [User].[IsDeleted] = False")       
        If Rs.EOF Then
            Dim InsertSql
            InsertSql = "INSERT INTO [User] (Name,UserName,Password,IsAdmin,IsSupervisor,IsDeleted) VALUES('" & FullName & "','" & UserName & "','" & Password & "'," & IsAdmin & "," & IsSupervisor & ",False)"
            'Response.Write(InsertSql)
            'Response.End
            Cmd.ActiveConnection = Conn
            Cmd.CommandText = InsertSql
            Cmd.Execute()
            'Set Rs = Conn.Execute(InsertSql)
            Response.Redirect("User.asp?Message=UserAdded")
        Else
            Response.Redirect("User.asp?Message=UserFound") 
        End If

[szlamany]

NAME, USERNAME and PASSWORD are truly bad choices for column names.

Place them all in []-brackets - they are most likely reserved keywords (check the link at the bottom of my signature)...

Some providers handle it better then others - the ACCESS tool probably "wraps them in []-brackets" behind the scenes for you...

[RobDog888]

Also, the tablename "User" may cause issues later down the road too.

[szlamany]

I had a colleage that decided to name a MS SQL Server table in an odd way - I think it started with a number.

He thought he would be able to always wrap it in []-brackets and live with it.

One day he discovered a bug in ENTERPRISE MANAGER - I think it would not backup the table since the name was not valid. Since he could not control the "process" in EM that did this, he was lost...

Just not worth it.

[RobDog888]

Colleges do that with their workstations. They give them serial numbers as names. After a few humdred systems you dont
know what is what.

[usamaalam]

Thanks a lot. I put the column names in brackets and the probem is resolved.

[szlamany]

Colleges do that with their workstations. They give them serial numbers as names. After a few humdred systems you dont
know what is what.

I can't spell today? Or am I just not understood?

Colleague - as in associate - another developer I work with at a customer site...

College - not what I meant

[RobDog888]

No, I understood you but was giving some insight to what I had to deal with at a particular college (what I mean ) backing up the notion of
naming systems/servers with numbers is
not that great an idea.

[szlamany]

No, I understood you but was giving some insight to what I had to deal with at a particular college (what I mean ) backing up the notion of
naming systems/servers with numbers is
not that great an idea.

Oh - I get it now...

I'm from the days before networks where known - big old single CPU mainframe boxes - then clustered systems...

Then Ethernet and DECNET came into our world - we went nuts - starting naming things after stars, planets, disney characters, old star trek stuff...

Now - seems the hardware guy who installed my SBS and a Server 2003 system at one of my main customer sites decided on bland names like SERVER01, SERVER02, SERVER03 and SERVER04. When I RDP into that other system all kinds of reference problems pop up - he should have asked what we like to name them!

[RobDog888]

I just came accross a tweak that changes the My Computer desktop icon name to "Username loged on to machinename". Let me
see if I can find it again.

Should help when logging into several desktops after another etc.

[RobDog888]

The registry tweak can be found here.