Cast Not Valid Exception

[MATAdmin]

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]

why not just load this data into a gridview?

 

[MATAdmin]

I'm not familiar with GridView, can u explain?

 

[Kodo]

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]

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]

ah..v1.1
in the tool box it's DataGrid.
The hook up should be the same.

 

[PaddyIrishMan]

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