how to use App_Data databases

Posted by mohammedr88-18313 under ASP.NET on 3/7/2013 | Points: 10 | Views : 13958 | Status : [Member] | Replies : 7
hi is there any possible to use App_Data sql database ie insert , select , update ect

i have used the connection string below

<add name="connectionstring"
connectionString="data source=.\SQLEXPRESS2008R2;Integrated Security=true;AttachDBFilename=app_dataLocation\database.mdf;User Instance=true"
providerName="System.Data.SqlClient" />
</connectionStrings>


thanks in adavance




Responses

Posted by: Satyapriyanayak on: 3/7/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
//SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter ad = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataTable dataTable;
SqlDataAdapter sqlda;
DataSet ds;
string str;
protected void Page_Load(object sender, EventArgs e)
{
Session["sortBy"] = null;
if (!IsPostBack)
{
FillVendorGrid();
}
}
private void FillVendorGrid()
{
SqlConnection conn = new SqlConnection(connStr);
dataTable = new DataTable();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM Vendor";
ad = new SqlDataAdapter(cmd);
ad.Fill(dataTable);
ResultGridView.DataSource = dataTable;
ResultGridView.DataBind();

}

protected void ResultGridView_RowEditing(object sender, GridViewEditEventArgs e)
{
ResultGridView.EditIndex = e.NewEditIndex;
FillVendorGrid();
}


protected void ResultGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
ResultGridView.PageIndex = e.NewPageIndex;
FillVendorGrid();
}

protected void ResultGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
SqlConnection conn = new SqlConnection(connStr);
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM Vendor WHERE VendorId='" + ResultGridView.DataKeys[e.RowIndex].Values[0].ToString() + "'";
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
FillVendorGrid();

}

protected void ResultGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtFName = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtFName");
TextBox txtLName = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtLName");
TextBox txtCity = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtCity");
TextBox txtState = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtState");
TextBox txtCountry = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtCountry");
TextBox txtDescription = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtDescription");

SqlConnection conn = new SqlConnection(connStr);
cmd.Connection = conn;
cmd.CommandText = "UPDATE Vendor SET VendorFName ='" + txtFName.Text + "',VendorLName ='" + txtLName.Text + "',VendorCity ='" + txtCity.Text + "',VendorState ='" + txtState.Text + "',VendorCountry ='" + txtCountry.Text + "',VendorDescription ='" + txtDescription.Text + "' WHERE VendorId='" + ResultGridView.DataKeys[e.RowIndex].Values[0].ToString() + "'";
conn.Open();
cmd.ExecuteNonQuery();
ResultGridView.EditIndex = -1;
FillVendorGrid();
conn.Close();

}

protected void ResultGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
ResultGridView.EditIndex = -1;
FillVendorGrid();

}

protected void ResultGridView_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{

TextBox txtFName = (TextBox)ResultGridView.FooterRow.FindControl("txtFName1");
TextBox txtLName = (TextBox)ResultGridView.FooterRow.FindControl("txtLName1");
TextBox txtCity = (TextBox)ResultGridView.FooterRow.FindControl("txtCity1");
TextBox txtState = (TextBox)ResultGridView.FooterRow.FindControl("txtState1");
TextBox txtCountry = (TextBox)ResultGridView.FooterRow.FindControl("txtCountry1");
TextBox txtDescription = (TextBox)ResultGridView.FooterRow.FindControl("txtDescription1");
SqlConnection conn = new SqlConnection(connStr);
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO Vendor(VendorFName, VendorLName,VendorCity,VendorState,VendorCountry,VendorDescription) Values('" + txtFName.Text + "', '" + txtLName.Text + "', '" + txtCity.Text + "', '" + txtState.Text + "', '" + txtCountry.Text + "' , '" + txtDescription.Text + "')";
conn.Open();
cmd.ExecuteNonQuery();
FillVendorGrid();
conn.Close();
}
}
protected void btn_search_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
str = "select * from Vendor where VendorFName like '" + TextBox1.Text + "%'";
cmd = new SqlCommand(str, conn);
sqlda = new SqlDataAdapter(cmd);
ds = new DataSet();
sqlda.Fill(ds, "Vendor");
conn.Close();


ResultGridView.DataSource = ds;
ResultGridView.DataMember = "Vendor";
ResultGridView.DataBind();

}
protected void ResultGridView_Sorting(object sender, GridViewSortEventArgs e)
{
Session["sortBy"] = e.SortExpression;
FillVendorGrid();
}
}


If this post helps you mark it as answer
Thanks

mohammedr88-18313, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 3/7/2013 [Member] [MVP] Gold | Points: 25

Up
0
Down
Try this,
<connectionStrings>

<add name="connectionstring" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings>


Refer this url:
http://aspdotnetsiva.blogspot.in/2012/10/insertupdatedelete-records-from.html

Please Mark As Answer if this helps you.

.

mohammedr88-18313, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raj.Trivedi on: 3/7/2013 [Member] [MVP] Starter | Points: 25

Up
0
Down
<connectionStrings>
<add name="ConnectionName"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|testdb.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved

mohammedr88-18313, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: mohammedr88-18313 on: 3/8/2013 [Member] Starter | Points: 25

Up
0
Down
Dear maam / sir

I have used your code but while connection to App_data database its throwing an exception beow

Server Error in '/' Application.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)



Thanks


mohammedr88-18313, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 3/8/2013 [Member] [MVP] Gold | Points: 25

Up
0
Down
Refer this url
http://www.sqlmusings.com/2009/03/11/resolving-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/

Please Mark As Answer if this helps you.

.

mohammedr88-18313, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raj.Trivedi on: 3/9/2013 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hello Mohammedr88,

can you please paste the code where are you getting error and also your code of appconfig.

One more thing to use the App_Data database you should have atleast sql server express.



Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved

mohammedr88-18313, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: mohammedr88-18313 on: 3/9/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,
thanks for helping me
<connectionStrings>
<add name="ConnectionName"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|testdb.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>


i have used above connection string ,the exception thrown while opening the connection to insert the values

mohammedr88-18313, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response