Query Select not working

[patrickstrijdonck]

Hi All,

Im very new to C#, trying to learn, but there is something i cant get to work, im sure im doing something wrong but i cant find what, little help in the good direction is very welcome

string constr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Patrick Strijdonck\Documents\IT4.mdb";
            string cmdstr = "SELECT Password = @textBox1 FROM tblGebruikers WHERE" + textBox1.Text;
    
            OleDbConnection con = new OleDbConnection(constr);
            OleDbCommand com = new OleDbCommand(cmdstr, con);
            con.Open();
            com.Parameters.Add("@textBox1", SqlDbType.VarChar);
            com.Parameters["@textBox1"].Value = name;
                                   
            con.Close();

What im trying to do, user fills in his name and password, and press sign on. then the SQL query shoud take a look at the table tblGebruikers and get that name and its password. put that password in a string and later(which is not in code) and if condition checks if String and textBox1.text are same, if so, continue, if not, cancel.

Hoping someone can help me

 

[Foada]

your code line

string cmdstr = "SELECT Password = @textBox1 FROM tblGebruikers WHERE" + textBox1.Text;

is incomplete regarding your "WHERE" condition unless you are entering something along the lines of UserName = JSmith in the text box. Place a break point at that line and step throught the code. It should give you an idea of what is missing in your SQL statement. That should at least get you started. It is hard to say if there are any other issues with the give code.

If you choose to battle wits with the witless be prepared to lose.

 

[patrickstrijdonck]

:O how is it possible i didnt saw that one, Thanks for the tip, Added the Username = in it, now it works

 

[jmeckley]

btw:
you should not use injected sql. use parameterized queries instead.
you are missing exception handling in the form of either try/finally (not catch) or the using keyword to dispose of the command(s)/connection(s) if an error should occur.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[patrickstrijdonck]

i dont really understand, can you give an example?

 

[Foada]

patrick, you are pretty close in what you are doing. What jmeckley is refering to is the fact that you have an "opening" in your SQL select statement. The user has the ability of typing "into" your SQL statement, mainly the "textBox1.text" portion of you SELECT statement. This opens the door for SQL injection. Your code is using parameters for the return of "@textbox1" but you can pass paramenters both ways to your SQL statement thereby reducing the possibility of SQL injection. If you set up an input parameter as well as the return parameter you will be better off. I am not sure where you are getting the "name" variable for your code sample or I could tweek your code a little as an example.

If you choose to battle wits with the witless be prepared to lose.

 

[Foada]

I stated that wrong. It appears that you are using the "@textbox1" as an input parameter. Your actual code would help.

If you choose to battle wits with the witless be prepared to lose.

 

[jmeckley]

more than sql injection attacks. sqlserver can process a parameterized query more efficiently than injected sql.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Foada]

I agree with regards to sql server but MS Access has some limitation.

If you choose to battle wits with the witless be prepared to lose.

 

[patrickstrijdonck]

So actually my code will just do fine, if i understand this correctly?

 

[Foada]

What Jason is refering to is the using of stored procedures in SQL Server which allows you to pass parameters to the stored procedure. You can pass parameter to MS Access in a query but it is done a little different. You will need to set up a specific query in MS Access to accept your parameter and returns. The code sample you gave is using a parameters collection object so that is a step in the right direction. What is unclear from the code you posted is what exactly you are attempting to do. You have a "command" object which allows you do perform both a "Select" statement or a parameterized query. To obtain what is being discussed you would need to do something along the lines of this in Access:

SELECT Logins.Password FROM Logins WHERE Logins.Username=[parauser];

In this case parauser is the parameter and you would set your code something like this:

OleDbConnection con = new OleDbConnection(constr);
OleDbCommand com = new OleDbCommand();
com.CommandText = AccessQueryName
com.CommandType = adCmdStoredProc
com.Parameters.Add("parauser", SqlDbType.VarChar);
com.Parameters["parauser"].Value = textBox1.text;
com.ActiveConnection = con;
                       
con.Close();

The resulting recordset would be the password for the given user name if my assumptions are right regarding your data layout etc. The given code sample is psuedocode by the way as I don't have C# in front of me and I am just doing things from memory but it should get you going in a direction.

If you choose to battle wits with the witless be prepared to lose.

 

[patrickstrijdonck]

SELECT Logins.Password FROM Logins WHERE Logins.Username=[parauser];

Where does the Logins.Password come from??

Is "Logins.Password" the Field in the Table?? if so why not just using "Password" instead of "Logins.Password?"

