InvalidCastException when doing reader.GetInt32(0)

D

dgleeson3

Hello All

I have VB code (.Net 2005) reading from an SQL server 2005 database.
Im getting InvalidCastException when doing reader.GetInt32(0)

Im simply reading an int from a simple database. It has two columns,
User and Tel number. User is filled with 1,2,3 and tel number has 3
telephone numbers.

The User data column is specified as (PK, int, not null) in MS SQL
Server management Studio.

My code works fine if I target the Northwind database, CategoryID from
the Categories Table. Its also specified as (PK, int, not null) in MS
SQL Server management Studio.

I thought it might be an access issue as Im working on Vista. SO I
copied everything across to a Windows 2000 machine. I got the same
error.

It must be something to do with my database if my code is OK with
Northwind. I just cant figure it.

Relevant Code below.

----------------------------------------------------------------------------------------------------

Public Sub New()
' Open database.

conDatabase = _
New SqlConnection("Data Source=LAP2;Database='Access1';" &
_
"Integrated Security=SSPI")

Try
' Try to open the database but catch errors if Server not
running or
' database not present.
conDatabase.Open()
Catch ex As Exception
' if we didnt sucessfuly open the database then put up a
msgbox.
If (conDatabase.State = ConnectionState.Closed) Then
' MessageBox.Show("DataBase NOT Open - Status of
DataBase Connection " + "ServerVersion: " +
conDatabase.ServerVersion _
' + ControlChars.Cr + "State: " +
conDatabase.State.ToString())
MessageBox.Show("DataBase Server NOT Running or
Database not present")

End If

End Try

End Sub

--------------------------------------------------------------------------------------------------------

Public Function Load_User_Details(ByVal ListView_X As ListView) As
Boolean

'1. set up query to get required data from database
' Assumes that conDatabase is a valid SqlConnection object.

Dim command As SqlCommand = New SqlCommand( _
"SELECT User, Telephone FROM Tel_Number", conDatabase)

'2. Set up something to hold data from database
Dim reader As SqlDataReader = command.ExecuteReader()

Do While reader.Read()
If reader.IsDBNull(0) Then

Console.Write("<NULL>")
Else
Try

Console.WriteLine("User Number " &
reader.GetInt32(0))

Catch ex As InvalidCastException
Console.Write("Invalid data type.")
End Try
End If
Console.WriteLine()
Loop


reader.Close()

' No problems
Return (True)

End Function



Many thanks for any input.

Regards

Denis
___________________
Denis Gleeson
http://www.CentronSolutions.com
 
?

=?ISO-8859-1?Q?G=F6ran_Andersson?=

Hello All

I have VB code (.Net 2005) reading from an SQL server 2005 database.
Im getting InvalidCastException when doing reader.GetInt32(0)

Im simply reading an int from a simple database. It has two columns,
User and Tel number. User is filled with 1,2,3 and tel number has 3
telephone numbers.

The User data column is specified as (PK, int, not null) in MS SQL
Server management Studio.

My code works fine if I target the Northwind database, CategoryID from
the Categories Table. Its also specified as (PK, int, not null) in MS
SQL Server management Studio.

I thought it might be an access issue as Im working on Vista. SO I
copied everything across to a Windows 2000 machine. I got the same
error.

It must be something to do with my database if my code is OK with
Northwind. I just cant figure it.

Relevant Code below.

----------------------------------------------------------------------------------------------------

Public Sub New()
' Open database.

conDatabase = _
New SqlConnection("Data Source=LAP2;Database='Access1';" &
_
"Integrated Security=SSPI")

Try
' Try to open the database but catch errors if Server not
running or
' database not present.
conDatabase.Open()
Catch ex As Exception
' if we didnt sucessfuly open the database then put up a
msgbox.
If (conDatabase.State = ConnectionState.Closed) Then
' MessageBox.Show("DataBase NOT Open - Status of
DataBase Connection " + "ServerVersion: " +
conDatabase.ServerVersion _
' + ControlChars.Cr + "State: " +
conDatabase.State.ToString())
MessageBox.Show("DataBase Server NOT Running or
Database not present")

End If

End Try

End Sub

--------------------------------------------------------------------------------------------------------

Public Function Load_User_Details(ByVal ListView_X As ListView) As
Boolean

'1. set up query to get required data from database
' Assumes that conDatabase is a valid SqlConnection object.

Dim command As SqlCommand = New SqlCommand( _
"SELECT User, Telephone FROM Tel_Number", conDatabase)

'2. Set up something to hold data from database
Dim reader As SqlDataReader = command.ExecuteReader()

Do While reader.Read()
If reader.IsDBNull(0) Then

Console.Write("<NULL>")
Else
Try

Console.WriteLine("User Number " &
reader.GetInt32(0))

Catch ex As InvalidCastException
Console.Write("Invalid data type.")
End Try
End If
Console.WriteLine()
Loop


reader.Close()

' No problems
Return (True)

End Function



Many thanks for any input.

Regards

Denis
___________________
Denis Gleeson
http://www.CentronSolutions.com
Click to expand...

It looks all right. Check what data type you are really getting in the
data reader by using reader.GetValue(0).GetType().Name.
 
D

dgleeson3

Hi Goran

Thanks for your input. It has been helpful.
first off I got back the type as you suggested.
Dim type1 As Type
type1 = reader.GetValue(0).GetType

Amazingly (to me anyway, it came back as "System.String")
So I decided to see what the string was with the following code.

Dim type1 As Type
Dim user_number As String
type1 = reader.GetValue(0).GetType
user_number = reader.GetString(0)

And the string is "dbo"
Isnt that the name of the table dbo.Tel_Number.

Oddly enough it does appear that I am reading from the correct table
as the code reads from 3 rows, reading dbo every time until all 3 rows
are read.

Any ideas?

Thanks again

Denis
 
?

=?ISO-8859-1?Q?G=F6ran_Andersson?=

Hi Goran

Thanks for your input. It has been helpful.
first off I got back the type as you suggested.
Dim type1 As Type
type1 = reader.GetValue(0).GetType

Amazingly (to me anyway, it came back as "System.String")
So I decided to see what the string was with the following code.

Dim type1 As Type
Dim user_number As String
type1 = reader.GetValue(0).GetType
user_number = reader.GetString(0)

And the string is "dbo"
Isnt that the name of the table dbo.Tel_Number.

Oddly enough it does appear that I am reading from the correct table
as the code reads from 3 rows, reading dbo every time until all 3 rows
are read.

Any ideas?

Thanks again

Denis
Click to expand...

When I saw the value that you are getting, it makes perfect sense. You
are not reading from the field User, you are reading a system variable
that contains the name of the current logged on database user account.
The user account that you are using to connect to the database is the
standard account dbo, which stands for database owner.

Rename the field, or put brackets around the name to access it: [User]
 
D

dgleeson3

Hi Goran

Many thanks, that was a sily one.
I just changed the query to.

"SELECT [User], Telephone FROM Tel_Number", conDatabase)


