DataSet - DataGrid

[NekoManu]

I have a datagrid that I fill with records in the following way:

 

DAL:

public DataSet ListThaiWords()
{
  DataSet ds = new DataSet();
  OleDbDataAdapter adapter = new OleDbDataAdapter("Select ID, Word From Thai Order By Word", MNLConnection);
  adapter.Fill(ds);
  return ds;
}

 

private void frmMainForm_Load(object sender, EventArgs e)
{
 DictionaryDAL ThaiDic = new DictionaryDAL("Dictionary");
 DataSet ThaiDicDS = new DataSet();
 ThaiDicDS = ThaiDic.ListThaiWords();
 grdThaiWords.DataSource = ThaiDicDS.Tables[0];
 grdThaiWords.Columns[0].Visible = false;
 grdThaiWords.Columns[1].Width = grdThaiWords.Width;
 this.StatusBarCount = ThaiDicDS.Tables[0].Rows.Count;
}

 

Now (and this might seem like a stupid question) do I go on? I mean how do I insert data in my database? How do insert data into a different table?

[Robby]

You can use an Insert Into statement with the ExecuteNonQuery method of the Command object.

 

Btw, I would not consider DictionaryDAL class to be a true DAL because you have a Select statement in there. A DAL should not have any knowledge of any specific tables, the Select statement should be in a Business layer. Just something to think about.

[NekoManu]

You can use an Insert Into statement with the ExecuteNonQuery method of the Command object.

 

Btw, I would not consider DictionaryDAL class to be a true DAL because you have a Select statement in there. A DAL should not have any knowledge of any specific tables, the Select statement should be in a Business layer. Just something to think about.

 

Can you please explain a bit more?

[Robby]

This may explain things a bit further ( but not much) http://www.xtremedotnettalk.com/showthread.php?threadid=78714

 

You can also read some of these articles http://www.google.com/search?hl=en&lr=&ie=UTF-8&newwindow=1&q=DAL+vb.net+article&btnG=Search

[NekoManu]

You can use an Insert Into statement with the ExecuteNonQuery method of the Command object.

 

I'm sorry but I do NOT understand what you mean. All I know is that I have a datagrid on my form and that when the user enters a new line I need to create a new record in my database.

 

In the old days, I used an event and in that event I wrote code (in this case an insert command) to create a record in my database. I tried looking for an event that would do what I want but so far there is nothing even close to being able to perform a very simple operation like creating a record in a database.

[Robby]

This is as simple as it gets...

 

Private Function ExecuteNonQuery(ByVal sql As String) As String

Dim con As New OleDbConnection(ConnectString())

con.Open()

 

Try

Dim cmd As New OleDbCommand( sql, con)

 

Dim affected As Integer

affected = cmd.ExecuteNonQuery()

 

Return "Records affected " & affected.ToString

Catch ex As Exception

Return "Error occured"

Finally

con.Close()

End Try

End Function

[NekoManu]

I still don't know how to link the datagrid with this function.

 

I know I must spund very stupid to you, but I have never worked with a datagrid before and I am just starting with C#.NET

 

I still don't know how I can insert a row in my database. I think I need an event so in that event I can execute an insert statement. I don't know which event.

[Robby]

The code in my last reply will INSERT a new row into your database. From your first post it seems that you already know how to bind your datagrid to a database.

 

I don't understand what you're stuck on at this time.

[NekoManu]

The problem is I don't know where to put your code.

[NekoManu]

This is where you can find my code: http://www.xtremedotnettalk.com/showthread.php?p=428386#post428386

 

Can you please have a look?

[NekoManu]

This is some code I found somewhere, but I still can't get the bloody thing to do any updates, inserts.

 

#region Using directives

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

#endregion

namespace Thai_Dictionary
{
   partial class frmWords : MNLForm
   {
       OleDbConnection myConn;
       OleDbDataAdapter myAdapter;
       DataSet ds;
       OleDbParameter workParam = null;
       string myQuery = "select ID, Word from Thai";

       public frmWords()
       {
           InitializeComponent();
           ConnectToData(); // establish database connection and create DataSet
           grdThaiWords.DataSource = ds.Tables[0].DefaultView;
           DataTable t = ds.Tables[0];
           t.RowChanged += new DataRowChangeEventHandler(Row_Changed);
       }

       private void Words_Load(object sender, EventArgs e)
       {

       }

       public void ConnectToData()
       {
           ds = new DataSet();
           myConn = new OleDbConnection(System.Configuration.ConfigurationSettings.ConnectionStrings["Dictionary"].ConnectionString);
           myAdapter = new OleDbDataAdapter();
           myAdapter.SelectCommand = new OleDbCommand(myQuery, myConn);
           myAdapter.Fill(ds, "Thai");
           insertCommand();
           updateCommand();
       }

           public void updateCommand()
           {
               string updateQuery = "Update Thai Set Word = @Word WHERE ID = @ID";
               myAdapter.UpdateCommand = new OleDbCommand(updateQuery, myConn);

               workParam = myAdapter.UpdateCommand.Parameters.Add("@Word", OleDbType.Char);
               workParam.SourceColumn = "Word";
               workParam.SourceVersion = DataRowVersion.Current;
           }

           private void Row_Changed(object ob, DataRowChangeEventArgs e)
           {
               DataTable t = (DataTable)ob;
               Console.WriteLine("RowChanged " + e.Action.ToString() + "\t" + e.Row.ItemArray[0]);
           }

           public void insertCommand()
           {
               string insertQuery = "Insert into Thai VALUES (@Word)";
               myAdapter.InsertCommand = new OleDbCommand(insertQuery, myConn);

               workParam = myAdapter.InsertCommand.Parameters.Add("@Word", OleDbType.Char);
               workParam.SourceColumn = "Word";
               workParam.SourceVersion = DataRowVersion.Current;
           }


           public void UpdateValue()
           {
               try
               {
                   myAdapter.Update(ds, "CardTest");
                   Console.Write("Updating DataSet succeeded!");
               }
               catch (Exception e)
               {
                   Console.Write(e.ToString());
               }
           }

       }
   }