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

 

[Steve C. Orr [MVP, MCSD]]

To create a generic connection that can connection to virtually any database
(as it seems you are trying to do) you should utilize the generic
IDBConnection interface.
Here's more info:
http://msdn.microsoft.com/library/d...ml/frlrfsystemdataidbconnectionclasstopic.asp

 

[John Saunders]

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

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
...
Dim strSystemName As Integer = cmd1.ExecuteScalar

If you get a "specified cast is invalid" exception, then you should ask
yourself which cast it's referring to.

Now, there's no cast visible on that line because VB.NET is allowing you to
get away without one (try turning Options Strict On). There's an implicit
cast to Integer:

Dim strSystemName as Integer = CType(cmd1.ExecuteScalar, Integer)

So, if the "specified cast is invalid", then it suggests that the
ExecuteScalar call is returning something which cannot be cast to an Integer
(in other words, it's not returning an integer). You might try the
following:

Dim o As Object = cmd1.ExecuteScalar
Response.Write (o.GetType().ToString())

Another clue is the fact that you named the variable "strSystemName", but
you say it is of type "Integer". Maybe the ExecuteScalar is returning a
string?

 

[John Howard]

John:

This is the response:

Specified cast is not valid.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Specified cast is not
valid.

Source Error:


Line 75: Dim strSystemName As Integer
Line 76: 'Dim strSystemName As Integer = cmd1.ExecuteScalar
Line 77: Dim o As Object = cmd1.ExecuteScalar
Line 78: Response.Write(o.GetType().ToString())
Line 79: '
-----------------------------------------------------------


Source File: C:\Inetpub\wwwroot\WebPDB2\Global.asax.vb Line: 77

Stack Trace:


[InvalidCastException: Specified cast is not valid.]
System.Data.OleDb.OleDbException..ctor(IErrorInfo errorInfo, Int32
errorCode, Exception inner)
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hResult,
OleDbConnection connection, Object src)

System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr)

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)

System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
System.Data.OleDb.OleDbCommand.ExecuteScalar()
WebPDB2.Global.Session_Start(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\WebPDB2\Global.asax.vb:77
System.Web.SessionState.SessionStateModule.RaiseOnStart(EventArgs
e)
System.Web.SessionState.SessionStateModule.OnStart(EventArgs e)
System.Web.SessionState.SessionStateModule.CompleteAcquireState()
System.Web.SessionState.SessionStateModule.BeginAcquireState(Object
source, EventArgs e, AsyncCallback cb, Object extraData)

System.Web.AsyncEventExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute()
System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
Boolean& completedSynchronously) +173