• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 977
  • Last Modified:

Microsoft OLE DB Provider for ODBC Drivers Input string Probems

I am having a heck of a time with an ODBC connection to an AS400.  I am getting the following error message:

Input string was not in a correct format.
   at System.Number.ParseDecimal(String s, NumberStyles style, NumberFormatInfo info)
   at System.Decimal.Parse(String s, IFormatProvider provider)
   at System.Data.Odbc.OdbcDataReader.internalGetDecimal(Int32 i)
   at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)
   at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
   at System.Data.Odbc.OdbcDataReader.GetValues(Object[] values)
   at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues, Boolean acceptChanges)
   at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at Zone30CycleCount.CycleCount.Button1_Click(Object sender, EventArgs e)


I wrote this program that pulls data from a table on the AS 400.  The connection string is as follows:

SORTTABLE=;UID=*****;PWD=*****;DBQ=pk992bpdta vg992mpdta;DSN=PKMS;PKG={QGPL/DEFAULT(IBM),2,0,1,0,512};SIGNON=1;DFTPKGLIB=QGPL;LANGUAGEID=ENU;QAQQINILIB=;SYSTEM=*****;CONNTYPE=2;DESC=Client Access Express ODBC data source;CMT=0;XLATEDLL=;

Now I can run this call 50,000 times in a row on the computer I wrote it on.  This computer runs on Windows 2000 which has access 97, access 2000, Client Access R5, MDAC 2.8, Framework 1.1.  Basically it is top of the line for my company.  I have to problems running the program multiple times in a row any time of the day.  The computers that I am having problems on range from 2000 to NT 4 sp6.  They can do many calls in a row early in the morning when there isnt much production in the warehouse, but in the evening you can forget it.  You have to close the program and reopen it up to run a select query.  Once the program it restarted, it will run the query, but not more than once at a time. I have upgraded everything on the computers from the framwork to Ibm client access R4 upgraded to R5, and MDAC 2.8.  Does anyone know what is causing this?
0
dressman
Asked:
dressman
  • 5
  • 4
1 Solution
 
bman9111Commented:
make sure the datasource (dsn) is on the pc. Look at administrative tools and then datasources see if u see PKMS. If u do then the client has to have the same too.

I connect and write programs to the as400 all the time. I use to use the odbc, but now with client access 5.3, it has a nice .net framework connections. Check it out sometime. Good to see IBM give us .net users tools....
0
 
BadgerBillCommented:
I think the problem may lie in the data. You say you can run the call lots of times on your pc - is this against test dara? If possible pull down some live data and see what happens. Nulls look fave if this is the case.
0
 
dressmanAuthor Commented:
The data I am using is read-only live data.  Before I installed 5 i could not get the odbc data call to work at all.  I had heard somewhere that if you update 4 to 5 then it will cause problems.  I was told to totally remove 4 then load 5.  Could it be that some remnants of 4 still remain and are causing problems?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
bman9111Commented:
did u check ur datasource, u have to physically create a connection dsn to connect to the as400, on another pc....
0
 
dressmanAuthor Commented:
ya, it is there if it was not, then I doubt I would be able to connect at all.  It does not explain why I can connect many times in a row in the morning, but later in the day, it connects once and the connection is blown.

0
 
bman9111Commented:
do u have any type of maintenance software on the as400 that is running. when u say blown, u mean this error

Input string was not in a correct format.
   at System.Number.ParseDecimal(String s, NumberStyles style, NumberFormatInfo info)
   at System.Decimal.Parse(String s, IFormatProvider provider)
   at System.Data.Odbc.OdbcDataReader.internalGetDecimal(Int32 i)
   at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)
   at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
   at System.Data.Odbc.OdbcDataReader.GetValues(Object[] values)
   at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues, Boolean acceptChanges)
   at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at Zone30CycleCount.CycleCount.Button1_Click(Object sender, EventArgs e)


now was this error provided to u by the try catch clause??

Also the program that u are using is it used a lot during the day???? or is it closed then opened, etc..
I don't beleive it is from upgrade to 5. I have upgrade from 3 to 5.3  with no problems. Have u tried to see if any service packs were available?

Also the error u are getting does not appear to be from the as400, usually if it was from the as400 it would say something like invalid token...
0
 
