Connection to SQL Server works in VB.NET, not ASP.NET

[Roy Lawson]

I am using SQL Server authentication (as opposed to Windows
authentication) to connect to a database and fill a dataset. I am
able to do this fine in VB, but not ASPx. I would think it to be
security if I were using integrated authentication, but since I am
using SQL authentication I am not sure.


Here is what i pass in to the class:

'### strCon is actually a dynamic property from a web.config key.
'### it looks like this:
'### "data source=DATA.MYWEBSITE.COM;initial
'### catalog=DATABASE;password=PW;user id=USER"

myInt = myCls.Authenticate(strCon, strLogin, strPW,
strProperty)



'### The class file (Authenticate) looks like so:

Imports System.Data
Imports System.Data.SqlClient

Public Class clsAuthenticate
Public Function Authenticate(ByVal strCon As String, ByVal
strLogin As String, ByVal strPW As String, ByVal strProperty As
String) As Integer

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
System.Data.SqlClient.SqlDataAdapter(cmdText, oConnection)
Dim oDS As New Data.DataSet()
Dim myResult As Integer
oDataAdapter.SelectCommand.CommandText.Equals(cmdText)

Try
'Fill the dataset
oConnection.Open() '### This is where the ERROR occurs ###
oDataAdapter.Fill(oDS, "viewLogin")
oConnection.Close()
Dim SecLevel As String
Dim Row As DataRow

Row = oDS.Tables("viewLogin").Rows(0)
SecLevel = Row.Item("SecurityRoleID")

myResult = CType(SecLevel, Integer)
oDS.Reset()

Return myResult
Catch e As Exception
'MsgBox(e.ToString)
Dim myex As String = e.ToString

Return 99
End Try

End Function

End Class

 

[Roy Lawson]

Try changing your connection string to,
server=DATA.MYWEBSITE.COM;uid=USER;password=PW; database=DATABASE

Hi Suresh, thanks for the security tips. That change to the
connection string did not work. My error is:
System.InvalidCastException: Specified cast is not valid. It occurs
as soon as I open the connection.

Still puzzled

-Roy

 

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

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