(sorry for these questions, im new to C# and eager to learn )

What i did now is this:

selectSQL = "SELECT Password FROM tblGebruikers ";
                selectSQL += "WHERE Naam= @textBox2";


                OleDbConnection con = new OleDbConnection(cmdstr);
                OleDbCommand com = new OleDbCommand(selectSQL, con);



                con.Open();
                com.Parameters.Add("@textBox2", SqlDbType.VarChar);
                com.Parameters["@textBox2"].Value = textBox2.Text;
                string varpass = Convert.ToString(com.ExecuteScalar());


                con.Close();

With the string "varpass" i can see with an IF, if the inserted password matches the password that the SQL got from the database with that user.

This WORKS!

i can sign in with the correct password and its not givin me access when the password doesnt match, i had to add a = null query to the password field, otherwise the SQL gave an error.

Now my question, is this piece of code correct or can this be improved or something??

 

[Foada]

>Where does the Logins.Password come from
Logins was the name of the Access table that I set up to test my query in Access. Password is the field name so the Logins table in Access looked like this:

ID Username Password
== ======== ========
1 Joe pass
2 Sally word
3 Skip along

and then I went to Access queries and entered

SELECT Logins.Password FROM Logins WHERE Logins.Username=[parauser];

as the SQL. When you run the query in Access it will ask you for the username in a pop up box, this is your parameter. If you enter "Joe" in the pop up the returned record will be "pass", "Sally" would return "word" etc.

Basically what this does for you is keeps the SQL "Select" statement in the database and allows you to pass in a variable (parameter) to the "Select" statement in the database.

Moving forward you do not need the line:

selectSQL = "SELECT Password FROM tblGebruikers ";
                selectSQL += "WHERE Naam= @textBox2";

in your code selection as this would be the query (SQL Select) statement that you put into Access. All you would need to do is set up the command object, the parameters, and then execute the command and the returning recordset would contain the password information that corresponds to the username that you passed to the database. The idea is to get the SQL Select statement out of your code and have the database contain it. Does that clear things up?

If you choose to battle wits with the witless be prepared to lose.

 

[Foada]

Ok finally got to a computer with VS on it. I don't know what version you are using but this is from VS2003. In MS Access create a database and call it "ProcTest". Create a table called "Logins" with the the following columns:

ID - Autonumber
Username - Text
Password - Text

Save the table as "Logins" and add some data to the fields. Next select "Queries" in Access and design a new one with the design view wizard and close the select table window. Right click and select "SQL" from the options and add the following

SELECT Logins.Password FROM Logins WHERE Logins.Username=[parauser];

and save the query as "MockProc"

Next in VS on a form add a command button and name it "btnGet", a text box and name it "txtUser" and a text box named "txtPass". In the command buttons Click event add the following:

private void btnGet_Click(object sender, System.EventArgs e)
{
	txtPass.Text = "";	// clear the password text box for good measure

	string filePath = @"C:\Projects\Access\ProcTest.mdb";
	string constr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath;

	// Set the connection object
	OleDbConnection con = new OleDbConnection(constr);

	// Set the command object
	OleDbCommand com = new OleDbCommand();
	com.CommandText = "MockProc";			//MockProc is the name of the Access Query
	com.CommandType = CommandType.StoredProcedure;
	com.Connection = con;

	// Set the parameters to pass to the database
	com.Parameters.Add("parauser",OleDbType.Char);
	com.Parameters["parauser"].Value = txtUser.Text;
	
	// Open and read the data
	con.Open();
	OleDbDataReader rdr = com.ExecuteReader();

	// Make sure the reader has data
	if (rdr.HasRows)
	{
		rdr.Read();
		txtPass.Text = rdr.GetString(0);
	}
	else
	{
		txtPass.Text = "No user found";
	}
	rdr.Close();
	con.Close();
}

This is just a quick example but it should get the point across. You may need to make some adjustments for different version of Visual Studio. Hope this helps.

If you choose to battle wits with the witless be prepared to lose.

 

[patrickstrijdonck]

Thanks for that, ill give that a try tomorrow as far as i can see, i understand the code which you are providing, and it should work for VS2008 which im using. Ill let you know!

Then another question:

    string filePath = @"C:\Projects\Access\ProcTest.mdb";    string constr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath;

Can this be placed somewhere else in the VS Project?? ive got a couple of forms, and when i need to change the location of the MS Access Database, i need to change all forms filepath to the database(dont got lots of forms now, but this project will include lots of them in the future......)

 

[AlexCuse]

In your folder tree in visual studio, expand the "Properties" folder. Look for a file called Settings.settings. Open this file.

You'll see a table for entering your different settings. In the "name" field enter a name (I used "PrimaryConnectionString").

Then set the "type" to (Connection String) and "scope" to Application.

Then, for the value, enter your entire connection string:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Projects\Access\ProcTest.mdb

Once all this is entered, save the file.

You can then access this string anywhere you need it in your code, like so:

MessageBox.Show(Properties.Settings.Default.PrimaryConnectionString);

Hope it helps, and have fun

Alex

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[patrickstrijdonck]

Thanks alot, That solution is working, i now also now where i can set strings for the whole application, didnt know that it seems that books dont tell everything

 

[patrickstrijdonck]

@Foada:

Thanks for that, It took some time to tweak your code into working with VS2008, but ive got it working now, de code looks like this now:

                string cmdstr = Properties.Settings.Default.IT4ConnectionString1;

                OleDbConnection con = new OleDbConnection(cmdstr);
                OleDbCommand com = new OleDbCommand();
                com.Connection = con;

                // Set the command object    
                    
                com.CommandText = "loginit";            //MockProc is the name of the Access Query    
                com.CommandType = CommandType.StoredProcedure;

                con.Open();
                com.Parameters.Add("parauser", OleDbType.Char); 
                com.Parameters["parauser"].Value = textBox2.Text;

                OleDbDataReader rdr = com.ExecuteReader();
                // Make sure the reader has data    
                if (rdr.HasRows)    
                {        
                    rdr.Read();
                    Properties.Settings.Default.Password = rdr.GetString(0);    
                }    
                else    
                {
                    MessageBox.Show("No such user in database!", "User Unknown");   
                }    
                rdr.Close();
                con.Close();

 

[Foada]

Glad to help. Keep in mind that this was just an example. You are going to want to utilize better OOP design methods and error checking on a real world app.

If you choose to battle wits with the witless be prepared to lose.

 

[patrickstrijdonck]

yup, but this progrem is going to be a real world app, but just for my business ive got plenty of time to finish this, and its not a huge app, actually, im getting quite far already. little by little, step by step, im learning and getting what i need

thanks!