sql server - Writing to an SQL database with ASP Classic

Question

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"
    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"

    'Establish a connection to the database
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open("Provider=SQLOLEDB; Data Source=" & db_server & "; Inital catalog=" & db_name & "; User ID=" & db_username & "; password=" & db_userpassword & ";")

    'Test the connection to make sure it is available and it's open.
    If IsObject(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
     end if

    'Query the tables I need
    Set rs= conn.execute("SELECT  * FROM [" & db_name & "].[" & db_schema & "].[" & db_tablename & "]")

    do until rs.EOF
      count = count + 1
      for each x in rs.Fields
        Response.Write(x.name)
        Response.Write(" = ")
        Response.Write(x.value & "<br>")
        next
      Response.Write("<br>")
      rs.MoveNext
    loop

    Set conn = nothing

    response.write "Records found = " & count
    %>

    ''''

So with the part above is actually working again. I set out to add a record to the database with the code below. It seems to work as it does not cause an error. However, it is not adding the record though.

    ''''
    <%
    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 & "; User ID=" & db_username & "; password=" & db_userpassword & ";")

   'Test the connection to make sure it is available and it's open.
    If IsObject(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
    end if

    sql="INSERT INTO " & db_name & "." & db_schema & "." & db_tablename & " (fname,lname,email,upassword)"
    sql=sql & " VALUES "
    sql=sql & "('John',"
    sql=sql & "'Doe',"
    sql=sql & "'[email protected]',"
    sql=sql & "'12345')"

    on error resume next
    conn.Execute sql,recaffected
    if err<>0 then
      Response.Write("No update permissions!")
    else
      Response.Write("<h3>" & recaffected & " record added</h3>")
    end if
    conn.close

    Set conn = nothing
    %>
   ''''

This is where I have been stuck. It is not writing to the database. It just runs and nothing gets done. If I remove the resume next portion, it still works with no error but, still, no record being written cause the resume next. I have tried two different users to run the code. Both of which have read and write permission. Now what? I'll just keep trying until someone puts me out of my misery... lol!

Answer 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.
    'If IsObject(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
    'end if

    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
      for each 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 & "'[email protected]',"
    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!