Invalid cast from SQL to other server

[John Howard]

Making the following call to a local MSAccess database works fine:

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
Dim intRows As Integer
Dim strSQL As String
Dim ds As New DataSet
' Create connection
Dim cn As New OleDbConnection
With cn
.connectionstring =
"provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PDB\Development\Database\PDB.mdb"
End With
' Open connection
cn.Open()
' -----------------------------------------------------------
' Get System Name
' -----------------------------------------------------------
' Create command
Dim cmd1 As New OleDbCommand
With cmd1
.Connection = cn
.CommandText = "SELECT System FROM Reference"
End With
' Execute the SQL
Dim strSystemName As Integer = cmd1.ExecuteScalar


However, changing the connectionstring to

.ConnectionString = "Provider=MS Remote;" & _
"Remote Server=http://scfmzcp1;" & _
"Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=W:\PDB\Development\PDB.mdb;" & _
"Connection Timeout=30"

causes a "Specified cast is not valid" message on the ExecuteScalar
line.

Thanks for your help

John

 

[David Browne]

Making the following call to a local MSAccess database works fine:

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
Dim intRows As Integer
Dim strSQL As String
Dim ds As New DataSet
' Create connection
Dim cn As New OleDbConnection
With cn
.connectionstring =
"provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PDB\Development\Database\PDB.mdb"
End With
' Open connection
cn.Open()
' -----------------------------------------------------------
' Get System Name
' -----------------------------------------------------------
' Create command
Dim cmd1 As New OleDbCommand
With cmd1
.Connection = cn
.CommandText = "SELECT System FROM Reference"
End With
' Execute the SQL
Dim strSystemName As Integer = cmd1.ExecuteScalar


However, changing the connectionstring to

.ConnectionString = "Provider=MS Remote;" & _
"Remote Server=http://scfmzcp1;" & _
"Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=W:\PDB\Development\PDB.mdb;" & _
"Connection Timeout=30"

causes a "Specified cast is not valid" message on the ExecuteScalar
line.

Yet another reason to never use ExecuteScalar. It's not as easy as it
looks. You're probably getting System.DbNull.Value back, which cannot be
cast to an Integer.

David

 

[John Howard]

I have also tried

'Dim temp As Object = cmd1.ExecuteScalar

with the same result, also when this section is commented out
altogether, the next SQL (Fill on a data adapter) gets the same
result.

I am beginning to believe that it is really not a cast issue at all.