Question Problem to Insert data

dcmphi

Member
Joined
Sep 14, 2017
Messages
10
Programming Experience
Beginner
Dear Sir,
I am newbie in c# i create a db in access and connect it with a windows form to insert data. But i could unable to insert data to db. It shows an error.
error image, access db and project file is attach.
here is error image:
ajnext.jpg


here is code;
C#:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
namespace MyApplication
{
    public partial class User_Registration : Form
    {
        //Declaration of global database connection string
        private OleDbConnection connection = new OleDbConnection();
        public User_Registration()
        {
            InitializeComponent();
            //access db connection String
            connection.ConnectionString = (@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = D:\db\BPS1.mdb; Jet OLEDB:Database Password = ;");                    
        }      
        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        //code for user availablity
        private void availability()
        {
            if (txtUserID.Text.Length == 0)
            {
                lblAvailablity.Text = "";
                return;
            }
            connection.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select UserID from tblUser where UserID='" + txtUserID.Text + "'", connection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                lblAvailablity.ForeColor = System.Drawing.Color.Red;
                lblAvailablity.Text = "UserID is Not Available";
            }
            else
            {
                lblAvailablity.ForeColor = System.Drawing.Color.Green;
                lblAvailablity.Text = "UserID is Available";
            }
            connection.Close();
        }
        //code for null all text field
        private void refresh()
        {
            txtUserID.Text = "";
            txtFName.Text = "";
            txtLName.Text = "";
            txtPassword.Text = "";
            txtRPassword.Text = "";
            cboUserLevel.Text = "";
            txtUserID.Focus();
        }
        //code for add user
        private void adduser()
        {
            try
            {
                connection.Open();


                OleDbCommand cmd = new OleDbCommand("insert into tblUser(UserID,Password,FirstName,LastName)values(@UserID, @Password, @FirstName, @LastName, @UserLevel)", connection);
                cmd.Parameters.AddWithValue("UserID", txtUserID.Text);
                cmd.Parameters.AddWithValue("Password", txtPassword.Text);
                cmd.Parameters.AddWithValue("FirstName", txtFName.Text);
                cmd.Parameters.AddWithValue("LastName", txtLName.Text);
                cmd.Parameters.AddWithValue("UserLevel", cboUserLevel.Text);
                cmd.ExecuteNonQuery();
                connection.Close();
                MessageBox.Show("Data Inserted Successfully");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error" +ex);
            }
        }


        private void btnRegister_Click(object sender, EventArgs e)
        {
            if (txtUserID.Text == "")
            {
                string myStringVariable1 = string.Empty;
                MessageBox.Show("UserID is requierd");
            }
            else if (txtFName.Text == "")
            {
                string myStringVariable2 = string.Empty;
                MessageBox.Show("First Name is requierd");
            }
            else if (txtLName.Text == "")
            {
                string myStringVariable2 = string.Empty;
                MessageBox.Show("Last Name is requierd");
            }
            else if (txtPassword.Text == "")
            {
                string myStringVariable2 = string.Empty;
                MessageBox.Show("Password is requierd");
            }
            else if (txtRPassword.Text == "")
            {
                string myStringVariable2 = string.Empty;
                MessageBox.Show("Confirm Password is requierd");
            }


            //For combobox validation we should follow the below code.
            else if (cboUserLevel.Text == "--Choose--")
            {
                string myStringVariable3 = string.Empty;
                MessageBox.Show("Select User Level");
            }
            else if (txtPassword.Text != txtRPassword.Text)
            {
                System.Windows.Forms.MessageBox.Show("Password and Confirm Password Field are not Same", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                txtPassword.Focus();
            }
            else if(lblAvailablity.Text == "UserID is Not Available")
            {
                MessageBox.Show("User name already exists");
                txtUserID.Focus();
            }              
            else 
            {
                adduser();
                refresh();
            }            
        }


        private void txtUserID_TextChanged(object sender, EventArgs e)
        {
            availability();
        }


        private void txtUserID_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                txtFName.Focus();
            }
        }


        private void txtFName_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                txtLName.Focus();
            }
        }


        private void txtPassword_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                txtRPassword.Focus();
            }
        }


        private void txtRPassword_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                cboUserLevel.Focus();
            }
        }


        private void cboUserLevel_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                btnRegister.Focus();
            }
        }


        private void txtLName_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                txtPassword.Focus();
            }
        }
    }
}
 

Attachments

  • MyApplication.zip
    69.8 KB · Views: 81
Last edited by a moderator:
new OleDbCommand("insert into tblUser(UserID,Password,FirstName,
Password is reserved, escape it like this: [password]
or change db field to not use reserved word.
 
@JohnH thanks for your reply. I tried to change password field to Pass and with bracket [Password] but same error. Data not inserted. please help.
 
You specify four columns to be inserted to and five values to be inserted.

By the way, you should use parameters EVERY time, not just some times. You have a query there that directly uses the Text of a TextBox. Apart from anything else, that would generate an exception if the user entered a single quote into that TextBox.
 
Back
Top Bottom