Connection String to SQL 2008 Database not working in Visual Studio 10

Below is code for Default.aspx.cs page
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using IasobergDataDrivenWebsite;

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

        }

        protected void bnLogin_Click(object sender, EventArgs e)
        {
            DataLayer.DataConnector dat = new DataLayer.DataConnector("Provider=System.Data.SqlClient;Data Source='" + Server.MapPath("IasobergData.mdf") + "'; Persist Security Info=False;");

            DataTable dt = dat.DataSelect("select UserID from tbl_login where Username = '" + txtUsername.Text.Replace("'", "''") + "' and Password = '" + txtPassword.Text.Replace("'", "''") + "'");
            if (dt.Rows.Count > 0)
            {
                Response.Redirect("Home.aspx");
            }
        }
    }
}

Open in new window


and when I ran code I got this error message:
error-message1.JPG
so I removed the "Provider" and used breakpoints and got through all breakpoints but the last which meant my code did not redirect me to the Home.aspx page.
stops-at-breakpoint.JPG
My code up until the database code works because if I remove references to the database and just redirect to Home.aspx page with bnLogin_Click, it works fine. The problem is not the username and password because the one I use for testing is indeed in the db so the row count should be >0.

I looked online and I believe I have the correct provider name yet it throws the exception. Here is the Class1.cs code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;

namespace DataLayer
{
    public class DataConnector
    {
        protected SqlDataAdapter DataAdaptor1 = new SqlDataAdapter();
        public string ErrorMessage = "";
        public DataConnector(string ConnectionString)
        {
            SqlConnection Connection1 = new SqlConnection(ConnectionString);
            this.DataAdaptor1.SelectCommand = new SqlCommand("", Connection1);
            this.DataAdaptor1.InsertCommand = new SqlCommand("", Connection1);
        }

        public DataTable DataSelect(string query)
        {
            DataTable dt = new DataTable();
            try
            {
                DataAdaptor1.SelectCommand.CommandText = query;
                DataAdaptor1.SelectCommand.Connection.Open();
                DataAdaptor1.Fill(dt);
                DataAdaptor1.SelectCommand.Connection.Close();
                ErrorMessage = "";
            }
            catch (Exception err)
            {
                ErrorMessage = err.Message;
                DataAdaptor1.SelectCommand.Connection.Close();
            }
            return dt;
        }
        public int DataInsert(string query)
        {
            int Result = 0;
            try
            {
                DataAdaptor1.InsertCommand.CommandText = query;
                DataAdaptor1.InsertCommand.Connection.Open();
                Result = DataAdaptor1.InsertCommand.ExecuteNonQuery();
                DataAdaptor1.InsertCommand.Connection.Close();
                ErrorMessage = "";
                return Result;
            }
            catch (Exception err)
            {
                ErrorMessage = err.Message;
                DataAdaptor1.InsertCommand.Connection.Close();
                return 0;
            }
        }

        public int DataUpdate(string query)
        {
            return DataInsert(query);
        }

        public int DataDelete(string query)
        {
            return DataInsert(query);
        }
    }
}

Open in new window


Just in case, here is the Default.aspx code as well.
<%@ Page Title="" Language="C#" MasterPageFile="~/Main.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="IasobergDataDrivenWebsite.Default" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <p>
        username
        <asp:TextBox ID="txtUsername" runat="server"></asp:TextBox>
    </p>
    <p>
        password
        <asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox>
    </p>
    <p>
        &nbsp;
        <asp:Button ID="bnLogin" runat="server" Text="Login" OnClick="bnLogin_Click" />
        &nbsp;</p>
</asp:Content>

                                
                                          

Open in new window


and here is the picture of my DB
screenshot-of-DB-Table.JPG
Bette LamoreWeb Designer/Developer/GIS ProfessionalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Miguel OzSoftware EngineerCommented:
Can you post bnLogin_Click code?
I need to check if your connection string match the ones shown here.
Kamal KhaleefaInformation Security SpecialistCommented:
try this

public object StartConnection()
{

	SqlClient.SqlConnection con = null;

	string ConnectionString = "Data Source=myserverName;Initial Catalog=DBName;Integrated Security=True";

	try {
		con = new SqlClient.SqlConnection(ConnectionString);
		con.Open();
		//do ur work here 
	} catch (Exception ex) {
		// MsgBox(ex.Message)
		return con;
	}





}

Open in new window

Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
Hello Miguel
All my code was posted with my question above, including the bnLogin_Click (see Default.aspx and Default.aspx.cs. Thank you for your assistance.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
Hello King2002
I already have this connectionString code, which I did not post yet, see below. Do I still need to add anything and where? This code in in my DataLayer app.config file (referenced in both the Class1.cs and Default.aspx.cs shown above).
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="DataLayer.Properties.Settings.IasobergDataConnectionString"
            connectionString="Data Source=.;AttachDbFilename=|DataDirectory|\IasobergData.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>  
</configuration>

Open in new window

Thank you for your assistance.
Bette
Miguel OzSoftware EngineerCommented:
The connection string shown in your last post will not work because it is for a compact database located in the data folder of your web site.
Please follow these steps to target SQL server:
1. Check SQL server by using  SSMS to verify you can access your database. (IasobergData)
2. If step 1 is OK, then you can change connection string config entry to:
<add name="DataLayer.Properties.Settings.IasobergDataConnectionString"
	connectionString="Data Source=.;initial catalog=IasobergData;Integrated Security=True;Connect Timeout=30;"
	providerName="System.Data.SqlClient" />

Open in new window

3. Check that web.config contains these connection string. I know it is frustrating but .NET will not read it from your DLL config file.
4. On your login method change constructor to:
string connString = ConfigurationManager.ConnectionStrings["DataLayer.Properties.Settings.IasobergDataConnectionString"].ToString();
DataLayer.DataConnector dat = new DataLayer.DataConnector(connString );

Open in new window

Note: Please add "using System.Configuration;" to your uses clause.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
WOW, Miguel
Don't know how to thank you!!! Works!! I'll have to study what you did so that I know for next time. I tested 2 usernames with passwords and they both worked!

I added your code to my web.config -- see below
<?xml version="1.0"?>

<configuration>
    <system.web>
        <compilation debug="true" targetFramework="4.0" defaultLanguage="c#" />
    </system.web>
    <connectionStrings>
        <add name="DataLayer.Properties.Settings.IasobergDataConnectionString"
	connectionString="Data Source=.;initial catalog=IasobergData;Integrated Security=True;Connect Timeout=30;" providerName="System.Data.SqlClient" />
    </connectionStrings>

</configuration>

Open in new window


and realized it is the same as my app.config (below)
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="DataLayer.Properties.Settings.IasobergDataConnectionString"
	connectionString="Data Source=.;initial catalog=IasobergData;Integrated Security=True;Connect Timeout=30;" providerName="System.Data.SqlClient" />
    </connectionStrings>  
</configuration>

Open in new window


Did I even need the app.config file? What is the purpose of 2 identical files?

I am awarding you full points yet would like you to answer this question first, if you don't mind.
Thanks!!
Miguel OzSoftware EngineerCommented:
You are welcome.
Regarding your question, the answer is NO; there is no need to have this info in the app.config file. For all practical purposes your web site code reads the required info from the web.config file.
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
Thanks for explaining the duplication of code.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.