473,289 Members | 1,961 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,289 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 2828
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: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.