"System.InvalidCastException: Specified cast is not valid." during connection.open

[Roy Lawson]

I have no idea what is going on here. I wrote a simple application in
VB.NET to generate a Crystal Report, and I am now trying to move it to
ASP.NET with Crstal Enterprise. I wish I could tell you how the
report works...but I can't even get past connecting to the database


I use the typical:

Dim oConnection As New SqlConnection(strCon)
Dim cmdText As String = "SELECT * FROM viewLogin WHERE " & _
viewLogin.username = '" & strLogin & "' AND viewLogin.password
" & _
= '" & strPW & "' AND viewLogin.company_cd = '" & strProperty
& "'"
Dim oDataAdapter As New SqlDataAdapter(cmdText, oConnection)
Dim oDS As New Data.DataSet()
oDataAdapter.SelectCommand.CommandText = cmdText


oConnection.Open() '### Error occurs here during debug
oDataAdapter.Fill(oDS, "viewLogin")
oConnection.Close()


So, why does this work in VB.NET on the same system/IDE and not in
ASP.NET?

 

[jef]

I have no idea what is going on here. I wrote a simple application in
VB.NET to generate a Crystal Report, and I am now trying to move it to
ASP.NET with Crstal Enterprise. I wish I could tell you how the
report works...but I can't even get past connecting to the database


I use the typical:

Dim oConnection As New SqlConnection(strCon)
Dim cmdText As String = "SELECT * FROM viewLogin WHERE " & _
viewLogin.username = '" & strLogin & "' AND viewLogin.password
" & _
= '" & strPW & "' AND viewLogin.company_cd = '" & strProperty
& "'"
Dim oDataAdapter As New SqlDataAdapter(cmdText, oConnection)
Dim oDS As New Data.DataSet()
oDataAdapter.SelectCommand.CommandText = cmdText


oConnection.Open() '### Error occurs here during debug
oDataAdapter.Fill(oDS, "viewLogin")
oConnection.Close()


So, why does this work in VB.NET on the same system/IDE and not in
ASP.NET?

Try this:

Dim cmdText As String = "SELECT * FROM viewLogin WHERE " _
& viewLogin.username & " = '" & strLogin & "' AND " _
& viewLogin.password & " = '" & strPW & "' AND " _ &
viewLogin.company_cd & " = '" & strProperty & "'"

I would also suggest moving this to a stored procedure to avoid SQL
injection attacks...

Hope this helps!

jef

 

[jef]

Try this:

Dim cmdText As String = "SELECT * FROM viewLogin WHERE " _
& viewLogin.username & " = '" & strLogin & "' AND " _
& viewLogin.password & " = '" & strPW & "' AND "
_ & viewLogin.company_cd & " = '" & strProperty & "'"

I would also suggest moving this to a stored procedure to avoid SQL
injection attacks...

Hope this helps!

jef

Oh man - nevermind what I said - I totally read that wrong. Sorry!

jef

 

[jef]

I have no idea what is going on here. I wrote a simple application in
VB.NET to generate a Crystal Report, and I am now trying to move it to
ASP.NET with Crstal Enterprise. I wish I could tell you how the
report works...but I can't even get past connecting to the database


I use the typical:

Dim oConnection As New SqlConnection(strCon)
Dim cmdText As String = "SELECT * FROM viewLogin WHERE " & _
viewLogin.username = '" & strLogin & "' AND viewLogin.password
" & _
= '" & strPW & "' AND viewLogin.company_cd = '" & strProperty
& "'"
Dim oDataAdapter As New SqlDataAdapter(cmdText, oConnection)
Dim oDS As New Data.DataSet()
oDataAdapter.SelectCommand.CommandText = cmdText


oConnection.Open() '### Error occurs here during debug
oDataAdapter.Fill(oDS, "viewLogin")
oConnection.Close()


So, why does this work in VB.NET on the same system/IDE and not in
ASP.NET?

