executenonquery problem in c#.net

Question

0.00/5 (No votes)

See more:

I'm having problem in executing non query sql statement. Bear in mind, I'm still new to .NET framework and MySQL. Here is my code.

private void Binding()
{
    string connStr = "datasource=localhost;port=3306;username=root;password=root;";
    conn = new MySqlConnection(connStr);
    MySqlCommand command = conn.CreateCommand();
    try
    {
        string database = schemaForm.getData;
        dtable = new DataTable();
        bindingSource = new BindingSource(); ;

            conn.Open();
            command.CommandText = "SELECT Metabolite_Name" +
                                  "FROM " + database +
                                  ".Metabolites WHERE"+
                                  " MetaboliteID IN ('met1', 'met2');";
            command.ExecuteNonQuery();
            sqlData.SelectCommand = command;
            sqlData.Fill(dtable);
            bindingSource.DataSource = dtable;
            dbMetName.DataSource = dtable;
            dtable.Columns.Add("Metabolite Name");
            dbMetName.DataSource = dtable;
            conn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Passing value from getData form

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace DynamicSimulator_v2
{
    public partial class SchemaName : Form
    {
        private static string data;
        public SchemaName()
        {
            InitializeComponent();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Hide();
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            data=txtDB.Text;
            this.Hide();
        }

        public string getData
        {
            set
            {
                data = txtDB.Text;
            }
            get
            {
              return data;
            }
        }
    }
}

The error shows that my query is wrong. I pretty sure the query was right as I tested it on MySQL script. And I try to show it in datagridview by the way. dbMetName is datagridview.

Posted 20-Oct-13 9:45am

arave0521

Updated 28-Apr-19 18:40pm

v2

Add a Solution

2 solutions

Solution 2

public partial class Login : Form
{
SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Library_management_system;Integrated Security=True;User Instance=True");
int count = 0;
public Login()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
SqlCommand cmd=con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Library_person where usernaem='"+textBox1.Text+"' and password='"+textBox2.Text+"'";

cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
count=Convert.ToInt32(dt.Rows.Count.ToString());
if(count==0)
{
MessageBox.Show("username password does not match");
}
else
{

this.Hide();
mdi_user mu = new mdi_user();
mu.Show();
}
}

private void Login_Load(object sender, EventArgs e)
{
if(con.State==ConnectionState.Open)
{
con.Close();
}
con.Open();

}
}

C#

Posted 28-Apr-19 18:41pm

Member 14344246

Comments

CHill60 1-May-19 10:25am

Apart from the huge security risk of storing a password in plain text in your database, and the huge security risk of being vulnerable to SQL Injection attack (concatenated string to create a query using user input), you are using the ExecuteNonQuery method to execute a query. Wrong on so many levels

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)

Ranjan.D · Accepted Answer · 2013-10-20T10:18:00

Solution 1

Quote:
command.ExecuteNonQuery();

is used for Insert/Update

You should be using ExecuteReader and load the data to DataTable and finally bind to Grid.

SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dt = new DataTable();
dt.Load(dr);

Posted 20-Oct-13 10:18am

Ranjan.D

Comments

arave0521 20-Oct-13 16:20pm

If I'm using ExecuteReader, can I insert and edit data during runtime? Can you show it based on my code. I still don't get it. Thanks

Ranjan.D 20-Oct-13 16:28pm

You can't do insert/update operation with ExecuteReader. Your code for Binding does get all the data and you are trying to bind to Grid. I do not see any Insert Operation.

Here's the modified code

private void Binding()
{
string connStr = "datasource=localhost;port=3306;username=root;password=root;";
conn = new MySqlConnection(connStr);
MySqlCommand command = conn.CreateCommand();
try
{
string database = schemaForm.getData;
dtable = new DataTable();

conn.Open();
command.CommandText = "SELECT Metabolite_Name" +
"FROM " + database +
".Metabolites WHERE"+
" MetaboliteID IN ('met1', 'met2');";
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
dtable.Load(dr);
dbMetName.DataSource = dtable;
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

arave0521 20-Oct-13 16:35pm

the code still have proble with sql command. I pretty sure the query was right.

Ranjan.D 20-Oct-13 16:40pm

I have modified the code a bit.. Try now.. Also put a breakpoint and check whether the datatable is being populated.

arave0521 20-Oct-13 16:45pm

The database has being populated. I had checked it in Mysql workbench. When I run the modified code, it says that error in SQL syntax near '.Metabolites WHERE MetaboliteID IN ('met1', 'met2');

Ranjan.D 20-Oct-13 16:52pm

I think the problem is with the table name - .Metabolites , Please remove (.) dot or make sure the table name is correct. Also you can put a breakpoint in command.CommandText , do a single step debugging by pressing F11 , then copy the sql query and execute the same in MySql bench , sure you will know what's the problem.

arave0521 20-Oct-13 16:49pm

Here is the project file that I am stuck
https://www.dropbox.com/s/c8z58opqorplom6/DynamicSimulator_v2.zip

Ranjan.D 20-Oct-13 17:00pm

Here's the modified code.

Note : The database connection string has been changed to include database.

private void Binding()
{
string connStr = string.Format("datasource=localhost;Database={0};port=3306;username=root;password=root;", schemaForm.getData);
try
{
using (conn = new MySqlConnection(connStr))
{
MySqlCommand command = conn.CreateCommand();
dtable = new DataTable();
bindingSource = new BindingSource(); ;
conn.Open();
command.CommandText = "SELECT Metabolite_Name FROM" +
"Metabolites " +
"WHERE MetaboliteID IN ('met1', 'met2');";
MySqlDataReader dr = command.ExecuteReader();
dtable.Load(dr);
dbMetName.DataSource = dtable;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

arave0521 20-Oct-13 17:05pm

The code already works! only the matter of arrangement at command.commandText. Thanks for the help. I appreciate it!

Ranjan.D 20-Oct-13 17:12pm

Ahh forgot to remove one more line of code which is not required

bindingSource = new BindingSource(); ;