Please help me here, I need to insert data from datagridview to ms access table, and I keep getting an error that says the insert into statement contains the following unknown field name : 'Username',please help me fix this.thanks
-
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.OleDb; namespace EmployeeAttendanceRegister { public partial class SignIN : Form { public SignIN() { InitializeComponent(); } public OleDbConnection GetsqlCon() { string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/game/Desktop/3rd year/2nd Semester/INYM 328/EmployeeAttendanceRegister/EmployeeAttendanceRegister/EmployeeAttendanceRegister.accdb"; OleDbConnection mycon = new OleDbConnection(connstring); mycon.Open(); return mycon; } public void getComm(string connstring1) { OleDbConnection sqlcon = this.GetsqlCon(); OleDbCommand sqlcomm = new OleDbCommand(connstring1, sqlcon); sqlcomm.ExecuteNonQuery(); sqlcomm.Dispose(); sqlcon.Close(); sqlcon.Dispose(); } private void button1_Click(object sender, EventArgs e) { try { string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col4 = dataGridView1[3, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col5 = dataGridView1[4, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col6 = dataGridView1[5, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col7 = dataGridView1[6, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string insert_sql = "INSERT INTO Attendance(Username,Lastname,Firstname,Cellnumber,Officenumber,DepartmentName,Passwrd) VALUES('" + col1 + "','" + col2 + "','" + col3 + "','" + col4 + "','" + col5 + "','" + col6 + "','" + col7 + "')"; this.getComm(insert_sql); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
-
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.OleDb; namespace EmployeeAttendanceRegister { public partial class SignIN : Form { public SignIN() { InitializeComponent(); } public OleDbConnection GetsqlCon() { string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/game/Desktop/3rd year/2nd Semester/INYM 328/EmployeeAttendanceRegister/EmployeeAttendanceRegister/EmployeeAttendanceRegister.accdb"; OleDbConnection mycon = new OleDbConnection(connstring); mycon.Open(); return mycon; } public void getComm(string connstring1) { OleDbConnection sqlcon = this.GetsqlCon(); OleDbCommand sqlcomm = new OleDbCommand(connstring1, sqlcon); sqlcomm.ExecuteNonQuery(); sqlcomm.Dispose(); sqlcon.Close(); sqlcon.Dispose(); } private void button1_Click(object sender, EventArgs e) { try { string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col4 = dataGridView1[3, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col5 = dataGridView1[4, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col6 = dataGridView1[5, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col7 = dataGridView1[6, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string insert_sql = "INSERT INTO Attendance(Username,Lastname,Firstname,Cellnumber,Officenumber,DepartmentName,Passwrd) VALUES('" + col1 + "','" + col2 + "','" + col3 + "','" + col4 + "','" + col5 + "','" + col6 + "','" + col7 + "')"; this.getComm(insert_sql); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
First things first. Google for "SQL Injection Attack" to find out why building a SQL query string like you have is such a bad idea. I'll give you two hints: Security and Maintainability. Then Google for "C# Access Parameterized Query" for what to do about it.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
First things first. Google for "SQL Injection Attack" to find out why building a SQL query string like you have is such a bad idea. I'll give you two hints: Security and Maintainability. Then Google for "C# Access Parameterized Query" for what to do about it.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.OleDb; namespace EmployeeAttendanceRegister { public partial class SignIN : Form { public SignIN() { InitializeComponent(); } public OleDbConnection GetsqlCon() { string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/game/Desktop/3rd year/2nd Semester/INYM 328/EmployeeAttendanceRegister/EmployeeAttendanceRegister/EmployeeAttendanceRegister.accdb"; OleDbConnection mycon = new OleDbConnection(connstring); mycon.Open(); return mycon; } public void getComm(string connstring1) { OleDbConnection sqlcon = this.GetsqlCon(); OleDbCommand sqlcomm = new OleDbCommand(connstring1, sqlcon); sqlcomm.ExecuteNonQuery(); sqlcomm.Dispose(); sqlcon.Close(); sqlcon.Dispose(); } private void button1_Click(object sender, EventArgs e) { try { string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col4 = dataGridView1[3, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col5 = dataGridView1[4, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col6 = dataGridView1[5, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col7 = dataGridView1[6, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string insert_sql = "INSERT INTO Attendance(Username,Lastname,Firstname,Cellnumber,Officenumber,DepartmentName,Passwrd) VALUES('" + col1 + "','" + col2 + "','" + col3 + "','" + col4 + "','" + col5 + "','" + col6 + "','" + col7 + "')"; this.getComm(insert_sql); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
"Username" is a reserved keyword[^], and a bad choice for a column-name. You'd have to escape it, probably by putting it in [username]. A better solution would be to rename the column. You'd also want to be using "parameterizes queries" to prevent people like me from deleting everything in there. And instead of manually disposing, I'd recommend using a
usingclause.Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
"Username" is a reserved keyword[^], and a bad choice for a column-name. You'd have to escape it, probably by putting it in [username]. A better solution would be to rename the column. You'd also want to be using "parameterizes queries" to prevent people like me from deleting everything in there. And instead of manually disposing, I'd recommend using a
usingclause.Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
ya i agree :)
-
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.OleDb; namespace EmployeeAttendanceRegister { public partial class SignIN : Form { public SignIN() { InitializeComponent(); } public OleDbConnection GetsqlCon() { string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/game/Desktop/3rd year/2nd Semester/INYM 328/EmployeeAttendanceRegister/EmployeeAttendanceRegister/EmployeeAttendanceRegister.accdb"; OleDbConnection mycon = new OleDbConnection(connstring); mycon.Open(); return mycon; } public void getComm(string connstring1) { OleDbConnection sqlcon = this.GetsqlCon(); OleDbCommand sqlcomm = new OleDbCommand(connstring1, sqlcon); sqlcomm.ExecuteNonQuery(); sqlcomm.Dispose(); sqlcon.Close(); sqlcon.Dispose(); } private void button1_Click(object sender, EventArgs e) { try { string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col4 = dataGridView1[3, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col5 = dataGridView1[4, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col6 = dataGridView1[5, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string col7 = dataGridView1[6, dataGridView1.CurrentCell.RowIndex].Value.ToString(); string insert_sql = "INSERT INTO Attendance(Username,Lastname,Firstname,Cellnumber,Officenumber,DepartmentName,Passwrd) VALUES('" + col1 + "','" + col2 + "','" + col3 + "','" + col4 + "','" + col5 + "','" + col6 + "','" + col7 + "')"; this.getComm(insert_sql); } catch (Exception ex) { MessageBox.Show(ex.Message); } }