Problems updating with Like

[uczmeg]

Hi,

I'm using ADO (via Microsoft.Jet.OLEDB.4.0) on an access database and having some issues updating using the like command.

I display a set of results in a grid based on users filters. These can filter on codes and so I have sql statements that contain the following:

... where code like 'X%'
... where code like 'X%X'
... where code like '%X'

These all work fine and produce the desired results.

I then use these filters for creating update statements based on what is shown in the grid.

Updates work fine in the first two cases:

... where code like 'X%'
... where code like 'X%X'

However when I try to do an update query using:

... where code like '%X'

I get the following error:

3001 : Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Does anyone have any ideas how I can fix or work around this?

Cheers
Marc

 

[RJFost]

Looks like you're using T/SQL wildcards in an Access query. Access uses "?" to represent any single character & "*" to represent any number of any character. Most likely, it's interpreting the "%" as an integer variable declaration & can't assign the value "X" to a numeric.

Try changing your wildcards & see if that helps...

 

[uczmeg]

Hi,

I believe have the wildcards correct (it works in all cases but the last), I'm accessing the access db through VB (oops should have metioned that) using ado commands and recordsets.

Switching to a * doesn't work at all.

Thanks
Marc

 

[uczmeg]

Does anyone have any ideas on a different way I can update a value based on all codes ending in X.

The database is a bit big to consider stepping through all the codes.

Cheers
Marc

 

[johnwm]

[tt]
Dim strNew As String
Dim cnn As New ADODB.Connection
Dim strSQL As String
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\user.mdb;"
strNew = "New One"
strSQL = "Update tblUser set [username] = '" & strNew & "' where [username] like '%X' "

cnn.Execute strSQL, , adCmdText
Set cnn = Nothing
[/tt]

Works for me! Obviously this is a snippet only - no error trapping, no sophistication, not production code.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[uczmeg]

That is all I am doing and it fails here and at the customers site....!

What version of MDAC are you using?

Cheers
Marc

 

[uczmeg]

Okay, I hadn't checked that a simple update like the above does actually work. Sorry.

My update is a little more complex with a join in it. The joined statement works fine for all 3 cases in Access and 2 of the 3 in VB.

UPDATE price_sets_data psd
INNER JOIN master_list m ON psd.code = m.code
set psd.modifier1= 1 , psd.price1= NULL
where psd.set_code=22 and
m.category=1 and psd.code like '%44'

Is there a better way to write that?

 

[uczmeg]

Obviously I was having a bad week last week.

The above all works fine and the problem was re-applying the filtering to the recordset. Oddly this does fail under those conditions, but was also unnecessary...

Sorry for wasting people's time.

Marc