?
Solved

ASP.Net and ODBC

Posted on 2011-09-09
15
Medium Priority
?
386 Views
Last Modified: 2012-05-12
I can create ODBC Data Source

As described at http://www.depts.ttu.edu/ithelpcentral/solutions/odbcsetup.php

and use the below code in classic ASP :

Set objConn= Server.CreateObject("ADODB.Connection")

strConnection= "DSN= MyDSNName; Database= MyDBName; UID= MyUserID; PWD= MyPassword"

objConn.Open strConnection

strQuery="Select Name from MyTable"

Set objRS= objConn.Execute(strQuery)
--------------------------------------------------------
in .Net (C#) i can create SqlConnection like below:

SqlConnection conn = new SqlConnection("Data Source=DatabaseServer;Initial Catalog=Northwind;User ID=YourUserID;Password=YourPassword");

My question is:

How to modify the above string {SqlConnection conn = new SqlConnection(..)} to use ODBC Data Source MyDSNName?

Thanks
0
Comment
Question by:niceguy971
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
15 Comments
 
LVL 4

Accepted Solution

by:
asp_net2 earned 1000 total points
ID: 36514837
Hii niceguy971,

What Database are you trying to connect to using ODBC?

Let me know before I post more examples.
0
 

Author Comment

by:niceguy971
ID: 36514871
Hi! I'm trying to connect to SQL Server 2005... I would like to use ODBC Data Source MyDSNName ( Data Source MyDSNName has been created as described at http://www.depts.ttu.edu/ithelpcentral/solutions/odbcsetup.php)

to create new SqlConnection..so MyDSNName should be included in the below:

SqlConnection conn = new SqlConnection(....MyDSNName....)

What would be the proper way to do it?

Thanks
0
 
LVL 4

Expert Comment

by:asp_net2
ID: 36514900
This is the way that I have always connected to SQL Server for all my projects. Of course you will hav to fill in your SQL Server name, Database Name, Username, and Password used for your Database.

For the web.config file, make sure you have the following setup (I used Mixed Authentication for SQL Server).

<connectionStrings>
    <add name="NameOfConnection" connectionString="Data Source=NameOfDBServer;Initial Catalog=YourDataBase;User ID=sa;Password=YourPassword" providerName="System.Data.SqlClient"/>
</connectionStrings>

For every page that will need to interact with SQL Server, make sure you have the following Namespaces declared at the top of your CodeBehind.

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

Create SQL Connection:
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NameOfConnection"].ConnectionString);

Create SQL Command:
SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "StoredProcedureName";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

Hope this helps!

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:niceguy971
ID: 36514945
it's a good example ..but it Does NOT answer my original question... Let me repeat my question:

I would like to use ODBC Data Source MyDSNName ( Data Source MyDSNName has been created as described at http://www.depts.ttu.edu/ithelpcentral/solutions/odbcsetup.php)

to create new SqlConnection..so MyDSNName should be included in the below:

SqlConnection conn = new SqlConnection(....MyDSNName....)

in classic asp: strConnection= "DSN= MyDSNName; Database= MyDBName; UID= MyUserID; PWD= MyPassword"

In Other words:

I would like to use the same DSN MyDSNName  {which has been created from Control Panel--->DataSources (ODBC)---> System DSN...and so on as described at at http://www.depts.ttu.edu/ithelpcentral/solutions/odbcsetup.php} to create new SqlConnection in C#.Net

What would be the proper syntax?
0
 
LVL 4

Expert Comment

by:asp_net2
ID: 36514952
Why do you wish to create a connection to SQL Server that way? If you are connecting to SQL Server than use the SQL Server Connection Class along with it's Methods.
0
 
LVL 4

Expert Comment

by:asp_net2
ID: 36514958
Not sure if this will work since I have never done it that way, but give this a shot and let me know if it works.

Modify your Connection in your web.config file to point to the name for your MyDSNName.

<add name="NameOfConnection" connectionString="Data Source=MyDSNName;Initial Catalog=YourDataBase;User ID=sa;Password=YourPassword" providerName="System.Data.SqlClient"/>
0
 
LVL 4

Expert Comment

by:Friman001
ID: 36515027
I am not at work right now, so I cannot give you specific examples from a real world example.  But, here is what I found for you.

private static void OpenSqlConnection()
{
    string connectionString = GetConnectionString();

    using (SqlConnection connection = new SqlConnection())
    {
        connection.ConnectionString = connectionString;

        connection.Open();

        Console.WriteLine("State: {0}", connection.State);
        Console.WriteLine("ConnectionString: {0}",
            connection.ConnectionString);
    }
}

static private string GetConnectionString()
{
    // To avoid storing the connection string in your code, 
    // you can retrieve it from a configuration file.
    return "Data Source=MSSQL1;Initial Catalog=AdventureWorks;"
        + "Integrated Security=true;";
}

Open in new window

0
 
LVL 4

Expert Comment

by:asp_net2
ID: 36515043
@Friman001:

Same thing above 36514900 but example in post 36514900 is easier and more widely used.
0
 
LVL 4

Expert Comment

by:Friman001
ID: 36515051
If need change GetConnectionString() to include the username and password.  On most SQL Servers, the username is sa.

static private string GetConnectionString()
{
    // To avoid storing the connection string in your code, 
    // you can retrieve it from a configuration file.
    return "Data Source=MSSQL1;Initial Catalog=AdventureWorks;"
        + "Integrated Security=true; User ID=sa; Password=Password$1";
}

Open in new window

0
 
LVL 4

Expert Comment

by:Friman001
ID: 36515058
I see that now.  That was actually what I would have shown him if I was at work (using the web.config to save the connection string and accessing it that way!)  Sorry.
0
 
LVL 4

Expert Comment

by:Friman001
ID: 36515066
@niceguy971: Basically, your DNS will become your Datasource, or the address you use to connect to the SQL database.
SO:
static private string GetConnectionString()
{
    // To avoid storing the connection string in your code, 
    // you can retrieve it from a configuration file.
    return "Data Source=MyDSNName;Initial Catalog=AdventureWorks;"
        + "Integrated Security=true; User ID=sa; Password=Password$1";
}

Open in new window


You really just needed to plug in the name with which you use to connect to SQL Server (You can use my version or asp_net2's, just so long as you plug in your server name with which you use as the Data Source portion.)  Also, you could try looking into the tools in the ToolBox if you use Visual Studio for a dev environment.  They have TableAdaptors which do the whole process of creating and accessing data from a database for you!
0
 
LVL 4

Expert Comment

by:Friman001
ID: 36515068
Initial Catalog would be the Database that has all your tables in it, btw!
0
 

Author Comment

by:niceguy971
ID: 36515417
Hi Friman001! Your example below is good. Back to my original question: Can I create Sql connection
like this  SqlConnection conn = new SqlConnection( Data Source=MyDSNName;Initial Catalog=AdventureWorks; Integrated Security=true; User ID=sa; Password=Password$1 ) ??

Is the above syntax correct?? Will it work?

---
static private string GetConnectionString()
{
    // To avoid storing the connection string in your code,
    // you can retrieve it from a configuration file.
    return "Data Source=MyDSNName;Initial Catalog=AdventureWorks;"
        + "Integrated Security=true; User ID=sa; Password=Password$1";
}
0
 
LVL 4

Assisted Solution

by:Friman001
Friman001 earned 1000 total points
ID: 36516311
You still need the quotes.

SqlConnection conn = new SqlConnection("Data Source=MyDSNName;Initial Catalog=AdventureWorks; Integrated Security=true; User ID=sa; Password=Password$1");

Open in new window


The part within the new SqlConnection(...) is called the connection string, so it needs to be in quotes, since it is a string!
0
 

Author Closing Comment

by:niceguy971
ID: 36532944
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month10 days, 18 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question