OLEDB Provider for SQL SERVER + Parameterized Queries

[Martin]

Hi,

I currently have an application that connects to an MS ACCESS database. This
application uses an OLEDB connection string for MS ACCESS.
Now, I'd like to upsize the application so I converted the db to SQL SERVER
and this went well.

Next thing I changed the connection string from OLEDB for MS ACCESS to OLEDB
for SQL SERVER.
I expected this to work resonably well, however I found that it never worked
at all.
The reason is that I have done all my MS ACCESS queries using dynamic SQL
and Parameterized queries like so

objCMD.CommandText = "SELECT COUNT(*) AS UserCount FROM tblSecurity WHERE
UserName=@UserName AND Secretword=@Password"
This seems to work fine for access, But the OLEDB provider for SQL SERVER
complains bitterly about this, for example, attempting to execurte the above
query with attached parameters gives me the following error

Must declare the variable '@UserName'.

Can anybody please explain why these two database will not work with the
same syntax even though I am using an OLEDB connection string / Provider for
both.


I have included my full code below.
Please note that this works with an MS ACCESS OLEDB connection string but
NOT an OLEDB SQL SERVER CONNECTION STRING.

many thanks in advance.

cheers

martin.
=========================================================================================================





Dim objConn As System.Data.OleDb.OleDbConnection = New
System.Data.OleDb.OleDbConnection()
Dim objCMD As System.Data.OleDb.OleDbCommand = New
System.Data.OleDb.OleDbCommand()
Try
'Trace.Warn("Con string", OleDbConnection1.ConnectionString)
objConn.ConnectionString() = Application("ConnectionString")
objCMD.Connection = objConn
objConn.Open()
Trace.Warn("Con string", objConn.ConnectionString())
objCMD.CommandText = "SELECT COUNT(*) AS UserCount FROM
tblSecurity WHERE UserName=@UserName AND Secretword=@Password"
Trace.Warn("Database", "database open")
Dim Param1 As System.Data.OleDb.OleDbParameter
Dim Param2 As System.Data.OleDb.OleDbParameter
Param1 = objCMD.Parameters.Add("@UserName",
TextBox1.Text.Trim())
Param2 = objCMD.Parameters.Add("@Password",
Password1.Value.Trim())
intNumUsers = objCMD.ExecuteScalar() 'Execute the query
'ERROR THROWN HERE WORKS ON ACCESS BUT NOT SS
Trace.Warn("Database", "No database error occured")
Catch err As Exception
Dim strErrMessage As String
strErrMessage = "Exception thrown function btnLogin_Click" &
Request.ServerVariables("SCRIPT_NAME") & "<br>"
Trace.Write("ERROR", err.Message.ToString)
'Response.End()
'EmailException(err.Message, strErrMessage)
Finally
If objConn.State = ConnectionState.Open Then
Try
'Attempt to close the connection
objConn.Close()
Catch err As Exception
'Catch any error that may occur
Dim strErrMessage As String
strErrMessage = "Exception thrown attemping to close
after checking the user is in admin. File " &
Request.ServerVariables("SCRIPT_NAME") & "<br>"
EmailException(err.Message, strErrMessage)
End Try
End If
End Try

 

[Guest]

I think as part of your migration to SQL Server you should migrate your
dynamic sql code to stored procedures, I'm not sure that there is anything
you can do to make the code you have work the way it is. Having that said if
your going to have to change it you might as well use sp's.

 

[Martin]

I agree entirely, you are totally right - If you use SQL SERVER the sp's are
definatly the way to go.
However I would be interested to find out why parameterised queries work
with the OLEDB provider for MS ACCESS but NOT with the OLEDB provider for
SQL SERVER.