[ASP] - Problem with connection string

[ludderz]

Heya all

I have a website that has been fully developed in asp. The website is database driven (sql) and is working fine. The website has a search feature that works fine however we are looking to re-build it to make it more user friendly. Not being very good at asp i am begining my experimentation with connecting to the database however it does not want to play ball.

I am using the following ConnectionString

ConnString="Driver={SQL Server};Server=server\server;Database=databasename;Uid=server\Administrator;Pwd=ourpassword;"

Obviously the various areas in the string are filled in properly as best i know (we were never provided with the full details) but i am getting the following message / error when i run the page.

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'

[Microsoft][ODBC SQL Server Driver]

Login failed for user 'server\Administrator'.

/connect7.asp, line 19

After getting this message i done some research and many people are saying that the user does not have access to the sql database or something like that however the developed website is working without any problems and it must be connecting somehow.

The questions i have are:

1. Am i doing something stupid and is there a simple solution i have missed?
   
2. If i am not doing something stupid how do the current pages connect to the database?
   

I am looking forward to any replies. Thanks in advance :)

Ludz~

[Antaris Veteran]

http://msdn.microsoft.com/en-us/library/aa337562.aspx

Check to make sure you have actually created a login for that user.

[ludderz]

Antaris, Thank you

I have managed to create a connection and extract the information via a .asp page. After doing so i realise it would be a better idea to use .aspx (asp.net) in the long run however i am having issues finding some code examples to help me through. Im looking for something that connects to the database, and lists all information from a specific table, maybe using a repeater from what im reading.

Anyone have any ideas or examples i could use?

Ludz~

[ludderz]

Update: Below i have provided the asp code i use to connect to the our current database and extract the information. What i would like is a replication of this that works with asp.net (.aspx). If anyone could point me in the right direction or provide some code snipets for me it would be a great help.

<%

'declare the variables

Dim Connection

Dim ConnString

Dim Recordset

Dim SQL

'define the connection string, specify database driver

ConnString="connection string code removed"

'declare the SQL statement that will query the database

SQL = "SELECT * FROM AllJobs"

'create an instance of the ADO connection and recordset objects

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

Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database

Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records

Recordset.Open SQL,Connection

'first of all determine whether there are any records

If Recordset.EOF Then

Response.Write("No records returned.")

Else

'if there are records then loop through the fields

Do While NOT Recordset.Eof

Response.write Recordset("JobID")

Response.write "<br>"

Response.write Recordset("UserID")

Response.write "<br>"

Response.write Recordset("Title")

Response.write "<br>"

Response.write Recordset("PostedDate")

Response.write "<br>"

Response.write Recordset("LastUpdated")

Response.write "<br>"

Response.write Recordset("ValidTo")

Response.write "<br>"

Response.write Recordset("Description")

Response.write "<br>"

Response.write Recordset("CountryID")

Response.write "<br>"

Response.write Recordset("Location")

Response.write "<br>"

Response.write Recordset("ExperienceID")

Response.write "<br>"

Response.write Recordset("NoOfVaccancies")

Response.write "<br>"

Response.write Recordset("JobTypeID")

Response.write "<br>"

Response.write Recordset("JobRoleID")

Response.write "<br>"

Response.write Recordset("JobDurationID")

Response.write "<br>"

Response.write Recordset("SalaryTypeID")

Response.write "<br>"

Response.write Recordset("MinimumEducationID")

Response.write "<br>"

Response.write Recordset("ActiveFlag")

Response.write "<br>"

Response.write Recordset("Validity")

Response.write "<br>"

Response.write Recordset("HotJob")

Response.write "<br>"

Response.write "<br>"

Recordset.MoveNext

Loop

End If

'close the connection and recordset objects to free up resources

Recordset.Close

Set Recordset=nothing

Connection.Close

Set Connection=nothing

%>

Ludz~

[AdzzzUK]

What software are you using to develop this in? I'd personally store the connection string in the web.config file and use a datagrid control (which is supported in ASP.net) to display the results in.

Just my $0.02...

Ad

[ludderz]

Ad

I am generaly using notepad to develop the files, is there something you would reccomend to make it easier? Also as far as the connection to the database i like to work from a flat file to begin with so i can keep all the errors / issues in one place. As i said earlier parts of the site are already working and the parts i am re-making i would to keep seperate until completed.

So any ideas?

Ludz~

[Antaris Veteran]

This is how I generally prepare connection strings in ASP.NET:

<appSettings>
	<add name="SqlServer" value="Development" />
</appSettings>
<connectionStrings>
	<add name="Development" providerName="SqlClient" connectionString="DEVELOPMENT_SERVER_CONNECTION_STRING"/>
	<add name="Production" providerName="SqlClient" connectionString="PRODUCTION_SERVER_CONNECTION_STRING"/>
</connectionStrings>

public SqlConnection GetConnection()
{
	string server = WebConfigurationManager.AppSettings["SqlServer"];
	string connection = WebConfigurationManager.ConnectionStrings[server].ConnectionString;

	SqlConnection conn = new SqlConnection(connection);
	return conn;
}

public DataSet GetSomeDataFromServer()
{
	using (SqlCommand command = new SqlCommand("TABLE/VIEW/PROCEDURE", conn))
	{
		SqlDataAdapter adapter = new SqlDataAdapter(command);
		DataSet data = new DataSet(); adapter.Fill(data);

		return data;
	}
}

