[RESOLVED] ERROR: Application uses a value of the wrong type for the current operation

[aikidokid]

I am using the example from the FAQ's titled - How Can I Add A Record To A Database, but I am trying to update a record, not add it.

I have the following code but get the error:
Application uses a value of the wrong type for the current operation
on the line that starts .Parameters.Append .......

Now, I'm not to sure what this exactly means, but is it something to do with the 4th parameter of the code to add - '50'?
What does this '50' stand for?
Is it the length of the string added?

strSQL = "UPDATE INTO tbl_Code (Code_Text) VALUES (rtfcode.text)"
 
With adoCmd
    .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandText = strSQL
    .Prepared = True
    .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, rtfCode.Text)
    .Execute , , adCmdText + adExecuteNoRecords
End With

[Hack]

It is not UPDATE INTO

It is simply UPDATE tablename SET fieldname = '" & Text1.Text & "' "

INTO is used with INSERT

Also, there is no VALUES clause with an UPDATE

Again, that is only used with INSERT

[si_the_geek]

Also, as you are using a Command you should not put any values into the SQL statement - just ? as a placeholder, eg:

"UPDATE tablename SET fieldname = ? "

These are then filled from the parameters that you append.

Now, I'm not to sure what this exactly means, but is it something to do with the 4th parameter of the code to add - '50'?
What does this '50' stand for?
Is it the length of the string added?

It's easy to find out.. click on CreateParameter and press F1.

[aikidokid]

Thanks Hack and si_the_geek

I have changed the SQL completely as you both suggested, but I still have the error.
I will take a look on MSDN online

It's easy to find out.. click on CreateParameter and press F1

Um, I can't. I installed MSDN but the link between VB6 and MSDN doesn't seem to work.
It may be some sort of registry entry, but if it is, I wouldn't have a clue where to start looking.

[si_the_geek]

Is that MSDN in general (in which case I'd recommend re-installing it), or does it work for other things like With?

[Hack]

What does your code look like now?

[aikidokid]

Is that MSDN in general (in which case I'd recommend re-installing it), or does it work for other things like With?

Error message says MSDN not installed for anything.
I have re-installed it twice now, once straight after re-installing VB6

What does your code look like now?

strSQL = "UPDATE tbl_Code SET Code_Text = ?"
 
With adoCmd
    .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandText = strSQL
    .Prepared = True
    .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 3000, rtfCode.Text)
    .Execute , , adCmdText + adExecuteNoRecords
End With

[si_the_geek]

In that case I'd recommend looking at previous threads about the MSDN library - I haven't had that kind of issue myself (a re-install has fixed it for me), but I'm pretty sure others have solved it.

The number is the maximum size of the data, I guess 3000 will be fine.

I think you should change adVarChar to adLongVarChar, but I'm not completely sure.


By the way, you should add a Where clause to the Update (eg: "WHERE CodeID = ?"), otherwise it will update all rows in the table!

[Hack]

Reinstall MSDN, but DO NOT do a "Typical" install.

Instead, run a FULL install.

[aikidokid]

Reinstall MSDN, but DO NOT do a "Typical" install.

Instead, run a FULL install.

I have a day off tomorrow, I will give this a go then, thanks.

I now have this code, but still with the same error:

strSQL = "UPDATE tbl_Code WHERE Code_Description = '" & CategoryToLoad & "' SET Code_Text = ?"
 
With adoCmd
    .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandText = strSQL
    .Prepared = True
    .Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, 3000, rtfCode.Text)
    .Execute , , adCmdText + adExecuteNoRecords
End With

The error says that the "rtfCode.Text" value is the wrong type. It says in MSDN that it is a Variant. Why would this cause a problem?

Visual Basic Reference. Variant data type. A special data type that can contain numeric, string, or date data as well as user-defined types and the special values ...

[salvelinus]

Try setting rtfCode.Text to a string variable first, check that it's correct, then use that in the code.
I'm not sure the code recognizes what rtfCode is. Usually you have to refer to the form in some way to access the controls - Me.rtfCode, for example. However, I'm used to working with Access/DAO, so may be wrong here.

[aikidokid]

Try setting rtfCode.Text to a string variable first, check that it's correct, then use that in the code.

Thanks salvelinus,
I have already tried this and it still had the same error.
I haven't tried "Me.rtfCode.Text" so I will give that a go.

[Hack]

Your query should read:

strSQL = "UPDATE tbl_Code SET Code_Text = ? "
strSQL = strSQL & "WHERE Code_Description = '" & CategoryToLoad & "' "

[si_the_geek]

Actually it should be like this:

strSQL = "UPDATE tbl_Code SET Code_Text = ? WHERE Code_Description = ?"

