Invalid cast on 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

 

[Patrick Steele [MVP]]

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.

To debug, change the line to:

dim temp as Object = cmd1.ExecuteScalar

And see what "temp" is in the debug window. It's obviously something
that can't be cast to an Integer.

 

[Jorge]

Hi John

In addition to what Patrick suggested i would use a
SqlDataReader to hold the result of your select command
since its possible that it is returning several rows.

Kind Regards
Jorge

-----Original Message-----
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;" & _

 

[Patrick Steele [MVP]]

In addition to what Patrick suggested i would use a
SqlDataReader to hold the result of your select command
since its possible that it is returning several rows.

But the whole point of the ExecuteScalar is to quickly return the first
column from the first row of a resultset. No connections are left open
-- unlike the SqlDataReader which keeps the connection open until you're
done with it.

 

[John Howard]

Dim temp As Object = cmd1.ExecuteScalar

Amazing - this gives the same error.

 

[John Howard]

This table has only one row.

Hi John

In addition to what Patrick suggested i would use a
SqlDataReader to hold the result of your select command
since its possible that it is returning several rows.

Kind Regards
Jorge
= "provider=Microsoft.Jet.OLEDB.4.0;" & _
Provider=Microsoft.Jet.OLEDB.4.0;" & _
Source=W:\PDB\Development\PDB.mdb;" & _

 

[John Howard]

Dim dr As OleDbDataReader = cmd1.ExecuteReader

Same error

 

[Herman Kheyfets]

Is SP return any value?
Maybe you have CAST in sp???

Gerasha

 

[Patrick Steele [MVP]]

Dim temp As Object = cmd1.ExecuteScalar

Amazing - this gives the same error.

Very odd... Does the Exception that is thrown contain anything in the
"InnerException" property? There's got to be a stack trace somewhere
showing exactly what is throwing the invalid cast exception.

 

[John Howard]

I don't understand

Is SP return any value?
Maybe you have CAST in sp???

Gerasha

 

[John Howard]

Here is the result. I can't make any sense of it:

Line 71: ' Execute the SQL
Line 72: 'Dim dr As OleDbDataReader = cmd1.ExecuteReader
Line 73: Dim temp As Object = cmd1.ExecuteScalar
Line 74: Dim strSystemName As Integer
Line 75: 'Dim strSystemName As Integer = cmd1.ExecuteScalar
Source File: C:\Inetpub\wwwroot\WebPDB2\Global.asax.vb Line: 73

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:73
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

 

[Herfried K. Wagner [MVP]]

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.

.... I suggest to ask this question in a more appropriate group:

<URL:
Web interface:

<URL:http://msdn.microsoft.com/newsgroups/?dg=microsoft.public.dotnet.framework.adonet>

 

[Cor Ligthert]

Hi John,

Is the field description of both databases equal

You can of course just try this
Dim strSystemName As Integer = Cint(cmd1.ExecuteScalar)

Just a thought,

Cor

 

[John Howard]

Same error again.

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 70: End With
Line 71: ' Execute the SQL
Line 72: Dim strSystemName As Integer =
CInt(cmd1.ExecuteScalar)
Line 73: 'Dim dr As OleDbDataReader = cmd1.ExecuteReader
Line 74: 'Dim temp As Object = cmd1.ExecuteScalar


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

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:72
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

 

[Cor Ligthert]

Hi John,

Same error again.

Line 74: 'Dim temp As Object = cmd1.ExecuteScalar

How can that be the same error?

Cor

 

[John Howard]

Cor:

I wish I knew. I'm beginning to think that this error message may be
a "Red Herring", leading us astray. I commented out this code and
allowed processing to drop through to the next SQL - a Fill for a data
adapter against anogther table. And guess what? Same error message.

This is leading me to believe that the problem may be completely
unrelated to casting, but more related to a db connection.

What do you thinK?

 

[Cor Ligthert]

Hi John,

That was what I was thinking, maybe just make another simple execute command
or even create a dataadapter or whatever than you know it.

dim ds as new dataset
dim da as new OleDbdataadapter(cmd1)
da.fill(da)

should work in my opinion.

You can try.

Cor

 

[John Howard]

This results in:

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 79: Dim da As New OleDbDataAdapter(cmd1)
Line 80: 'ds9.Fill(da)
Line 81: da.Fill(ds)
Line 82:
Line 83: Dim strSystemName As Integer


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

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.ExecuteReader(CommandBehavior
behavior)

System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
WebPDB2.Global.Session_Start(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\WebPDB2\Global.asax.vb:81
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

 

[Cor Ligthert]

Hi John,

Did you try it already with a simple connections string to that virtual W
drive?

I never saw this one, where did you get it?

Cor

 

[John Howard]

Cor:

I assume you are referring to the connectionstring:

..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"

which I got from a web site after having problems connecting to a
remote (LAN) drive after just changing the Data Source, as follows:

.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data
Source=W:\PDB\Development\PDB.mdb"


And yes, I did try that with the following result:

'W:\PDB\Development\PDB.mdb' is not a valid path. Make sure that the
path name is spelled correctly and that you are connected to the
server on which the file resides.
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.Data.OleDb.OleDbException:
'W:\PDB\Development\PDB.mdb' is not a valid path. Make sure that the
path name is spelled correctly and that you are connected to the
server on which the file resides.

Source Error:


Line 55: ' Open connection
Line 56: 'Try
Line 57: cn.Open()
Line 58: 'Catch
Line 59: ' Response.Write("Connection error: " &
Err.Description)


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

Stack Trace:


[OleDbException (0x80004005): 'W:\PDB\Development\PDB.mdb' is not a
valid path. Make sure that the path name is spelled correctly and
that you are connected to the server on which the file resides.]
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
System.Data.OleDb.OleDbConnection.InitializeProvider()
System.Data.OleDb.OleDbConnection.Open()
WebPDB2.Global.Session_Start(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\WebPDB2\Global.asax.vb:57
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