• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1075
  • Last Modified:

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?
  • 6
  • 2
1 Solution
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.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now