• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Arguments are of the wrong type SQL error

It appears that I am going to struggle to get every SQL statement I ever write to work 1st time!  At present I have the code below but seem to get a 3001 error - "Arguments are of the wrong type, are out of acceptable range or are in conflict with another".

txtUsername variable value is "simonwait"


Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Dim enPubs As ADODB.Connection
Set enPubs = New ADODB.Connection

If txtUsername = "" Then
MsgBox ("No user selected")
Exit Sub
End If
'Now open the connection.
enPubs.Open strConn
With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = enPubs
    ' Update the required records.
.Update "Users SET Removed=1 WHERE Username='" & txtUsername & "'"
 .Close
End With

dnPubs.Close
Set rsPubs = Nothing
Set enPubs = Nothing

Open in new window

0
simonwait
Asked:
simonwait
  • 5
  • 5
  • 4
  • +1
1 Solution
 
ZhaolaiCommented:
Try this:

.Update "Update Users SET Removed=1 WHERE Username='" & txtUsername & "'"

0
 
rockiroadsCommented:
why dont you query the record to return the one item in your recordset then edit it?

rsPubs.open("select * from users WHERE Username='" & txtUsername & "'")
rsPubs.edit
rsPubs!Removed =1
rsPubs.update

obviously the code above is simple, no validation checks etc
0
 
rockiroadsCommented:
validation check is simple, I shud of put it in

rsPubs.open "select * from users WHERE Username='" & txtUsername & "'"
if rsPubs.eof = false then
    rsPubs.edit
    rsPubs!Removed =1
    rsPubs.update
end if
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mohit VijayCommented:
REason of Error:
Update method will not accept string parameter as input.
0
 
simonwaitAuthor Commented:
Zhaolai - your suggestion gives the same error.

rockiroads - your suggestion (the 2nd one) throws an error with the .edit ("Method or data member not found")
0
 
simonwaitAuthor Commented:
VjSoft.  But the condition to be meat is a string.  Surely there is a way to handle this?
0
 
simonwaitAuthor Commented:
Have noticed a typo.  dnPub.close on line 20 should read enPub.close
0
 
rockiroadsCommented:
sorry, required for dao, doh! remove that line and try again
0
 
Mohit VijayCommented:
It will not accept string as input. Its expecting Fields and values, and both are optional.
0
 
Mohit VijayCommented:
0
 
ZhaolaiCommented:
Complete code:


Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Dim enPubs As ADODB.Connection
Set enPubs = New ADODB.Connection

If txtUsername = "" Then
MsgBox ("No user selected")
Exit Sub
End If
'Now open the connection.
enPubs.Open strConn
With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = enPubs
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .CursorLocation = adUseServer
    .Open "select * from users WHERE Username='" & txtUsername & "'"
    ' Update the required records.
    !Removed = 1
    .Update
    .Close
End With

dnPubs.Close
Set rsPubs = Nothing
Set enPubs = Nothing

Open in new window

0
 
ZhaolaiCommented:
Ah, of cause you will need validation. Just replace lines 20 and 21 with the following code:


    If Not (.EOF Or .BOF) Then
        !Removed = 1
        .Update
    End If

Open in new window

0
 
simonwaitAuthor Commented:
rockiroads - deleted that line and now get
ERROR:
Run-time error '3251':
Current Recordset does not support updating. This may be a
limitation of the provider, or of the selected locktype.
0
 
ZhaolaiCommented:
That's resolved in my earlier posted code.
Just a typo in the code: dnPubs.Close should be enPubs.Close.
0
 
rockiroadsCommented:
try opening your recordset like this

    rsPubs.open "select * from users WHERE Username='" & txtUsername & "'", connectionObjectGoesHere, adOpenDynamic, adLockPessimistic
0
 
ZhaolaiCommented:
Here is the final code (tested and worked for me):

Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Dim enPubs As ADODB.Connection
Set enPubs = New ADODB.Connection

If txtUsername = "" Then
MsgBox ("No user selected")
Exit Sub
End If
'Now open the connection.
enPubs.Open strConn
With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = enPubs
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .CursorLocation = adUseServer
    .Open "select * from users WHERE Username='" & txtUsername & "'"
    ' Update the required records.
    If Not (.EOF Or .BOF) Then
        !Removed = 1
        .Update
    Else
        MsgBox "Username does not exist: " & txtUsername
    End If
    .Close
End With

enPubs.Close
Set rsPubs = Nothing
Set enPubs = Nothing

Open in new window

0
 
rockiroadsCommented:
the default cursor is probably what is preventing you from updating the recordset. Its most likely adForwardOnly.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 5
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now