IvanHowarth
asked on
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?
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?
Ashish Patel
can you paste your update statement which is being fired?
ASKER
Me.BindingContext(Me.MyDat
Debug.WriteLine("DataType.
IsConnecting = True
While IsConnecting
Try
'Connect to DB
Me.MyDataAdapter.Update(Me
Catch ex As Exception
Conn1.Close()
Debug.WriteLine(ex.ToStrin
Exit Function
Finally
If Me.Conn1.State = ConnectionState.Open Then
Conn1.Close()
End If
IsConnecting = False
End Try
End While
Debug.WriteLine("DataType.
IsConnecting = True
While IsConnecting
Try
'Connect to DB
Me.MyDataAdapter.Update(Me
Catch ex As Exception
Conn1.Close()
Debug.WriteLine(ex.ToStrin
Exit Function
Finally
If Me.Conn1.State = ConnectionState.Open Then
Conn1.Close()
End If
IsConnecting = False
End Try
End While
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.
ASKER
The full Exception error message:
System.OverflowException: Value was either too large or too small for an Int16.
at System.Data.Common.DbDataA
at System.Data.Common.DbDataA
System.OverflowException: Value was either too large or too small for an Int16.
at System.Data.Common.DbDataA
at System.Data.Common.DbDataA
ASKER
MyDataAdapter.SelectComman
SELECT PK, MyField, MyField2 FROM dbo.MySQLTable WHERE (MyField2 = @MyField2)
SELECT PK, MyField, MyField2 FROM dbo.MySQLTable WHERE (MyField2 = @MyField2)
ASKER
My DataTable XML Code:
<xs:element name="MaintContracts">
<xs:complexType>
<xs:sequence>
<xs:element name="PK" msdata:ReadOnly="true" msdata:AutoIncrement="true
<xs:element name="MyField" type="xs:int" minOccurs="0" />
<xs:element name="MyField2" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MaintContracts">
<xs:complexType>
<xs:sequence>
<xs:element name="PK" msdata:ReadOnly="true" msdata:AutoIncrement="true
<xs:element name="MyField" type="xs:int" minOccurs="0" />
<xs:element name="MyField2" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!