Listview in winforms CRUD Access Database.

Sharpie

14y5k

Hi,

I'm trying to insert, update and delete content in my listview in winforms application at same time my access database.
My puzzle is still missing some important pieces in the coding or wrong query ... I need to be able to use the functionality of the controls and not do it hardcoded.

I have a listview, 3 buttons and a textbox. What I want to do is by selecting a row being able to delete it or change it in the textbox (or another control, I'm open for improvement :-) ), being able if I click on insert, to insert a row and fill it, this is just an idea behind it, as I said, I'm open for suggestions. I've done this successfully in a not-winforms application, but this way I'm stuck and I have to use the listview, Access database (Northwind -Noordenwind in Dutch-) which is connected to my application and winforms.

Thank you very much for your detailed replies, I appreciate.

MY CODE:

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;

using System.Data.OleDb;

namespace LWTestNoordenwindView

{

public partial class Form1 : Form

{

private OleDbConnection Connection;

private DataTable dTable; //behoudt query resultaten

private DataSet dSet;

private OleDbDataAdapter dAdapter;

public Form1()

{

InitializeComponent();

this.listView1.Focus();

}

private void listView1_SelectedIndexChanged(object sender, EventArgs e)

{

}

private void Form1_Load(object sender, EventArgs e)

{

try

{

Connection = new OleDbConnection();

Connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\LayerWise\Documents\Noordenwind.accdb";

Connection.Open();

dTable = new DataTable();

dSet = new DataSet();

dSet.Tables.Add(dTable);

dAdapter = new OleDbDataAdapter("select * from Klanten;", Connection); //select alle tabellen

dAdapter.Fill(dTable);

foreach (DataRow myRow in dTable.Rows)

{

listView1.Items.Add(myRow[0].ToString());

for (int i = 1; i < dTable.Columns.Count; i++)

{

listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[i].ToString());

}

catch (Exception error)

{

MessageBox.Show("FOUT:" + error.Message);

}

private void butInsert_Click(object sender, EventArgs e)

{

try

{

string Insertstring = "";

OleDbCommand InsertCMD = new OleDbCommand(Insertstring);

InsertCMD.ExecuteNonQuery();

}

catch (Exception error)

{

MessageBox.Show("FOUT:" + error.Message);

}

private void butUpdate_Click(object sender, EventArgs e)

{

try

{

OleDbCommand UpdateCMD = new OleDbCommand("UPDATE Klanten SET Bedrijf='dffgdg' WHERE Id = 1;", Connection);

OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder();

UpdateCMD.ExecuteNonQuery();

}

catch (Exception error)

{

MessageBox.Show("FOUT:" + error.Message);

}

private void butDelete_Click(object sender, EventArgs e)

{

foreach (ListViewItem RecordtoDelete in listView1.SelectedItems)

{

try

{

OleDbCommand DeleteCMD = new OleDbCommand("DELETE from Klanten where Id=3;", Connection);

listView1.Items.Remove(listView1.SelectedItems[0]);

DeleteCMD.ExecuteNonQuery();

RecordtoDelete.Remove();

}

catch (Exception error)

{

MessageBox.Show("FOUT:" + error.Message);

}

private void Form1_FormClosed(object sender, FormClosedEventArgs e)

{

Connection.Close();

}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs14y ago
Except for the line with "this.dataGridView1.Focus();" I don't see the code doing anything with dataGridView1.

Where did you get that code from? What article or whatever? It will help for me to know that so I can see how it was done there.
Sharpie14y ago
Ok guys ... this is really nut, I still didn't make it to do this coding.
I followed your advice and use datagridview and then I changed to SQL server and I use the northwind sample database which I downloaded (I also get this network login issue and an empty datagrid which is driving me crazy) ... at least my listview got filled when running
THIS IS MY CODING, I TRUELY HOPE SOMEONE COULD JUST CORRECT IT FOR ME AS I DON'T KNOW WHAT'S GOING WRONG.

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;
using System.Data.SqlClient;

namespace WFA1SQL
{
public partial class Form1 : Form

{
//private SqlConnection Connection;
private DataTable dTable;
private DataSet dSet;
private SqlDataAdapter dAdapter;
SqlCommand Command = new SqlCommand();
//SqlDataAdapter DataAdapter = new SqlDataAdapter();
//DataSet DataSet = new DataSet();
//SqlConnection Connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=" + char.ConvertFromUtf32(34) + @"C:\SQL Server 2000 Sample Databases\NORTHWND.MDF" + char.ConvertFromUtf32(34) + ";Integrated Security=True;Connect Timeout=30;User Instance=True");
//SqlConnection Connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=""..\Documents\Visual Studio 2010\Projects\NorthwindSqlDatagridADOProject\NorthwindSqlDatagridProject\NORTHWND.MDF"";Integrated Security=True;User Instance=True");
SqlConnection Connection = new SqlConnection(@"Data Source=.;Initial Catalog=NORTHWND;Integrated Security=True");

public Form1()
{
InitializeComponent();
this.dataGridView1.Focus();
}

private void Form1_Load(object sender, EventArgs e)
{
//this.customersTableAdapter.Fill(this.northwindSqlDataSet.Customers);

try
{
SqlConnection Connection = new SqlConnection();
Connection.ConnectionString = @"Data Source=.;Initial Catalog=NORTHWND;Integrated Security=True";
Connection.Open();

dTable = new DataTable();
dSet = new DataSet();
dSet.Tables.Add(dTable);

dAdapter = new SqlDataAdapter("SELECT * from Customers;", Connection);
dAdapter.Fill(dTable);
//Command = new SqlCommand("SELECT * from Customers", Connection);
//Ddapter = new SqlDataAdapter(Command);
//DAdapter.Fill(DataSet, "Customers");
MessageBox.Show("Connected to Database");
}

catch (Exception error)
{
MessageBox.Show("ERROR:" + error.Message);
}
}

private void DeleteBtn_Click(object sender, EventArgs e)
{
try
{
SqlCommand DeleteCommand = new SqlCommand("DELETE from Customers where CustomerID=2;", Connection);
SqlCommandBuilder CommandBuilder = new SqlCommandBuilder();
DeleteCommand.ExecuteNonQuery();
MessageBox.Show("Records deleted");
}

catch (Exception error)
{
MessageBox.Show("ERROR:" + error.Message);
}
}

private void SaveBtn_Click(object sender, EventArgs e)
{
try
{
SqlCommand UpdateCommand = new SqlCommand("UPDATE Customers SET CompanyName='abcdef' WHERE CustomerID=1;", Connection);
SqlCommandBuilder CommandBuilder = new SqlCommandBuilder();
UpdateCommand.ExecuteNonQuery();
MessageBox.Show("Records updated");
}

catch (Exception error)
{
MessageBox.Show("ERROR:" + error.Message);
}
}

private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
Connection.Close();
MessageBox.Show("Database connection closed");
}
}
}
kible hana14y ago
I'm completely agree with Mr.Sam Hobbs.I think you should use DataGridview and for more information about it, you can visit on http://dapfor.com/en/net-suite/net-grid/features
Sam Hobbs14y ago
My article Database Table Update in a DataGridView without Writing Code might help.

I suggest using a DataGridView instead of ListView; the ListView control is older and less flexibile.

You can use a TableAdapter as described in my article or you can use the Entity Framework. There are articles about the EF in this web site. The EF is newer technology. You can write the code yourself to load the data but one way or another you should read the data into a DataTable or a collection and then bind the control(s) to that. You almost do that but instead of the "foreach (DataRow myRow in dTable.Rows)" you can bind the control to dTable. There are articles about doing that in this web site. However you probably need to use a DataGridView instead of a ListView.