Okay I’m using VB.net and added a SQL Server connection (.mdf file), and a table. I have CHANGED the input type from int to varchar (and others) to deal with split postal codes (ie “101 00”, instead of “339022”).
Anyways, now my table will not accept a string variable in the insert query because it’s telling me the SQL Server table wants an integer. I’ve updated that information and refreshed it. Not quite sure what I need to change or do for this to click. I’ve tried different variables, closing, reopening etc.
I tried rebuilding my query with no luck. This is the error I’m getting after turning option strict on. This exception was originally thrown at this call stack:
System.Number.StringToNumber(string, System.Globalization.NumberStyles, ref System.Number.NumberBuffer, System.Globalization.NumberFormatInfo, bool) System.Number.ParseInt32(string, System.Globalization.NumberStyles, System.Globalization.NumberFormatInfo) string.System.IConvertible.ToInt32(System.IFormatProvider) System.Data.Common.Int32Storage.Set(int, object) System.Data.DataColumn.this[int].set(int, object)
Advertisement
Answer
You have a database, and it has a table with some number column:
TblAddress -------- Street Varchar PostalCode int
And you wrote (or visual studio wrote) some code to put data into the column. Now it’s not exactly clear what method you’re using to access this database but it doesn’t matter- the concept will remain the same. I’m going to guess that it’s either straight insert queries, ie you wrote something like this:
Dim cmd As New SqlCommand("INSERT INTO tblAddress VALUES(@street, @postalcode", "some connection string here")
cmd.Parameters.Add("@street", SqlDbType.Varchar).Value = "Microsoft way"
cmd.Parameters.Add("@postalcode", SqlDbType.Integer).Value = 339022
And so on. Or maybe you’re using a DataSet (you have a file called Something.XSD in your solution explorer) and when you double click it you see something that looks like a db. It is important to remember that a DataSet is a client side container that has datatables for storing data and tableadapters for pushing data between the dataset and true db – it can be created with reference to the db bit it is not the db and it can diverge – a datatable can have more or fewer columns than the table in the db to which it relates etc. So your will have been set up as an int column
Or maybe you’re using entity framework, which like the dataset is a suite of client side code that can be modelled on the db but it can diverge
The crucial point here is that if you originally had an int column on db table and you created client side code targeting an int, if you change the db type later there is nothing that automagicallly updates the client side code. If you’re using an insert directly you have to go and change the SqlDbType of the parameter. If you’re using a DataSet you have to open the dataset, find the column and update its type. If you’re using EF you find your client side class entity and update the type of the property
TLDR: even if you change the db type there is still another change you need to make in your code
A major thing potentially tripping you up here is vb.net option strict being off. VB language variants default settings have long been targeted towards hobbyist programmers who don’t necessarily know or care the difference between a number and a string that contains numerical characters. It allows things like:
Dim i as Integer = "123"
“123” is a String, not an Integer, there is a conversion possibility but only if the string doesn’t have any crap in it. By default if you do something like this VB will do the conversion for you implicitly and this isn’t really a great idea. By not forcing the developer to think about the conversion at design time it means they also haven’t thought about errors that will happen during the conversion nor written in any handling for them.
Click your project properties and in the compiler options turn On every option that has an On. You might well see tens of errors suddenly appear but that answer to that is not to turn the option off again, but to properly fix the error
It won’t catch everything, for example a DbParameter .Value is an Object, which will still accept your postalcode string with a space in, and fail internally if you declared the parameter type as some numeric, but being mindful of your datatypes at all times and not treating them as synonymous will resolve a lot of headaches