Unable to INSERT data in database

Question

0.00/5 (No votes)

See more:

I have a Microsoft Access Database file that I'm using for my project.
Here are my specs:

OS: Windows 7
IDE: Visual Studio 2010 Ultimate
Database Tool: Access 2007
Application Build Settings: x86 Debug

My Code:

C#

using System.Data.OleDb;//Required
private void simpleButton1_Click(object sender, EventArgs e)
        {
//Here I check if the inputs are empty or null.
            if (string.IsNullOrEmpty(textEdit1.Text) == false || string.IsNullOrEmpty(textEdit2.Text) == false || string.IsNullOrEmpty(textEdit3.Text) == false)
            {
//Function made to insert data
                SaveData(textEdit1.Text, textEdit2.Text, textEdit3.Text);
//Post action display message
                MessageBox.Show("User creation successful!");
                this.Close();
            }
            else
            {
                MessageBox.Show("Please fill out all of the information");
            }
        }
        void SaveData(string userName, string passWord, string email)
        {
            try//not sure how to use try/catch here but I tried
            {
//I'm using the Jet db provider so I can use the .mdb db file instead of the newer file type
                string connectionString = @"Data Source=C:\Users\Sagitta\Documents\RS_Studio_Login.mdb";
                 // SQL command
                System.Data.OleDb.OleDbConnection con = new OleDbConnection(connectionString);
                con.Open();
                string sqlCommand = "INSERT INTO Users (Username,Password,RegisterEmail)  " + "VALUES('" + userName + "','" + passWord + "','" + email + "')";
                System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(sqlCommand,con);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }

My Code Explained:

When the user wants to create a new login id, a new form will appear. The new form will have 3 textboxes. First textbox for username, second for password and third for email address (to recover login info).
The database is setup as follows:
Table: Users
Column1: ID
Column2: Username
Column3: Password
Column4: RegisterEmail

Problem:

I can't get my code to insert the data into the database. I ran it and tried to login with the data I tried to insert into the database. Also I tried opening the file in Microsoft Access to see if there were changes to the file.

Posted 3-Jul-12 17:25pm

shelby67

Updated 3-Jul-12 17:30pm

walterhevedeich

v2

Add a Solution

Comments

Vani Kulkarni 3-Jul-12 23:33pm

What is the error you are facing? Or is it running without any errors?

shelby67 3-Jul-12 23:36pm

yes, runtime no errors.

3 solutions

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)

SavariM · Answer 1 · 2012-07-03T18:16:00

Solution 2

it may help u

C#

string sqlCommand = "INSERT INTO Users (Username,[Password],RegisterEmail) VALUES ('" + userName + "','" + passWord + "','" + email + "')";

this is the standard query format

Posted 3-Jul-12 18:16pm

SavariM

Updated 3-Jul-12 18:56pm

v2

Comments

shelby67 4-Jul-12 0:56am

Here's what I have so far:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.OleDb;

namespace RS_Studio
{
public partial class Register : DevExpress.XtraEditors.XtraForm
{
public Register()
{
InitializeComponent();
}

private void simpleButton2_Click(object sender, EventArgs e)
{//cancel button
this.Close();
}

private void simpleButton1_Click(object sender, EventArgs e)
{//submit
if (string.IsNullOrEmpty(textEdit1.Text) == false || string.IsNullOrEmpty(textEdit2.Text) == false || string.IsNullOrEmpty(textEdit3.Text) == false)
{
SaveData();
}
else
{
MessageBox.Show("Please fill out all of the information");
}
}
void SaveData()
{//do the real work here
string insertCMD = "INSERT INTO Users (Username,Password,RegisterEmail) VALUES (@Username,@Password,@RegisterEmail)";
string ConnStr = @"Provider=SQLOLEDB;Data Source=|DataDirectory|\RS_Studio_Login.mdb";
using (OleDbConnection MyConn = new OleDbConnection(ConnStr))
{
try
{//insertCommand.Parameters.Add("Time", OleDbType.Char).Value = strTime;
MyConn.Open();
OleDbCommand Cmd = new OleDbCommand(insertCMD, MyConn);
Cmd.Parameters.Add("@Username", OleDbType.Char).Value = textEdit1.Text;
Cmd.Parameters.Add("@Password", OleDbType.Char).Value = textEdit2.Text;
Cmd.Parameters.Add("@RegisterEmail", OleDbType.Char).Value = textEdit3.Text;
Cmd.ExecuteNonQuery();
MyConn.Close();
MessageBox.Show("User creation successful!");
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

}
}
}

and when the submit button is pressed it throws this exception:
"Invalid Authorization Specification"

SavariM · Answer 2 · 2012-07-04T00:30:00

Solution 4

this exception occur in you connection string check it .

Posted 4-Jul-12 0:30am

SavariM

DamithSL · Answer 3 · 2012-07-03T17:45:00

Solution 1

try

C#

string sqlCommand = "INSERT INTO Users (Username,[Password],RegisterEmail)  " + "VALUES('" + userName + "','" + passWord + "','" + email + "')";

And use connection string with Provider

C#

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Sagitta\Documents\RS_Studio_Login.mdb";

More information about connection strings :
http://www.connectionstrings.com/access/[^]

Posted 3-Jul-12 17:45pm

DamithSL

Updated 3-Jul-12 18:02pm

v3

Comments

shelby67 3-Jul-12 23:47pm

Ok, so let me ask a few questions about your syntax so I can better understand this. Why did you use RegisterEmail rather than [RegisterEmail].
Also, how could I format this into the database.

shelby67 3-Jul-12 23:49pm

Also, the code still failed. No errors/exceptions. Just simple runtime error. It won't insert still.

Vani Kulkarni 3-Jul-12 23:51pm

What is the runtime error?

DamithSL 3-Jul-12 23:50pm

in access Password is a keyword, you can't use it as normal column, that's why I have added "[]", i'm not sure about Username, let me check

shelby67 4-Jul-12 11:55am

I changed the columns in my database and in my code. Instead of "Password" it's "Passw". same for the username. Did this to avoid issues and it still doesnt like it