Invalid cast on SQL to other server

J

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
 
P

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.
Click to expand...

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

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
Click to expand...
= "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
Click to expand...
Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Click to expand...
Source=W:\PDB\Development\PDB.mdb;" & _
 
P

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.
Click to expand...

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

John Howard

Dim temp As Object = cmd1.ExecuteScalar

Amazing - this gives the same error.
 
J

John Howard

This table has only one row.

Jorge said:
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;" & _
Click to expand...
 
P

Patrick Steele [MVP]

Dim temp As Object = cmd1.ExecuteScalar

Amazing - this gives the same error.
Click to expand...

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

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
 
H

Herfried K. Wagner [MVP]

* "John Howard said:
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.
Click to expand...

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

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
 
J

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
 
J

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?
 
C

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
 
J

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
 
C

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Invalid cast from SQL to other server 2
connection to sql server 2005 problem 5
Why won't this simple SQL statement INSERT a value? 4
Unable to cast COM object of type 'ADODB.RecordsetClass' to classtype 'System.Object[]' 20
error 40 could not open a connection to sql server when passingselected values 2
update dbase file with function 1
Web Database (Insert) 1
VBA Excel data to SQL Sever table 1

Top