Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 959
  • Last Modified:

Writing to AS400 compatability problem

I am trying to insert a record to a table in AS400 from VB 6.0 program. I am reading the data from a table on SQL server. It works fine with the following code

      strAS400 = ReadINI(gtINIFile, AS400, "ConnectString", "")
      cnAS400.Open strAS400
   
      Do While Not rsShip.EOF
         With rsShip.Fields
            strSQL = "INSERT INTO FUTBARPRNT.RSPBPHST " & _
                          "(COMPNY, SERIAL#, CABBV, CPART, PARTNO, DABBV, LBLFMT, PCNAME, PRNTER, BARPT#) " & _
                   "VALUES " & _
                   "('" & .Item("fdCompany").Value & "', '" & _
                          .Item("fdSerialNumber").Value & "', '" & _
                          .Item("fdCustDestination").Value & "', '" & _
                          .Item("fdCustPartNumber").Value & "', '" & _
                          .Item("fdShortNumber").Value & "', '" & _
                          .Item("fdDestination").Value & "', '" & _
                          .Item("fdLabelFormat").Value & "', '" & _
                          .Item("fdIPAddress").Value & "', '" & _
                          .Item("fdPrinter").Value & "', '" & _
                          .Item("fdBarPartNumber").Value & "')”

But when I add another field  called QTY it gives the following error.
Error Number: -2147467259
Error Description: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0408 - VALUE FOR COLUMN OR VARIABLE QTY NOT COMPATIBLE.

Here is the code after adding the QTY field.


      strAS400 = ReadINI(gtINIFile, AS400, "ConnectString", "")
            cnAS400.Open strAS400
   
                Do While Not rsShip.EOF
                     With rsShip.Fields

                      strSQL = "INSERT INTO FUTBARPRNT.RSPBPHST " & _
                                    "(COMPNY, SERIAL#, CABBV, CPART, PARTNO, DABBV, LBLFMT, PCNAME, PRNTER, BARPT#, QTY " & _
                       "VALUES " & _
                       "('" & .Item("fdCompany").Value & "', '" & _
                          .Item("fdSerialNumber").Value & "', '" & _
                          .Item("fdCustDestination").Value & "', '" & _
                          .Item("fdCustPartNumber").Value & "', '" & _
                          .Item("fdShortNumber").Value & "', '" & _
                          .Item("fdDestination").Value & "', '" & _
                          .Item("fdLabelFormat").Value & "', '" & _
                          .Item("fdIPAddress").Value & "', '" & _
                          .Item("fdPrinter").Value & "', '" & _
                          .Item("fdBarPartNumber").Value & "', '" & _
                          .Item("fdQuantity").Value & "')"

The field fdQuantity on the SQL Server is numeric(5). On the AS400 side the field QTY is decimal(8). Does anybody know how to fix this error? One thing I thought about is using the CAST function. But I don’t know the syntax of the CAST function on this particular program.

0
shamif
Asked:
shamif
  • 2
1 Solution
 
leonstrykerCommented:
Since this is not a char field in AS400 you do not need to put it in single quotes.  Try:

                      .Item("fdBarPartNumber").Value & "', " & _
                      .Item("fdQuantity").Value & ")"

Leon
0
 
shamifAuthor Commented:
thanks leonstryker,

It worked. I feel so stupid.
0
 
leonstrykerCommented:
No problem.  We all been there.

Leon
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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