Your code seems to work for me...is there a chance that the issue could
be with the strCon?

FWIW, the DataAdapter will automatically open and close the connection
for you, so there's no need to do it explicitly...

Good luck!!!

 

[Kevin Yu [MSFT]]

Hi Roy,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you received an InvalidCastException
when trying to open an connection to the SQL server. If there is any
misunderstanding, please feel free to reply to the post.

This seems to be a very strange problem. According to the following MSDN
document, SqlConnection.Open method will never throw an
InvalidCastException.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqlconnectionclassopentopic.asp

Please try to check if the connection has been opened before. Please also
try to use the following connection string to connect to a default database
(Northwind) on a SQL server.

Password=PW;Persist Security Info=True;User ID=User;Initial
Catalog=Northwind;Data Source=ServerName

If this works, it means that the there might be something wrong with the
database. If it doesn't work, please use please use the SQL Profiler to
trace the actions performing on the SQL server. We might find which SQL
statement goes wrong from the trace log.You can also use breakpoints to
step through the code and pause before the SqlConnection.Open method and
check the real connection string using watch window.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Roy Lawson]

Your code seems to work for me...is there a chance that the issue could
be with the strCon?

If that is the case, why would the same connection work in VB.NET and
not ASP.NET? I thought my installation might of become corrupted, so
I re-installed VS.NET and components, and the .NET framework. Still,
no dice. The same System.InvalidCastException error.

 

[Kevin Yu [MSFT]]

Hi Roy,

Have you tried my suggestion in my last post to connect to another
database? It's very strange that the SqlConnection.Open method will throw
an InvalidCastException. Are you sure that the exception was thrown on that
line?

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Roy Lawson]

Hi Kevin,

Thanks for the reply. I am sure the InvalidCastException occurs at the
SqlConnection.Open method, and I tried to connect to another database.
I am completely puzzled on this one.

-Roy

 

[Kevin Yu [MSFT]]

Hi Roy,

I'm puzzled at this, too.

Please try to check this with SQL profiler to see what's actually wrong
with the SQL server. Profiler can expose the operations on server to us.
Here are the steps:

1. Open SQL Profiler from the Start menu.
2. Create a new trace. Make sure that the login events have been selected.
3. Run the trace.
4. Try to reproduce the problem.
5. Check the trace log to see what's happening on server.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi Roy,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Roy Lawson]

Hi Kevin,

I was unable to solve the problem; what I finally did was to install VS
..NET on another system and am developing on that one now. The system I
was developing on was not a dedicated development system, so there were
all types of software installed on it. It had W2K SP4 and MDAC 2.8. I
am just going to use SP2 and MDAC 2.7 on my development system until I
am forced to move up.

Thank you for you help in this, I wish I knew what was causing the
problem

-Roy

 

[Kevin Yu [MSFT]]

Hi Roy,

Sorry that I cannot solve the problem for you. As you know, this issue
requires further debugging with the exact same environment as yours. If
there is anything I can do, please feel free to let me know.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[gdekhayser]

Hi Roy,

Sorry that I cannot solve the problem for you. As you know, this issue
requires further debugging with the exact same environment as yours. If
there is anything I can do, please feel free to let me know.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Roy and Kevin:

Guess what? I am having the EXACT SAME PROBLEM on one of my production
boxes, running W2K3 fully patched. It's intermittent however. The
program works just fine for like 1-7 days, then once I get the error,
any subsequent attempt to call the open() method on the
sqlClient.sqlconnection gets the 'the specified cast is invalid' on the
open call (or whatever the specific language is). I restart the app,
and I'm fine again.

My app takes on a pretty heavy burden; and it can call the open
statement as many as 10x/sec or more.

Did anyone get anywhere on this?

Glenn Dekhayser
Voyant Strategies, inc.
g.d.e.k.h.a.y.s.e.r.-at-.v.o.y.a.n.t.i.n.c..c.o.m