Trying to insert data into MS Access database using C# but insert command is not working

Question

I am getting an error with my insert command. I am trying to add the users input data from the text boxes on the html page into the access database I already have created and connected. I am just having a problem with the syntax of my insert command.

This is my HTML page

<form name="insert" method="post" action="insertinventory.aspx">
<center>
    <h1> FLOATEEZ Add Inventory </h1>

    Item Number: <input type="text" name="txtnum"> <br>
    Item Name: <input type="text" name="txtname"> <br>
    Item Description: <input type="text" name="txtdescription"> <br>
    Item Price: <input type="text" name="txtprice"> <br>
    Item Quantity on Hand: <input type="text" name="txtqoh"> <br>
    Item Picture: (text only) <input type="text" name="txtpicture"> <br><br>

    <input type="submit" value="Submit"> &nbsp &nbsp <input type="reset">        
</center>
</form>

This is my aspx page minus my database information

<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data.Odbc" %>
<%
Response.Write("<html><head><title>Insert into Inventory </title></head></body>");
Response.Write("<body bgcolor=lightblue>");

OdbcConnection myconn;
OdbcCommand mycmd;
OdbcDataReader myreader;

myconn= new OdbcConnection( I removed this part );
mycmd = new OdbcCommand("insert into inventory 
    (Itemnum,Itemname,Itemdescription,Itemprice,Itemqoh,Itempicture) values 
    ('"+ txtnum.Text +"','"+ txtname.Text +"','"+ txtdescription.Text 
    +"','"+ txtprice.Text +"','"+ txtqoh.Text +"','"+ txtpicture.Text 
    +"')",myconn);

myreader.Close();
myconn.Close();
%>
<br>
<center> <a href ="Company.html" > Back to our Homepage </a> </center>

wazz · Accepted Answer · 2020-04-30 03:32:44Z

1

be sure to use parameters! a question mark is used for each value you get from the user.

example below taken from https://www.mikesdotnetting.com/article/26/parameter-queries-in-asp-net-with-ms-access. (a great resource.)

string ConnString = Utils.GetConnString();    
string SqlString = "Insert Into Contacts (FirstName, LastName) Values (?,?)";

using (OleDbConnection conn = new OleDbConnection(ConnString))    
{    
  using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))    
  {    
    cmd.CommandType = CommandType.Text;    
    cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);    
    cmd.Parameters.AddWithValue("LastName", txtLastName.Text);

    conn.Open();    
    cmd.ExecuteNonQuery();    
  }    
}

answered Apr 30, 2020 at 3:32

wazz

5,0885 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shawn Xiao · Accepted Answer · 2020-04-30 03:29:18Z

0

You need to bind the connection to command, and call command execution statement, e.g.:

...
mycmd.Connection = myconn;
mycmd.ExecuteNonQuery();

myconn.Close();

No reader needed.

The better way to operate the DB is:

using(OdbcConnection myconn = new OdbcConnection(connectionString))
{
    using(OdbcCommand mycmd = OdbcCommand(your code here))
    {
        mycmd.Connection = myconn;
        mycmd.ExecuteNonQuery();
    }
}

For using keyword, it will be disposing the connection and command instance automatically.

answered Apr 30, 2020 at 3:29

Shawn Xiao

5905 silver badges19 bronze badges

Collectives™ on Stack Overflow

Trying to insert data into MS Access database using C# but insert command is not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related