Crashing on DataAdapter.Update() - Its using Int16 when it needs to be Int32

I have a SQL Database table with MyField datatype of smallint. Using VisualStudio 2003, I have created a dataAdapter and generated a dataset/datatable to Select/Update etc the above Sql Table. On my VB.Net Form, I have a TextBox control who's Text property is bound to MyDataView (which is bound to the datatable).

Originally, if I put a value in my TextBox of over 32767(Int16), then it would immediately revert back to it's original value. Realising this was because when generating the DataTable, VisualStudio2003 assigned this column the dataType Int16. Needing a higher value, I opened up the DataSet Schema and manually changed the Type of MyField from Short(Int16) to Int(Int32). This allowed higher values to remain (up to 2,147,483,647), but still throws an OverflowException when the DataAdapter.Update() is called. The error message returned is: "Value was either too large or too small for an Int16".

Problem: What have I missed out to change the DataType from Int16 to Int32 which the Update method calls? Or how can I force all SQL Fields with a smallint DataType to a VB Col DataType of Int32 and not Int16?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
can you paste your update statement which is being fired?
IvanHowarthAuthor Commented:
        Debug.WriteLine("DataType.FullName = " + Me.MyDataSet.MyDataTable.MyField.DataType.Name)
         IsConnecting = True
        While IsConnecting
                'Connect to DB

            Catch ex As Exception
              Exit Function
                If Me.Conn1.State = ConnectionState.Open Then
                End If
                IsConnecting = False
            End Try
        End While
Ashish PatelCommented:
thanks now, can you post the select statement (query) by which you are creatng the DataSet for the datatable. Also please paste the table structure.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

IvanHowarthAuthor Commented:
The full Exception error message:

System.OverflowException: Value was either too large or too small for an Int16.
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
IvanHowarthAuthor Commented:
MyDataAdapter.SelectCommand.CommandText =

SELECT PK, MyField, MyField2 FROM dbo.MySQLTable WHERE (MyField2 = @MyField2)
IvanHowarthAuthor Commented:
My DataTable XML Code:

<xs:element name="MaintContracts">
      <xs:element name="PK" msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:short" />
      <xs:element name="MyField" type="xs:int" minOccurs="0" />
      <xs:element name="MyField2" type="xs:string" minOccurs="0" />
IvanHowarthAuthor Commented:
Following on from the XML Code, both the PK and MyField have a SQL dataType of smallint. MyFiled has a type="xs:int" indicated from when I maunally set it. Up to the point of Update, DataTableCol("MyField") still has a dataType of Int32. But somewhere within the Update, an Int16 (or XS:short) is still being seen and thus throwing the Exception.
I'm missing something here.  SQL smallint and VB.NET Int16 are the equivalent datatypes: both range from -32,768 to -32,767 and take 2 bytes to store.  If your VB.NET app is linked to an SQL db then if you change the datatype in one but not the other they will no longer be compatible.  So, if you need Int32 at the VB end, you will also need the equivalent - int - at the SQL end.  Altering things in the dataset or any dataadapters or tableadapters or commands is not going to change that basic point.

Quite where, along the line, the error you are reporting is occurring I'm not absolutely sure.  My guess, from the detail of the error message is that your dataadapter has parameters set to smallint/Int16 which are using as the sourcecolumn for their parameter values column/s that you have changed to Int32, and that that is the error that is currently being reported.  But - even if that is correct, and you alter the code in your dataadapter - you will get an error from the SQL db end if you then try to pass a Int32 value to a smallint field.

Unless I have misunderstood what you're after, I think you are going to have change the datatype in the SQL db and then regenerate your VB.NET dataset.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IvanHowarthAuthor Commented:
Roger - Everything you said makes perfect sense! And now realise there is no problem at all - my mistake is a fundamental one, I've always thought SQL smallint and VB.NET Int32 were equivalent datatypes (2^15 - 1 = 2,147,483,647 (It doesn't)). As you've rightly pointed out, the highest number I could get from a smallint is 32,767 so the VB.NET conversion to Int16 was correct, and my attempts to change the datatype was simply going against the grain and bound to only cause problems.

I will just have to upgrade all my SQL smallint fields that require a higher value to int, then regenerate all my datasets accordingly as you suggest.

Thanks again Roger!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.