dressmanAuthor Commented:
This is what the problem is to a T, next thing is trying to resolve it by figuring out what the difference is between my computer which is running the same version of R5

Problem: Attempting to call the Fill method a second time on a
parameterized query fails with the error
"microsoft.data.odbc.dll System Error".  The problem can be
reproduced using the Visual Studio .NET "Walkthrough:
Displaying Data in a Windows Form Using a Parameterized Query".
This is a problem or limitation of the Microsoft.Data.Odbc .NET
provider.  An ODBC trace shows that the Microsoft ODBC
provider fails to bind the parameter on the second fill.
The provider reconnects and then issues an SQLExecDirect
without first calling SQLBindParameter.  iSeries Access for
Windows ODBC driver returns the error:
DIAG [07002] [IBM][Client Access Express ODBC
  Driver (32-bit)]Wrong number of parameters. (30007)

While these managed providers are useful to help customers that
desire to use .NET in the short term, they are not a complete
solution.  For one thing, they are always slower than a native
managed provider, due to managed to unmanaged transitions
and the COMInterop bridge.  IBM is investigating
the creation of a native managed provider optimized
for iSeries.
0
 
dressmanAuthor Commented:
the error was provided from a try catch clause
0
 
bman9111Commented:
show me the code that is failing.....
0
 
