login/password C# and access.

[Jordi Maicas]

Hello again!



I've got a typical problem validating a user/pass through an access database
with C#. I tried with two functions, and both tell me that the resulting
string is out of context.



How could I solve it?



Thanks.



Boolean comprueba_loguin(String usuario,String password)

{



String prueba,cadena;

OleDbConnection myConn = new
OleDbConnection(TabStripApp.Properties.Settings.Default.onlydentConnectionString);

OleDbCommand myComm = new OleDbCommand("select usuario,password from
usuarios", myConn);

myConn.Open();

OleDbDataReader myReader = myComm.ExecuteReader();

if (myReader.HasRows)

{

cadena = myReader.GetString(0);

myReader.Close();

return true;

}

else

{

myReader.Close();

return false;

}

}

Boolean comprueba_loguin2(String usuario, String password)

{

String prueba, cadena;


String sqlquery= "select usuario,password from usuarios";

String ConnectionString =
TabStripApp.Properties.Settings.Default.onlydentConnectionString;

OleDbConnection con = new OleDbConnection(ConnectionString);

OleDbCommand cmd = new OleDbCommand(sqlquery, con);

con.Open();

cmd.ExecuteNonQuery();

object resultquery = cmd.ExecuteReader();

string resultado = resultquery.ToString();

con.Close();

return true;

}

 

[Arne Vajhøj]

I've got a typical problem validating a user/pass through an access database
with C#. I tried with two functions, and both tell me that the resulting
string is out of context.

Boolean comprueba_loguin(String usuario,String password)

{



String prueba,cadena;

OleDbConnection myConn = new
OleDbConnection(TabStripApp.Properties.Settings.Default.onlydentConnectionString);

OleDbCommand myComm = new OleDbCommand("select usuario,password from
usuarios", myConn);

myConn.Open();

OleDbDataReader myReader = myComm.ExecuteReader();

if (myReader.HasRows)

{

cadena = myReader.GetString(0);

myReader.Close();

return true;

}

else

{

myReader.Close();

return false;

}

}

Boolean comprueba_loguin2(String usuario, String password)

{

String prueba, cadena;


String sqlquery= "select usuario,password from usuarios";

String ConnectionString =
TabStripApp.Properties.Settings.Default.onlydentConnectionString;

OleDbConnection con = new OleDbConnection(ConnectionString);

OleDbCommand cmd = new OleDbCommand(sqlquery, con);

con.Open();

cmd.ExecuteNonQuery();

object resultquery = cmd.ExecuteReader();

string resultado = resultquery.ToString();

con.Close();

return true;

}

Can we get a complete class and the exact error message.

In the code posted there are only a few unused variable
and TabStripApp.Properties.Settings.Default.onlydentConnectionString
which we do not what is.

Arne

 

[Jordi Maicas]

That's for the connectionstring, and the message watching to String
resultado is:



The name 'resultado' does not exist in the current context.





<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<configSections>

</configSections>

<connectionStrings>

<add name="TabStripApp.Properties.Settings.onlydentConnectionString"

connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=|DataDirectory|\onlydent.mdb"

providerName="System.Data.OleDb" />

</connectionStrings>

</configuration>

 

[Hans Kesting]

Jordi Maicas has brought this to us :

Hello again!



I've got a typical problem validating a user/pass through an access database
with C#. I tried with two functions, and both tell me that the resulting
string is out of context.



How could I solve it?



Thanks.



Boolean comprueba_loguin(String usuario,String password)

{



String prueba,cadena;

OleDbConnection myConn = new
OleDbConnection(TabStripApp.Properties.Settings.Default.onlydentConnectionString);

OleDbCommand myComm = new OleDbCommand("select usuario,password from
usuarios", myConn);

myConn.Open();

OleDbDataReader myReader = myComm.ExecuteReader();

if (myReader.HasRows)

{

cadena = myReader.GetString(0);

myReader.Close();

return true;

}

else

{

myReader.Close();

return false;

}

}

Boolean comprueba_loguin2(String usuario, String password)

{

String prueba, cadena;


String sqlquery= "select usuario,password from usuarios";

String ConnectionString =
TabStripApp.Properties.Settings.Default.onlydentConnectionString;

OleDbConnection con = new OleDbConnection(ConnectionString);

OleDbCommand cmd = new OleDbCommand(sqlquery, con);

con.Open();

cmd.ExecuteNonQuery();

object resultquery = cmd.ExecuteReader();

string resultado = resultquery.ToString();

con.Close();

return true;

}

In the first example you fill a local variable "cadena" with some
result-string, and then ignore it. That local variable is only
available inside that method.
In the second example you even ignore that "cadena" variable and create
a new one (that is in turn discarded when the method returns).
You could change the result type to "string" and return the value you
found (null meaning 'not found'), or change that local variable to an
"out" parameter.

Some other remarks:
* If you want just the first returned value, you could use
ExecuteScalar
* You might wrap the connection in a "using" statement, so the
connection will always be closed, even when the database command
failed.

using (OleDbConnection con = new OleDbConnection(ConnectionString))
{
// use the connection
}

Hans Kesting

 

[JordiXip]

I only receive an String resultado=

resultado "System.Data.OleDb.OleDbDataReader" string

so, resultado="System.Data.OleDb.OleDbDataReader" , but it's not the first
record in my table.

I changed the second example to:

Boolean comprueba_loguin2(String usuario, String password)
{
String prueba, cadena;

String sqlquery= "select usuario,password from usuarios";
String ConnectionString =
TabStripApp.Properties.Settings.Default.onlydentConnectionString;

OleDbConnection con = new OleDbConnection(ConnectionString);
OleDbCommand cmd = new OleDbCommand(sqlquery, con);

con.Open();
cmd.ExecuteNonQuery();
object resultquery = cmd.ExecuteReader();
resultado = resultquery.ToString();
con.Close();

return true;
}

and I added resultado declaration as global....

namespace TabStripApp
{
public partial class Form2 : Form
{
public String resultado;

public Form2()
{
InitializeComponent();
}

 

[Joachim Van den Bogaert]

Hi,

            String sqlquery= "select usuario,password from usuarios";

This is actually a SELECT statement without any parameters that will
return ALL users and passwords in the table, are you sure you don't
want
to use "SELECT usario, password FROM usarios WHERE usario = '" +
usario + "' AND password = '" + password + "'".

If this would be the case, you can execute the command with the reader
and grant access if there is exactly one user with the required
username and password (otherwise something is definetely wrong) - why
do you execute the query twice anyway (once with ExecuteNonQuery and
the second time with ExecuteReader)?

Also, I recommend using parameters instead of strings to avoid
injection:

Here's how to do it:

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx

What it does: it keeps the parameters in place and makes sure that no
hostile strings can be passed. See SQL injection in wiki:

http://en.wikipedia.org/wiki/SQL_injection

Regards,
Joachim











SqlCommand cmd = new SqlCommand("SELECT usario, password FROM usarios
WHERE usario = '" + usario + "' AND password = '" + password + "'".",
conn);

 

[Hans Kesting]

I only receive an String resultado=

resultado "System.Data.OleDb.OleDbDataReader" string

so, resultado="System.Data.OleDb.OleDbDataReader" , but it's not the first
record in my table.

ExecuteScalar will not return the first result from the *table*, it
will return the value of the first column in the first record of the
*resultset*, that is *after* the "select" has executed.

Hans Kesting

 

[Jordi Maicas]

It's only two ways for doing the same, I think..

I'll probe it now, and .... I'll continue.

Thanks

"Joachim Van den Bogaert" <[email protected]> escribió en el
mensaje
Hi,

String sqlquery= "select usuario,password from usuarios";

This is actually a SELECT statement without any parameters that will
return ALL users and passwords in the table, are you sure you don't
want
to use "SELECT usario, password FROM usarios WHERE usario = '" +
usario + "' AND password = '" + password + "'".

If this would be the case, you can execute the command with the reader
and grant access if there is exactly one user with the required
username and password (otherwise something is definetely wrong) - why
do you execute the query twice anyway (once with ExecuteNonQuery and
the second time with ExecuteReader)?

Also, I recommend using parameters instead of strings to avoid
injection:

Here's how to do it:

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx

What it does: it keeps the parameters in place and makes sure that no
hostile strings can be passed. See SQL injection in wiki:

http://en.wikipedia.org/wiki/SQL_injection

Regards,
Joachim











SqlCommand cmd = new SqlCommand("SELECT usario, password FROM usarios
WHERE usario = '" + usario + "' AND password = '" + password + "'".",
conn);

 

[Jordi Maicas]

Solved!!

It works (after that I'll rewrite it to detect inject-sql),

Boolean comprueba_loguin(String usuario,String password)

{

String prueba,cadena;

OleDbConnection myConn = new
OleDbConnection(TabStripApp.Properties.Settings.Default.onlydentConnectionString);

OleDbCommand myComm = new OleDbCommand("select usuario,password from
usuarios where usuario='"+usuario+"' and password='"+password+"'", myConn);

myConn.Open();

OleDbDataReader myReader = myComm.ExecuteReader();

if (myReader.HasRows)

{

myReader.Close();

return true;

}

else

{

myReader.Close();

return false;

}

}

 

[Paul Clement]

¤ I've got a typical problem validating a user/pass through an access database
¤ with C#. I tried with two functions, and both tell me that the resulting
¤ string is out of context.
¤
¤ How could I solve it?
¤
¤ Thanks.
¤
¤ Boolean comprueba_loguin(String usuario,String password)
¤
¤ {
¤
¤ String prueba,cadena;
¤
¤ OleDbConnection myConn = new
¤ OleDbConnection(TabStripApp.Properties.Settings.Default.onlydentConnectionString);
¤
¤ OleDbCommand myComm = new OleDbCommand("select usuario,password from
¤ usuarios", myConn);

FYI, "password" is a reserved keyword in Jet SQL. If you use it then it must be enclosed in
brackets, otherwise you should rename the column.

SELECT usuario, [password] from usuarios


Paul
~~~~
Microsoft MVP (Visual Basic)