Luka Bulatovic Luka Bulatovic - 2 years ago 175
SQL Question

The conversion of the nvarchar value overflowed an int column VB.Net

I am writing an application to work with my DB (on Microsoft SQL Server 2014). I have this code:

Private Sub Ucitaj()
Dim command As New SqlCommand
command.Connection = Me.conn
command.CommandText = "select * from zaposleni where jmbg=" & Me.jmbg
Dim reader As SqlDataReader

reader = command.ExecuteReader

Me.TextBox2.Text = reader("ime")
Me.TextBox3.Text = reader("prezime")
Me.TextBox4.Text = reader("br_rac")
Me.TextBox5.Text = reader("tel")
Me.TextBox6.Text = reader("datum_zap")

Catch ex As Exception

End Try
End Sub

At line: reader.Read(), it throws the following error:
{"The conversion of the nvarchar value '1008994254963' overflowed an int column."}

By the way, '1008995254963' is the value in column 'jmbg' of my first entry in the table. So I suppose, when it does comparison, it tries to convert this into int. However, my datatype in database is nvarchar(20), so why does this happen? Shouldn't it treat it as a string? Why does it try to convert it to int, when I can basically write words(not only digits) in this column and when the datatype is not an int? Thanks in advance

Answer Source

The issue there is that you are using a literal value in your SQL code and not wrapping it in single quotes. Literal text MUST be wrapped in single quotes so your value is interpreted as a number rather than text. That means that, in order to compare, the value in the column is also converted to a number and it is too big for an int, hence the overflow.

That may make it sound like the solution is to add some single quotes but that is a dirty solution. The proper solution is to ALWAYS use parameters to insert values into SQL code. Doing so means that issues like this and many others don't arise.

command.CommandText = "select * from zaposleni where jmbg = @jmbg"
command.Parameters.Add("@jmbg", SqlDbType.NVarChar, 20).Value = Me.jmbg
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download