Writing To An Sql Database With Asp Classic

Update - 2/14/21 Ok, this is where I'm at now. the code below works! Yay! However, there are no records in the database to read. '''' <% db_server = 'my_server' d

Solution 1:

Well, it seems a friend and I finally found out what was going on. The code actually works but, for some reason, the database's ID field is not auto-incrementing. So, in order to add a record, we had to first check to see how many records are in the DB then add 1 to the count to use for our new records id. Seems every solution leads to a new issue. That's programing...

Just in case any of that makes sense or even if it doesn't make sense, here the id's properties. ID (PK, uniqueidentifier, not null)

    db_server = "my_server"
    db_name = "my_db-name"
    db_username = "my_username"
    db_userpassword = "my_password"
    db_fieldname = "my_fieldname"
    db_tablename = "my_tablename"
    db_schema = "my_schema"
    count = 0'Establish a connection to the database
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open("Provider=SQLOLEDB; Data Source=" & db_server & "; Inital 
    catalog=" & db_name & "; intergaraded security = false ; User ID=" & 
    db_username & "; password=" & db_userpassword & ";")

    'Test the connection to make sure it is available and it's open.
    'IfIsObject(conn) then
    ' response.write "The connection is active!<br />"
    ' if conn.State = 1 then
    '   response.write "A connection is made, and is open.<br />"
    ' end if'endif

    Set rs= conn.execute("Select top 1 id from " & db_name & "." & db_schema & "." & db_tablename & " order by id desc")
    on error resume next
    do until rs.EOF
      foreach x in rs.Fields
        count = x.value
        'response.write x.value & "<br>"
      next
      rs.MoveNext
    loop'conn.Execute sql,recaffected
    if err<>0 then
      Response.Write("<br>id: " & err.description)
    else'Response.Write("<h3>" & count & " record in db</h3>")
    end if'conn.close

    Set rs= conn.execute("Select top 1 id from " & db_name & "." & db_schema & "." & db_tablename & " order by id desc")
    sql=sql & " VALUES "
    sql=sql & "(" & cstr(count + 1) & ","
    sql=sql & "'John',"
    sql=sql & "'Doe',"
    sql=sql & "'user@website.com',"
    sql=sql & "'1234abcd')"'response.write sql & "<br>"

    on error resume next
    conn.Execute sql,recaffected

    if err<>0 then
      Response.Write("<br>insert: " & err.description)
    else'Response.Write("<br>" & recaffected & " record added</h3>")
    end if
    conn.close

    Set conn = nothing

I left all the error checking in there but, it is all commented out. I hope this is the last time I have to come back to this. Goodluck!