No build errors in Visual Studio 2010 yet my connection string to my SQL 2008 Database is not working

No build errors and my connection tests "OK" yet my connection string to my SQLDatabase is not working; the user is not being referred to the Home.aspx page if username and password found in DB.
DataConnector Class (Class1.cs)

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


Login Page (Default.aspx)

<%@ 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" />
        &nbsp;</p>
</asp:Content>

Open in new window


Code behind (Default.aspx.cs)

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

screenshot of DB ConnectionAdvanced DB connection propertiesscreenshot of DB table tbl_logini have been troubleshooting for a week -- any assistance greatly appreciated!
Bette
Bette LamoreWeb Designer/Developer/GIS ProfessionalAsked:
Who is Participating?
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't understand what you're trying to achieve.
The screenshot shows that you defined a Windows Authentication mode to connect to the database and then your last screen shows a table with User and Password. Can you explain?
0
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
Thank you for responding, Vitor. The code on my Default.aspx.cs page above is checking on whether there is a row with the username and password entered in the Default.aspx text boxes in the SQL 2008 Database. If there is a row with those entries, then the user is supposed to be redirected to the Home page -- Home.aspx.

Although the Build shows no errors and my test on my SQL database says "OK," I am not redirected when I enter a username with the password that I know is in the database table tbl_login.

Am I using the wrong provider name?

Thank you for any assistance.
Bette
0
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
PS I cannot post a comment on Firefox -- now using Chrome -- Very strange....
That caused the delay in response -- trying to track down that problem as well.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm a DBA not a Developer. I'm not aware of the use of MSSQL data file name as connector. Usually I'm expecting an instance name and a database name in the connection string.
Let's wait for someone with more knowledge than me in development.
0
DcpKingCommented:
When you say "my connection tests ok" are you logged in as "you" (the developer) or "him/her" (the user) ? You're specifying Windows login and that obviously works find for you ... if your users don't have sufficient permissions it won't work for them.

hth

Mike
0
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
Hello Mike
Thank you for responding. No, I am just running my script in Visual Studio -- you can see my coding above. I am not logging in to the SQL database as anything. I'm just attempting to use a login page and then when I run the script, I put a username with password that I already know is in the database I created. The code should direct the user who inputs through the browser, to the Home.aspx page.

Thanks for any assistance
Bette
0
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
Postscript -- When I go to the Server Explorer in Visual Studio and use "Modify connection" to test my connection, the test says OK. See image below
testConnection.JPG
0
DcpKingCommented:
Hi Bette,
You say "I am not logging in to the SQL database as anything.". Well, actually you are. The database (one possibly of many attached to an instance of SQL Server) will have a number of properties associated with it. One set of such are grouped as  "security" and include Logon Names and Passwords and sets of permissions. When you use VS you are presenting your  Windows ID and Password as credentials and these obviously match with some stored with the database. Your identity might be iasoberg\lamore and a password of fred1234. Have you tried these? It doesn't matter that they're not in the table - you have to be able to log in to the db to read the table before you can find out that you're not in it !!

hth

Mike
0
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
Thanks for the clarification, Mike.  I never entered  my login name or any password upon starting nor setting up my Visual Program program so I am surprised. I didn't realize that when I logged into Visual Studio that I needed to sign-in with my Windows login to connect me to the database. I could see the structure of the DB and the tables so I figured that I was connected.

At any rate, my page was supposed to check for a row containing the username and password entered in the form and if there (which I did enter a name and correct password), I was supposed to be directed to the Home page. I wasn't redirected, even though the username and password were there.

I used Windows authentication for SQL (and actually I have no password for my computer - just start the program with Bette (admin) user). I created no password when I originally set up the SQL DB and used my name Bette (admin) as user.

This is strictly a learning situation for me so I appreciate your help. The scripts I am using are from an online training video as to how to create a basic web site in VS with login page. The instructor example for my login page was exactly what I used, verbatim, with the exception that the instructor used Access and I am using SQL 2008. That's why I suspected that the problem lay with the provider name, because all my other pages went just as the instructor's examples ran -- no problems -- until it came to the login page where I had to use a different provider name.When I use the localhost:8080, and run my script,

