Adding a row to a database

[temlehdrol]

I keep getting "The ConnectionString property has not been initialized." on the line da.Update(ds1. "Mail")

I've tried wrapping the section in a open and close (data base connection) and now I get the following error message: Syntax error in INSERT INTO statement. on this segment of code: da.Update(ds1, "Mail");

I put in a break point and checked and the DataSet has been updated with the new information

I'm able to read from the database, just can't add a new row to it...

Using C# 2005

 
 System.Data.OleDb.OleDbConnection con;
        DataSet ds1;
        System.Data.OleDb.OleDbDataAdapter da;
        int intMaxRows = 0;
        int intCurrentRow = 0;
 
        private void Form1_Load(object sender, EventArgs e)
        {
            con = new System.Data.OleDb.OleDbConnection();
            ds1 = new DataSet();
 
            openConnection();
 
            string sql = "Select * From EmailLog";
            da = new System.Data.OleDb.OleDbDataAdapter(sql, con);
 
            da.Fill(ds1, "Mail");
            NavigateRecords();
            intMaxRows = ds1.Tables["Mail"].Rows.Count;
 
            closeConnection();
 
        }
 
        private void NavigateRecords()
        {
            DataRow dRow = ds1.Tables["Mail"].Rows[intCurrentRow];
            txtDate.Text = dRow.ItemArray.GetValue(1).ToString();
            txtFrom.Text = dRow.ItemArray.GetValue(2).ToString();
            txtSubject.Text = dRow.ItemArray.GetValue(3).ToString();
            txtEmailMsg.Text = dRow.ItemArray.GetValue(4).ToString();
            txtMessageId.Text = dRow.ItemArray.GetValue(5).ToString();
 
        }
private void btnSave_Click(object sender, EventArgs e)
        {
            openConnection();
 
            System.Data.OleDb.OleDbCommandBuilder cb;
            cb = new System.Data.OleDb.OleDbCommandBuilder(da);
 
            DataRow dRow = ds1.Tables["Mail"].NewRow();
 
            dRow[1] = txtDate.Text;
            dRow[2] = txtFrom.Text;
            dRow[3] = txtSubject.Text;
            dRow[4] = txtEmailMsg.Text;
            dRow[5] = txtMessageId.Text;
 
            ds1.Tables["Mail"].Rows.Add(dRow);
 
            intMaxRows = intMaxRows + 1;
            intCurrentRow = intMaxRows - 1;
 
            da.Update(ds1, "Mail"); //Not Working
 
            MessageBox.Show("New Data Added!");
 
            closeConnection();  
        }
        private void openConnection()
        {
            con.ConnectionString = ("provider=Microsoft.Jet.OLEDB.4.0;" + "data source = C:\\PaymentFileTracker.mdb");
 
            con.Open();
            MessageBox.Show("Connection to Data Base Open");
        }
        private void closeConnection()
        {
            con.Close();
            MessageBox.Show("Connection to Data Base is Closed");
 
            con.Dispose();
        }
 

[Bassem]

Hi,

Error #1:
It is obvious that you didn't set the connection string to a valid one to the command builder. Which I can not judge here.

Error #2:
What is the schema of your table. I think Id is a numeric and Date is a DateTime. But here you add five strings as a row!
Perhaps, you should cast these values to meet the table schema.

Thanks,
Bassem

[temlehdrol]

Actually they are all strings on the database, the date I'm reading/writing actually reads "Fri, 19 Feb 2010 10:22:34 -0500" for example. Though I will eventually change that once I get everything working to just a regular DateTime field.

As far as the first error goes, I thought that the commandbuilder reconnects you to the database and all you have to do is pass it a data adapter which I do with

cb = new System.Data.OleDb.OleDbCommandBuilder(da);

When I get that error the OpenConnection and CloseConnection in private void btnSave_Click are commented out.

[Bassem]

I don't follow you, but I think my suggestions were not right.

One last thing, did you set the Id in your table to be an index - auto increased - ?

[temlehdrol]

The columns in the table are as follows:
EmailID (Auto Number), DateReceived, From, Subject, Message, MessageID (rest are all strings)

when I get the error "The ConnectionString property has not been initialized." the code reads:

# private void btnSave_Click(object sender, EventArgs e)
# {
# //openConnection();
#
# System.Data.OleDb.OleDbCommandBuilder cb;
# cb = new System.Data.OleDb.OleDbCommandBuilder(da);
#
# DataRow dRow = ds1.Tables["Mail"].NewRow();
#
# dRow[1] = txtDate.Text;
# dRow[2] = txtFrom.Text;
# dRow[3] = txtSubject.Text;
# dRow[4] = txtEmailMsg.Text;
# dRow[5] = txtMessageId.Text;
#
# ds1.Tables["Mail"].Rows.Add(dRow);
#
# intMaxRows = intMaxRows + 1;
# intCurrentRow = intMaxRows - 1;
#
# da.Update(ds1, "Mail"); //Not Working
#
# MessageBox.Show("New Data Added!");
#
# // closeConnection();
# }

That's what I meant, sorry for any confusion