Welcome to WebmasterWorld Guest from 54.167.29.254

Forum Moderators: ocean10000

Message Too Old, No Replies

Help with autofilling an Access Db Column

     
1:01 am on Feb 5, 2005 (gmt 0)

Full Member

10+ Year Member

joined:June 16, 2004
posts:232
votes: 0


Yeah hi again, ok wondering if anyone could me out. I am trying to autofill a newly created column ("School") in my Acess DB with a defualt value ("SBC"), however i am having problems. The thing is i am guessing you would have to use a loop of some kind with a .eof restiction, but of which i dont know how to do. Here is what i got so far

<%
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1

Response.Buffer = true
dim cnn
dim rst
dim lngRecordNo
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=*****books.mdb;"

sqltext = "SELECT * FROM books"
rst.CursorType = 2
rst.LockType = 3
rst.Open sqltext,cnn

If cnn.BOF OR cnn.EOF Then

5:43 am on Feb 5, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 12, 2002
posts:1482
votes: 0


<%
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1

Response.Buffer = true
dim cnn
dim rst
dim lngRecordNo
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=*****books.mdb;"

sqltext = "SELECT * FROM books"
rst.CursorType = 2
rst.LockType = 3
rst.Open sqltext,cnn

do while not rst.eof
rst("your_column") = "SBC"
rst.update()
loop

rst.close()
set rst = nothing

cnn.close()
set cnn = nothing

12:17 pm on Feb 5, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 31, 2003
posts:196
votes: 0


I'd prefer:

dim cnn
set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=*****books.mdb;"
cnn.Execute "UPDATE Books SET School='SBC'"
cnn.Close
Set cnn = Nothing

3:01 pm on Feb 5, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 12, 2002
posts:1482
votes: 0


For the record, I agree with ZB's version. The only reason I made mine different was because you were looking for the loop method, using cursors (I'm assuming you're new to the game).

Do some research on using the UPDATE command, and using the Execute method of the Connection object with your queries.

5:04 pm on Feb 5, 2005 (gmt 0)

Full Member

10+ Year Member

joined:June 16, 2004
posts:232
votes: 0


Works Perfect for some reason was getting "Not enogh disk space error" but the new script works fine. THanks again.