|
How to fill directly the cells of a datagrid linked to a table in a databasis? I tried this code but no data is added to the table. It only works when the data comes from a textbox and then sent to the datagrid cells :
public void BD_Conexao()
{
try
{
OdbcConnection con = new OdbcConnection("driver= {MySQL ODBC 5.1 Driver};server=xxxxx; database=lic; uid=es; password=1234; option = 3 ");
con.Open();
}
catch (Exception ex)
{
Console.WriteLine("Erro na ligação à base de dados. \n{0}", ex.Message);
return;
}
}
public void Consulta()
{
con = new OdbcConnection("driver= {MySQL ODBC 5.1 Driver};server=xxx; database=lice; uid=estagio; password=1234; option = 3 ");
con.Open();
OdbcCommand Command = con.CreateCommand();
Command.CommandText = "select lojas.Id, lojas.NIF, lojas.Loja, lojas.Bloqueado, lojas.DataFim, lojas.lastupdate, lojas.Nome";
Command.CommandType = CommandType.Text;
Command.Connection = con;
OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = Command;
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
grid_lic.DataSource = dataSet;
grid_lic.DataMember = dataSet.Tables[0].TableName;
}
private void bt_preencher_Click(object sender, EventArgs e)
{
BD_Conexao();
string DataFim = dateTimePicker.Value.ToString("yyyy-MM-dd");
string lastupdate = dateTimePicker2.Value.ToString("yyyy-MM-dd");
Commandtext = "insert into lojas (NIF,Loja,bloqueado, DataFim, lastupdate, Nome) values (@NIF,@Loja,@Bloqueado,@DataFim,@lastupdate,@Nome)";
OdbcCommand Command = new OdbcCommand(Commandtext, con);
Command.CommandType = CommandType.Text;
Command.Parameters.AddWithValue("@NIF", grid_lic.CurrentRow.Cells[1].Value);
Command.Parameters.AddWithValue("@Loja", grid_lic.CurrentRow.Cells[2].Value);
Command.Parameters.AddWithValue("@Bloqueado", checkBox_bloq.Checked);
Command.Parameters.AddWithValue("@DataFim", grid_lic.CurrentRow.Cells[4].Value);
Command.Parameters.AddWithValue("@lastupdate", grid_lic.CurrentRow.Cells[5].Value);
Command.Parameters.AddWithValue("@Nome", grid_lic.CurrentRow.Cells[6].Value);
Command.ExecuteNonQuery();
Consulta();
}
modified 9-Dec-15 10:09am.
|
|
|
|
|
Command.CommandText = "select lojas.Id, lojas.NIF, lojas.Loja, lojas.Bloqueado, lojas.DataFim, lojas.lastupdate, lojas.Nome";
That SELECT statment does not look complete, you have not told it which table to extract the data from. You should also add some error checking to your code to test that your commands work as expected.
|
|
|
|
|
The problem is not there, actually I forgot to include here the end of that select ("select lojas.Id, lojas.NIF, lojas.Loja, lojas.Bloqueado, lojas.DataFim, lojas.lastupdate, lojas.Nome from lojas";).
I´ve made exactly the same but using textboxes instead of filling directly the datagrid cell, and the data was added. The code written below works, but data is inserted in texboxes instead of datagrid cells. I don't want to use any buttons, I want to add data directly in the datagrid with no buttons nor texboxes:
string NIF = textBoxNIF.Text;
int loja = int.Parse(textBoxLoja.Text);
int bloqueador = 0;
if (checkBoxBloq.Checked == true)
bloqueador = 1;
else
bloqueador = 0;
string DataFim = dateTimePicker1.Value.ToString("yyyy-MM-dd");
string lastupdate = dateTimePicker2.Value.ToString("yyyy-MM-dd HH:mm:ss");
string Nome = textBoxNome.Text;
string Commandtext = "insert into lojas (NIF,loja,Bloqueado,DataFim,lastupdate,Nome) values ('" + NIF + "', " + loja + "," + bloqueador + ",'" + DataFim + "','" + lastupdate + "','" + Nome + "')";
OdbcCommand cm2 = new OdbcCommand(Commandtext, con);
cm2.ExecuteNonQuery();
Consulta();
Why does this code works but the first I put doesn't?
|
|
|
|
|
Why have you taken a perfectly good parameterized query, and replaced it with one that's vulnerable to SQL Injection[^]?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Which query is vulnerable to injection? What do you propose in order to fill de datagrid directly in its cells without texboxes?
Thank you.
|
|
|
|
|
Member 11449447 wrote: Which query is vulnerable to injection?
Take a guess!
Member 11449447 wrote:
string Commandtext = "insert into lojas (NIF,loja,Bloqueado,DataFim,lastupdate,Nome) values ('" + NIF + "', " + loja + "," + bloqueador + ",'" + DataFim + "','" + lastupdate + "','" + Nome + "')";
OdbcCommand cm2 = new OdbcCommand(Commandtext, con);
NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.
The OdbcCommand uses positional, not named, parameters. You have to use ? as the parameter placeholder in the query, and add the parameters in the same order as they appear in the query.
using (OdbcConnection connection = CreateConnection())
using (OdbcCommand command = new OdbcCommand("insert into lojas (NIF, Loja, bloqueado, DataFim, lastupdate, Nome) values (?, ?, ?, ?, ?, ?)", connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("NIF", grid_lic.CurrentRow.Cells[1].Value);
command.Parameters.AddWithValue("Loja", grid_lic.CurrentRow.Cells[2].Value);
command.Parameters.AddWithValue("Bloqueado", checkBox_bloq.Checked);
command.Parameters.AddWithValue("DataFim", grid_lic.CurrentRow.Cells[4].Value);
command.Parameters.AddWithValue("lastupdate", grid_lic.CurrentRow.Cells[5].Value);
command.Parameters.AddWithValue("Nome", grid_lic.CurrentRow.Cells[6].Value);
connection.Open();
command.ExecuteNonQuery();
}
As to your original problem: you're storing the OdbcConnection instance in a field, which is a bad idea. Your BD_Conexao method is creating and opening a new connection, but only stores it in a local variable. The field is never updated, so your bt_preencher_Click method is unable to use that field.
Remove the field, and use a method which creates and returns the connection object instead:
private static OdbcConnection CreateConnection()
{
return new OdbcConnection("driver= {MySQL ODBC 5.1 Driver};server=xxxxx; database=lic; uid=es; password=1234; option = 3 ");
}
public void Consulta()
{
using (OdbcConnection connection = CreateConnection())
using (OdbcCommand command = new OdbcCommand("SELECT Id, NIF, Loja, Bloqueado, DataFim, lastupdate, Nome FROM lojas", connection))
{
...
}
}
private void bt_preencher_Click(object sender, EventArgs e)
{
using (OdbcConnection connection = CreateConnection())
using (OdbcCommand command = new OdbcCommand("insert into lojas (NIF, Loja, bloqueado, DataFim, lastupdate, Nome) values (?, ?, ?, ?, ?, ?)", connection))
{
...
}
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you very much, it seems to be functioning now.
|
|
|
|
|
I design a form which show color scheme on datagridview(alternate rows).it work fine. But when I call it from mdi parent form menu strip tab, Then it does not show color on datagridview(which fill on form load function).When I run only child form it shows like color on gridview3 and datagridview4.But when I call from parent then it does not show color in datagridview3(alternate rows) and datagridview4(alternate rows).
private void Form1_Load(object sender, EventArgs e)
{
comboBox1.SelectedItem = "Select Gender";
using (SqlConnection con = new SqlConnection(conn))
{
SqlDataAdapter sda = new SqlDataAdapter("getdata", con);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
sda.Fill(ds);
ds.Tables[0].TableName = "Product";
ds.Tables[1].TableName = "Category";
dataGridView3.DataSource = ds.Tables["Product"];
dataGridView4.DataSource = ds.Tables["Category"];
}
gridrowcolor();
}
public void gridrowcolor()
{
DataGridViewCellStyle st = new DataGridViewCellStyle();
st.Font = new Font("Arial", 12, FontStyle.Bold);
for (int i = 0; i < dataGridView4.Rows.Count-1; i++)
{
int ii = Convert.ToInt32(dataGridView4.Rows[i].Cells[0].Value.ToString());
if (ii % 2 == 0)
{
dataGridView4.Rows[i].DefaultCellStyle.BackColor = Color.Gray;
dataGridView4.Rows[i].DefaultCellStyle = st;
}
else
dataGridView4.Rows[i].DefaultCellStyle.BackColor = Color.Brown;
}
for (int i = 0; i < dataGridView3.Rows.Count - 1; i++)
{
dataGridView3.CellBorderStyle = DataGridViewCellBorderStyle.None;
DataGridViewCellStyle style = new DataGridViewCellStyle();
style.Font = new Font(dataGridView3.Font, FontStyle.Bold);
int ii = Convert.ToInt32(dataGridView3.Rows[i].Cells[0].Value.ToString());
if (ii % 2 == 0)
{
dataGridView3.Rows[i].DefaultCellStyle.BackColor = Color.Yellow;
dataGridView3.Rows[i].DefaultCellStyle = style;
dataGridView3.DefaultCellStyle.SelectionForeColor = Color.Chocolate;
}
else
dataGridView3.Rows[i].DefaultCellStyle.BackColor = Color.Orange;
}
}
From Parent MDI Form
private void receiptCancelRequestToolStripMenuItem_Click(object sender, EventArgs e)
{
Form1 frm = new Form1();
frm.MdiParent = this;
frm.Show();
}
modified 9-Dec-15 6:37am.
|
|
|
|
|
You need to show us the code and explain where you think it is going wrong. Please edit your question and add the details. Note: please ensure you add <pre> tags around your code, you can use the code link above the edit window to do it.
|
|
|
|
|
I have a nice idea for the work of the program running on Android platform
A Dictionary Is it possible to help CODE
|
|
|
|
|
OK.... what? Your post doesn't make any sense at all. Are you asking how to write a Dictionary application?
|
|
|
|
|
Same question posted on QA using the name 'eng_aza'
Please do not double-post.
«I want to stay as close to the edge as I can without going over. Out on the edge you see all kinds of things you can't see from the center» Kurt Vonnegut.
|
|
|
|
|
Please make your question understandable.
|
|
|
|
|
Gentleman,
And so it goes... I created the following code to accept data from my dataset on another page and propagate it in a report viewer.
private void DisplayReport_Load(object sender, EventArgs e)
{
dataGridViewReport.DataSource = null;
DataTable dt = ds.Tables[0];
dataGridViewReport.DataSource = ds.Tables[0].DefaultView;
this.reportViewer1.Reset();
this.reportViewer1.LocalReport.ReportPath = Application.StartupPath + @" \Report1.rdlc";
ReportDataSource rds = new ReportDataSource("Precheck Report", dt);
reportViewer1.LocalReport.DataSources.Clear();
reportViewer1.LocalReport.DataSources.Add(rds);
reportViewer1.RefreshReport();
}
When the page loads, this page accepts the dataset value from the constructor and uses it in the code above. This has been an ordeal up until now, but I think this might be close. I think the problem might be that the report file cannot be found. Now, when I created the report file (Report1.rdlc), it appears with the other form files. What I thought I read is that this would then be located, on RUNTIME, in the /bin OR /release folder, but it does not appear to be there, nor can I copy it to that location. I cannot find where the actual path is, only the path while it is not running. So ... first, I am not sure if I have the code correct to create the report but it is no longer throwing any errors. The view display simply says that it cannot locate this file. And second, just where should I be pointing at so that this code (or any code) has the correct path to the file so the report can be created. Thank You!
|
|
|
|
|
You need to check the "Properties" (Build Action; Copy ... option) of the report in Solution Explorer ... That will tell you where the report winds up.
Consider making the report an "embedded resource" and referencing it that way.
(Review your LocalReport object options).
|
|
|
|
|
Thank you Gerry. I did both. No Difference. I appreciate your response. Any followup and I'll be looking. I'm a few days into this problem and I have gone nowhere. Rare for me and a total waste of my time. Appreciate your advice. Pat
|
|
|
|
|
Maybe check your path name again (your post has a "blank\" in the report's file name).
The "embedded resource name" depends on the namespace; it's not the same as a report's "file name / path".
|
|
|
|
|
Thanks Gerry. I removed the space. No difference. I put a in complete long hand path id. No Difference. No Errors. Datagrid is fully populated. I tried alternating between dataset and dataTable. Nothing. No errors. Just sits there blank. I almost feel angry that there is no easy way to take the data that is just sitting there in the grid and moving it into the report. This is do-able with Excel. But I have chosen not to do that in this case. I did not want to use Crystal Reports (never liked them), and now I am not sure if that will work either, but I certainly need a solution. I regularly use Code Project (since Y2K days), but this may be the first time in over 10 years that I cannot neither get nor figure out a solution. I greatly appreciate your time and expertise. I'll keep looking. Thank You. Pat
|
|
|
|
|
I'm retrieving data from using an SQLiteDataAdapater. Because one of the fields is a date, and WPF date fields won't except null values, I'm trapping the error and continuing processing. If the search entry contains a date all of the data is returned. If the search entry does not have a date (null), I'm only getting the first three fields of data.
104, 14, 0, 2014-01-15, 1, MB3,...
103, 12, 0, (null), , , ,...
Database call:
string sql = @"SELECT * FROM " + table + " WHERE " + table + "ID = " + id;
SQLiteCommand com = new SQLiteCommand(sql, conn);
dataTable = new DataTable(table);
sqliteDataAdapter = new SQLiteDataAdapter(sql, conn);
sqliteDataAdapter.FillError += new FillErrorEventHandler(FillError);
sqliteDataAdapter.Fill(dataTable);
FillError:
protected static void FillError(object sender, FillErrorEventArgs args)
{
DataRow theRow;
switch (args.DataTable.ToString())
{
case "Camera":
theRow = args.DataTable.Rows.Add(new object[] { args.Values[0], args.Values[1], args.Values[2], null, args.Values[4], args.Values[5], args.Values[6], args.Values[7] });
args.Continue = true;
return;
case "Equipment":
theRow = args.DataTable.Rows.Add(new object[] { args.Values[0], args.Values[1], args.Values[2], null, args.Values[4], args.Values[5], args.Values[6] });
args.Continue = true;
return;
case "Lens":
theRow = args.DataTable.Rows.Add(new object[] { args.Values[0], args.Values[1], args.Values[2], null, args.Values[4], args.Values[5], args.Values[6], args.Values[7], args.Values[8], args.Values[9], args.Values[10], args.Values[11] });
args.Continue = true;
return;
case "Software":
theRow = args.DataTable.Rows.Add(new object[] { args.Values[0], args.Values[1], args.Values[2], null, args.Values[4], args.Values[5], args.Values[6], args.Values[7] });
args.Continue = true;
return;
}
}
What am I doing wrong?
|
|
|
|
|
Robert Kamarowski wrote: What am I doing wrong?
Well, the most obvious thing is that you're writing code which is vulnerable to SQL Injection[^].
The ID should be passed as a parameter. Table and column names can't be passed as parameters, so you should verify that the name is one of the expected values, and cannot be modified by the user. Alternatively, since you only seem to have four tables, use a specific query for each table.
DataTables can accept null values in columns of any type. You shouldn't get an error when you try to load a null date into a DataTable, so I'm not sure that your FillError handler is actually doing anything.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
The FillError handler is definitely working. I've walked through the code and it works. The error is happening during the Fill() method.
I don't think Injection is the problem here. The code works fine, except for when there is a date. If I run the search against SQLiteManager there are no errors. This is a self-contained application and does not go over the internet.
VS 2015
|
|
|
|
|
Robert Kamarowski wrote: I don't think Injection is the problem here
He's not saying that it is: he's giving you a serious warning that your code is wide open to accidental or deliberate damage (or deletion) of your database, and that it needs to be fixed throughout the whole of your application as a matter of extreme priority. It doesn't matter if the app is internet or local to "just your PC" - SQL injection is a real risk. Google "Bobby Tables" one day.
Your truncated data is exactly what you should get: the fill error terminates the fill operation.
I would change the DB design to not use null dates, but use the minimum sensible value instead.
Failing that, I'd use a query that didn't return null values, replacing them at the SQL end with a "sensible" default.
Once I'd fixed my code to avoid SQL Injection, of course.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
Robert Kamarowski wrote: This is a self-contained application and does not go over the internet.
That's nice. Most damage to databases occurs from inside the company, not outside, for a multitude of reasons. Don't get into the habit of writing bad code because you think "nothing's going to happen". You don't know the future of your code or application and you could end up putting this code into an environment where the risks are increased.
|
|
|
|
|
Robert Kamarowski wrote: The error is happening during the Fill() method.
OK, what's the error?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Error:
String was not recognized as a valid DateTime
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