Your choice in .NET control varies, depending on the level control you want. Although you can use DataGrids, I prefer to do the construction manually through Repeaters, e.g.:

<asp:Repeater ID="repeat_Data" runat="server">
	<ItemTemplate>
		<asp:Literal ID="lit_Name" runat="server" Text='<%# Bind("FIELD_FROM_DATASET") %>' />
	</ItemTemplate>
</asp:Repeater>

public void Page_Load(object sender, EventArgs e)
{
	DataSet data = GetSomeDataFromServer();
	repeat_Data.DataSource = data;
	repeat_Data.DataBind();
}

This of course is my personal preference, a lot of people enjoy the fidelity you get from using the VS designer to manage all of this.

I did this as a C# example, because (although I know VB.NET), I prefer C# :p

[ludderz]

Ant

Thanks again for the information however is there any chance you could post the code for one full .aspx page that connects to the database at the top and displays information from the database that is being connected to? I am trying to use the code snippets you have provided but do not seem to be having much luck.

A full working page would be a great help

Ludz~

[Antaris Veteran]

Sample01.zip

Hows that.

[ludderz]

I am just heading out Ant but i will be playing with it as soon as i can, thank you so much for the time and effort invested. I will keep you posted on my progress, probaly via this thread.

Thanks Again

Ludz~

[ludderz]

Ant

I have been playing with the pages and code you sent over however i am having real issues getting it working. The server already has a website as stated before and the web.config file must be different to the one you provided as i cannot make it work.

Is there anyway to do this in one flat file? Just one file that connects to the database at the top, runs an sql query and then displays the results of that on the page? All being done in asp.net (.aspx)? Please tell me there is a way..

Any help is much apreciated and an example page would be a great help if its possible.

Ludz~

[Antaris Veteran]

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
	protected void Page_Load(object sender, EventArgs e)
	{
		string connection = "<connection string here>";
		System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connection);
		conn.Open();

		using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("SELECT * FROM AllJobs", conn))
		{
			System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(command);
			System.Data.DataSet data = new System.Data.DataSet(); adapter.Fill(data);

			repeat_Data.DataSource = data;
			repeat_Data.DataBind();
		}

		conn.Close();
	}
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
	<title>Untitled Page</title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
		<asp:Repeater ID="repeat_Data" runat="server">
			<ItemTemplate>
				<asp:Literal ID="lit_Title" runat="server" Text='<%# Bind("Title") %>' />
			</ItemTemplate>
		</asp:Repeater>
	</div>
	</form>
</body>
</html>

Many (including myself) feel its bad design and bad practice to code pages like this. Seperating code from presentation is a key goal you will come to realise ;)

[ludderz]

Thank you for the code Ant it has been a great help. However guess what! I have run into another little hitch....

For the connection string i have inputer the exact string used in the current web.config file so i figure that it is correct as it is powering the rest of the website. When i run the page however i get this error.

Compiler Error Message: CS1009: Unrecognized escape sequence

After looking up the error online it seemed it was caused by the "\" in the servername.

string connection = "Server=S15273662\S15273662;UID=....

So i changed it to..

string connection = "Server=S15273662;UID=....

In hope that it would work however it now throws a new error and when i look that up most people are saying that the error is because the connection string is wrong.

Am i displaying the server name wrong or is there something small i need to tweak to have it work? Or could it be something completly different?

Ludz~

[Antaris Veteran]

In C# (like many other C-like languages), it doesn't accept the slash '\' character on it's own, unless its an escaped string.

You can do 1 of 2 things:

string connection = "SERVER=<servername>\\<instance>;DATA...

or

string connection = @"SERVER=<servername>\<instance>;DATA...

[Josh]

This is slightly off topic but related to some posted code.

Antaris, as far as this example is concerned, why are you using a DataAdapter/DataSet instead of just a DataReader which is much faster and requires much less overhead for a read-only scenario.

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
	protected void Page_Load(object sender, EventArgs e) {

		using (SqlConnection Conn = new SqlConnection(<connection string here>)) {
			using (SqlCommand Cmd = new SqlCommand("SELECT Title FROM AllJobs", Conn)) {
				Conn.Open();
				repeat_Data.DataSource = Cmd.ExecuteReader();
				repeat_Data.DataBind();
			} //end using sqlcommand
		} //end using sqlconnection

	}

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
	<title>DataReader</title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
		<asp:Repeater ID="repeat_Data" runat="server">
			<ItemTemplate>
				<%# Eval("Title") %><br />
			</ItemTemplate>
		</asp:Repeater>
	</div>
	</form>
</body>
</html>

[Antaris Veteran]

No reason in particular, just habit I guess.

[Josh]

Okey dokey. I figured as much, but wanted to ask.

For the OP... Using the DataReader for read-only parts of large applications makes a HUGE difference in page load times, especially when binding data to multiple bound controls in a page. You'll also want to steer away from "SELECT * FROM..." statements if you're actually using the data being returned from the database for similar reasons.

[ludderz]

Ant

Thanks for the help mate it has worked perfectly, now that i have a foundation i can see without getting a load of errors i hope i can construct the rest of the page myself. It has given me a great boost, Thanks again.

Josh

Thanks for the advice, after i get something together that i am happy with i will work on optimizing it.