ExecuteScalar

private void buttonLogin_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\\PEService.mdf;Integrated Security=True;User Instance=True";
conn.Open();
string strSQL = "Select Count(*) as ctr From Cust Where Email=" + textBoxEmail + "and Passwd=" + textBoxPW;

SqlCommand cmd = new SqlCommand(strSQL,conn);
int ctr=(int)cmd.ExecuteScalar();
if (ctr == 1)
MessageBox.Show("Correct");
else
MessageBox.Show("Wrong");
conn.Close();
}

i have this code for my login form. when i remove conn.Open(); in the code
it says... ExecuteScalar requires an open and available Connection. The connection's current state is closed.

and when i put conn.Open();
it says... An attempt to attach an auto-named database for file C:\... failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

what is the problem?Hi,

I think the strSQL should be set as follows. I added the (') characters.

string strSQL = "Select Count(*) as ctr From Cust Where Email='" + textBoxEmail + "'and Passwd='" + textBoxPW + "'";

Eralper
http://www.kodyaz.com|||Is this production code? You know it is pretty well textbook bad practice as regards security yeah?|||besides the serious sql injection issues pootle_flump is referring to, there are some other (less serious) problems:

you should use the "using" keyword around use of SqlCommand and SqlConnection. that way they get disposed properly when they go out of scope.

currently you are not disposing your SqlCommand at all, so it will cause resource leaks.|||now, i added the using keyword as well as the correct sql statement but still, the same problem.

i have also read about SQL Server Management Studio. Has it something to do with the problems occurring? but i don't have it installed with my visual studio 2005! how to have it?

and to pootle flump, can u further explain because im just a newbie. tnx|||i've solved my problem.. it was all about connection failure. tnx!|||Here's what you should do to avoid the sql injection problem:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

and here's what could happen if you don't fix it (credit pootle for this link, it's a nice little video demonstration):

http://www.rockyh.net/AssemblyHijacking/AssemblyHijacking.htmlsql