473,322 Members | 1,352 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

OLEDB Provider for SQL SERVER + Parameterized Queries

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

Nov 19 '05 #1
2 2830
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" wrote:
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

Nov 19 '05 #2
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.

"Alien2_51" <da***************@monacocoach.removeme.com> wrote in message
news:5C**********************************@microsof t.com...
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" wrote:
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

Nov 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Parking Meters | last post by:
Today I managed to get the OleDB provider to let me put datasets from an as400 using sql. Great, the provider string I used is: Provider=IBMDA400.DataSource.1;User ID=XXXXX;Password=XXXX;Data...
4
by: NS | last post by:
Hi, I am trying to execute a prepare statement using oledb provider for DB2. The command.Prepare() statement is giving me an exception " No error information available:...
0
by: silesius | last post by:
I've been using VS 2003 to develop a webapplication using C#. Today I exported the application to a remote webserver I begun experiencing problems. It's a simple application that retrieves some...
0
by: silesius | last post by:
I've been using VS.NET 2003 to develop a webapplication using C#. Today I exported the application to another webserver I begun experiencing problems. It's a simple application that retrieves...
5
by: petro | last post by:
Hello all, My asp.net web application works on my machine but I get the following error on our test web server, There is only one oracle home on the test server. Does anyone know how to resolve...
3
by: blue875 | last post by:
When I run this connection, I get a security error. String connectStr = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"User Id=USER; Password=PASS;" + @"Data Source=\\SERVER\DIRECTORY\Data.mdb;" +...
2
by: ram_palavalasa | last post by:
Hi all, I have a assignment of vc++.net and sybase to populate data and generate reports which is the best one? is OLEDB more efficient than ODBC? i think OLEDB itself is a layer on the...
0
by: Tom | last post by:
Looking for some help with stored procedure call issues. Conceptually, I need to pass a data structure as the sole parameter to the Oracle stored procedure. Sounds simple enough....but how? ...
1
by: ErikJL | last post by:
I have a simple webservice that performs a SELECT query against a database, and then an INSERT statement on the same database/table. The problem arises at the time when we create the second OleDB...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.