Advertisement

05.07.2008 at 02:21PM PDT, ID: 23384355
[x]
Attachment Details

C# inserts empty row to MS Access database

Asked by Tarxmees in Microsoft Visual C#.Net, Microsoft Access Database, C# Programming Language

Tags: Microsoft, C#, Visual Studio 2005

I created dataset and added tableadapter manually. Also I created Insert, Delete and Update commands manually.
Problem is that update is working perfectly, but delete does not function at all and insert inserts an empty row with only primary key.... I am using MS Access 2007 database.
Can anyone tell me what I am doing wrong?
If there is something more you need me to provide you, please let me know.

Thanks!

Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:

public int SaveVendors(DataTable VendorsDatatable)
        { 
            //Save changes made to vendors datasource to database
            conn = new OleDbConnection(strConn);
 
            //Set update statment
            string strUpdate = @"UPDATE Saatjad SET saatja_nimi = ?, adr1 = ?, adr2 = ?, linn = ?, riik = ?, tel1 = ?, tel2 = ?, fax = ?, kontaktisik = ? WHERE saatja_id = ?";
            OleDbCommand UpdateCommand = new OleDbCommand();
            UpdateCommand.CommandText = strUpdate;
            UpdateCommand.Connection = conn;
            UpdateCommand.Parameters.Add(new OleDbParameter("saatja_nimi", OleDbType.VarChar, 50, "saatja_nimi"));
            UpdateCommand.Parameters.Add(new OleDbParameter("adr1", OleDbType.VarChar, 30, "adr1"));
            UpdateCommand.Parameters.Add(new OleDbParameter("adr2", OleDbType.VarChar, 30, "adr2"));
            UpdateCommand.Parameters.Add(new OleDbParameter("linn", OleDbType.VarChar, 15, "linn"));
            UpdateCommand.Parameters.Add(new OleDbParameter("riik", OleDbType.VarChar, 15, "riik"));
            UpdateCommand.Parameters.Add(new OleDbParameter("tel1", OleDbType.VarChar, 20, "tel1"));
            UpdateCommand.Parameters.Add(new OleDbParameter("tel2", OleDbType.VarChar, 20, "tel2"));
            UpdateCommand.Parameters.Add(new OleDbParameter("fax", OleDbType.VarChar, 20, "fax"));
            UpdateCommand.Parameters.Add(new OleDbParameter("kontaktisik", OleDbType.VarChar, 20, "kontaktisik"));
            UpdateCommand.Parameters.Add(new OleDbParameter("saatja_id", OleDbType.Integer, 5, ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "saatja_id", DataRowVersion.Original, null));
 
            //Set insert statement
            string strInsert = @"INSERT INTO Saatjad(saatja_id, saatja_nimi, adr1, adr2, linn, riik, tel1, " +
                "tel2, fax, kontaktisik) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            OleDbCommand InsertCommand = new OleDbCommand();
            InsertCommand.CommandText = strInsert;
            InsertCommand.Connection = conn;
            InsertCommand.Parameters.Add(new OleDbParameter("saatja_id", OleDbType.Integer, 5, "saatja_id"));
            InsertCommand.Parameters.Add(new OleDbParameter("saatja_nimi", OleDbType.VarChar, 50, "saatja_nimi"));
            InsertCommand.Parameters.Add(new OleDbParameter("adr1", OleDbType.VarChar, 30, "adr1"));
            InsertCommand.Parameters.Add(new OleDbParameter("adr2", OleDbType.VarChar, 30, "adr2"));
            InsertCommand.Parameters.Add(new OleDbParameter("linn", OleDbType.VarChar, 15, "linn"));
            InsertCommand.Parameters.Add(new OleDbParameter("riik", OleDbType.VarChar, 15, "riik"));
            InsertCommand.Parameters.Add(new OleDbParameter("tel1", OleDbType.VarChar, 20, "tel1"));
            InsertCommand.Parameters.Add(new OleDbParameter("tel2", OleDbType.VarChar, 20, "tel2"));
            InsertCommand.Parameters.Add(new OleDbParameter("fax", OleDbType.VarChar, 20, "fax"));
            InsertCommand.Parameters.Add(new OleDbParameter("kontaktisik", OleDbType.VarChar, 20, "kontaktisik"));
 
            //Set delete statement
            string strDelete = "DELETE FROM Saatjad WHERE saatja_id = ?";
            OleDbCommand DeleteCommand = new OleDbCommand();
            DeleteCommand.CommandText = strDelete;
            DeleteCommand.Connection = conn;
            DeleteCommand.Parameters.Add(new OleDbParameter("saatja_id", OleDbType.Integer, 5, ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "saatja_id", DataRowVersion.Original, null));
 
            //Initialize dataadapter
            OleDbDataAdapter odaVendors = new OleDbDataAdapter();
            odaVendors.UpdateCommand = UpdateCommand;
            odaVendors.InsertCommand = InsertCommand;
            odaVendors.DeleteCommand = DeleteCommand;
 
            //Set table mappings
            odaVendors.TableMappings.AddRange(new DataTableMapping[] 
                {new DataTableMapping("Saatjad", "Saatjad", new DataColumnMapping []
                    {new DataColumnMapping("saatja_id", "saatja_id"),
                     new DataColumnMapping("saatja_nimi", "saatja_nimi"),
                     new DataColumnMapping("adr1", "adr1"),
                     new DataColumnMapping("adr2", "adr2"),
                     new DataColumnMapping("linn", "linn"),
                     new DataColumnMapping("riik", "riik"),
                     new DataColumnMapping("tel1", "tel1"),
                     new DataColumnMapping("tel2", "tel2"),
                     new DataColumnMapping("fax", "fax"),
                     new DataColumnMapping("kontaktisik", "kontaktisik")})});
 
            //Update database
            int rows = odaVendors.Update(VendorsDatatable);
 
            return rows;
        }
[+][-]05.08.2008 at 01:26AM PDT, ID: 21523131

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.08.2008 at 01:33AM PDT, ID: 21523161

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.08.2008 at 01:41AM PDT, ID: 21523198

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.08.2008 at 03:48AM PDT, ID: 21523621

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.08.2008 at 05:01AM PDT, ID: 21523911

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Microsoft Visual C#.Net, Microsoft Access Database, C# Programming Language
Tags: Microsoft, C#, Visual Studio 2005
Sign Up Now!
Solution Provided By: Tarxmees
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628