Can't Update Database (Resovled)

Printable View

Hi when i try to update my db i get this error

http://htmlschool.co.uk/images/prollem.JPG

this is the code that it has a problem with

VB Code:

Set myrs = New ADODB.Recordset
        'set the varible to get ready
Set MyConn = New ADODB.Connection
        'set the varible to get ready
    connstr = "Provider=MS Remote;" & _
           "Remote Server=http://192.168.0.2;" & _
           "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Inetpub\wwwroot\BCIMusrs.mdb"
           MyConn.ConnectionString = connstr
    MyConn.Open
 
'the line below is the problem the stuff above might help i think
 
Set myrs = MyConn.Execute("UPDATE users SET ScreenName = '" 
& userscrname & "' WHERE Username ='" & employeeperson & "'")

Please Help

Sam Lad

Dec 10th, 2003, 04:08 PM
techgnome
Since there is no recordset being returned.... you don't need the set.... just this:
VB Code:
```
MyConn.Execute("UPDATE users SET ScreenName = '" 
& userscrname & "' WHERE Username ='" & employeeperson & "'")
```
TG

thanks but still same error
this is the code now

VB Code:

Set MyConn = New ADODB.Connection
        'set the varible to get ready
    connstr = "Provider=MS Remote;" & _
           "Remote Server=http://192.168.0.2;" & _
           "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Inetpub\wwwroot\BCIMusrs.mdb"
           MyConn.ConnectionString = connstr
    MyConn.Open
MyConn.Execute ("UPDATE users SET ScreenName = '" & 
userscrname & "' WHERE Username ='" & employeeperson & "'")

Hmm... remote accessing an MDB? You will have problems...

Remove the parentheses:

VB Code:

Set MyConn = New ADODB.Connection
        'set the varible to get ready
    connstr = "Provider=MS Remote;" & _
           "Remote Server=http://192.168.0.2;" & _
           "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Inetpub\wwwroot\BCIMusrs.mdb"
 
 
    MyConn.Open connstr
 
MyConn.Execute "UPDATE users SET ScreenName = '" & 
userscrname & "' WHERE Username ='" & employeeperson & "'"

Dec 10th, 2003, 10:54 PM
nkad

If the .mdb file is opened (meaning you have it openend in MS Access) or if you are hosting this from a server and the IUSR account has no read permissions on the folder/file you are writting to then, in both cases, you will get that error.
Dec 12th, 2003, 12:34 PM
Clothahump

You'll get this error if the table does not have a primary key established. Check to see if you do. If you don't, put one on it, even if you just add an identity field and make it the primary key.
Dec 14th, 2003, 02:27 PM
señorbadger

done both of those IUSR has write permissions still same problem

help please !
Dec 14th, 2003, 05:33 PM
kovan

make sure the db is not read-only

Ado is in type read only so now i ammended that, also field names were reserved words for connection (how foolish of me)

thanks all

this is the code to solve the issue

VB Code:

Dim MyConn As ADODB.Connection
     Set MyConn = New ADODB.Connection
 
         MyConn.ConnectionString = "Provider=MS Remote;" & _
                "Remote Server=http://127.0.0.1;" & _
                "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\Inetpub\wwwroot\db.mdb"
  MyConn.CursorLocation = adUseClient 'open the database in r/w mode
  MyConn.Open
 
MyConn.Execute ("UPDATE users SET UserPWD = '" _
& TxtnewPWD.Text & "' WHERE UserID ='" & username & "'")

so to write back pick sensible field names and before you open
(like the line before type)

VB Code:

MyConn.CursorLocation = adUseClient

thanks again all !