How Can I Fix Instance Failure Using SQL Database In C#

Question

I'm doing a login form following this video here: https://www.youtube.com/watch?v=tcmmCcMs8yU

This is my code:

private void button2_Click(object sender, EventArgs e)
{
    this.Close();
}

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(@"Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Hp\\Documents\\Data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

    SqlDataAdapter sda = new SqlDataAdapter("'Select Count (*) From Login Where Username='" + textBox1.Text + "'and Password='" + textBox2.Text + "''" ,con);

    DataTable dt = new DataTable();
    sda.Fill(dt);

    if (dt.Rows[1][1].ToString() == "1")
    {
        this.Hide();
        Main ss = new Main();
        ss.Show();
    }
    else
    {
        MessageBox.Show("No Good");
    }
}

My problem is on the sda.Fill(dt) line, where it tells me I have an Instance Failure.

What can I do to fix it?

Thanks in advance!

At which time in linked video we can see complete error message? — Sinatr, May 05 '20 at 13:24
It isn't shown in the video, I checked it a thousand times and it's not running well :(. — Tommy Miyar, May 05 '20 at 13:33
In C# indexes start with 0. It should be `dt.Rows[0][0]`. BTW I would suggest a) using `.ExecuteScalar` and b) reading on SQL Injection and parametrized queries — Cleptus, May 05 '20 at 14:05
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, May 05 '20 at 14:21
You mix a literal string and escape chars in your connection string. Try using .\SQLEXPRESS instead of .\\SQLEXPRESS. I think you also need to have sqlserver-express installed since you need an instance to actually attach your mdf file to. — JonC, May 05 '20 at 15:27

score 0 · Answer 1 · answered May 05 '20 at 13:34

0

Are you sure you have the correct connection string?

Did you try to remove the double-slash between the server name and the database instance name in the connection string?

This from your code: .\\SQLEXPRESS

To be this: .\SQLEXPRESS

Check this: Instance Failure in asp.net

answered May 05 '20 at 13:34

Atanas Sarafov

27
6

Really it should remove the `@` from `@"Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\.....` – Cleptus May 05 '20 at 14:07

Palle Due · Answer 2 · 2020-05-05T14:36:56.880

It looks like you have three problems:

First: when you start your connection string with @, you shouldn't escape backslashes with backslashes. Either remove the @, or remove the all double backslashes.

Your other problem is that your SQL query is wrapped in single-quotes:

SqlDataAdapter sda = new SqlDataAdapter("'Select Count (*) From Login Where Username='" + textBox1.Text + "'and Password='" + textBox2.Text + "''" ,con);

This should work:

SqlDataAdapter sda = new SqlDataAdapter("Select Count (*) From Login Where Username='" + textBox1.Text + "'and Password='" + textBox2.Text + "'" ,con);

Your third problem is that you are vulnerable to SQL-injection. That's not keeping your solution from working, but it is extremely bad practise. Never use user input directly in SQL queries, use a parameterized query, see the discussion here.

Edit

As @bradbury9 points out dt.Rows[1][1] would throw an IndexOutOfRangeException. There is only one row, so it should be dt.Rows[0][whichever_zero_based_column_you_are_interested_in].

How Can I Fix Instance Failure Using SQL Database In C#

2 Answers2