Thanks for your help so far.
Bette
0
DcpKingCommented:
Hi Bette,

Try downloading and installing an evaluation Edition of MS SQL Server. It's good for 180 days (after which you can spend $50 for a Developer Edition (Amazon) if you're still using it). With that you can see what permissions the database has associated with it (I'm assuming that you downloaded the .MDF file from a web site).

For more help keep asking :)

Mike
0
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
Hi Mike
I have SQL server 2008 installed and I have permission as admin (Windows login). I created my database with the SQL server management studio. I had originally had 2008R on my computer until I learned that it does not work with Visual Studio 2010. I got the complete program (and 2005) during my BS IT program at U of MA -- part of a MSDN contract for students to get free software. I also downloaded Visual Studio 2008 and 2010 at that time. I created the mdf file.

Thanks for your responses.
0
DcpKingCommented:
Hi Bette,

In that case you should be logging in to SS2008 as something like DOMAIN\elamore and whatever your Windows password is (supplied to SS2008 by Windows under the covers). Try changing the way your web program is connecting to the database: it should really be something like what I've attached

Mike
Connections.PNG
0
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
I'm confused, Mike
If you look at my initial question, I am connected to the mdf file in Visual Studio -- I can see the table. My problem lay with the login page I created in that that particular page was not redirecting the user to the Home page. I used C# coding. Please see notes above.
Thanks!
0
DcpKingCommented:
From what I understood, the problem was that you weren't connecting to the database. Have you tried skipping the part that queries the database and just going straight to the call to present the home page? i.e. checking that your code will load the page when it's allowed to by the database response? If _that_ works then we have to look at the database - it not then there may not be a database problem at all!

Mike
0
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
You are truly onto something, Mike. I commented out ALL my database connector code and just left the redirect to the Home page and it still does not redirect. I checked and all my code is the same as the instructor's (except for the provider of the DB and the fact I now commented out all reference to a DB) and this code works for her but not for me -- perplexing!

Can you see anything in the code below that looks incorrect and would prevent the redirect? (I just removed the db connection with if statement).
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;


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)
        {
          Response.Redirect("Home.aspx"); 
        }
    }
}

Open in new window

Thanks
 Oh also I thought it may be helpful to have connection class shown -- it is below (class1.cs)
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

0
DcpKingCommented:
Hi Bette,

In weird cases like this I usually try to work out what assumptions I'm making and test them. The assumption we were making previously was that the database code was the problem, but you've disproved that.

Now you have a piece of code in bnLogin_Click that doesn't appear to work.
Q1. Is the code actually being called ? Put something else in bnLogin_Click that will tell you "hello! I'm in bnLogin_Click".
Q2. Is "Home.aspx" really called that ? Is your system case-sensitive ?
Q3. Is "Home.aspx" really where the Response.Redirect code is looking for it ?

I realise that these questions may not solve your problem, but hopefully you'll get the idea. Be like Sherlock Holmes! Eliminate the impossible and what's left, however seemingly improbable, is what you're looking for.

Good luck!

Mike
1

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:
Hi Mike
I've been "Googling" as my problem is not the database connection as I originally believed so you get the points for getting me to check that out. I set a break point and my program is not doing anything with the btn_Click code (I wrote bn_Click in my program).
private void bnLogin_Click(object sender, EventArgs e)

Open in new window

Apparently I am not the only one having this problem with VS 2010 and the only solution I have been able to find online is in a book that I have to buy -- I will if no one comes up with a work-around here. It defined the problem yet does not give away solution -- smart author :-)

Thanks for your help thus far and I will post the other question -- the btn_Click problem,
1
DcpKingCommented:
So it seems that there's a bug in VS? Well, I'm very sorry I couldn't just wave a magic wand for your program! Good luck!

Mike
0
Bette LamoreWeb Designer/Developer/GIS ProfessionalAuthor Commented:
Thank you for taking so much time with me. Your suggestions led to me discovering the error in VS. Have a great week!
Bette
1
DcpKingCommented:
Thanks - you too!
0
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.