Why wont my INSERT work instead of AddNew?

[b][red]This message was edited by tschuman at 2002-10-17 12:59:43[/red][/b][hr]
I am creating a web page where a user must create an account and then log in to access the site. After registering, I attempt to insert the new information into a MS Access 2000 database. Originally I was using the AddNew function and listing each field and value and finally calling at update at the end which worked fine before I uploaded it to the hosting server. I figured it doesnt support this feature perhaps. I then rewrote it in an SQL INSERT statement. I get no error when i run it but it doesnt add anything to my database.

My first question is: Does any one know why the first way I used with AddNew worked on Windows 2000 with IIS and it doesnt work on the hosting server?

And second: Why doesn't this SQL statement work. (its kinda big) Im sure I have all the fields matched up correctly and have done a response.write on the SQL statement to make sure it working the way I want it to. There is one field that is not included which is an autonumber which should take care of itself I believe.

Set objRS=Server.CreateObject("ADODB.Recordset")
str = request.form("LastName") &", " &request.form("FirstName")

SQL = "INSERT INTO ADDRESS_BOOK (ADDR_ID, ADDR_EMP_ID, ADDR_LOOKUP, ADDR_GROUP, ADDR_FIRST, ADDR_LAST, ADDR_COMP, ADDR_ADDR1,"

SQL = SQL & "ADDR_ADDR2, ADDR_ADDR3, ADDR_CITY, ADDR_ST, ADDR_ZIP, ADDR_COUNTRY, ADDR_PHONE, ADDR_EMAIL)"

SQL = SQL & "VALUES ("&Session("ID")&","&UCase(str)&","&Request.Form("Group")&","&Trim(Request.Form("FirstName"))&","

SQL = SQL & ""&Trim(Request.Form("LastName"))&","&Trim(Request.Form("Company"))&","&Request.Form("Addr_1")&","

SQL = SQL & ""&Request.Form("Addr_2")&","&Request.Form("Addr_3")&","&Trim(Request.Form("City"))&","

SQL = SQL & ""&Request.Form("State")&","&Trim(Request.Form("Zip"))&","&Trim(Request.Form("Country"))&","

SQL = SQL & ""&Trim(Request.Form("Phone"))&","&Trim(Request.Form("Email"))&");"

objRS.Open SQL, Conn, adOpenStatic, adLockOptimistic, adCmdText

Any help would be greatly appreciated.

Thanks!


