使用ASP Classic写入SQL数据库 [英] Writing to an SQL database with ASP Classic
问题描述
更新-2/14/21
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 & "'JohnD@email.com',"
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!
推荐答案
好吧，看来是一个朋友，我终于知道发生了什么事.该代码实际上有效，但是由于某种原因，数据库的ID字段不会自动递增.因此，为了添加一条记录，我们必须首先检查数据库中有多少条记录，然后将1加到计数中以用于我们的新记录ID.似乎每种解决方案都会导致新的问题.正在编程...
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...
只要这是有道理的，即使它没有道理，这里都是id的属性.ID(PK，唯一标识符，不为空)
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 & "'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!
这篇关于使用ASP Classic写入SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
