Any SQL GURU - "INSERT INTO" PROBLEM

[omarswan]

This is my first time working with INSERT INTO, could you please take a look an see what is the problem.

' Adds The New User

Public Function ADD_NEW_USER(ByVal pUSER_USERNAME As String, _
       ByVal pUSER_PASSWORD As String, _
       ByVal pFULL_NAME As String, _
       ByVal pUSER_ACCESSLEVEL As String, _
       Optional pUSER_EMAILADDRESS As String = "[email protected]", _
       Optional pUSER_URL As String = AUTHOR_HOME_PAGE, _
       Optional pUSER_SMTP_SERVER As String = AUTHOR_SMTP_SERVER) As Boolean
       
   tmpString = ""
   tmpString = "INSERT INTO " & USER_TABLENAME & " (" & _
         "USER_LOGIN_NAME, USER_PASSWORD," & _
         " FULL_NAME, USER_ACCESS_LEVEL," & _
         " USER_EMAIL_ADDDRESS, USER_SMTP_SERVER," & _
         " USER_HOMEPAGE_URL, DATE_ADDED, USER_LOCKED)" & _
         " VALUES (" & _
         "pUSER_USERNAME, pUSER_PASSWORD," & _
         " pFULL_NAME, pUSER_ACCESSLEVEL," & _
         " pUSER_EMAILADDRESS, pUSER_SMTP_SERVER," & _
         " pUSER_URL, #" & Now() & "#, " & False & ")"

         PUBLIC_DATABASE.CONNECTION.Execute tmpString
         DoEvents

End Function


Note:
tmpString is already declared.
PUBLIC_DATABASE.CONNECTION is the Database conection it is open.
USER_TABLENAME stores the name of the table that I want to Update.
DATE_ADDED is a date/time field in the Table

P.S. I'm using ADO

I'm getting this error about too few parameters

[MrShickadance]

try using "{" instead of "#" for the date field

[MrShickadance]

Also look at your values.. it's structured wrong.

" VALUES (" & _
"pUSER_USERNAME, pUSER_PASSWORD," & _
" pFULL_NAME, pUSER_ACCESSLEVEL," & _
" pUSER_EMAILADDRESS, pUSER_SMTP_SERVER," & _
" pUSER_URL, #" & Now() & "#, " & False & ")"

-=- should be -=-

" VALUES (" & _
pUSER_USERNAME & ", " & pUSER_PASSWORD & ", " & _
pFULL_NAME & ", " & pUSER_ACCESSLEVEL & ", " & _
pUSER_EMAILADDRESS & ", " & pUSER_SMTP_SERVER & ", " & _
pUSER_URL & ", {" & Now() & "}, False)"

I'm not sure if you also need something around "False" like #False#... dunno about that one.

[dcarlson]

What type of database?

It looks like you variables names are being sent to the database and not the variable values.

ie.:

"Values ('" & pUSER_NAME & "', '" & pPASSWORD & "')"

[omarswan]

Thanks MrShickadance

God Bless You

[*Vince]

Hi,

I only took a quick look at this but,
I think this section :

" VALUES (" & _
"pUSER_USERNAME, pUSER_PASSWORD," & _
" pFULL_NAME, pUSER_ACCESSLEVEL," & _
" pUSER_EMAILADDRESS, pUSER_SMTP_SERVER," & _
" pUSER_URL, #" & Now() & "#, " & False & ")"

should look more like this :

" VALUES (" & pUSER_USERNAME & ", " & pUSER_PASSWORD & _

etc

do not enclose your variables in the string as text but
more as parameters.

Hope I help and be awared of caracters like ' in your
parameters. String type params must be enclosed by '
caracters. Even more important, be awared to double the
' IN your data to prevent your Querry from crashing because
let say, there is a ' caracter missing.

Now that I'am reading what I wrote I see that it's as
clear as mud :-) so feel free to E-mail me for more infos...

[omarswan]

Thanks