8 Replies - 17958 Views - Last Post: 16 July 2012 - 03:06 AM Rate Topic: -----

#1 hemo  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 70
  • Joined: 02-January 07

Web.Config connection to Remote Mysql database

Posted 09 July 2010 - 04:59 AM

I am currently accessing my remote Mysql database on each aspx.page
 Sub Page_Load(sender As Object, e As EventArgs)
    Dim myConnection  As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataSet     As DataSet
    Dim strSQL        As String
       
    myConnection = New MySqlConnection("Database=mydata;Data       Source=xx.xxx.xx.x;port=xxxx;UId=username;Password=1234;")   
    strSQL = "SELECT * FROM data"
    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
    myDataSet = New Dataset()
    myDataAdapter.Fill(myDataSet,"data")

    GridView1.DataSource = myDataSet
    GridView1.DataBind()
End Sub



I am trying to connect the remote database using a web.config file

 <configuration>
<connectionStrings>
        <add name=" ConnStringName" connectionString="server-xx.xxx.x.x;uid=username;pwd=1234;database=mydata"
            providerName="MySql.Data.MySqlClient" />
    </connectionStrings>
</configuration> 



If my web.config string is correct, can anyone help accessing it from any aspx.page either in a sub or body.

Is This A Good Question/Topic? 0
  • +

Replies To: Web.Config connection to Remote Mysql database

#2 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 203
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: Web.Config connection to Remote Mysql database

Posted 09 July 2010 - 08:10 AM

After you have added the connection string to the web.config file (as you have) you need to retrieve it in your code. To do this you use the ConfigurationManager class to retrieve the connection string in your C# or VB.NET code...specifically you use the ConfigurationManager.ConnectionStrings Property to retrieve the connection string.

For example:
Dim conString As String 
If ConfigurationManager.ConnectionStrings("ConnStringName") IsNot Nothing Then
  conString = ConfigurationManager.ConnectionStrings("ConnStringName").ConnectionString
  myConnection = New MySqlConnection(conString)
End If



The nice thing about storing your connection strings in the web.config file is that you can use the aspnet_regiis tool to encrypt this sensitive information so that it is not stored as plain text for anyone to view (see this MSDN article on how to secure connection strings for more information on how to do this[/url])

-Frinny
Was This Post Helpful? 1
  • +
  • -

#3 hemo  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 70
  • Joined: 02-January 07

Re: Web.Config connection to Remote Mysql database

Posted 10 July 2010 - 02:01 AM

Thank you for your helpful response and my apologies for the delay in replying.

I have used your example in the page load sub ,

Sub Page_Load(sender As Object, e As EventArgs)
    Dim conString as String
    Dim myConnection  As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataSet As DataSet
    Dim strSQL As String
If ConfigurationManager.ConnectionStrings("ConnStringName")
    IsNot Nothing Then
    conString =
    ConfigurationManager.ConnectionStrings("ConnStringName").ConnectionString
    myConnection = New MySqlConnection(connString)
 End If
           
    strSQL = "SELECT * FROM images"
    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
    myDataSet = New Dataset()
    myDataAdapter.Fill(myDataSet,"images")

    GridView1.DataSource = myDataSet
    GridView1.DataBind()
End Sub



But get error “System.Configuration.ConnectionStringSettings' cannot be converted to 'Boolean”

If this is removed only using the connection string.

ConfigurationManager.ConnectionStrings("ConnStringName").ConnectionString
myConnection = New MySqlConnection(connString) 



Returns an error “Object reference not set to an instance of an object”

My aspx.page Imports are:
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>


Is there a reference missing?
Was This Post Helpful? 0
  • +
  • -

#4 hemo  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 70
  • Joined: 02-January 07

Re: Web.Config connection to Remote Mysql database

Posted 10 July 2010 - 02:01 AM

Thank you for your helpful response and my apologies for the delay in replying.

I have used your example in the page load sub ,

Sub Page_Load(sender As Object, e As EventArgs)
    Dim conString as String
    Dim myConnection  As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataSet As DataSet
    Dim strSQL As String
If ConfigurationManager.ConnectionStrings("ConnStringName")
    IsNot Nothing Then
    conString =
    ConfigurationManager.ConnectionStrings("ConnStringName").ConnectionString
    myConnection = New MySqlConnection(connString)
 End If
           
    strSQL = "SELECT * FROM images"
    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
    myDataSet = New Dataset()
    myDataAdapter.Fill(myDataSet,"images")

    GridView1.DataSource = myDataSet
    GridView1.DataBind()
End Sub



But get error “System.Configuration.ConnectionStringSettings' cannot be converted to 'Boolean”

If this is removed only using the connection string.

ConfigurationManager.ConnectionStrings("ConnStringName").ConnectionString
myConnection = New MySqlConnection(connString) 



Returns an error “Object reference not set to an instance of an object”

My aspx.page Imports are:
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>


Is there a reference missing?
Was This Post Helpful? 0
  • +
  • -

#5 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 203
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: Web.Config connection to Remote Mysql database

Posted 12 July 2010 - 08:54 AM

There is no reference missing.
I'm sorry but I'm not sure what to suggest.
This should work for you.


Try creating a new project, add the connection string to the web.config...and try retrieving it using the ConfigurationManager. It might be some sort of configuration issue with your current project.

-Frinny
Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Web.Config connection to Remote Mysql database

Posted 12 July 2010 - 09:00 AM

Well in your web.config you have the name specified at name=" ConnStringName" so either remove that space or call it like this in your code

ConfigurationManager.ConnectionStrings(" ConnStringName").ConnectionString


Was This Post Helpful? 1
  • +
  • -

#7 hemo  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 70
  • Joined: 02-January 07

Re: Web.Config connection to Remote Mysql database

Posted 12 July 2010 - 03:33 PM

Thank you PsychoCoder for pointing out my carelessness. The space in the web.config was causing the connection problem. However, there remains an error with the conditional If statement, ‘cannot be converted to Boolean. I will try as Frinny suggestion with a new project.

Thank you both for your help

hemo
Was This Post Helpful? 0
  • +
  • -

#8 athena_bucket  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 16-July 12

Re: Web.Config connection to Remote Mysql database

Posted 16 July 2012 - 02:16 AM

Hello,
Did you find a solution to this exception --> System.NullReferenceException: Object reference not set to an instance of an object. at _Default.Button2_Click(Object sender, EventArgs e) ??????
Was This Post Helpful? 0
  • +
  • -

#9 hemantwithu  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 5
  • View blog
  • Posts: 31
  • Joined: 18-October 11

Re: Web.Config connection to Remote Mysql database

Posted 16 July 2012 - 03:06 AM

Hi I think you have defined the Connection String in the Web.Config.If you want to get the Connection String from the web.Config you need to add the NameSpace "System.Configuration".

using(SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionString["TestCon"].ConnectionString))
{
//Code 
}



As given above you need to get the Connection from the web.config.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1