DSN ConnectionString?

[Arpan]

I created a System DSN named "NETData" for a SQL Server 2005 Express
database. Now to make use of this System DSN to access records in the
DB table, this is how I framed the ConnectionString:

Dim sqlConn As SqlConnection

sqlConn = New SqlConnection("DSN=NETData")

But the following error gets generated pointing to the above line:

Keyword not supported: 'dsn'.

How do I overcome the above error?

Thanks,

Arpan

 

[Mark Rae]

I created a System DSN named "NETData" for a SQL Server 2005 Express
database.

Is there a specific reason that you need to use ODBC rather than the native
..NET provider?
http://www.connectionstrings.com/

 

[Arpan]

Is there a specific reason that you need to use ODBC rather than the native

.NET provider?

Actually 2 reasons....the first one being just for testing purpose &
the second one being whether DSN parameters cannot be inserted into a
SQLConnection or a SQLCommand object which I was told by one of my
colleagues though he wasn't very much sure if that's indeed the case.
So just want to get this verified from some MVPs/ASP.NET gurus.

Thanks,

Regards,

Arpan

 

[Bren]

Hi Arpan

I had a very similar issue connecting via ODBC to a MySQL DB, How I got
round it was to put the connection in the web.config file and used the DSN
from there.

<connectionStrings>

<add name="connectionname" connectionString="Dsn=DSNNetData"
providerName="System.Data.Odbc" />

</connectionStrings>

This worked for me in an intranet environment, I believe if you are using
this method on the Internet it is advisable to encrypt the connection. There
is a good artical on 4guysfromrolla.com on how to encrypt the connection.

HTH

rgds
bren

Why do I climb mountains? Simple! because they are there
www.3peakschallenge.co.uk

 

[Arpan]

Thanks, Bren, for your input. After adding the <connectionStrings> tag
in the web.config file, how do I use the SqlConnection in the ASPX
page? Using the native .NET Provider, the ConnectionString I used is
shown below:

<script runat="server">
Sub Page_Load(ByVal obj As Object, ByVal ea As EventArgs)
Dim sqlConn As SqlConnection
Dim sqlDapter As SqlDataAdapter
Dim dSet As DataSet

sqlConn = New SqlConnection("Data
Source=MyDS\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True")
sqlDapter = New SqlDataAdapter("SELECT * FROM Tests", sqlConn)

dSet = New DataSet()
sqlDapter.Fill(dSet, "Tests")
......................
......................
End Sub
</script>

But now since I am setting the DSN connection in the web.config file,
how do I reference the DSN connection in the ASPX page?

This is how my web.config looks:

<configuration
xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings>
<connectionStrings>
<add name="NetDSN" connectionString="Dsn=NETData"
providerName="System.Data.Odbc"/>
</connectionStrings>
</appSettings>
<system.web>
<compilation debug="true" strict="false" explicit="true"/>
</system.web>
</configuration>

Thanks once again,

Regards,

Arpan

 

[Bren]

Hi Arpan

Sorry for the delay in getting back to you, I've just spent the weekend in
the dentists chair having my tooth ripped out :-]

Anyhow I am assuming you are using VS 2005 here.

How I do it is as follows:

Add the connection string info into web.config - which you have now done.

Drag and drop a SqlDataSource onto your webform and
Set the following properties in the properties window:

ID - name it something like dsMyDataSource

ConnectionString - Choose your connection name from the dropdown, in this
case it should be NetDSN. This will then enter your dsn name automatically.

DataSource Mode - From the dropdown choose either DataSet or DataReader

Once you have done this you have 2 options:

1. Declare a variable in your code page to store your SQL statement in e.g.

Dim varSQL As String = "SELECT * FROM Tests"
and then add this to your dsMyDataSource at run time e.g.

Sub Page_Load(etc....

dsMyDataSource.SelectCommand = varSQL

End Sub

2. Add the SELECT Statement atribute into the HTML view of your
dsMyDataSource e.g.

SelectCommand="SELECT * FROM Tests"

I personally prefer to use option one as that gives full code separation at
runtime and you can then just reset the varSQL value for your UPDATE
command, DELETE command etc.

Hope this helps, any more questions just post on the group and I will try to
help.

Best Regards
Bren

Why do I climb mountains? Simple! because they are there
www.3peakschallenge.co.uk

 

[andrewbacon]

And then what?

I'm used to ADO and am just trying to hack my way through the asp.NET data stuff... Once you've got this:

<asp:sqldatasource runat="server"
ID="SelectAllContacts"
ConnectionString="<%$ ConnectionStrings:sfsff %>"
SelectCommand="SELECT * FROM contacts"
/>

What do you do with it? How do you access field values, manipulate data, etc.? I don't get it. I can't even tell if my connection string is working, because Visual Studio assumes that all resources are local - and I'm working on a remote web server.

Thanks for your help...
Andrew