Is there any other system variables I should watch out for?

Many thanks

Denis

___________________
Denis Gleeson
http://www.CentronSolutions.com

[email protected] said:
Hi Goran
Click to expand...
Thanks for your input. It has been helpful.
first off I got back the type as you suggested.
Dim type1 As Type
type1 = reader.GetValue(0).GetType
Click to expand...
Amazingly (to me anyway, it came back as "System.String")
So I decided to see what the string was with the following code.
Click to expand...
Dim type1 As Type
Dim user_number As String
type1 = reader.GetValue(0).GetType
user_number = reader.GetString(0)
Click to expand...
And the string is "dbo"
Isnt that the name of the table dbo.Tel_Number.
Click to expand...
Oddly enough it does appear that I am reading from the correct table
as the code reads from 3 rows, reading dbo every time until all 3 rows
are read.
Click to expand...
Any ideas?
Click to expand...
Thanks again
Click to expand...
Denis
Click to expand...

When I saw the value that you are getting, it makes perfect sense. You
are not reading from the field User, you are reading a system variable
that contains the name of the current logged on database user account.
The user account that you are using to connect to the database is the
standard account dbo, which stands for database owner.

Rename the field, or put brackets around the name to access it: [User]

--
Göran Andersson
_____http://www.guffa.com- Hide quoted text -

- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Common procedures for executing all stored procedure 1
export datagridview to text file and import text file to sql servertable on the fly 2
Returning multiple resultsets usind datadreader 11
Generic Database handling class 13
How can ASPNET access the Northwind database 1
import excel sheet into a sql server table 7
search sql server by udp and list servers and respective databasesin listboxes 2
DOCX FILE CORRUPTED WHEN RETRIEVED FROM SQL2005 3

Top