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
– Tim Schmelter, Commented Jul 24, 2014 at 14:25

Community · Accepted Answer · 2017-05-23 12:05:54Z

1

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.

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Jul 24, 2014 at 14:44

Steve

217k22 gold badges242 silver badges296 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related