Comments

  • : [b][red]This message was edited by tschuman at 2002-10-17 12:59:43[/red][/b][hr]
    : I am creating a web page where a user must create an account and then log in to access the site. After registering, I attempt to insert the new information into a MS Access 2000 database. Originally I was using the AddNew function and listing each field and value and finally calling at update at the end which worked fine before I uploaded it to the hosting server. I figured it doesnt support this feature perhaps. I then rewrote it in an SQL INSERT statement. I get no error when i run it but it doesnt add anything to my database.
    :
    : My first question is: Does any one know why the first way I used with AddNew worked on Windows 2000 with IIS and it doesnt work on the hosting server?
    :
    : And second: Why doesn't this SQL statement work. (its kinda big) Im sure I have all the fields matched up correctly and have done a response.write on the SQL statement to make sure it working the way I want it to. There is one field that is not included which is an autonumber which should take care of itself I believe.
    :
    : Set objRS=Server.CreateObject("ADODB.Recordset")
    : str = request.form("LastName") &", " &request.form("FirstName")
    :
    : SQL = "INSERT INTO ADDRESS_BOOK (ADDR_ID, ADDR_EMP_ID, ADDR_LOOKUP, ADDR_GROUP, ADDR_FIRST, ADDR_LAST, ADDR_COMP, ADDR_ADDR1,"
    :
    : SQL = SQL & "ADDR_ADDR2, ADDR_ADDR3, ADDR_CITY, ADDR_ST, ADDR_ZIP, ADDR_COUNTRY, ADDR_PHONE, ADDR_EMAIL)"
    :
    : SQL = SQL & "VALUES ("&Session("ID")&","&UCase(str)&","&Request.Form("Group")&","&Trim(Request.Form("FirstName"))&","
    :
    : SQL = SQL & ""&Trim(Request.Form("LastName"))&","&Trim(Request.Form("Company"))&","&Request.Form("Addr_1")&","
    :
    : SQL = SQL & ""&Request.Form("Addr_2")&","&Request.Form("Addr_3")&","&Trim(Request.Form("City"))&","
    :
    : SQL = SQL & ""&Request.Form("State")&","&Trim(Request.Form("Zip"))&","&Trim(Request.Form("Country"))&","
    :
    : SQL = SQL & ""&Trim(Request.Form("Phone"))&","&Trim(Request.Form("Email"))&");"
    :
    : objRS.Open SQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
    :
    : Any help would be greatly appreciated.
    :
    : Thanks!
    :
    :
    :

    wow! it's mess and confuse when do this way, anyway.
    1) try to set property in this way
    objRS.CursorLocation = adUseClient
    objRS.CursorType = adOpenKeyset
    objRS.LockType = adLockOptimistic

    2) you should write a function to read through form or put the name in the array. believe me, it better reading or editing for later on.

    2.1)you should put a space in the line #10 -- (SQL = SQL & " VALUES .....").

    2.2)you can omit all database fileds in the line #3, #4 -- (SQL = "INSERT INTO ADDRESS_BOOK VALUES(..........)")

    2.3) try to use execute method [Conn.Execute(SQL, 0, 0x00000080)] on the sql statement like insert or update. since execute method will not reture recordset, it's faster.

    i hope these help you out.
  • : : [b][red]This message was edited by tschuman at 2002-10-17 12:59:43[/red][/b][hr]
    : : I am creating a web page where a user must create an account and then log in to access the site. After registering, I attempt to insert the new information into a MS Access 2000 database. Originally I was using the AddNew function and listing each field and value and finally calling at update at the end which worked fine before I uploaded it to the hosting server. I figured it doesnt support this feature perhaps. I then rewrote it in an SQL INSERT statement. I get no error when i run it but it doesnt add anything to my database.
    : :
    : : My first question is: Does any one know why the first way I used with AddNew worked on Windows 2000 with IIS and it doesnt work on the hosting server?
    : :
    : : And second: Why doesn't this SQL statement work. (its kinda big) Im sure I have all the fields matched up correctly and have done a response.write on the SQL statement to make sure it working the way I want it to. There is one field that is not included which is an autonumber which should take care of itself I believe.
    : :
    : : Set objRS=Server.CreateObject("ADODB.Recordset")
    : : str = request.form("LastName") &", " &request.form("FirstName")
    : :
    : : SQL = "INSERT INTO ADDRESS_BOOK (ADDR_ID, ADDR_EMP_ID, ADDR_LOOKUP, ADDR_GROUP, ADDR_FIRST, ADDR_LAST, ADDR_COMP, ADDR_ADDR1,"
    : :
    : : SQL = SQL & "ADDR_ADDR2, ADDR_ADDR3, ADDR_CITY, ADDR_ST, ADDR_ZIP, ADDR_COUNTRY, ADDR_PHONE, ADDR_EMAIL)"
    : :
    : : SQL = SQL & "VALUES ("&Session("ID")&","&UCase(str)&","&Request.Form("Group")&","&Trim(Request.Form("FirstName"))&","
    : :
    : : SQL = SQL & ""&Trim(Request.Form("LastName"))&","&Trim(Request.Form("Company"))&","&Request.Form("Addr_1")&","
    : :
    : : SQL = SQL & ""&Request.Form("Addr_2")&","&Request.Form("Addr_3")&","&Trim(Request.Form("City"))&","
    : :
    : : SQL = SQL & ""&Request.Form("State")&","&Trim(Request.Form("Zip"))&","&Trim(Request.Form("Country"))&","
    : :
    : : SQL = SQL & ""&Trim(Request.Form("Phone"))&","&Trim(Request.Form("Email"))&");"
    : :
    : : objRS.Open SQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
    : :
    : : Any help would be greatly appreciated.
    : :
    : : Thanks!
    : :
    : :
    : :
    :
    : wow! it's mess and confuse when do this way, anyway.
    : 1) try to set property in this way
    : objRS.CursorLocation = adUseClient
    : objRS.CursorType = adOpenKeyset
    : objRS.LockType = adLockOptimistic
    :
    : 2) you should write a function to read through form or put the name in the array. believe me, it better reading or editing for later on.
    :
    : 2.1)you should put a space in the line #10 -- (SQL = SQL & " VALUES .....").
    :
    : 2.2)you can omit all database fileds in the line #3, #4 -- (SQL = "INSERT INTO ADDRESS_BOOK VALUES(..........)")
    :
    : 2.3) try to use execute method [Conn.Execute(SQL, 0, 0x00000080)] on the sql statement like insert or update. since execute method will not reture recordset, it's faster.
    :
    : i hope these help you out.
    :
    Hey thanks for getting back to me. I tried what you suggested but it still isn't adding anything to my table. I had a couple questions about what you mentioned.

    1. What are the numbers you used in this example?
    [Conn.Execute(SQL, 0, 0x00000080)]
    What is the first 0 for and what is the other number

    2. Should i be setting the connection properties to
    Conn.CursorLocation = adUseClient
    Conn.CursorType = adOpenKeyset
    Conn.LockType = adLockOptimistic
    instead of
    objRS.CursorLocation = adUseClient
    objRS.CursorType = adOpenKeyset
    objRS.LockType = adLockOptimistic
    if im going to do an Conn.Execute?

    3. Can you explain more about what you wrote below? How would I use an array?
    "you should write a function to read through form or put the name in the array. believe me, it better reading or editing for later on."

    Thanks again for your help!

  • : : : [b][red]This message was edited by tschuman at 2002-10-17 12:59:43[/red][/b][hr]
    : : : I am creating a web page where a user must create an account and then log in to access the site. After registering, I attempt to insert the new information into a MS Access 2000 database. Originally I was using the AddNew function and listing each field and value and finally calling at update at the end which worked fine before I uploaded it to the hosting server. I figured it doesnt support this feature perhaps. I then rewrote it in an SQL INSERT statement. I get no error when i run it but it doesnt add anything to my database.
    : : :
    : : : My first question is: Does any one know why the first way I used with AddNew worked on Windows 2000 with IIS and it doesnt work on the hosting server?
    : : :
    : : : And second: Why doesn't this SQL statement work. (its kinda big) Im sure I have all the fields matched up correctly and have done a response.write on the SQL statement to make sure it working the way I want it to. There is one field that is not included which is an autonumber which should take care of itself I believe.
    : : :
    : : : Set objRS=Server.CreateObject("ADODB.Recordset")
    : : : str = request.form("LastName") &", " &request.form("FirstName")
    : : :
    : : : SQL = "INSERT INTO ADDRESS_BOOK (ADDR_ID, ADDR_EMP_ID, ADDR_LOOKUP, ADDR_GROUP, ADDR_FIRST, ADDR_LAST, ADDR_COMP, ADDR_ADDR1,"
    : : :
    : : : SQL = SQL & "ADDR_ADDR2, ADDR_ADDR3, ADDR_CITY, ADDR_ST, ADDR_ZIP, ADDR_COUNTRY, ADDR_PHONE, ADDR_EMAIL)"
    : : :
    : : : SQL = SQL & "VALUES ("&Session("ID")&","&UCase(str)&","&Request.Form("Group")&","&Trim(Request.Form("FirstName"))&","
    : : :
    : : : SQL = SQL & ""&Trim(Request.Form("LastName"))&","&Trim(Request.Form("Company"))&","&Request.Form("Addr_1")&","
    : : :
    : : : SQL = SQL & ""&Request.Form("Addr_2")&","&Request.Form("Addr_3")&","&Trim(Request.Form("City"))&","
    : : :
    : : : SQL = SQL & ""&Request.Form("State")&","&Trim(Request.Form("Zip"))&","&Trim(Request.Form("Country"))&","
    : : :
    : : : SQL = SQL & ""&Trim(Request.Form("Phone"))&","&Trim(Request.Form("Email"))&");"
    : : :
    : : : objRS.Open SQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
    : : :
    : : : Any help would be greatly appreciated.
    : : :
    : : : Thanks!
    : : :
    : : :
    : : :
    : :
    : : wow! it's mess and confuse when do this way, anyway.
    : : 1) try to set property in this way
    : : objRS.CursorLocation = adUseClient
    : : objRS.CursorType = adOpenKeyset
    : : objRS.LockType = adLockOptimistic
    : :
    : : 2) you should write a function to read through form or put the name in the array. believe me, it better reading or editing for later on.
    : :
    : : 2.1)you should put a space in the line #10 -- (SQL = SQL & " VALUES .....").
    : :
    : : 2.2)you can omit all database fileds in the line #3, #4 -- (SQL = "INSERT INTO ADDRESS_BOOK VALUES(..........)")
    : :
    : : 2.3) try to use execute method [Conn.Execute(SQL, 0, 0x00000080)] on the sql statement like insert or update. since execute method will not reture recordset, it's faster.
    : :
    : : i hope these help you out.
    : :
    : Hey thanks for getting back to me. I tried what you suggested but it still isn't adding anything to my table. I had a couple questions about what you mentioned.
    :
    : 1. What are the numbers you used in this example?
    : [Conn.Execute(SQL, 0, 0x00000080)]
    : What is the first 0 for and what is the other number
    :
    : 2. Should i be setting the connection properties to
    : Conn.CursorLocation = adUseClient
    : Conn.CursorType = adOpenKeyset
    : Conn.LockType = adLockOptimistic
    : instead of
    : objRS.CursorLocation = adUseClient
    : objRS.CursorType = adOpenKeyset
    : objRS.LockType = adLockOptimistic
    : if im going to do an Conn.Execute?
    :
    : 3. Can you explain more about what you wrote below? How would I use an array?
    : "you should write a function to read through form or put the name in the array. believe me, it better reading or editing for later on."
    :
    : Thanks again for your help!
    :
    :
    hi again, sorry about take so long to answer your questions. i've busy day.

    1) 0 is returns the number of records that the operation affected
    0x80 is the "adExecuteNoRecords". The Connection object have two procedures which have same name. You should call the Sub instead a function (Conn.Execute SQL, 0, 0x80).

    2)No, set the properties to Recordset object and add objRS.Update after objRS.AddNew.

    3)Well, you can do this way
    3.1) Dim arr, size, , limitor, SQL
    arr = Array("Group", "FirstName", "LastName", "Company", .....)
    SQL = "INSERT INTO VALUES("
    SQL = SQL & Session("ID") & "," & UCase(str) & ","
    size = UBound(arr)
    For s = 0 To size
    If s < size Then
    limitor = ","
    Else
    limitor = ")"
    End If
    SQL = SQL & Request.Form(s) & limitor
    Next

    3.2)there is an alternate way to do, if you set the database fields and the form in same order;

    Dim size, frm, SQL
    SQL = "INSERT INTO VALUES("
    SQL = SQL & Session("ID") & "," & UCase(str) & ","
    frm = Request.Form
    size = frm.Count - 1
    For s = 0 To size - 1
    SQL = SQL & frm(s) & ","
    Next
    SQL = SQL & frm(size) & ")"

    this should help you out.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories