Multiple-step operation generated errors. Check each status value.

jozi86
04-19-2004, 09:09 AM
Multiple-step operation generated errors. Check each status value.

I have worked through the problem to the point that I know that I am getting the error only on the "status column" - my guess

is it has something to so with the constant in the select list.

For some this field is not updatable.

Any ideas please?

Thanks in advance

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim ssql As String

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\mar2004test.xls;Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With

ssql = ""
ssql = ssql + "SELECT [Employee No],"
ssql = ssql + " Surname,"
ssql = ssql + " [First Name],"
ssql = ssql + " [Other Name],"
ssql = ssql + " DOB,"
ssql = ssql + " [ID Number],"
ssql = ssql + " [Married],"
ssql = ssql + " Gender,"
ssql = ssql + " 'Default' as Status"
ssql = ssql + " FROM [Sheet1$]"
rs.Open ssql, conn, adOpenStatic, adLockPessimistic

If rs.RecordCount > 0 Then
rs("Status") = 12 ' Error is thrown here - all other cols work just fine
rs.Update
End If

rs.Close
conn.Close

HardCode
04-19-2004, 09:17 AM
Maybe 'Status" is a reserved word/key word too? Change it to "[Status]" and try it again.

jozi86
04-20-2004, 12:35 AM
Thanks for the reply

But no go that is not it.

Any other ideas Ladys or chaps?

wengwashere
04-20-2004, 01:33 AM
I think its because 'Status' is not part of [Sheet1$], you only defined it and set a value ('Default') to it.

Im not sure if you could update that field, since, you yourself already defined the value for that.

may i ask, why do you need to change the value of 'STATUS', for what purpose?

mosh111
04-20-2004, 02:29 AM
Might want to check STATUS field expectation i.e expected format, or field size

This error commonly arises in SQL Server database based on the format or size of the value being applied.

wengwashere
04-20-2004, 02:39 AM
I tried doing the code, and tried to rename the 'STATUS' field into another value, it still returns the same error. I cant give a good explanation to this one but im sure its about on the line 'DEFAULT' as 'Status' and then changing the value of it in run-time. Ive done some google searching and this was the thing ive seen so far :

This error means that one or more fields you are inserting/updating contain an invalid value. Some of the possible scenarios are:
1. A string value is being inserted into a numeric field.
2. An invalid date expression is being inserted into a date field.
3. A string value being inserted is longer than the size of the string field.
4. A null value is being inserted into a field that does not allow nulls.

MKoslof
04-20-2004, 07:13 AM
Just out of curiousity, why are you using an alias for this field. If you remove the alias, what happens? Also, there are no INSERTIONS going on here...this is a simple select statement.

If you change your code to simply SELECT * FROM does it work? I would first try removing the As Alias.

wengwashere
04-20-2004, 10:39 PM
Just out of curiousity, why are you using an alias for this field. If you remove the alias, what happens?

I think hes trying to update a value of the field in a recordset that is set as an alias - which, i think, is impossible to update since its just an alias


If you change your code to simply SELECT * FROM does it work? I would first try removing the As Alias.

I tried his code and it works fine. The only problem is when you try to update the one with the alias - either its called 'Status' or any valid alias.

Just want to ask my pending question : why do you need to change the value of 'STATUS' - since it doesnt exists in [Sheet1$], for what purpose?

MKoslof
04-21-2004, 06:17 AM
Wengwahere:

Are you reading the same SELECT statement that I am? There is no updating here at all. From that exact SQL statement, if he removes the alias I bet it will work. I don't know what "status" is supposed to represent. But this query has nothing to do with updating a recordset.

wengwashere
04-21-2004, 06:39 PM
Wengwahere:

Are you reading the same SELECT statement that I am? There is no updating here at all. From that exact SQL statement, if he removes the alias I bet it will work. I don't know what "status" is supposed to represent. But this query has nothing to do with updating a recordset.

Yup. Correct me if im wrong :


