string method null return

Question

1.00/5 (2 votes)

See more:

I am creating Student attendance info system.
These methods are related to each other as you can see.
"students" table has general info about each student.
Some students are always absent, so no students_ID in "register" table.
And this cause for an error with da.Fill(dt) saying :
MySqlException was unhandled by user code.
"You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1".
How can I eliminate this error?

C#

private string getUquvchilar_ID()
    {
        var fish = ddlName.Text.Replace("'", "''");
        MySqlConnection con = new MySqlConnection(constr);
        con.Open();
        string query = "Select Students_ID from students where CONCAT(Familiyasi , ' ', Ismi , ' ', Sharifi)='" + fish + "'";
        MySqlCommand cmd = new MySqlCommand(query);
        cmd.Connection = con;
        String s = (cmd.ExecuteScalar() ?? String.Empty).ToString();
        con.Close();
        return s;
    }
    private void BindGrid()
    {
        MySqlConnection con = new MySqlConnection(constr);
        MySqlCommand cmd = new MySqlCommand("SELECT * FROM rasmlar where Rasm_ID in (" + String.Join(",", getImage_ID()) + ")", con);
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        gvImages.DataSource = dt;
        gvImages.DataBind();
    }
    private List<int> getImage_ID()
    {
        List<int> i = new List<int>();
        MySqlConnection con = new MySqlConnection(constr);
        con.Open();
        string query = "Select bosh_rasm_ID, Tugal_rasm_ID from register where students_ID='" + getUquvchilar_ID() + "'"; 
        MySqlCommand cmd = new MySqlCommand(query);
        cmd.Connection = con;
        MySqlDataReader reader = cmd.ExecuteReader();
        if (reader != null && reader.HasRows)
        {
            foreach (DbDataRecord s in reader)
            {
                if (!reader.IsDBNull(reader.GetOrdinal("bosh_rasm_ID")))
                    i.Add(s.GetInt32(0));
                else
                    i.Add(0);

                if (!reader.IsDBNull(reader.GetOrdinal("Tugal_rasm_ID")))
                    i.Add(s.GetInt32(1));
                else
                    i.Add(0);
            }
        }
        return i;
    }

Posted 18-Sep-15 1:59am

abdujalilc

Updated 18-Sep-15 2:25am

v3

Add a Solution

Comments

Naveen.Sanagasetti 18-Sep-15 8:15am

Where is the error log, please share those details instead of share your full content.

ZurdoDev 18-Sep-15 8:43am

1. Only include the relevant code. You posted 3 SQL statements. Please don't ask us to guess which one is causing the error.
2. Use parameterized queries instead of concatenating in your values. Your code is open to sql attacks right now.

Richard Deeming 18-Sep-15 10:36am

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Krunal Rohit 19-Sep-15 0:18am

What line causing the error ?

-KR

2 solutions

Solution 1

If students_ID field is numeric data type, below sql query is incorrect, because of single quote around expression ('):

C#

string query = "Select bosh_rasm_ID, Tugal_rasm_ID from register where students_ID='" + getUquvchilar_ID() + "'";

As Richard Deeming[^] had mentioned in a comment to the question, do not use such of queries, use parametrized queries instead:

C#

string query = "Select bosh_rasm_ID, Tugal_rasm_ID from register where students_ID=@stuId";

where @stuid is a named parameter. Use AddWithValue method to add parameter to the MySqlCommad.ParameterCollection

C#

cmd.Parameters.AddWithValue("@stuId", getUquvchilar_ID());

For further information, please see: 6.9.1 Preparing Statements in Connector/Net[^]

Posted 20-Sep-15 8:59am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-09-20T09:50:00

To add to previous solution: One more potential source for errors is the IN clause on the following line:

C#

MySqlCommand cmd = new MySqlCommand("SELECT * FROM rasmlar where Rasm_ID in (" + String.Join(",", getImage_ID()) + ")", con);

If the getImage_ID() returns an empty list this IN clause would be empty thus causing a syntactic error.

Beyond that I wonder why you fetch the data separately in the first place. If I'm rading the code correctly you could combine the two select statements and save time and code with that. In other words the SQL statement could be something like

SQL

SELECT a.* 
FROM rasmlar a
WHERE a.Rasm_ID IN (SELECT COALESCE(bosh_rasm_ID, Tugal_rasm_ID) 
                    FROM register r
                    WHERE r.students_ID = @students_ID)