Select Statement in ASP.NET (VB) using Parameters - Error: Input string was not in a correct format

Question

So I have some code in a VB module. When I run this with the SQL statement having hard coded values in the where statement it works. I am now trying to add Parameters to the module so that the Form can except an Input from a user, but I am getting the message: Input string was not in a correct format.

Here is the section of code:

    objCon.Open()
    objCmd.Connection = objCon
    objCmd.CommandType = CommandType.Text
    objCmd.CommandText = sSQL

    objDataAdapter = New iDB2DataAdapter(objCmd)
    objDataSet = New DataSet
    objDataAdapter.Fill(objDataSet)

    sSQL = ""
    sSQL += "SELECT "
    sSQL += "    DIGITS(DDS#) AS STORE, "
    sSQL += "    DIGITS(DSKU) || DIGITS(DCHK) SKU, "
    sSQL += "    DORQ AS ORIGQTY, "
    sSQL += "    DQTY AS DISTQTY, "
    sSQL += "    DAKQ AS ACKQTY, "
    sSQL += "    CHAR(DDTI,ISO) AS DISTRIBUTIONDATE, "
    sSQL += "    DDC# AS DISTNO, "
    sSQL += "    DPIC AS PICKSHEET, "
    sSQL += "    DALC AS ALLOCNO, "
    sSQL += "    DSTS AS STATUS, "
    sSQL += "    CHAR(DPKI,ISO) AS PICKDATE, "
    sSQL += "    CHAR(DMNI,ISO) AS MANIFESTDATE, "
    sSQL += "    CHAR(DAKI,ISO) AS ACKDATE, "
    sSQL += "    CHAR(DASI,ISO) AS SHIPMENTDATE "
    sSQL += "FROM "
    sSQL += "    IPTSFIL.IPPNDST "
    sSQL += "WHERE "
    sSQL += "    DDS# = @STORENO AND "
    sSQL += "    DSKU = @SKU AND "
    sSQL += "    DCHK = @CHK "
    sSQL += "ORDER BY "
    sSQL += "    DISTRIBUTIONDATE DESC "

    objCmd.CommandType = CommandType.Text
    objCmd.CommandText = sSQL

    objCmd.Parameters.Add("@STORENO", iDB2DbType.iDB2Char, 5, "DDS#")
    objCmd.Parameters("@STORENO").Value = sStoreNo

    objCmd.Parameters.Add("@SKU", iDB2DbType.iDB2Char, 9, "DSKU")
    objCmd.Parameters("@SKU").Value = Mid(sSKU, 1, Len(sSKU) - 1)

    objCmd.Parameters.Add("@CHK", iDB2DbType.iDB2Char, 1, "DCHK")
    objCmd.Parameters("@CHK").Value = Right(sSKU, 1)

    objDataAdapter = New iDB2DataAdapter(objCmd)
    objDataSet = New DataSet
    objDataAdapter.Fill(objDataSet)

Like I said, if I subsitute the Parameters for fixed values it all works.

Community · Accepted Answer · 2017-05-23 12:07:14Z

2

What database are you connecting to? Some databases don't support named parameters.

See AS400 SQL query with Parameter for alternate syntax.

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Apr 7, 2010 at 12:59

bryanjonker

3,4543 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Iskevosi Over a year ago

Connecting to a DB2 Database on an iSeries.

bryanjonker Over a year ago

Yeah, when I was connecting to DB2, you had to use "?" and rely on ordinal position. Change the where clause to: sSQL += "WHERE DDS# = ? AND DSKU = ? AND DCHK = ? " publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/…

Collectives™ on Stack Overflow

Select Statement in ASP.NET (VB) using Parameters - Error: Input string was not in a correct format

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related