1: If rs.RecordCount > 0 Then
2: rs("Status") = 12 ' Error is thrown here - all other cols work just fine
3: rs.Update
4: End If


but isnt he trying to update here? As what he said, and as what ive tried, the error occurs whenever the program passes line 2. The SELECT statement has no errors - aint got a problem there. Hes having the "Multiple-step operation generated errors" whenever he calls line 2.

HardCode
04-21-2004, 08:11 PM
You are defining the ad hoc field STATUS to a char data type because you assign it 'Default', but then you are trying to assign an integer with rs("Status") = 12

Change that to either 0 As Status or change this line to rs("Status").Value = "12 "

MKoslof
04-22-2004, 06:17 AM
Yes, and if you remove the alias, something I said several times...it will work. This has NOTHING to do with updating the database. You could select, insert, delete, etc. It has everything to do with the alias and nothing to do with the procedure.

You should be using an alias within an initial Select statement. AND as I said, there is NOTHING wrong with the SELECT statment. Then, simply remove the alias and the update works fine.

HardCode
04-22-2004, 09:30 AM
Kos, just to make sure :D did you notice that 'Default' isn't a field name? It is an explicit string value. My question is, aren't you forced to alias the field when you add an explicit value into a select, and not a field? I though you have to, and if you don't it adds the Expr1 alias for you.

MKoslof
04-22-2004, 09:34 AM
Correct, but this is not the recordset FIELD definition. If you want to update a FIELD in the recordset, you need to provide the actual field name :). That is my point. You can't update a field that doesn't exist. Whether or not this is needed within a SELECT statement is irrelevant. This question really has nothing to do with SQL (unless running an UPDATE sql statment and the same concept would apply). If you want to UPDATE a FIELD in a recordset, you need to reference the actual field value.

wengwashere
04-22-2004, 06:26 PM
Sorry if this sound stupid but it cant get off my mind...

As you said, remove the alias... so his code will be


ssql = ""
ssql = ssql + "SELECT [Employee No],"
ssql = ssql + " Surname,"
ssql = ssql + " [First Name],"
ssql = ssql + " [Other Name],"
ssql = ssql + " DOB,"
ssql = ssql + " [ID Number],"
ssql = ssql + " [Married],"
ssql = ssql + " Gender,"
ssql = ssql + " 'Default'" '<-- i removed the alias
ssql = ssql + " FROM [Sheet1$]"


right? If so, then what would be the value in Line 2 of the following code? Since youre not defining any alias for it?


1:If rs.RecordCount > 0 Then
2:rs("Status") = 12 ' Error is thrown here - all other cols work just fine
3:rs.Update
4:End If


does this mean you cant update it?

if yes, then that would go back to my first post in this thread, asking him why update it. if no, then what would be the workaround?

im just curious, its not my thread but i just wanna now how to do a workaround on this. thanks!

MKoslof
04-22-2004, 07:38 PM
Ok, I understand your confusion, this thread has gone way beyond its initial point :).

Basically, if the point of this query is simply to update a field, you are right, there is NO NEED for an alias. It appears to be a simple select statement and I don't see where this alias serves any purpose in the final result (aggregate function, etc.)

Basically, if the goal in the end is simply to update a field within a table, you have to reference the actual FIELD NAME you want to update. I see no purpose or point to having an alias at all, given the brief code sample shown.

There is no "work around"..if you want to update a FIELD on a table, then reference the field and update it. Using an alias holds no purpose in this regard. Unless we get more code, or a better explanation, the alias can just be removed.

wengwashere
04-22-2004, 08:14 PM
Ok, I understand your confusion, this thread has gone way beyond its initial point :).

I definitely agree.


Basically, if the point of this query is simply to update a field, you are right, there is NO NEED for an alias. It appears to be a simple select statement and I don't see where this alias serves any purpose in the final result (aggregate function, etc.)

i agree


Basically, if the goal in the end is simply to update a field within a table, you have to reference the actual FIELD NAME you want to update. I see no purpose or point to having an alias at all, given the brief code sample shown.

i agree

thanks dude! :)

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum