CODEWITHSUNDEEP
sql-serverdatabaseazureasp-classic
user3317155
user3317155

Reputation: 21

classic asp and sql database azure

We are migrating to Azure and have one app that is still in classic ASP. We have migrated the database that it needs to communicate with to SQL database in Azure.

Looking for guidance or examples on how to get classic ASP to connect with SQL database.

Here's what we have now.

ConnString = "DRIVER=SQL Server Native Client 10.0;Server=servername.database.windows.net,1433;Database=azuredb;UID=user@serve‌ rname;PWD=password;Encrypt=yes;TrustServerCertificate=no;" 

Set Conn = CreateObject("ADODB.Connection") 
Conn.Open ConnString 
set rs=Server.CreateObject("ADODB.recordset") 
SQL = "SELECT * from table" 
rs.Open( SQL )

Connection String (for readability)

SQL Server Native Client 10.0;Server=servername.database.windows.net,1433;
Database=azuredb;UID=user@serve‌ rname;PWD=password;Encrypt=yes;
TrustServerCertificate=no;

With the above connection we get the following error

Microsoft OLE DB Provider for ODBC Drivers error '80004005' 
[Microsoft][ODBC Driver Manager] 
Data source name not found and no default driver specified

We are running this in a VM running Server2008 R2

Upvotes: 1

Views: 3990

Answers (6)

Salih
Salih

Reputation: 17

For classic ASP:

Set Con = server.createobject("ADODB.Connection") Con.Open "Provider=SQLOLEDB;Server=XXXXX.database.windows.net,1433;User ID=XXXXX;Password=XXXXX;Initial Catalog=XXXXX;"

you must change XXXXX to your infos.

Upvotes: 0

DrewWash
DrewWash

Reputation: 11

Experienced the same issue. While installing the correct SQL native client as another answer mentions may be the ideal fix, the quick fix is to simply change the driver to "SQL Server".

For example from Driver={SQL Server Native Client 10.0}; to Driver={SQL Server};

In your case your connection string should be:

ConnString = "Driver={SQL Server};Server=servername.database.windows.net,1433;Database=azuredb;UID=user@serve‌ rname;PWD=password;Encrypt=yes;TrustServerCertificate=no;"

Upvotes: 1

Rashid Mirza
Rashid Mirza

Reputation: 1

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Volta_Reporting_Application.DBL
{
    public class DBHandler
    {
        public SqlConnection _SqlConnection { get; set; }
        public String _SqlConnectionString { get; set; }
        public DataSet _DataSet { get; set; }
        public List<SqlCommand> _CommandList { set; get; }
        public DBHandler()
        {
            //_SqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; ;
            _SqlConnectionString = Helpers.Helper.GetConnectionString();
        }
        public bool OpenConnection()
        {
            
                _SqlConnection = new SqlConnection(_SqlConnectionString);
            if (SqlConnection != null && SqlConnection.State == ConnectionState.Closed)
            {
               
                _SqlConnection.Open();
            }
   .Open);
        }

        public bool CloseConnection()
        {
            if (SqlConnection != null && SqlConnection.State == ConnectionState.Open)
                _SqlConnection.Close();
            if (_SqlConnection != null)
                _SqlConnection.Dispose();
            return _SqlConnection == null;
        }
        public object ExecuteMyCommand(SqlCommand cmd) 
        {
            bool res = false;
            try
            {
                OpenConnection();
                cmd.Connection = _SqlConnection;
                if (cmd.ExecuteNonQuery() > 0)
                {
                    res = true;
                }
            }
            catch (Exception)
            {

                res = false;
            }
            finally 
            {
                CloseConnection();
            }
            return res;
        }

        public object CRUD(string Query, char operation = 'c')
        {
            operation = char.Parse(operation.ToString().ToLower());
            object res = null;
            try
            {
                OpenConnection();
                SqlDataAdapter da = new SqlDataAdapter();
                switch (operation)
                {
                    case 'c':
                    case 'i':
                        da.InsertCommand = _SqlConnection.CreateCommand();
                        da.InsertCommand.CommandText = Query;
                        da.InsertCommand.ExecuteNonQuery();
                        res = true; 
                        break;
                    case 'z':
                        da.SelectCommand = _SqlConnection.CreateCommand();
                        da.SelectCommand.CommandText = Query;
                        return da.SelectCommand.ExecuteScalar();
                    case 's':
                    case 'r':
                        DataSet ds = new DataSet();
                        da.SelectCommand = _SqlConnection.CreateCommand();
                        da.SelectCommand.CommandText = Query;
                        //da.SelectCommand.ExecuteReader();
                        da.Fill(ds);
                        res = ds;
                       //ds.Dispose(); 
                        break;
                    case 'u':
                        da.UpdateCommand = _SqlConnection.CreateCommand();
                        da.UpdateCommand.CommandText = Query;
                        res=da.UpdateCommand.ExecuteNonQuery();
                        break;
                    case 'd':
                        da.DeleteCommand = _SqlConnection.CreateCommand();
                        da.DeleteCommand.CommandText = Query;
                        da.DeleteCommand.ExecuteNonQuery();
                        res = true;
                        break;
                    default: break;
                }
                if (res == null)
                    res = false;
            }
            catch (Exception)
            {
                res = null;
            }
            finally
            {
                CloseConnection();
            }
             return res;
        }
    }
}

Upvotes: -1

user3317155
user3317155

Reputation: 21

The answer is that you must install the SQL native client.

http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409

Upvotes: 1

mohamed iqbal
mohamed iqbal

Reputation: 1

use the following connection string

Driver={SQL Server Native Client 10.0};Server=tcp:[serverName].database.windows.net;
Database=myDataBase;Uid=[LoginForDb]@[serverName];Pwd=myPassword;Encrypt=yes;

see the link.

Upvotes: -1

Simon W
Simon W

Reputation: 5496

You will most likely need to use ODBC to achieve a connection. Details can be found on MSDN: http://msdn.microsoft.com/en-us/library/azure/hh974312.aspx

Upvotes: -2

Related Questions