Howdy, Stranger!

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

Categories

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

  • alexzhenalexzhen Member Posts: 13
    : [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.
  • tschumantschuman Member Posts: 2
    : : [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!

  • alexzhenalexzhen Member Posts: 13
    : : : [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.
  • Shawn CarterShawn Carter Member Posts: 0

    ( http://forcoder.org ) free ebooks and video tutorials about { Delphi, Swift, Visual Basic, Python, C, Objective-C, Go, Assembly, R, Java, Visual Basic .NET, Perl, C#, Ruby, MATLAB, PHP, PL/SQL, C++, JavaScript, Scratch F#, Rust, Prolog, Alice, LabVIEW, Logo, Ada, Clojure, Kotlin, Lisp, Erlang, Scala, Hack, Transact-SQL, Dart, ML, Crystal, COBOL, SAS, D, Bash, VBScript, Julia, Fortran, FoxPro, Awk, Scheme, Apex, ABAP, Lua } __

Sign In or Register to comment.