Hi, I am working with ASP.NET...but the code is from my ASP days...and I can read etc from my database, but I can't seem to get the adding to work! I was wondering if someone could help me out...

Here is the code...

Sub CreateUser( Src As Object, E as EventArgs )
 Dim tPass1, tPass2, tUser
 
 tPass1 = Password1.Text
 tPass2 = Password2.Text
 tUser = Username.Text
 
 Message.Text = ""
 
 If tPass1 = tPass2 Then
  Dim dbconn, rs, x, y, conn, connConfig, bUserFound, xv
  bUserFound = False
  conn = Server.CreateObject("ADODB.Connection")
  conn.Provider = "Microsoft.Ject.OLEDB.4.0"
  connConfig = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("databases/users.mdb")
  conn.Open( connConfig )
  
  rs = Server.CreateObject("ADODB.Recordset")
  rs.Open("users", conn)
  
  do until rs.EOF
   for each x in rs.fields
    if x.name = "Username" then
     Dim tLoweredUser as String
     Dim tLoweredValue as String
     
     tLoweredUser = tUser.ToLower()
     tLoweredValue = x.value
     tLoweredValue = tLoweredValue.ToLower()
     
     if tLoweredValue = tLoweredUser then
      bUserFound = True
     end if
    end if
   next
   rs.movenext
  loop
  
  conn.Close
  
  if bUserFound then
   Message.Text = "Username already exists in database!"
   Exit Sub
  else
   'conn = Nothing
   conn = Server.CreateObject("ADODB.Connection")
   conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.Open(server.mapPath("databases/users. mdb"))
   
   Dim sql
   
   sql = "INSERT INTO users (Username,Password,Rank,Joined,Profile,Website)"
   sql = sql & " VALUES "
   sql = sql & "('" & tUser & "',"
   sql = sql & "'" & tPass1 & "',"
   sql = sql & "'1','" & now() & "','a','a')"
   
   On Error Resume Next
   conn.Execute( sql )
   if err.Number <> 0 then
    Message.Text = "Cannot register, please contact the administrator!"
    Response.Write(err.Number & " :: " & err.description)
    Response.Write("<br />")
    Response.Write(sql)
   else
    Message.Text = "You have been registered!"
   end if
   
   conn.close
  end if
 Else
  Message.Text = "Passwords do not match!"
  Exit Sub
 End If
End Sub

It says:

-2147217900 :: Syntax error in INSERT INTO statement.
INSERT INTO users (Username,Password,Rank,Joined,Profile,Website) VALUES ('admin','1234','1','11/4/2003 3:38:47 PM','a','a')

The first line is the err.number and err.description

the second line is sql's value...

Thanks... It has been frustrating me ever since last night...I spent at least a good 2 hours trying to figure out what was wrong without finding a thing...

Some of it is in ASP.NET but the actual database stuff is all in ASP. The ASP.NET code includes the Sub statements, the textbox statements etc. Message is an <asp:Label> and Username and Password are both <asp:TextBox>s...

ASP 3.0 != ASP.NET

do your code in ASP.NET terms and believe it or not, it'll work

http://www.4guysfromrolla.com/webtech/chapters/ASPNET/ch06.2.shtml - exellent article on 4guys showing the basics of ADO.NET

Dates need to be surrounded by ## in Access. Password may also be a reserved word.

But yes, there's not much point coding in .net if you're not going to make the most of its features.

For starters, forget about 'On Error Resume Next!'  We've now got structured error handling.  Try/Catch/Finally is the way to go.

Cheers,
Andrew

For the record, "Password" is http://www.flws.com.au/showusyourcode/codeLib/code/odbc_keywords.asp - not a reserved word

for two replies on this thread, read first line in his post:
Hi, I am working with ASP.NET...but the code is from my ASP days.

oops, you missed that... lol

I was getting mixed messages, he/she said it was asp but it had these arguments:

Sub CreateUser( Src As Object, E as EventArgs )

which are asp.net and it refers to a label or literal control:

  Message.Text = "Passwords do not match!"

So i was a bit confused