Nvarchar casting as integer error... when no integer column used

Question

I have a piece of code that worked when my customer numbers were all numeric. So customer numbers have been added that are alpha now and I get a "The conversion of the nvarchar value ' 4000545398' overflowed an int column. Maximum integer value exceeded" error which I know is over the 2.1million etc... My issue lay in that number 4000545398 is a lot number in the very first record of table shipper_sii [ a nvarchar (10)].

Columns in my tables are as follows...

custaddr - name (nvarchar (60)), cust_num (nvarchar(7))
co - cust_num (nvarchar(7)), co_num (nvarchar(10))
shipper_sii - co_num (nvarchar(10)), lot (nvarchar(15))

my declarations are...

 Dim Carton1 As String
 Dim dbCustName As String
 Dim dbCustNum As String

Anyone have a fix for me? I am scratching my head here... I am pretty sure its in my join, but I am stumped...

' Get Customer Name

Using connObj2 As New SqlClient.SqlConnection("server=Server;database=App;uid=sa;pwd=password;")
  Using cmdObj2 As New SqlClient.SqlCommand("SELECT c2.name, c2.cust_num from custaddr as c2 INNER JOIN co as c1 ON (c2.cust_num = c1.cust_num) INNER JOIN shipper_sii as s1 on (c1.co_num = s1.co_num) WHERE lot = " & Carton1, connObj2)
    connObj2.Open()
    Using readerObj2 As SqlClient.SqlDataReader = cmdObj2.ExecuteReader
        'This will loop through all returned records 
        While readerObj2.Read

            dbCustNum = readerObj2("cust_num").ToString
            dbCustName = readerObj2("name").ToString
            'handle returned value before next loop here

        End While
    End Using
    connObj2.Close()
  End Using
End Using

My error pops at the "While readerObj2.Read".

What is the value of `Carton1` when you get here? Why don't you use sql-parameter? — Tim Schmelter, Jul 24 '14 at 14:25

score 1 · Accepted Answer · edited May 23 '17 at 12:05

You should use a parameterized query to avoid this kind of errors and Sql Injection problems

Dim cmdText = "SELECT c2.name, c2.cust_num from custaddr as c2 " & _
                "INNER JOIN co as c1 ON (c2.cust_num = c1.cust_num)  " & _
                "INNER JOIN shipper_sii as s1 on (c1.co_num = s1.co_num)  " & _
                "WHERE lot = @car"
Using cmdObj2 As New SqlClient.SqlCommand(cmdText, connObj2)
     cmdObj2.Parameters.AddWithValue("@car",  Carton1)
   .....

Here lot is a NVarChar field, so, in a string concatenation this requires the quotes around the Carton1 variable, but with a parameterized query you avoid that problem.

Nvarchar casting as integer error... when no integer column used

1 Answers1