I understand the same thing has been asked before and closed due to a simple typo. From what I can see I don't have any typos and I've tried to figure out the problem by Googling but no luck.
I have created this Login Window. Main Login Window
I have created a local SQL Database from within Visual Studio (2015) to store my users. To establish the connection to this database I have written this line of code in my Enter button that is visible in the Main Login Window.
SqlConnection sqlConn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=C: \USERS\NIKOS\DESKTOP\NIKOS();\SAFE BOX\DATABASE\SAFEBOXDB.MDF;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
This path has been pasted by right clicking my database and selecting properties. In the properties there is a field named Connection String. That's what I have copied and pasted, into the above path in the code.
This is all my code.
//Find path for SQL Connection
SqlConnection sqlConn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=C:\USERS\NIKOS\DESKTOP\NIKOS();\SAFE BOX\DATABASE\SAFEBOXDB.MDF;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
//Add query for actions to be taken once connection is established, select the user
string sqlQuery = "Select * from dbo.Table Where username = '" + txtEnterUserName.Text.Trim() + "' and password = '" + txtEnterPassword.Text.Trim();
//Add SQL Data Adapter passing in the connection and the query
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlQuery, sqlConn);
//Create new Datatable object
DataTable dataTable = new DataTable();
//Fill SQL Data Adapter with the dataTable
sqlDataAdapter.Fill(dataTable);
if (dataTable.Rows.Count == 1)
{
loginMain objFormMain = new loginMain();
this.Hide();
UserDashboard userDash = new UserDashboard();
userDash.Show();
}
else
{
MessageBox.Show("Check Username and Password");
}
When I run the program, my Main Login Window appears as it's the main window, I enter my credentials as per the table in the database and I get this error as soon as I press the "Enter" button.
ArgumentException was unhandled
I have checked and rechecked the path but I can't seem to get it working and I have no idea what the problem is. General Google searches have not helped.
Due to low reputation as I am a new user, I cannot upload my table data, I only have one row with a user name and a password. Presume these are being typed correctly.
The error says that a keyword is not supported. I can't seem to understand this.
EDIT. I have reinstalled the server and the new path is now
using (SqlConnection sqlConn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Nikos\Documents\SafeBox.mdf;Integrated Security=True;Connect Timeout=30"))
as per the new Connection String. So the new code for the Enter button is now
private void enterButton_Click(object sender, EventArgs e)
{
string sqlQuery = @"Select * from dbo.Table
Where username = @user AND
password = @pass";
using (SqlConnection sqlConn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Nikos\Documents\SafeBox.mdf;Integrated Security=True;Connect Timeout=30"))
using (SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn))
{
sqlConn.Open();
cmd.Parameters.Add("@user", SqlDbType.NVarChar).Value = txtEnterUserName.Text.Trim();
cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = txtEnterPassword.Text.Trim();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
loginMain objFormMain = new loginMain();
this.Hide();
UserDashboard userDash = new UserDashboard();
userDash.Show();
}
else
{
MessageBox.Show("Check Username and Password");
}
}
}
}
The new error I have is {"Incorrect syntax near the keyword 'Table'."} and the error points to this line.
using (SqlDataReader reader = cmd.ExecuteReader())
There are many errors in your code.
The first one is the space between the C: drive letter and the remaining path is wrong and should be removed. Also adding a semicolon in the middle of the connection string as part of the path confuses the connectionstring parser that uses the semicolon as separator between keys and values. This is the origin of the error message because after the NIKOS(); semicolon the parser ends its discover of the path and tries to make sense of \SAFE BOX.... as it was a key to parse.
You should remove it from your disk path and adjust your connectionstring
SqlConnection sqlConn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;
Initial Catalog=C:\USERS\NIKOS\DESKTOP\NIKOS\SAFE BOX\DATABASE\SAFEBOXDB.MDF;
Integrated Security=True;
Connect Timeout=30;
Encrypt=False;
TrustServerCertificate=True;
ApplicationIntent=ReadWrite;
MultiSubnetFailover=False");
Now the problems in code are even worse
string sqlQuery = @"Select * from [Table]
Where username = @user AND
password = @pass";
using(SqlConnection sqlConn = new SqlConnection(....))
using(SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn))
{
sqlConn.Open();
cmd.Parameters.Add("@user", SqlDbType.NVarChar).Value = txtEnterUserName.Text.Trim();
cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = txtEnterPassword.Text.Trim();
using(SqlDataReader reader = cmd.ExecuteReader())
{
if(reader.HasRows)
{
loginMain objFormMain = new loginMain();
this.Hide();
UserDashboard userDash = new UserDashboard();
userDash.Show();
}
else
{
MessageBox.Show("Check Username and Password");
}
}
}
First of all, you don't need a complex SqlDataAdapter if you just want to check if the user exists or not. A simple SqlCommand with an SqlDataReader will do just fine.
Second, all disposable objects should go inside an using statement to be sure that when you have finished to use them, they will be destroyed also in case of exceptions.
Finally, parameters are always the way to go when you need to pass values to your database. Failing to use them will lead to Sql Injection attacks or unexpected syntax errors when your strings contains single quotes.