dressmanAuthor Commented:
       'notcyclecounted
        '
        Me.notcyclecounted.SelectCommand = Me.OdbcSelectCommand1
        Me.notcyclecounted.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "CCPROD00", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("CCPRDIV", "CCPRDIV"), New System.Data.Common.DataColumnMapping("CCPRDEPT", "CCPRDEPT"), New System.Data.Common.DataColumnMapping("CCPRSTYL", "CCPRSTYL"), New System.Data.Common.DataColumnMapping("CCPRCHKD", "CCPRCHKD"), New System.Data.Common.DataColumnMapping("CCPRCOLR", "CCPRCOLR"), New System.Data.Common.DataColumnMapping("CCPRSIZE", "CCPRSIZE"), New System.Data.Common.DataColumnMapping("CCPRDIMS", "CCPRDIMS"), New System.Data.Common.DataColumnMapping("CCPRZONE", "CCPRZONE"), New System.Data.Common.DataColumnMapping("CCPRAISL", "CCPRAISL"), New System.Data.Common.DataColumnMapping("CCPRBAY", "CCPRBAY"), New System.Data.Common.DataColumnMapping("CCPRLEVL", "CCPRLEVL"), New System.Data.Common.DataColumnMapping("CCPRDCR", "CCPRDCR"), New System.Data.Common.DataColumnMapping("CCPRTCR", "CCPRTCR"), New System.Data.Common.DataColumnMapping("CCPRUSER", "CCPRUSER")})})
        '
        'OdbcSelectCommand1
        '
        Me.OdbcSelectCommand1.CommandText = "SELECT VG992MPDTA.CCPROD00.CCPRDIV, VG992MPDTA.CCPROD00.CCPRDEPT, VG992MPDTA.CCPR" & _
        "OD00.CCPRSTYL, VG992MPDTA.CCPROD00.CCPRCHKD, VG992MPDTA.CCPROD00.CCPRCOLR, VG992" & _
        "MPDTA.CCPROD00.CCPRSIZE, VG992MPDTA.CCPROD00.CCPRDIMS, VG992MPDTA.CCPROD00.CCPRZ" & _
        "ONE, VG992MPDTA.CCPROD00.CCPRAISL, VG992MPDTA.CCPROD00.CCPRBAY, VG992MPDTA.CCPRO" & _
        "D00.CCPRLEVL, VG992MPDTA.CCPROD00.CCPRDCR, VG992MPDTA.CCPROD00.CCPRTCR, VG992MPD" & _
        "TA.CCPROD00.CCPRUSER FROM { oj PK992BPDTA.ACLOCN00 RIGHT OUTER JOIN VG992MPDTA.C" & _
        "CPROD00 ON PK992BPDTA.ACLOCN00.ACZONE = VG992MPDTA.CCPROD00.CCPRZONE AND PK992BP" & _
        "DTA.ACLOCN00.ACAISL = VG992MPDTA.CCPROD00.CCPRAISL AND PK992BPDTA.ACLOCN00.ACBAY" & _
        " = VG992MPDTA.CCPROD00.CCPRBAY AND PK992BPDTA.ACLOCN00.ACLEVL = VG992MPDTA.CCPRO" & _
        "D00.CCPRLEVL AND PK992BPDTA.ACLOCN00.ACSEA = VG992MPDTA.CCPROD00.CCPRDIV AND PK9" & _
        "92BPDTA.ACLOCN00.ACSYR = VG992MPDTA.CCPROD00.CCPRDEPT AND PK992BPDTA.ACLOCN00.AC" & _
        "STYL = VG992MPDTA.CCPROD00.CCPRSTYL AND PK992BPDTA.ACLOCN00.ACSSFX = VG992MPDTA." & _
        "CCPROD00.CCPRCHKD AND PK992BPDTA.ACLOCN00.ACCOLR = VG992MPDTA.CCPROD00.CCPRCOLR " & _
        "AND PK992BPDTA.ACLOCN00.ACCSFX = VG992MPDTA.CCPROD00.CCPRSIZE AND PK992BPDTA.ACL" & _
        "OCN00.ACSDIM = VG992MPDTA.CCPROD00.CCPRDIMS AND PK992BPDTA.ACLOCN00.ACCYDT = VG9" & _
        "92MPDTA.CCPROD00.CCPRDCR } WHERE (PK992BPDTA.ACLOCN00.ACZONE IS NULL) AND (PK992" & _
        "BPDTA.ACLOCN00.ACAISL IS NULL) AND (PK992BPDTA.ACLOCN00.ACBAY IS NULL) AND (PK99" & _
        "2BPDTA.ACLOCN00.ACLEVL IS NULL) AND (PK992BPDTA.ACLOCN00.ACSEA IS NULL) AND (PK9" & _
        "92BPDTA.ACLOCN00.ACCYDT IS NULL) AND (PK992BPDTA.ACLOCN00.ACSDIM IS NULL) AND (P" & _
        "K992BPDTA.ACLOCN00.ACCOLR IS NULL) AND (PK992BPDTA.ACLOCN00.ACCSFX IS NULL) AND " & _
        "(PK992BPDTA.ACLOCN00.ACSSFX IS NULL) AND (PK992BPDTA.ACLOCN00.ACSTYL IS NULL) AN" & _
        "D (PK992BPDTA.ACLOCN00.ACSYR IS NULL) ORDER BY VG992MPDTA.CCPROD00.CCPRZONE, VG9" & _
        "92MPDTA.CCPROD00.CCPRDIV"
        Me.OdbcSelectCommand1.CommandTimeout = 0
        Me.OdbcSelectCommand1.Connection = Me.OdbcConn

      'Dsnotcyclecounted1
        '
        Me.Dsnotcyclecounted1.DataSetName = "dsnotcyclecounted"
        Me.Dsnotcyclecounted1.Locale = New System.Globalization.CultureInfo("en-US")
        '



 Dim i As Int32
        Dim rdr As OdbcDataReader
        Try
            Me.OdbcConn.Open()
            Me.Label8.Text = CStr(Me.notcyclecounted.Fill(Dsnotcyclecounted1))
            Dsnotcyclecounted1.Dispose()

            rdr = Me.notcyclecounted.SelectCommand.ExecuteReader

            Me.ListBox3.Items.Add("SKU                                        LOCATION        DATE              TIME          USER")
            Me.ListBox3.Items.Add("------------------------------------------------------------------------------------------------------------------------------------")

            While rdr.Read
                Me.ListBox3.Items.Add(Trim(CStr(rdr(0))) & "-" & Trim(CStr(rdr(1))) & "-" & Trim(CStr(rdr(2))) & "-" & Trim(CStr(rdr(3))) & "-" & Trim(CStr(rdr(4))) & "-" & Trim(CStr(rdr(5))) & "-" & Trim(CStr(rdr(6))) & "       " & Trim(CStr(rdr(7))) & "-" & Trim(CStr(rdr(8))) & "-" & Trim(CStr(rdr(9))) & "-" & Trim(CStr(rdr(10))) & "       " & Trim(CStr(rdr(11))) & "       " & Trim(CStr(rdr(12))) & "       " & Trim(CStr(rdr(13))))
                Me.ListBox3.Refresh()
            End While

            rdr.Close()
            Me.OdbcConn.Close()
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now