Why Int DataType field is holding the string value without any error

[ADQUSIT]

Hi. I have a Table in SQL Server 2005 Standard, in which StID is a Primary Key, set to int DataType. When I insert the data from Front End using VB.Net, so If I insert the String Value deliberately and not the Integer. So.

My question is that why it doesn't give any error, for sending string to DataType Int? Please guide me?

[jggtz]

There's an internal data type conversion from String to Integer
Try inserting the string value but with letters or special characters and see the result

[FunkyDexter]

so i insert the String Value

Not really. I think you're saying you build up sql as a string so therefore it's a string you're inserting right? Something like this:-

sql = "Insert Into MyTable (stID) Values (" & anInteger.ToString & ")"

which looks like you're passing a string in. But what that will produce is something like:-

Insert into MyTable (stID) Values (10)

you'll notice that the 10 doesn't have quotes in it which means that, in SQL world, it's a number. If you passed in a string you'd have:-

sql = "Insert Into MyTable (stID) Values ('" & anInteger.ToString & "')"

which would produce

Insert into MyTable (stID) Values ('10')

Even then, as jggtz said, SQL would simply convert it as long as that's possible.

[ADQUSIT]

@ Jggtz and FunkyDexter
I updated my Question, Post # 1.


I didn't use any such converting in my code. So does it mean that SQL Server does this automatically?

[FunkyDexter]

So does it mean that SQL Server does this automatically?

It depends on how you're sending the value. If you build up a sql string and don't wrap it in quotes then, as far as SQL Server is concerned it's already an integer. If you do wrap it in quotes then sql server converts it. IF you use an ADO parameter then VB.Net is automatically converting it (assuming you have option strict off). I can't think of any other scenario to cover. Post some example code and I might be able to give a better explanation.