7 Replies - 36414 Views - Last Post: 21 April 2009 - 11:12 AM Rate Topic: -----

#1 DbqSmurf  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 13-April 09

ASP.NET MySQL web.config connection

Post icon  Posted 20 April 2009 - 02:53 PM

I am trying to use a connection string from a web.config file and not hard code the connection string into the code behind page. I DO NOT WANT OCDB or OLE DB strings I AM using mySql.NET connector

web.config file:

<connectionStrings>
<add name="BMREConnString" connectionString="Server=;Port=;Database=;Uid=;Pwd=;pooling=false;" providerName="MySql.Data.MySqlClient"  />
</connectionStrings>



and the behind code is:
	myConnection = ConfigurationManager.ConnectionStrings["BMREConnString"].ConnectionString



but I get the following errors:
BC30311: Value of type 'System.Configuration.ConnectionStringSettingsCollection' cannot be converted to
'MySql.Data.MySqlClient.MySqlConnection'.

Philip

Is This A Good Question/Topic? 0
  • +

Replies To: ASP.NET MySQL web.config connection

#2 PsychoCoder  Icon User is offline

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

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

Re: ASP.NET MySQL web.config connection

Posted 20 April 2009 - 03:29 PM

I have a feeling what's happening here. I bet myConnection is your MySqlConnection object. Getting the connection string from the web.config returns the connection string, not a connection object, thus the error. The connection string is passed to the connection object telling it what to open and such. Try this

string connString = ConfigurationManager.ConnectionStrings["BMREConnString"].ConnectionString;
MySqlConnection myConnection = new MySqlConnection(connString);



From there you can use it as any other connection object :)
Was This Post Helpful? 0
  • +
  • -

#3 DbqSmurf  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 13-April 09

Re: ASP.NET MySQL web.config connection

Posted 20 April 2009 - 06:14 PM

View PostPsychoCoder, on 20 Apr, 2009 - 02:29 PM, said:

I have a feeling what's happening here. I bet myConnection is your MySqlConnection object. Getting the connection string from the web.config returns the connection string, not a connection object, thus the error. The connection string is passed to the connection object telling it what to open and such. Try this

string connString = ConfigurationManager.ConnectionStrings["BMREConnString"].ConnectionString;
MySqlConnection myConnection = new MySqlConnection(connString);



From there you can use it as any other connection object :)


Now I get the Error.

BC30311: Value of type 'System.Configuration.ConnectionStringSettingsCollection' cannot be converted to 'String'.

I thought the point of putting the connection string in the web.config file was to use it from there for all the pages that need database access and not just as a string for the mysqlconnection request. Never done it this way before (never had enough pages with database connections to need it) so any help would be great.
Philip
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

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

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

Re: ASP.NET MySQL web.config connection

Posted 20 April 2009 - 06:27 PM

Show the code you're using because the example I gave came directly from my website, and this is how it looks

/// <summary>
/// method to retrieve the database connection string from the web.config
/// </summary>
/// <param name="name">name of the connection string we want (this allows us to have multiple if needed)</param>
/// <returns></returns>
public string GetConnectionString(string name)
{
	try
	{
		//variable to hold our connection string for returning it
		string connString = string.Empty;
		//check to see if the user provided a connection string name
		//this is for if your application has more than one connection string
		if (!string.IsNullOrEmpty(name)) //a connection string name was provided
		{
			//get the connection string by the name provided
			connString = ConfigurationManager.ConnectionStrings[AntiXss.HtmlEncode(name)].ConnectionString;
		}
		else //no connection string name was provided
		{			
			//get the default connection string
			connString = ConfigurationManager.ConnectionStrings["XXXX"].ConnectionString;
		}
		_status = true;
		//return the connection string to the calling method
		return connString;
	}
	catch (Exception ex)
	{
		_message = ex.Message;
		_status = false;
		return string.Empty;
	}
}


Was This Post Helpful? 0
  • +
  • -

#5 DbqSmurf  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 13-April 09

Re: ASP.NET MySQL web.config connection

Posted 20 April 2009 - 06:36 PM

the full code is

<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" runat="server">

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
	Dim iRecordCount  As Integer 
	Dim connString	 As String
connString = ConfigurationManager.ConnectionStrings["BMREConnString"].ConnectionString
myConnection = new MySqlConnection(connString)

'the following line works not commented out, just not reading from the web.config file.

'myConnection = New MySqlConnection("Server=;Port=;Database=;Uid=;Pwd=;")
	strSQL = "SELECT * FROM Agents;"

	myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
	myDataSet = New Dataset()
	myDataAdapter.Fill(myDataSet, "Agents")

	MySQLDataGrid.DataSource = myDataSet
	MySQLDataGrid.DataBind()

End Sub
</script>



Thanks

This post has been edited by DbqSmurf: 20 April 2009 - 06:36 PM

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: ASP.NET MySQL web.config connection

Posted 20 April 2009 - 06:44 PM

Try it this way and see if this helps

connString = ConfigurationManager.ConnectionStrings("BMREConnString").ToString()
myConnection = new MySqlConnection(connString)



I cant figure out why you're getting these errors on this.
Was This Post Helpful? 0
  • +
  • -

#7 DbqSmurf  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 13-April 09

Re: ASP.NET MySQL web.config connection

Posted 20 April 2009 - 06:51 PM

this is what i get when i do that. I tried it earlier

BC30311: Value of type 'System.Configuration.ConnectionStringSettingsCollection' cannot be converted to 'String'.
Was This Post Helpful? 0
  • +
  • -

#8 DbqSmurf  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 13-April 09

Re: ASP.NET MySQL web.config connection

Posted 21 April 2009 - 11:12 AM

View PostPsychoCoder, on 20 Apr, 2009 - 02:29 PM, said:

I have a feeling what's happening here. I bet myConnection is your MySqlConnection object. Getting the connection string from the web.config returns the connection string, not a connection object, thus the error. The connection string is passed to the connection object telling it what to open and such. Try this

string connString = ConfigurationManager.ConnectionStrings["BMREConnString"].ConnectionString;
MySqlConnection myConnection = new MySqlConnection(connString);



From there you can use it as any other connection object :)



This is the answer for anyone looking on the forum. It wasn't working yesterday but now it works this afternoon... not sure why!!

(yes i am for VB it needs to be ConfigurationManager.ConnectionStrings("BMREConnString").ConnectionString; not ConfigurationManager.ConnectionStrings["BMREConnString"].ConnectionString;)

I did a response.write(connstring) to see what is was writing today and it was the conn string it need to be so it works now...

I HATE MICROSOFT (the makers of asp.net...and BSOD)!!! thats my problem...

Thanks for your help PsychoCoder
Philip

This post has been edited by DbqSmurf: 21 April 2009 - 11:21 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1