Asp.net C# Save Textbox Values to Access Database

mcw92 · 14-01-2013 1:43pm #1

Hi,

Im looking for a simple piece of code that will take entered textbox values from a webform and save them to an Access database on a button click.

This is the HTML

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="Sample_Web_Applications.WebForm2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <p>
First name:<asp:TextBox ID="TextBox1" runat="server" Text=""></asp:TextBox>
    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="TextBox1" runat="server" ErrorMessage="Please enter a first name"></asp:RequiredFieldValidator><br /><br />
Last name:<asp:TextBox ID="TextBox2" runat="server" Text=""></asp:TextBox>
    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" ControlToValidate="TextBox2" runat="server" ErrorMessage="Please enter a last name"></asp:RequiredFieldValidator><br /><br />

<asp:Button ID="Button1" runat="server" Text="Insert Record" OnClick="Button1_Click" />
    </p>
    </form>
</body>
</html>

And this is the code i have for connecting

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data.OleDb;
using System.Data;

namespace Sample_Web_Applications
{
    public partial class WebForm1 : System.Web.UI.Page
    {

        string conn_string = @"Provider=Microsoft.ACE.OLEDB.12.0;
                            Data Source=C:\Users\Ciaran\Desktop\customers.accdb;
                            Persist Security Info=False;";


        protected void Page_Load(object sender, EventArgs e)
        {

        }

        

        protected void Button1_Click(object sender, EventArgs e)
        {




            OleDbConnection connection = new OleDbConnection();

            OleDbCommand mySQLCommand = new OleDbCommand();
            connection.ConnectionString = conn_string;
            connection.Open();


            mySQLCommand.CommandText = "Insert into tblCustomers(Username) Values('" + TextBox1.Text + "')";
            mySQLCommand.Connection = connection;
            connection.Open();
            mySQLCommand.ExecuteNonQuery();
            connection.Close();
        }
    }
}

Can someone tell me what is wrong?

Also i know i dont have it but i want to save but textbox 1 and textbox 2 values to the access database in the same table as seperate rows.
Thanks.

srsly78 · 14-01-2013 2:32pm

What does it say is wrong? Any diagnostic output?

Start by moving your access database to a public folder. Your web server may not have permission to access your user folder. Try putting database in c:\temp.

mcw92 · 14-01-2013 5:10pm

srsly78 wrote: »

What does it say is wrong? Any diagnostic output?

Start by moving your access database to a public folder. Your web server may not have permission to access your user folder. Try putting database in c:\temp.

Ok ive managed to add a single textbox value into a access database.

Im wondering now how would i go about adding a textbox2.text to it?

Ive tried this :

"Insert into tblCustomers(Username, Password) Values('" + TextBox1.Text + "', '" + Textbox2.Text + "')"

But i get the error , syntax error in ISERT INTO statement.
So its that line thats wrong.

Also in the end, i will be adding 7 different items to the database, which will then have to be read and displayed on different pages.
Also a username and password system that allows users to login, is there an easy way to do this?

Thanks.

srsly78 · 14-01-2013 5:18pm

Validate your sql by running it manually first. Then if you still have problems debug the final sql string generated, and make sure it matches what you were testing manually.

Do you know how to use breakpoints etc in visual studio? F9 is the default shortcut for it, but depends on what version/flavour of VS you use.

mcw92 · 14-01-2013 6:12pm

srsly78 wrote: »

Validate your sql by running it manually first. Then if you still have problems debug the final sql string generated, and make sure it matches what you were testing manually.

Do you know how to use breakpoints etc in visual studio? F9 is the default shortcut for it, but depends on what version/flavour of VS you use.

Ok ive managed to add numerous values to the database. But ive encountered a problem, users are required to enter a password, which is in textmode="password", when i try to add it to the database i get an error, anyone have a solution for this thanks?
Is there anyway i can change the password back to a normal string before i pass it to the database?

Also i have a dropdown box with numbers as the data,

how would i go about saving them to the database?

Ive tried :

int year = Convert.ToInt16(DropCourseYear.SelectedValue);
.
.
.
.
..........."Insert into Users(Year) Values('" + year + "')";
.
.
.

but i get the same SYNTAX error.

I know its to due with the .selectedvalue part bit not sure what to do about it?

Cheers.

srsly78 · 14-01-2013 6:24pm

Show us the actual generated sql string... The DropCourseYear.SelectedValue probably does not contain what you think.

mcw92 · 14-01-2013 6:31pm

srsly78 wrote: »

Show us the actual generated sql string... The DropCourseYear.SelectedValue probably does not contain what you think.

What do you mean?

Im using a data source to read data from an access database to populate the drop down box.

<asp:DropDownList ID="DropCourseYear" runat="server" DataSourceID="AccessDataSource2" 
            DataTextField="Year" DataValueField="Year" >
    </asp:DropDownList>
        <asp:AccessDataSource ID="AccessDataSource2" runat="server" 
            DataFile="~/Users.accdb" SelectCommand="SELECT [Year] FROM [Course Year]">
        </asp:AccessDataSource>

The access tables contains, 1,2,3,4 and 5.

Users select one of the value, and i want it to save to the database as an int value.

The row year in the table has a data type of Number.

srsly78 · 14-01-2013 6:38pm

Instead of executing the sql, throw up a message box showing the exact sql text. You have been making assumptions about what this sql contains instead of actually checking..

mcw92 · 14-01-2013 6:40pm

srsly78 wrote: »

Instead of executing the sql, throw up a message box showing the exact sql text.

Sorry ive lost you, im only learning about this yet, im not great on the SQL side of things

srsly78 · 14-01-2013 6:48pm

Exactly, which is why you need to actually look at your sql string.

Put a breakpoint in your code and inspect the sql string, either that or print it out in a messagebox (or to the console or whatever). This way you can see why you are getting a syntax error.

This is called debugging, and is a very important skill to learn.

mewso · 17-01-2013 12:54pm

Wow no mention of sql injection risks eh. Your current error is more than likely because you are putting single quotes around a number (only required for strings and dates) but regardless of this you need to learn to use parameters rather than passing in your values using string concatenation.

Here is a simple example - http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

Passing values entered by a user directly into your sql string is a major no no security wise. It is called sql injection because a malicious user could very easily pass something like "delete * from users" to your code.

Also you can construct your sql without going through the hoops you are now to ensure the syntax is good:-

"Insert into tblCustomers(Username, Password) Values('" + TextBox1.Text + "', '" + Textbox2.Text + "')"

can become

"Insert into tblCustomers (Username, Password) Values (@username, @password)"

Cleaner and easier to debug.

Asp.net C# Save Textbox Values to Access Database

Comments