PDA

View Full Version : Connection string for SQL Express (in Visual Web Developer) // asp.net v2.0



arkitect101
04-20-2007, 05:15 PM
Hi guys,
This is quite a difficult topic so I appreciate it if no one knows the answer, but I welcome all comments.

OK so I am messing around with web applications and databases. I have the knowledge to create a connection string to the access database but do not know how to access SQL Express database which is now part of Visual Web Developer (the development software I am using).

I have quickly drafted up an aspx page with its counterpart C# page as follows:

[ASPX PAGE]

<%@ Page Language="C#" AutoEventWireup="true" Inherits="addContractor" CodeFile="Default.aspx.cs" %>
<html>
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Select a Category:
<asp:DropDownList ID="list1" runat="server" OnSelectedIndexChanged="ContractorList" AutoPostBack="true">

</asp:DropDownList><br />
<br />
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label><br />
<br />
Contractor Description:
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
Contractor Add1:
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
Contractor Add1:
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />
Contractor Add1:
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br />
Contractor Add1:
<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br />
Contractor Contact:
<asp:TextBox ID="TextBox6" runat="server"></asp:TextBox><br />
<br />
<asp:Button ID="Button1" runat="server" Text="Button" onClick="submitContractor"/></div>
</form>
</body>
</html>

[C# PAGE]

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;


public partial class addContractor : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

if (IsPostBack)
{
Label2.Text = "Category ID: " + list1.SelectedItem.Value;
}
else
{

Label2.Text = "Category ID: ";

rtvContractorID();

DateTime d = DateTime.Now;
Label1.Text = d.ToString("d");

OleDbConnection conn = new OleDbConnection();
string connStr;
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;";
connStr += "Data Source= " + Server.MapPath("App_Data/test.mdb");
conn.ConnectionString = connStr;

string queryString;
queryString = "SELECT CategoryID, CategoryDesc FROM Category";
OleDbCommand dbCommand = new OleDbCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = conn;

conn.Open();

OleDbDataReader dataReader = dbCommand.ExecuteReader();

list1.DataSource = dataReader;
list1.DataValueField = "CategoryID";
list1.DataTextField = "CategoryDesc";
list1.DataBind();


}
}

public void ContractorList (object sender, EventArgs e)
{
Label2.Text = "Category ID: " + list1.SelectedItem.Value;

}

protected void rtvContractorID()
{

Guid ContractorID = Guid.NewGuid();
Session["sContractorID"] = ContractorID;

}


protected void submitContractor(object sender, EventArgs e)
{


OleDbConnection conn;
conn = new OleDbConnection();
string connStr;
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;";
connStr += "Data Source= " + Server.MapPath("App_Data/test.mdb");
conn.ConnectionString = connStr;

string queryString4;
queryString4 = "insert into Contractor (ContractorID, CategoryID, ContDateAdded, ContDesc, Add1, Add2, Add3, Add4, ContactTel) values (";
queryString4 += "'" + Convert.ToString(Session["sContractorID"]) + "', '";
queryString4 += Convert.ToString(list1.SelectedItem.Value) + "', '";
queryString4 += Label1.Text;
queryString4 += "', '";
queryString4 += TextBox1.Text;
queryString4 += "', '";
queryString4 += TextBox2.Text;
queryString4 += "', '";
queryString4 += TextBox3.Text;
queryString4 += "', '";
queryString4 += TextBox4.Text;
queryString4 += "', '";
queryString4 += TextBox5.Text;
queryString4 += "', '";
queryString4 += TextBox6.Text;
queryString4 += "')";

OleDbCommand dbCommand4 = new OleDbCommand();
dbCommand4.CommandText = queryString4;
dbCommand4.Connection = conn;
conn.Open();
dbCommand4.ExecuteNonQuery();

conn.Close();
Server.Transfer("default.aspx");

}
}

So basically what I am asking is instead of connecting to an access database, I want to connect to an SQL express database. This requires modifcation in the connection string. If you know then please explain.

Thanks.

bikithalee
06-05-2012, 06:37 PM
The SqlConnection Object is Handling the part of physical communication between the ASP.NET application and the SQL Server Database

Make a connection string and add it in web.config file

<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="SQLDbConnection"
connectionString="Server=servername; Database=pubs; User Id=username; password=password"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>




and write the following code to your asp.net page

string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
Label1.Text = "Connected to Database Server !!";
connection.Close();

http://asp.net-informations.com/data-providers/sql-connection.htm

Please check above link to full source code