I am just learning asp.net and i know this is a simple question but how do I insert data into my Ms Access file? I dont get any errors when i click the submit button. But when i open the access file it not here. Here is my code..
To your codes, I think the problem is due to that you haven't executed your SqlCommand, so you must call ExecuteNonQuery() first. And you should put the codes before connection.Close().
PS: I don't suggest you using strings combination, which is very dangerous because of SQL injection and it's hard for both of us to read it out.
@Mikesdotnetting:
Yes, to be honest I know that your answer is right and nice, but mine is also answering directly the questioner's question.;)
Aido222
Member
2 Points
8 Posts
Adding data to a ms access file
Nov 27, 2012 06:34 PM|LINK
Hi,
I am just learning asp.net and i know this is a simple question but how do I insert data into my Ms Access file? I dont get any errors when i click the submit button. But when i open the access file it not here. Here is my code..
string conn_string = @"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Users\user\Documents\College Work\3rd Year\web Programming\House100DataBase.accdb;
Persist Security Info=False;";
protected void btnSubmit_Click(object sender, EventArgs e)
{
OleDbConnection connection = new OleDbConnection();
OleDbCommand mySQLCommand = new OleDbCommand();
OleDbDataReader myDataReader;
connection.ConnectionString = conn_string;
connection.Open();
mySQLCommand.Connection = connection;
mySQLCommand.CommandText = "INSERT INTO users (fName,sName) VALUES ('" + txtbFirstName + "','" + txtbLastName + "')";
connection.Close();
Mikesdotnett...
All-Star
154955 Points
19872 Posts
Moderator
MVP
Re: Adding data to a ms access file
Nov 27, 2012 07:31 PM|LINK
Vile. Don't do that. Use parameters (http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access). And ExecuteNonQuery.
using (OleDbConnection connection = new OleDbConnection(conn_string)){ string query = "INSERT INTO users (fName,sName) VALUES (@FirstName, @LastName)"; OleDbCommand mySQLCommand = new OleDbCommand(query, connection); mySQLCommand.Parameters.AddWithValue("@FirstName", txtbFirstName.Text); mySQLCommand.Parameters.AddWithValue("@LastName", txtbLasstName.Text); connection.Open(); mySQLCommand.ExecuteNonQuery(); }Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Adding data to a ms access file
Nov 28, 2012 05:07 AM|LINK
To your codes, I think the problem is due to that you haven't executed your SqlCommand, so you must call ExecuteNonQuery() first. And you should put the codes before connection.Close().
PS: I don't suggest you using strings combination, which is very dangerous because of SQL injection and it's hard for both of us to read it out.
@Mikesdotnetting:
Yes, to be honest I know that your answer is right and nice, but mine is also answering directly the questioner's question.;)
Aido222
Member
2 Points
8 Posts
Re: Adding data to a ms access file
Nov 28, 2012 05:54 PM|LINK
Thank you, that code worked perfect
I have one more thing to ask. How would i go about checking wether a username already exists in the DB before adding a new user.
Thanks again
Mikesdotnett...
All-Star
154955 Points
19872 Posts
Moderator
MVP
Re: Adding data to a ms access file
Nov 28, 2012 06:52 PM|LINK
Select the count of rows that match the user name
using (OleDbConnection connection = new OleDbConnection(conn_string)){ string query = "SELECT Count(*) FROM users WHERE fName = @FirstName" OleDbCommand mySQLCommand = new OleDbCommand(query, connection); mySQLCommand.Parameters.AddWithValue("@FirstName", txtbFirstName.Text); connection.Open(); if(mySQLCommand.ExecuteScalar() > 0){ //name exists, do nothing } else { mySQLCommand.CommandText = "INSERT INTO users (fName,sName) VALUES (@FirstName, @LastName)"; mySQLCommand.Parameters.Clear(); mySQLCommand.Parameters.AddWithValue("@FirstName", txtbFirstName.Text); mySQLCommand.Parameters.AddWithValue("@LastName", txtbLastName.Text); mySQLCommand.ExecuteNonQuery(); } }Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
Aido222
Member
2 Points
8 Posts
Re: Adding data to a ms access file
Nov 28, 2012 10:04 PM|LINK
Thank you, Great help, much appreciated