Solved

Arguments are of the wrong type SQL error

Posted on 2010-08-23
17
255 Views
Last Modified: 2012-05-10
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
Comment
Question by:simonwait
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +1
17 Comments
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33503679
Try this:

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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33503728
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33503745
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33504171
REason of Error:
Update method will not accept string parameter as input.
0
 
LVL 1

Author Comment

by:simonwait
ID: 33504198
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
 
LVL 1

Author Comment

by:simonwait
ID: 33504226
VjSoft.  But the condition to be meat is a string.  Surely there is a way to handle this?
0
 
LVL 1

Author Comment

by:simonwait
ID: 33504241
Have noticed a typo.  dnPub.close on line 20 should read enPub.close
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33504263
sorry, required for dao, doh! remove that line and try again
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33504291
It will not accept string as input. Its expecting Fields and values, and both are optional.
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33504321
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33504793
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
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33504820
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
 
LVL 1

Author Comment

by:simonwait
ID: 33504839
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
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33504945
That's resolved in my earlier posted code.
Just a typo in the code: dnPubs.Close should be enPubs.Close.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33505111
try opening your recordset like this

    rsPubs.open "select * from users WHERE Username='" & txtUsername & "'", connectionObjectGoesHere, adOpenDynamic, adLockPessimistic
0
 
LVL 17

Accepted Solution

by:
Zhaolai earned 250 total points
ID: 33505123
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33505133
the default cursor is probably what is preventing you from updating the recordset. Its most likely adForwardOnly.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question