..with another parameter for Code_Description.

[Hack]

Good point.

[aikidokid]

..with another parameter for Code_Description.

So for every '?' you add, it adds a parameter to the CreateParameter?

[si_the_geek]

Something like that... each ? is a potential parameter.

You need to use CreateParameter etc to 'convert' it into one, and put a value into it.

[aikidokid]

I think I have got that with the following.
It goes past the first Parameter, but still errors out on the second.

CategoryToLoad returns the Code_Description as expected.

strSQL = "UPDATE tbl_Code SET Code_Text = ?"
strSQL = strSQL & " WHERE Code_Description = ?"
 
With adoCmd
    .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandText = strSQL
    .Prepared = True
    .Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, 50, CategoryToLoad)
    .Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, 3000, rtfCode.Text)
    .Execute , , adCmdText + adExecuteNoRecords
End With

[si_the_geek]

You need to change the order of the parameters, so that they are created in the same order that they appear in the SQL statement.

There isn't anything obviously wrong with your code... as I'm short on time I can only suggest you try using a different data type, such as adChar / adLongVarWChar / adVarWChar / adWChar

[brucevde]

What is the length of the text in the rtfCode control?
That error occurs when the Value's length is greater than the size argument.

.Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, Len(rtfCode.Text), rtfCode.Text)

Also, since you are executing a sql statement the Parameter's data type is almost irrelevant, as long as the Value matches the Type (or the Value can be implicity converted to the Type) it should work.

[aikidokid]

Thanks si and bruce.

This change now stops the original error;

.Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, Len(rtfCode.Text) ,rtfCode.Text)

but doesn't save the changes!

If I change the order of the parameters to this, it now gives me the error;
The Search key was not found in any record
on the Execute line;

.Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, Len(rtfCode.Text), rtfCode.Text)
.Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, Len(CategoryToLoad), CategoryToLoad)
.Execute , , adCmdText + adExecuteNoRecords

[aikidokid]

I have got a bit further along with this one.

I now have worked through this and found that this code WILL save files that aren't too big. The larger files error out on the Execute line with this error:
Run-Time Error '-2147467259(80004005)
The search key was not found in any record

What exactly does this mean? I have done a lot of searching and it seems to have various answers.

Anyway, I now know it works with smaller text (string) entries, so this may give somebody an idea what is causing this.

strSQL = "UPDATE tbl_Code SET Code_Text = ?"
strSQL = strSQL & " WHERE Code_Description = ?"
 
With adoCmd
    .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandText = strSQL
    .Prepared = True
    .Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, Len(rtfCode.Text), rtfCode.Text)
    .Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, Len(CategoryToLoad), CategoryToLoad)
    .Execute , , adCmdText + adExecuteNoRecords
End With
 
cn.Close

[aikidokid]

There isn't anything obviously wrong with your code...
as I'm short on time I can only suggest you try using a different data type, such as adChar / adLongVarWChar / adVarWChar / adWChar

I tried all of these and they all give the same error as in the last post

[si_the_geek]

From post #22 I assume it is purely down to the length. What size is the amount of characters that are "safe", and "fail"?

The size of a memo is "up to 64,000 characters.", so that in itself is probably not the issue - I suspect that you are reaching the limit of another part of the process .

[aikidokid]

The length of CategoryToLoad is no more than 100 characters, set in the DB design View.

The length of rtfCode.text varies.

Original length for testing = 2234

3198 chars fails
1334 passes

Tested some more:
2234 passes
2316 fails

tested more again:

2199 failed
2233 failed
2234 > 1546 failed

[brucevde]

How big is your database? Can you zip it and post it here?

This is an unusual error and I don't believe it has anything to do with your code. A quick search on google indicates it might be a corrupt database issue. Try a compact and repair.

Another possible cause is that the memo field has an index. Remove it if that is the case.

http://forums.aspfree.com/microsoft-...ord-18419.html

[aikidokid]

This is an unusual error and I don't believe it has anything to do with your code. A quick search on google indicates it might be a corrupt database issue. Try a compact and repair.

I did this first and it seemed to work and then fail, and generally be inconsistant.

Another possible cause is that the memo field has an index. Remove it if that is the case.

I have two memo fields, one of which had an index. I have removed this now, and hopefully, so far all is well.
I will test it a bit more, but so far large or small files can be saved.

http://forums.aspfree.com/microsoft-...ord-18419.html

Thanks for the link - very helpful and interesting.

I came across one site in my travels that had so many different reasons for the one error I didn't know where to begin.
This thread was exactly what I was experiencing.
Bookmarked this site now

Thanks for the help brucevbd.