need help on manipulating msaccess using c#

Question

0.00/5 (No votes)

See more:

try 
            { 
                OleDbConnection conn = new OleDbConnection(
                    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|OCSRSRecord.mdb;Persist Security Info=True");
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("INSERT INTO USERS VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", conn);
                cmd.Parameters.Add("user_id", OleDbType.VarChar);
                cmd.Parameters.Add("password", OleDbType.VarChar);
                cmd.Parameters.Add("levelType", OleDbType.VarChar);
                cmd.Parameters.Add("user_type", OleDbType.VarChar);
                cmd.Parameters.Add("user_lastName", OleDbType.VarChar);
                cmd.Parameters.Add("user_firstName", OleDbType.VarChar);
                cmd.Parameters.Add("user_middleName", OleDbType.VarChar);
                cmd.Parameters.Add("user_gender", OleDbType.VarChar);
                cmd.Parameters.Add("user_civilStatus", OleDbType.VarChar);
                cmd.Parameters.Add("user_birthday", OleDbType.Date);
                cmd.Parameters.Add("user_address", OleDbType.VarChar);
                cmd.Parameters.Add("user_emailAddress", OleDbType.VarChar);
                if (passBox.Text.Equals(confirmPassBox.Text))
                {
                    cmd.Parameters["user_id"].Value = userIDBox.Text;
                    cmd.Parameters["password"].Value = passBox.Text;
                    cmd.Parameters["user_type"].Value = userTypeBox.Text;
                    cmd.Parameters["user_lastName"].Value = lastNameBox.Text;
                    cmd.Parameters["user_firstName"].Value = firstNameBox.Text;
                    cmd.Parameters["user_middleName"].Value = middleNameBox.Text;
                    cmd.Parameters["user_gender"].Value = genderBox.Text;
                    cmd.Parameters["user_civilStatus"].Value = civilStatusBox.Text;
                    cmd.Parameters["user_birthday"].Value = DateTime.Parse(birthDateBox.Text);
                    cmd.Parameters["user_address"].Value = addressBox.Text;
                    cmd.Parameters["user_emailAddress"].Value = emailBox.Text;
                    cmd.Parameters["levelType"].Value = teachTypeBox.Text;

                    cmd.ExecuteNonQuery();

                    MessageBox.Show("Account " + userIDBox.Text + " saved", "Account Creation Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    conn.Close();
                    this.Close();
                }
                else 
                {
                    MessageBox.Show("Password did not match", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }

Here is my code... and i think it is working because when i create a new account on my USERS table, and when i logout and use the new account that i created to login into my application, it works... but the problem is when i open my msaccess database, i dont see the new record i created... and when i tried to to login again on my application using my new created account, it does not work anymore...

base on my observation. Everytime i open my msaccess database, my new accounts that i created disappears... please help :(

Posted 21-Oct-11 1:16am

Badudles

Add a Solution

2 solutions

Solution 1

I would suggest some improvements in the code........

Instead of this .....

C#

OleDbCommand cmd = new OleDbCommand("INSERT INTO USERS VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", conn);

try using

C#

OleDbCommand cmd = new OleDbCommand("INSERT INTO USERS(col1,col2,col3,col4,col5) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", conn);

col1 are name of actual columns in tables.......

You can confirm if record was inserted in table by using.........

C#

int i=cmd.ExecuteNonQuery();
if(i>0)
{
MessageBox.Show("Record Added");
}

Instead of using parameter two times like.......

C#

cmd.Parameters.Add("user_id", OleDbType.VarChar);
cmd.Parameters["user_id"].Value = userIDBox.Text;

use the following thus reduce you coding effort and time if you like I m too lazy.....;-)

C#

cmd.Parameters.Add("user_id", OleDbType.VarChar).Value=userIDBox.Text;

Best of luck......

Posted 21-Oct-11 1:35am

Mantu Singh

Updated 21-Oct-11 1:57am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Badudles · Accepted Answer · 2011-10-21T02:01:00

Solution 2

i've tried your solution sir... an error occur and it says "Syntax error in INSERT INTO statement"... i think its the column part... btw, thanks for the less code tip :)
sir Mantu Singh, do you think the problem is on my msaccess database?

Posted 21-Oct-11 2:01am

Badudles

Updated 21-Oct-11 2:17am

v2

Comments

Mantu Singh 21-Oct-11 8:30am

the col1,col2 etc must match the table columns
the order you give in columns you need to add parameters in same order

Try to use this @ symbol in parameter name............
cmd.Parameters.Add("@user_id", OleDbType.VarChar).Value=userIDBox.Text;
check insert statement according to columns you need to insert no of ? mark must be equal to no of columns for which you insert data

Mantu Singh 21-Oct-11 8:36am

try to use Access 2002 data file format for better results
OleDbType.Date) may sometimes cause problem try using varchar instead
The format of date is not fixed in acces ;means if you insert 20/10/2011(d/m/y)
if ur system regional setting changes to(m/d/y) it automatically changes in table to 10/20/2011 so check that..........best of luck