Showing posts with label provider. Show all posts
Showing posts with label provider. Show all posts

Saturday, March 24, 2012

OLE DB Connections

Using dreamweaver...

Can one connect via an OLE DB connection with an ODBC DSN? IE I don't have an OLE DB provider but an ODBC provider and I was wondering if you really need an OLE DB provider.

In order to use Dreamweaver's datagrid/datalist components with asp.net vb you must connect via OLE DB (for mysql) and that is why I"m asking. I only have an ODBC set up.

OLEDB and ODBC are different technologies. First, you shouldn't use an ODBC connection if at all possible. Second, DreamWeaver will let you connect using either just fine, it just won't write the code in a drag and drop method for you. Since you're working in ASP.NET, you might look at using Visual Web Developer for your coding portion of your site.

Jeff


I wanted to use Dreamweaver's Datagrid abilities...but it seems to only allow an ole db or sql connection. No option for ODBC or MySQL

Thursday, March 22, 2012

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=@dotnet.itags.org.UserName AND Secretword=@dotnet.itags.org.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 '@dotnet.itags.org.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=@dotnet.itags.org.UserName AND Secretword=@dotnet.itags.org.Password"
Trace.Warn("Database", "database open")
Dim Param1 As System.Data.OleDb.OleDbParameter
Dim Param2 As System.Data.OleDb.OleDbParameter
Param1 = objCMD.Parameters.Add("@dotnet.itags.org.UserName",
TextBox1.Text.Trim())
Param2 = objCMD.Parameters.Add("@dotnet.itags.org.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 TryI 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
>
>
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" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:5CDBF3A1-A823-4796-8D85-D1C655B66459@.microsoft.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
>>
>>
>>
>

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=@dotnet.itags.org.UserName AND Secretword=@dotnet.itags.org.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 '@dotnet.itags.org.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=@dotnet.itags.org.UserName AND Secretword=@dotnet.itags.org.Password"
Trace.Warn("Database", "database open")
Dim Param1 As System.Data.OleDb.OleDbParameter
Dim Param2 As System.Data.OleDb.OleDbParameter
Param1 = objCMD.Parameters.Add("@dotnet.itags.org.UserName",
TextBox1.Text.Trim())
Param2 = objCMD.Parameters.Add("@dotnet.itags.org.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 TryI 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. Th
is
> application uses an OLEDB connection string for MS ACCESS.
> Now, I'd like to upsize the application so I converted the db to SQL SERVE
R
> and this went well.
> Next thing I changed the connection string from OLEDB for MS ACCESS to OLE
DB
> for SQL SERVER.
> I expected this to work resonably well, however I found that it never work
ed
> 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 abo
ve
> 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 f
or
> 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.ConnectionStrin
g)
> 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 clo
se
> after checking the user is in admin. File " &
> Request.ServerVariables("SCRIPT_NAME") & "<br>"
> EmailException(err.Message, strErrMessage)
> End Try
> End If
> End Try
>
>
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" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:5CDBF3A1-A823-4796-8D85-D1C655B66459@.microsoft.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:
>

OleDbDataAdapter Configuration On Server? :(

Hi,

I am using OleDbDataAdapter to implement data grid web control and to configure the database connection i have to provide it with the provider and database name... as i am using access database so i select the provider as Jet Microsoft .... and then i have to browse for my database file and the connection gets created,

Now the problem is when i post the thing on my server it still looks for the path C:\inetpub\db\..... how can i configure that for the server. I tried to change the path in code window as ../db/... somthing like that but it didnt work ... plz help.

Thanks,
imranFor a database connection you have to use a physical path and not a relative path. To do what you are looking to acomplish you have to use "server.mappath" and the virtual path as follows:"


dim DBname as string = server.mappath("mydb.mdb")

then just plug the DBname string into your connection string
Hi,

I have used the mappath thing everywhere in my code but i dont know how to use it with the OleDbDataAdapter control as when u right click on the control it gives an option to configure the control and there i am not able to find a way to relate my access database file with server mappath .. it just lets me to browse the database file on the hard drive.
plzz suggest a way,

thanks,
imran
post the code of your page and i'll show you what to do