Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
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
Updated 28-Apr-19 18:40pm
v2

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);
 
Share this answer
 
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');
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#

 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900