Cast Not Valid Exception (1 Viewer)

MATAdmin

New member
Local time
Today, 02:52
Joined
Dec 9, 2005
Messages
7
I am writing an application in vb.Net that pulls data from a table in a SQL Server, loads it into an array, and then checks certain fields against another array that I pulled in from an excel file. I am doing this because certain users are unable to simply import the excel file and query the results.

I've got everything working fine with the test data tables but when I switch the tables to the real ones needed it gives me a Cast Not Valid Exception on the line of code that loads the data table from the SQL Server. Is it because the table is too large to load into the array? Here is the code that gives the error message.


Sub DatabaseConnect()
'Create a Connection object.
myConn = New SqlConnection("Initial Catalog=MAT;" & "Data Source=localhost;Integrated Security=SSPI;")
'Create a Command object.
myCmd = myConn.CreateCommand
myCmd.CommandText = "SELECT * FROM tblMembers" 'This works with the test data

Try
'Open the connection.
myConn.Open()

'Use the SqlDataReader Object to Retrieve Data from SQL Server
myReader = myCmd.ExecuteReader()

'Load the data from the table into DbArray array
'Do While myReader.Read()
'DbRowCounter += 1
'Loop

Dim i As Integer
Do While myReader.Read()
For i = 0 To myReader.FieldCount() - 1
DbArray(DbRowCounter, i) = CStr(myReader.GetString(i)).Trim
Next
DbRowCounter += 1
''results = results & myReader.GetString(0) & vbTab & myReader.GetString(1) & vbLf
Loop
 

Kodo

"The Shoe"
Local time
Today, 05:52
Joined
Jan 20, 2004
Messages
707
why not just load this data into a gridview?
 

MATAdmin

New member
Local time
Today, 02:52
Joined
Dec 9, 2005
Messages
7
I'm not familiar with GridView, can u explain?
 

Kodo

"The Shoe"
Local time
Today, 05:52
Joined
Jan 20, 2004
Messages
707
Yup!
The gridview is a control that display data in table structure for you. You have to do very little to make this happen.

Drag a gridView onto your page from the toolbox and on page load you can grab the data from the databaes and bind it to the gridView.



Public Function GetMembers()as DataTable
'Create a Connection object.
myConn = New SqlConnection("Initial Catalog=MAT;" & "Data Source=localhost;Integrated Security=SSPI;")
'Create a Command object.
myCmd = myConn.CreateCommand
myCmd.CommandText = "SELECT * FROM tblMembers" 'This works with the test data

Dim DA As New SqlDataAdapter(myCmd)
Dim DT As New DataTable("Members")
DA.Fill(DT)

Return DT



End Function

now on the page load event of the page you're loading you do this


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If not page.ispostback
LoadGridView()
end if
End Sub


next up, the LoadGridView sub


Sub LoadGridView()
Try

Me.GridViewName.DataSource=GetMembers()
Me.GridViewName.DataBind()

End Sub

and there ya go.
Ideally, you would have a Data Access Layer Class that you would put, well, your data access methods instead of putting them page level. This would follow the nTier design better, but for the sake of showing you how this works, I'll stop here.
 

MATAdmin

New member
Local time
Today, 02:52
Joined
Dec 9, 2005
Messages
7
Can't find GridView

I can't seem to find the GridView tool in my toolbox. It isn't in the list of tools to add or remove either. I'm using Visual Studio 2003. Does this not come with it?
 

Kodo

"The Shoe"
Local time
Today, 05:52
Joined
Jan 20, 2004
Messages
707
ah..v1.1
in the tool box it's DataGrid.
The hook up should be the same.
 

PaddyIrishMan

Registered User.
Local time
Today, 09:52
Joined
Jun 5, 2002
Messages
166
Hi,
Maybe you have a Null in your 'real world' data that wouldnt be in your test data? I would expect a SQL error if the data was too big etc.

HTH,
Patrick
 

Users who are viewing this thread

Top Bottom