CODEWITHSUNDEEP
c#sqlasp.netazureconnection-string
user7554035
user7554035

Reputation: 399

'MultipleActiveResultsSets' Keyword Not Supported

I am trying to read from an SQL Server database which is hosted on MS Azure, through an ASP.NET WebForms website created in Visual Studio 2013.

I've stored the Connection String in Web.Config, and have referenced it in my Code-Behind.

However, when I try to run Default.aspx locally, this error is displayed.

Here is my Web.Config:

  <connectionStrings>
     <add name="FYPConnectionString1" 
     connectionString="Data Source=damo.database.windows.net‌​;Initial Catalog=Ballinora_db;         
     Persist Security Info=True; User ID={Username};Password={Password};" />
  </connectionStrings>

I removed "MultipleActiveResultsSets=False" from the Connection String to see if the error stopped, but instead, the error now displays for "Encrypt".

So the error is appearing for the next item after the Password part of the connection string. Would the password have anything to do with the problem?

Also, this username and password which are required, are they the Server Admin Login details which appear in the Azure portal?

Here is the Code-Behind also:

private void bindRepeater()
{
    string constr = ConfigurationManager.ConnectionStrings["FYPConnectionString1"].ConnectionString;  
    //-- assuming Azure connection string stored in ConnectionString config in Web.Config as YourConnString 
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT Name FROM Users", con))
        {
            cmd.CommandType = CommandType.Text;
            con.Open();
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            repTest.DataSource = dt;
            repTest.DataBind();
            con.Close();
        }
    }
}

protected void btnDisplay_Click(object sender, EventArgs e)
{
    this.bindRepeater();
}

Upvotes: 9

Views: 26906

Answers (6)

Jon
Jon

Reputation: 1180

In my case had the string

multiple active result sets=True

been added in addition to the correct string

MultipleActiveResultSets=True

This happened in a .NET 4.8 project while using Visual Studio 2022 when changing server from production name to localhost with the help of the Update wizardUpdate Wizard

I solved it by removing multiple active result sets=True from App.config/Web.config.

Upvotes: 0

Aladein
Aladein

Reputation: 312

in my case i deleted

MultipleActiveRe‌​‌​sultSets=True

from connectionString in web.config

and its work fine.

Upvotes: 0

raab
raab

Reputation: 46

I also had this problem. For me the password that I had created contained a semicolon and the password was leaking into the next part of the connection string.

I removed the semicolon in the password and it worked.

Eg the end of the password was "j;.&6". The error I was getting was

"- $exception {"Keyword not supported: '**.&6;**multipleactiveresultsets'."} System.ArgumentException"

Upvotes: 0

IronSean
IronSean

Reputation: 1578

Note for others finding this question: This can also happen if you forget to choose the proper Type (data provider) in the Azure configuration for the connection string. It happened for me with MySQL selected instead of SQLAzure. Since the keyword isn't on that provider it causes an error.

enter image description here

Upvotes: 4

KOB
KOB

Reputation: 1216

You mistyped "MultipleActiveResultsSets". The "Result" in it is not plural.

Correct way: "MultipleActiveResultSets".

Upvotes: 13

Kay Lee
Kay Lee

Reputation: 952

The default value of MultipleActiveRe‌​‌​sultSets is False.

And if you don't need the function, just get out (delete) ' MultipleActiveRe‌​‌​sultSets=False ' of connectionstring.

Because the default value is false. If you want false, you don't need to write it by intention.

We need the function of MultipleActiveRe‌​‌​sultSets as we open second SQL connection inside while loop of SqlDataReader of the first SQL connection.

However, why ' MultipleActiveRe‌​‌​sultSets=False ' is recognized as wrong syntax is still question.

Update-

That's why I was curious why it was wrong. I think the syntax for MultipleActiveRe‌​‌​sultSets and Encrypt don't have problem.

Here is my connectionString in Web.Config:

<connectionStrings>
     <add name="GreenLeaf"  connectionString="Server=tcp:greentree.database.secure.windows.net,1433;Database=greentea;User ID=greenusers@greentree;Password=abc123;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;MultipleActiveResultSets=True;"/>
</connectionStrings>

The differences with yours are,

1) tcp 2) secure 3) @greentree (the Servername greentree together with ID like (greenusers@greentree) 4) without provider

'secure' is new feature that Azure automatically provide secure connection and you can find about this on internet.

Check the firewall of Server and just try to give a shot with above points.

Using the admin ID is correct but in aspect of security, you need to create additional Login and User for external users with limited roles and permissions.

Can you connect to Azure Server(Database) through Azure Portal? Visual Studio? Server Management Studio?

When you enter Server Management Studio, in the pop-up for connection, there's 'Server Name'. Input 'greentree.database.windows.net' there and try to login with your admin credentials.

Or can you connect to Azure Server without connectionString in Web.Config but in plain code?

And if possible, please update with the screencapture of exception, not the text only.

And for last, I'm sure you know very well, we're needed to encrypt the connectionString later for security.

Upvotes: 0

Related Questions