How to use a JOIN SQL statement in ASP.NET using Access?

Question

OK so I am a programming student with a headache that hopefully someone will be able to cure.

I am currently creating a website using ASP.NET in Microsoft Visual Studio for a project and here is my problem:

I have a database in Microsoft Access that contains user log in data table(Name,Surname,email,password) And a linked table containing the users' security question number(the number is a reference to an ArrayList which has the actual question) which the user chose and then also the actual answer that they submitted.

I am making use of a Web Service to handle anything that needs access to the database.

What I want to do is to select the security question number in the database and return its value but I am not sure what is the correct SQL statement for this or whether my code will allow this to work. So if anyone can help me I will appreciate it dearly. Please note that I am fairly new to programming.

Here is my code:

 //This retrieves the security question of the user.
[WebMethod]
public string GetUserQuestion(string email)
{
    try
    {
        //Connect to database.
        this.ConnectToDatabase();

        OleDbCommand cmd = conn.CreateCommand();

        //The query to return the value of the index of the question that is stored in the arraylist.
        cmd.CommandText = @"SELECT SecurityInfo.QuestionNumber FROM [SecurityInfo],[UserDetails] WHERE (UserDetails.Email = '" + email + "' JOIN [UserDetails] ON SecurityInfo.ID = UserDetails.ID)";

        //cast the value to a string
        string userQuestion = cmd.ExecuteReader().ToString();

        return userQuestion;

    }
    catch (Exception e)
    {
        return e.ToString();
    }
    finally
    {
        this.DisconnectDatabase();
    }
}

PS: The methods pretty much do what they are named for so it should not be necessary to include them in the question.

This is the error I get when testing this method in the service:

<string xmlns="http://tempuri.org/">
System.Data.OleDb.OleDbException (0x80040E14): Syntax error (missing operator) in query expression '(UserDetails.Email = '[email protected]' JOIN [UserDetails] ON SecurityInfo.ID = UserDetails.ID)'. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at PTWService.GetUserQuestion(String email) in c:\Users\Rudi\Documents\Visual Studio 2010\WebSites\PTWService\App_Code\PTWService.cs:line 108
</string>

I altered my code to this (which gets rid of the error but I am still not getting what I want from the array):

//This retrieves the security question of the user.
[WebMethod]
public string GetUserQuestion(string email)
{
    try
    {
        //Connect to database.
        this.ConnectToDatabase();

        OleDbCommand cmd = conn.CreateCommand();

        //The query to return the value of the index of the question that is stored in the arraylist.
        cmd.CommandText = @"SELECT SecurityInfo.QuestionNumber FROM [SecurityInfo] WHERE (UserDetails.Email = '" + email + "') JOIN [UserDetails] ON SecurityInfo.ID = UserDetails.ID";

        //Parse the value to a int
        int UserQuestionNr = Int32.Parse(cmd.ExecuteReader().ToString());

        //Get the question from the array.
        string Question = (string)questionArray[UserQuestionNr];


        return Question;

    }
    catch (Exception)
    {
        return null;
    }
    finally
    {
        this.DisconnectDatabase();
    }
}

So if anyone can tell me if my query statement is wrong and how to correct it or if its something else entirely I would be so grateful.

Can you show the result set you're getting along with what the correct output should be? — JeffO
– JeffO, Commented Dec 5, 2014 at 18:39
The result I am getting is null because there is some exception teasing me somewhere. The result from the database should be a number between 0-7 which represents an Index of an ArrayList which stores 8 Security questions. This is then used to get that value from the array and store it in the 'Question' variable which is then returned. — Rudi Visagie
– Rudi Visagie, Commented Dec 5, 2014 at 19:07

JeffO · Accepted Answer · 2014-12-05 18:42:19Z

2

Not sure what you're expecting. Are you getting too few/many records.

This is what I would typically expect in sql code for Access.

"SELECT SecurityInfo.QuestionNumber 
FROM [SecurityInfo] 
INNER JOIN [UserDetails] 
ON SecurityInfo.ID = UserDetails.ID
WHERE (((UserDetails.Email) = '" + email + "'));"

You should also examine the string that is being sent to the database for trouble-shooting purposes.

answered Dec 5, 2014 at 18:42

JeffO

8,1233 gold badges46 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Rudi Visagie Over a year ago

Thanks for the answer. The sql code worked great! I am getting a row now. Thanx again for the trouble. I just have some problems taking that returned row from the database and putting that value in a variable. But I am still learning so Ill pluck at it till I win hehe. But essentially you helped me with my main problem.

Rudi Visagie Over a year ago

I have found that I forgot to use the Read() method from the DataReader. That and the sql you corrected. I'm set now. THNX

Esben Skov Pedersen Over a year ago

-1 because this sql injection is passed along without as much as a warning.

JeffO Over a year ago

@EsbenSkovPedersen - Fine, use a parameter. Hopefully the code won't get released into the enterprise any time soon.

JeffO Over a year ago

@EsbenSkovPedersen - I just don't like running the risk of discouraging beginner programmers with too many best practices. Shocking the MS Access trolls haven't made their presence known.

|

Rudi Visagie · Accepted Answer · 2014-12-05 19:32:20Z

This is the solution if anyone ever wonders:

 [WebMethod]
public string GetUserQuestion(string email)
{
    try
    {
        //Connect to database.
        this.ConnectToDatabase();

        OleDbCommand cmd = conn.CreateCommand();

        //The query to return the value of the index of the question that is stored in the arraylist.
        cmd.CommandText = "SELECT SecurityInfo.QuestionNumber FROM [SecurityInfo] INNER JOIN [UserDetails] ON SecurityInfo.ID = UserDetails.ID WHERE (((UserDetails.Email) = '" + email + "'));";

        OleDbDataReader reader = cmd.ExecuteReader();

        //Only needs to be read once since only one row will always be returned.
        reader.Read();
        int questionNumber = (int)reader["QuestionNumber"];

        //Get the question from the array.
        string Question = (string)questionArray[questionNumber];


        return Question;

    }
    catch (Exception)
    {
        return null;
    }
    finally
    {
        this.DisconnectDatabase();
    }
}

Collectives™ on Stack Overflow

How to use a JOIN SQL statement in ASP.NET using Access?

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related