[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.odbcnet

ERROR [HY090][IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]Invalid value stored in pcbValue

Cliffe Hodgkinson

7/10/2002 8:57:00 PM

I'm trying to update a single record in an AS/400 database file using
OdbcDataAdapter.Update. I'm able to create the record, using
OdbcCommand.ExecuteScalar, but when try to update it, I get an OdbcError:
"[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]Invalid value
stored in pcbValue." The SQLState is HY090, and the Native Error is 10001.

I'm not sure what the problem is, exactly. HY090 apparently indicates an
invalid string or buffer length. I've tried padding all my text values to
the size of the field and I've tried trimming them all. I also set the
DefaultValue for all my numeric fields to 0 and for text fields to "",
thinking maybe there was a problem with a DBNull value.

The only thing I can think of that's out of the ordinary is that the
DataTable I'm passing to the Update method has more fields than does my
UpdateCommand CommandText string. That is, my DataTable (and the database
table on the 400) have a whole bunch of fields, but I'm only updating a few
of them. I can hardly believe that's the problem, though.

Any suggestions?

Thanks,
Cliffe Hodgkinson
Nichirin Inc.
4 Answers

Bob Beauchemin

7/11/2002 1:21:00 AM

0

Can you trace the update statement that is being sent to the database
(including the error)? If the update isn't being sent to the database at
all, it's definately in the software. Does the same update work with the
ODBC query sample in the platform SDK (that's a pretty
straightforward/vanilla utility). If so, is the update statement sent to the
database different? How about a native utility program; does it work with
that?

Bob Beauchemin
bobb@develop.com


"Cliffe Hodgkinson" <cliffeh51@hotmail.com> wrote in message
news:O6xvGOEKCHA.2664@tkmsftngp09...
> I'm trying to update a single record in an AS/400 database file using
> OdbcDataAdapter.Update. I'm able to create the record, using
> OdbcCommand.ExecuteScalar, but when try to update it, I get an OdbcError:
> "[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]Invalid
value
> stored in pcbValue." The SQLState is HY090, and the Native Error is 10001.
>
> I'm not sure what the problem is, exactly. HY090 apparently indicates an
> invalid string or buffer length. I've tried padding all my text values to
> the size of the field and I've tried trimming them all. I also set the
> DefaultValue for all my numeric fields to 0 and for text fields to "",
> thinking maybe there was a problem with a DBNull value.
>
> The only thing I can think of that's out of the ordinary is that the
> DataTable I'm passing to the Update method has more fields than does my
> UpdateCommand CommandText string. That is, my DataTable (and the database
> table on the 400) have a whole bunch of fields, but I'm only updating a
few
> of them. I can hardly believe that's the problem, though.
>
> Any suggestions?
>
> Thanks,
> Cliffe Hodgkinson
> Nichirin Inc.
>
>

Cliffe Hodgkinson

7/11/2002 3:35:00 PM

0

OK, I ran a trace. The first sign of a problem is in this section:

>>
POForm 220-21f ENTER SQLGetConnectAttrW
SQLHDBC 04402220
SQLINTEGER 1209 <unknown>
SQLPOINTER 0x001A5A70
SQLINTEGER 1024
SQLINTEGER * 0x0012ECB8

POForm 220-21f EXIT SQLGetConnectAttrW with return code -1
(SQL_ERROR)
SQLHDBC 04402220
SQLINTEGER 1209 <unknown>
SQLPOINTER 0x001A5A70
SQLINTEGER 1024
SQLINTEGER * 0x0012ECB8

DIAG [HY092] [IBM][Client Access Express ODBC Driver (32-bit)]Option type
out of range. (0)
<<

I understand that it's that last SQLINTEGER * that stores the pcbValue.
Anyway, then there's a couple of SQLBindParameters that return SQL_SUCCESS,
and then comes my error:

>>
POForm 220-21f ENTER SQLExecDirectW
HSTMT 04402350
WCHAR * 0x0100DEF4 [ -3] "UPDATE pomast SET

CONNM=?,CONTL=?,BUYNO=?,FAXN=?,EADR=?,VNDNR=?,FOBDS=?,VIACD=?,VIADS=?,TRMDS=
?,CURID=?,CURDS=?,ACTDT=?,MDATE=? WHERE ordno=?\ 0"
SDWORD -3

POForm 220-21f EXIT SQLExecDirectW with return code -1
(SQL_ERROR)
HSTMT 04402350
WCHAR * 0x0100DEF4 [ -3] "UPDATE pomast SET

CONNM=?,CONTL=?,BUYNO=?,FAXN=?,EADR=?,VNDNR=?,FOBDS=?,VIACD=?,VIADS=?,TRMDS=
?,CURID=?,CURDS=?,ACTDT=?,MDATE=? WHERE ordno=?\ 0"

SDWORD -3

DIAG [HY090] [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400
SQL]Invalid value stored in pcbValue. (10001)
<<

Here's something else:
I ran a similar (but simpler) statement using VB6 and just putting the
information in the SQL statement (ie. no parameters) but using the same
driver. For the SQLGetConnectAttrW, it looked like this:

>>
VB6 1ac-175 ENTER SQLGetConnectAttrW
SQLHDBC 03BE1328
SQLINTEGER 108 <SQL_ATTR_TXN_ISOLATION>
SQLPOINTER 0x0012F33C
SQLINTEGER 4
SQLINTEGER * 0x00000000

VB6 1ac-175 EXIT SQLGetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 03BE1328
SQLINTEGER 108 <SQL_ATTR_TXN_ISOLATION>
SQLPOINTER 0x0012F33C
SQLINTEGER 4
SQLINTEGER * 0x00000000
>>

(The first SQLINTEGER is quite different from the used by ADO.NET, as is the
last SQLINTEGER* which is where the pcbValue is stored).

And the SQLExecDirectW:

>>VB6 1ac-175 ENTER SQLExecDirectW
HSTMT 03BE1568
WCHAR * 0x039C7BB0 [ 53] "UPDATE pomast SET vndnr='xxxxx'
WHERE ordno='P12345'"
SDWORD 53

VB6 1ac-175 EXIT SQLExecDirectW with return code 0
(SQL_SUCCESS)
HSTMT 03BE1568
WCHAR * 0x039C7BB0 [ 53] "UPDATE pomast SET vndnr='xxxxx'
WHERE ordno='P12345'"
SDWORD 53
<<

I'm not 100% sure what all of this means, and I'm certainly not sure how to
fix it, but any help would be appreciated.

Thanks,
Cliffe Hodgkinson
Nichirin Incorporated

(Ben)

7/18/2002 11:06:00 PM

0

Has anyone figured out what is going on with this error? I am
interested because I am getting [DB2/400 SQL]Invalid value stored in
pcbValue when I attempt to execute a stored procedure that returns a
cursor. I am attempting to put it into an odbcdatareader object. I
am able to do this in VB6. Anybody have any ideas? Sample code is
below.

dim oCmd as new OdbcCommand
dim dr as odbcdatareader
'oconn is a connection object that is open

oCmd = New OdbcCommand("{call SPNAME(?,?,?,?)}", oConn)
oCmd.CommandType() = CommandType.StoredProcedure

pP1 = oCmd.Parameters.Add("PARM1", OdbcType.Char, 21)
pP2 = oCmd.Parameters.Add("PARM2", OdbcType.Char, 1024)
pP2 = oCmd.Parameters.Add("PARM3", OdbcType.Char, 2)
pP3 = oCmd.Parameters.Add("PARM4", OdbcType.Char, 3)
oCmd.Parameters(0).Direction = ParameterDirection.InputOutput
oCmd.Parameters(1).Direction = ParameterDirection.InputOutput
oCmd.Parameters(2).Direction = ParameterDirection.InputOutput
oCmd.Parameters(3).Direction = ParameterDirection.InputOutput
oCmd.Prepare()
pP1.Value = "criteria"
pP1.Value = "criteria"
pP1.Value = "criteria"
pP1.Value = "criteria"

drReturn = oCmd.ExecuteReader

"Cliffe Hodgkinson" <cliffeh51@hotmail.com> wrote in message news:<#DcgJ$NKCHA.1744@tkmsftngp13>...
> OK, I ran a trace. The first sign of a problem is in this section:
>
> >>
> POForm 220-21f ENTER SQLGetConnectAttrW
> SQLHDBC 04402220
> SQLINTEGER 1209 <unknown>
> SQLPOINTER 0x001A5A70
> SQLINTEGER 1024
> SQLINTEGER * 0x0012ECB8
>
> POForm 220-21f EXIT SQLGetConnectAttrW with return code -1
> (SQL_ERROR)
> SQLHDBC 04402220
> SQLINTEGER 1209 <unknown>
> SQLPOINTER 0x001A5A70
> SQLINTEGER 1024
> SQLINTEGER * 0x0012ECB8
>
> DIAG [HY092] [IBM][Client Access Express ODBC Driver (32-bit)]Option type
> out of range. (0)
> <<
>
> I understand that it's that last SQLINTEGER * that stores the pcbValue.
> Anyway, then there's a couple of SQLBindParameters that return SQL_SUCCESS,
> and then comes my error:
>
> >>
> POForm 220-21f ENTER SQLExecDirectW
> HSTMT 04402350
> WCHAR * 0x0100DEF4 [ -3] "UPDATE pomast SET
>
> CONNM=?,CONTL=?,BUYNO=?,FAXN=?,EADR=?,VNDNR=?,FOBDS=?,VIACD=?,VIADS=?,TRMDS=
> ?,CURID=?,CURDS=?,ACTDT=?,MDATE=? WHERE ordno=?\ 0"
> SDWORD -3
>
> POForm 220-21f EXIT SQLExecDirectW with return code -1
> (SQL_ERROR)
> HSTMT 04402350
> WCHAR * 0x0100DEF4 [ -3] "UPDATE pomast SET
>
> CONNM=?,CONTL=?,BUYNO=?,FAXN=?,EADR=?,VNDNR=?,FOBDS=?,VIACD=?,VIADS=?,TRMDS=
> ?,CURID=?,CURDS=?,ACTDT=?,MDATE=? WHERE ordno=?\ 0"
>
> SDWORD -3
>
> DIAG [HY090] [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400
> SQL]Invalid value stored in pcbValue. (10001)
> <<
>
> Here's something else:
> I ran a similar (but simpler) statement using VB6 and just putting the
> information in the SQL statement (ie. no parameters) but using the same
> driver. For the SQLGetConnectAttrW, it looked like this:
>
> >>
> VB6 1ac-175 ENTER SQLGetConnectAttrW
> SQLHDBC 03BE1328
> SQLINTEGER 108 <SQL_ATTR_TXN_ISOLATION>
> SQLPOINTER 0x0012F33C
> SQLINTEGER 4
> SQLINTEGER * 0x00000000
>
> VB6 1ac-175 EXIT SQLGetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 03BE1328
> SQLINTEGER 108 <SQL_ATTR_TXN_ISOLATION>
> SQLPOINTER 0x0012F33C
> SQLINTEGER 4
> SQLINTEGER * 0x00000000
> >>
>
> (The first SQLINTEGER is quite different from the used by ADO.NET, as is the
> last SQLINTEGER* which is where the pcbValue is stored).
>
> And the SQLExecDirectW:
>
> >>VB6 1ac-175 ENTER SQLExecDirectW
> HSTMT 03BE1568
> WCHAR * 0x039C7BB0 [ 53] "UPDATE pomast SET vndnr='xxxxx'
> WHERE ordno='P12345'"
> SDWORD 53
>
> VB6 1ac-175 EXIT SQLExecDirectW with return code 0
> (SQL_SUCCESS)
> HSTMT 03BE1568
> WCHAR * 0x039C7BB0 [ 53] "UPDATE pomast SET vndnr='xxxxx'
> WHERE ordno='P12345'"
> SDWORD 53
> <<
>
> I'm not 100% sure what all of this means, and I'm certainly not sure how to
> fix it, but any help would be appreciated.
>
> Thanks,
> Cliffe Hodgkinson
> Nichirin Incorporated

Cliffe Hodgkinson

7/29/2002 9:03:00 PM

0

I was able to remedy it by using a different constructor for the parameters:

New OdbcParameter("parm1", OdbcType.Text, 20, ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "column1", DataRowVersion.Current,
Nothing)

for text, and

New OdbcParameter("parm2", OdbcType.Int, 0, ParameterDirection.Input, True,
CType(7, Byte), CType(0, Byte), "column2", DataRowVersion.Current, Nothing)

for numeric.

Hope this helps.
Cliffe Hodgkinson


Ben <beng@aisstl.com> wrote in message
news:e9e5e517.0207181226.62fb7792@posting.google.com...
> Has anyone figured out what is going on with this error? I am
> interested because I am getting [DB2/400 SQL]Invalid value stored in
> pcbValue when I attempt to execute a stored procedure that returns a
> cursor. I am attempting to put it into an odbcdatareader object. I
> am able to do this in VB6. Anybody have any ideas? Sample code is
> below.
>
> dim oCmd as new OdbcCommand
> dim dr as odbcdatareader
> 'oconn is a connection object that is open
>
> oCmd = New OdbcCommand("{call SPNAME(?,?,?,?)}", oConn)
> oCmd.CommandType() = CommandType.StoredProcedure
>
> pP1 = oCmd.Parameters.Add("PARM1", OdbcType.Char, 21)
> pP2 = oCmd.Parameters.Add("PARM2", OdbcType.Char, 1024)
> pP2 = oCmd.Parameters.Add("PARM3", OdbcType.Char, 2)
> pP3 = oCmd.Parameters.Add("PARM4", OdbcType.Char, 3)
> oCmd.Parameters(0).Direction = ParameterDirection.InputOutput
> oCmd.Parameters(1).Direction = ParameterDirection.InputOutput
> oCmd.Parameters(2).Direction = ParameterDirection.InputOutput
> oCmd.Parameters(3).Direction = ParameterDirection.InputOutput
> oCmd.Prepare()
> pP1.Value = "criteria"
> pP1.Value = "criteria"
> pP1.Value = "criteria"
> pP1.Value = "criteria"
>
> drReturn = oCmd.ExecuteReader
>
> "Cliffe Hodgkinson" <cliffeh51@hotmail.com> wrote in message
news:<#DcgJ$NKCHA.1744@tkmsftngp13>...
> > OK, I ran a trace. The first sign of a problem is in this section:
> >
> > >>
> > POForm 220-21f ENTER SQLGetConnectAttrW
> > SQLHDBC 04402220
> > SQLINTEGER 1209 <unknown>
> > SQLPOINTER 0x001A5A70
> > SQLINTEGER 1024
> > SQLINTEGER * 0x0012ECB8
> >
> > POForm 220-21f EXIT SQLGetConnectAttrW with return code -1
> > (SQL_ERROR)
> > SQLHDBC 04402220
> > SQLINTEGER 1209 <unknown>
> > SQLPOINTER 0x001A5A70
> > SQLINTEGER 1024
> > SQLINTEGER * 0x0012ECB8
> >
> > DIAG [HY092] [IBM][Client Access Express ODBC Driver (32-bit)]Option
type
> > out of range. (0)
> > <<
> >
> > I understand that it's that last SQLINTEGER * that stores the pcbValue.
> > Anyway, then there's a couple of SQLBindParameters that return
SQL_SUCCESS,
> > and then comes my error:
> >
> > >>
> > POForm 220-21f ENTER SQLExecDirectW
> > HSTMT 04402350
> > WCHAR * 0x0100DEF4 [ -3] "UPDATE pomast SET
> >
> >
CONNM=?,CONTL=?,BUYNO=?,FAXN=?,EADR=?,VNDNR=?,FOBDS=?,VIACD=?,VIADS=?,TRMDS=
> > ?,CURID=?,CURDS=?,ACTDT=?,MDATE=? WHERE ordno=?\ 0"
> > SDWORD -3
> >
> > POForm 220-21f EXIT SQLExecDirectW with return code -1
> > (SQL_ERROR)
> > HSTMT 04402350
> > WCHAR * 0x0100DEF4 [ -3] "UPDATE pomast SET
> >
> >
CONNM=?,CONTL=?,BUYNO=?,FAXN=?,EADR=?,VNDNR=?,FOBDS=?,VIACD=?,VIADS=?,TRMDS=
> > ?,CURID=?,CURDS=?,ACTDT=?,MDATE=? WHERE ordno=?\ 0"
> >
> > SDWORD -3
> >
> > DIAG [HY090] [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400
> > SQL]Invalid value stored in pcbValue. (10001)
> > <<
> >
> > Here's something else:
> > I ran a similar (but simpler) statement using VB6 and just putting the
> > information in the SQL statement (ie. no parameters) but using the same
> > driver. For the SQLGetConnectAttrW, it looked like this:
> >
> > >>
> > VB6 1ac-175 ENTER SQLGetConnectAttrW
> > SQLHDBC 03BE1328
> > SQLINTEGER 108 <SQL_ATTR_TXN_ISOLATION>
> > SQLPOINTER 0x0012F33C
> > SQLINTEGER 4
> > SQLINTEGER * 0x00000000
> >
> > VB6 1ac-175 EXIT SQLGetConnectAttrW with return code 0
> > (SQL_SUCCESS)
> > SQLHDBC 03BE1328
> > SQLINTEGER 108 <SQL_ATTR_TXN_ISOLATION>
> > SQLPOINTER 0x0012F33C
> > SQLINTEGER 4
> > SQLINTEGER * 0x00000000
> > >>
> >
> > (The first SQLINTEGER is quite different from the used by ADO.NET, as is
the
> > last SQLINTEGER* which is where the pcbValue is stored).
> >
> > And the SQLExecDirectW:
> >
> > >>VB6 1ac-175 ENTER SQLExecDirectW
> > HSTMT 03BE1568
> > WCHAR * 0x039C7BB0 [ 53] "UPDATE pomast SET
vndnr='xxxxx'
> > WHERE ordno='P12345'"
> > SDWORD 53
> >
> > VB6 1ac-175 EXIT SQLExecDirectW with return code 0
> > (SQL_SUCCESS)
> > HSTMT 03BE1568
> > WCHAR * 0x039C7BB0 [ 53] "UPDATE pomast SET
vndnr='xxxxx'
> > WHERE ordno='P12345'"
> > SDWORD 53
> > <<
> >
> > I'm not 100% sure what all of this means, and I'm certainly not sure how
to
> > fix it, but any help would be appreciated.
> >
> > Thanks,
> > Cliffe Hodgkinson
> > Nichirin Incorporated