469,631 Members | 1,748 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

InvalidCastException when doing reader.GetInt32(0)

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

Sep 7 '07 #1
6 4803
Try reader.GetInt32(0).ToString()

--
Rgds,
Anand
VB.NET MVP
http://www.dotnetindia.com
"dg*******@eircom.net" wrote:
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

Sep 7 '07 #2
dg*******@eircom.net wrote:
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
It looks all right. Check what data type you are really getting in the
data reader by using reader.GetValue(0).GetType().Name.

--
Göran Andersson
_____
http://www.guffa.com
Sep 8 '07 #3
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

On Sep 8, 12:56 pm, Göran Andersson <gu...@guffa.comwrote:
dglees...@eircom.net wrote:
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

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

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

- Show quoted text -

Sep 8 '07 #4
dg*******@eircom.net wrote:
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
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
Sep 8 '07 #5
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

On Sep 8, 6:24 pm, Göran Andersson <gu...@guffa.comwrote:
dglees...@eircom.net wrote:
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

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 -

Sep 8 '07 #6
dg*******@eircom.net wrote:
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
Here's a handy list:

http://sqlserver2000.databases.aspfa...-keywords.html

--
Göran Andersson
_____
http://www.guffa.com
Sep 8 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Craig Lister | last post: by
2 posts views Thread by Jeremy H | last post: by
2 posts views Thread by lmnorms1 | last post: by
2 posts views Thread by netnatter | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.