ADO.net, how to use INSERT statement

when execute insert statement, happend exception "fail to connect datasource". if change the value( , , ) to real value(3,'dfdf',11). it work well. so it prove that it can connect to DB. and execute select statement, no problem. why insert can happened the problem how to handle it CageNo defined to AutoNumber, CageName to Text and Food to Number(long integer) in Access.

Any help, i will appreciate it.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.Odbc;

namespace WindowsApplication3
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
  private System.Windows.Forms.Button button1;
  private System.Windows.Forms.TextBox textBox1;
  private System.Windows.Forms.TextBox textBox2;
  /// <summary>
  /// Required designer variable.
  /// </summary>
  private System.ComponentModel.Container components = null;

  public Form1()
  {

   InitializeComponent();

}

  protected override void Dispose( bool disposing )
  {
   if( disposing )
   {
    if (components != null)
    {
     components.Dispose();
    }
   }
   base.Dispose( disposing );
  }

  #region Windows Form Designer generated code

  private void InitializeComponent()
  {
   this.button1 = new System.Windows.Forms.Button();
   this.textBox1 = new System.Windows.Forms.TextBox();
   this.textBox2 = new System.Windows.Forms.TextBox();
   this.SuspendLayout();
   //
   // button1
   //
   this.button1.Location = new System.Drawing.Point(144, 232);
   this.button1.Name = "button1";
   this.button1.Size = new System.Drawing.Size(136, 40);
   this.button1.TabIndex = 0;
   this.button1.Text = "button1";
   this.button1.Click += new System.EventHandler(this.button1_Click);
   //
   // textBox1
   //
   this.textBox1.Location = new System.Drawing.Point(120, 72);
   this.textBox1.Name = "textBox1";
   this.textBox1.Size = new System.Drawing.Size(104, 20);
   this.textBox1.TabIndex = 1;
   this.textBox1.Text = "";
   //
   // textBox2
   //
   this.textBox2.Location = new System.Drawing.Point(112, 144);
   this.textBox2.Name = "textBox2";
   this.textBox2.Size = new System.Drawing.Size(128, 20);
   this.textBox2.TabIndex = 2;
   this.textBox2.Text = "";
   //
   // Form1
   //
   this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
   this.ClientSize = new System.Drawing.Size(464, 374);
   this.Controls.Add(this.textBox2);
   this.Controls.Add(this.textBox1);
   this.Controls.Add(this.button1);
   this.Name = "Form1";
   this.Text = "Form1";
   this.ResumeLayout(false);

}
#endregion

  /// <summary>
  /// The main entry point for the application.
  /// </summary>
  [STAThread]
  static void Main()
  {
   Application.Run(new Form1());
  }

  private void dataGrid1_Navigate(object sender, System.Windows.Forms.NavigateEventArgs ne)
  {

  }

  private void button1_Click(object sender, System.EventArgs e)
  {

   System.Data.Odbc.OdbcDataAdapter da=null;
   System.Data.Odbc.OdbcConnection conn= new OdbcConnection();
   System.Data.Odbc.OdbcDataReader rd = null;
   System.Data.Odbc.OdbcCommand scommand = null;

   try
   {
    conn.ConnectionString = "FIL=MS Access;DSN=CIS424";
    conn.Open();
    scommand = new OdbcCommand("select CageName, Food from Cage", conn);
    rd = scommand.ExecuteReader();
    int count = rd.FieldCount;
    MessageBox.Show("dd"+count);
    string s=null;

    while(rd.Read())
    {
    if(rd.GetString(0)!= this.textBox2.Text)
    {

    int number = int.Parse (this.textBox1.Text);
    string cname = this.textBox2.Text;

string istr = "INSERT INTO Cage (CageNo, CageName, Food) " +
"VALUES ( @cNumber, @cName, @cFood)";

    OdbcCommand icommand = new OdbcCommand(istr, conn);
            icommand.Parameters.Add("@cNumber", OdbcType.Int);
    icommand.Parameters.Add("@cName", OdbcType.VarChar,50);
    icommand.Parameters.Add("cFood",OdbcType.Int);

    icommand.Parameters["@cNumber"].Value = number;
    icommand.Parameters["@cName"].Value=cname;
    icommand.Parameters["@cFood"].Value=0;

    icommand.ExecuteNonQuery();
      }
     }

   }

   catch (Exception ex)
   {
    MessageBox.Show("Failed to connect to data source");
   }

   finally
   {
    if(conn!=null)
     conn.Close();
   }

  }
}
}

thx alot!!!

Answer this question

ADO.net, how to use INSERT statement

geometrikal

The main error what that Odbc doesn't accept Named Parameters, so here is the modified working code:

private void button1_Click(object sender, System.EventArgs e)
{
    System.Data.Odbc.OdbcConnection conn= new OdbcConnection();

    try
    {
        conn.ConnectionString = "FIL=MS Access;DSN=CIS424";
        conn.Open();

        using( OdbcCommand command = conn.CreateCommand() )
        {
            OdbcParameter pNumber = new OdbcParameter( "@Number", OdbcType.Int );
            OdbcParameter pName = new OdbcParameter( "@Name", OdbcType.VarChar );
            OdbcParameter pFood = new OdbcParameter( "@Food", OdbcType.Int );
            pNumber.Value = int.Parse (this.textBox1.Text);
            pName.Value = this.textBox2.Text;
            pFood.Value = 0;

            command.CommandText = "SELECT COUNT(*) FROM Cage WHERE CageName = ";
            command.Parameters.Add( pName );

            int count = (int)command.ExecuteScalar();

            if( count > 0 )
            {
                MessageBox.Show( this, "There is allready a Cage with that name." );
                return;
            }
            else
            {
                command.Parameters.Clear();
                command.Parameters.Add( pNumber );
                command.Parameters.Add( pName );
                command.Parameters.Add( pFood );

                command.CommandText = "INSERT INTO Cage (CageNo, CageName, Food) " +
                                        "VALUES ( , , )";

                command.ExecuteNonQuery();
            }
        }
    }
    catch( Exception caught )
    {
        MessageBox.Show(caught.ToString());
    }
    finally
    {
        if(conn!=null)
            conn.Close();
    }
}

Note: I did some improvements.

Andrew D
Yes, but i don't have the database to test it. When i setup a Access Database and run a quick test it works like a sharm.
Jaylou
I do not know how to add attachment to a thread. so I send the DB to your gmail mailbox. thx alot.
selvi
System.FormatException: input string was not in a correct format. which format i need to define in db CageName is Text.
Bartjes
First take a look at the exception message:

catch (Exception ex)
{
MessageBox.Show( this, "Error while inserting values.\r\n\r\nDetails:\r\n" + ex.ToString() );
}
SriniX
i have added line:icommand.Parameters.Add("@food", OdbcType.Int);

old error:

System.Data.OdbcException: ERROR[07002][Microsoft][ODBC Microsoft Access Driver] Too few parameters Expected 3.(form 2 to 3)

at System.Data.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)

at System.Data.Odbc.OdbcCommand.ExcuteNonQuery()

at WindowsApplication3.Form1.button1_Click(Object sender, EventArgs e) in d:\cis\windowsappliction3\form1.cs:line160

icommand.ExecuteNonQuery();
ianhoc
thanks for your help!
Ronim5
I changed to follwing:

try
{
conn.ConnectionString = "FIL=MS Access;DSN=CIS424";
conn.Open();
scommand = new OdbcCommand("select CageName, Food from Cage", conn);
rd = scommand.ExecuteReader();
int count = rd.FieldCount;
MessageBox.Show("dd"+count);
string s=null;

while(rd.Read())
{
if(rd.GetString(0)!= this.textBox2.Text)
{
rd.Close();
int number = int.Parse (this.textBox1.Text);
string cname = this.textBox2.Text;
int food =int.Parse(this.textBox3.Text);

string istr = "INSERT INTO Cage (CageNo, CageName, Food) " +
"VALUES ( @cNumber, @cName,0)";

OdbcCommand icommand = new OdbcCommand(istr, conn);
icommand.Parameters.Add("@cNumber", OdbcType.Int);
icommand.Parameters.Add("@cName", OdbcType.Text,50);
// icommand.Parameters.Add("cFood",OdbcType.Int);

icommand.Parameters["@cNumber"].Value = number;
icommand.Parameters["@cName"].Value=cname;
//icommand.Parameters["@cFood"].Value=food;

icommand.ExecuteNonQuery();
}
}

}

catch (Exception ex)
{
MessageBox.Show(this, "Error while inserting values.\r\n\r\nDetails:\r\n" + ex.ToString() );
}

finally
{
if(conn!=null)
conn.Close();
}

promption:

System.Data.OdbcException: ERROR[07002][Microsoft][ODBC Microsoft Access Driver] Too few parameters Expected 2.

at System.Data.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)

at System.Data.Odbc.OdbcCommand.ExcuteNonQuery()

at WindowsApplication3.Form1.button1_Click(Object sender, EventArgs e) in d:\cis\windowsappliction3\form1.cs:line160

icommand.ExecuteNonQuery();

thanks!
davidpmiller
the first posted in this thread is all content. thank for your help.

ejay

Sorry, my bad made a typo.

try
{
conn.ConnectionString = "FIL=MS Access;DSN=CIS424";
conn.Open();
scommand = new OdbcCommand("select CageName, Food from Cage", conn);
rd = scommand.ExecuteReader();
int count = rd.FieldCount;
MessageBox.Show("dd"+count);
string s=null;

while(rd.Read())
{
if(rd.GetString(0)!= this.textBox2.Text)
{
rd.Close();

int number = int.Parse (this.textBox1.Text);
string cname = this.textBox2.Text;
int food =int.Parse(this.textBox3.Text);

string istr = "INSERT INTO Cage (CageNo, CageName, Food) " +
"VALUES ( @cNumber, @cName,@food)";

OdbcCommand icommand = new OdbcCommand(istr, conn);
icommand.Parameters.Add("@cNumber", OdbcType.Int);
icommand.Parameters.Add("@cName", OdbcType.Text,50);
icommand.Parameters.Add("@food", OdbcType.Int);

icommand.Parameters["@cNumber"].Value = number;
icommand.Parameters["@cName"].Value=cname;
icommand.Parameters["@food"].Value=food;

icommand.ExecuteNonQuery();
}
}
}

catch (Exception ex)
{
MessageBox.Show(this, "Error while inserting values.\r\n\r\nDetails:\r\n" + ex.ToString() );
}

finally
{
if(conn!=null)
conn.Close();
}

Ayzan

Try this:

try
{
conn.ConnectionString = "FIL=MS Access;DSN=CIS424";
conn.Open();
scommand = new OdbcCommand("select CageName, Food from Cage", conn);
rd = scommand.ExecuteReader();
int count = rd.FieldCount;
MessageBox.Show("dd"+count);
string s=null;

while(rd.Read())
{
if(rd.GetString(0)!= this.textBox2.Text)
{
rd.Close();

int number = int.Parse (this.textBox1.Text);
string cname = this.textBox2.Text;
int food =int.Parse(this.textBox3.Text);

string istr = "INSERT INTO Cage (CageNo, CageName, Food) " +
"VALUES ( @cNumber, @cName,@food)";

OdbcCommand icommand = new OdbcCommand(istr, conn);
icommand.Parameters.Add("@cNumber", OdbcType.Int);
icommand.Parameters.Add("@cName", OdbcType.Text,50);

icommand.Parameters["@cNumber"].Value = number;
icommand.Parameters["@cName"].Value=cname;
icommand.Parameters["@food"].Value=food;

icommand.ExecuteNonQuery();
}
}
}

catch (Exception ex)
{
MessageBox.Show(this, "Error while inserting values.\r\n\r\nDetails:\r\n" + ex.ToString() );
}

finally
{
if(conn!=null)
conn.Close();
}

Gary F
What is the stack-trace Can you post the full exception message
ksvasan
Can you provide us the solution, so we can debug it for you
Tsprao
still has problem. I really do not where is error.

System.IndexOutOfRangeException: An OdbcParameter with ParameterName '@food' is not containded by this OdbcParameterCollection. System.Data.OdbcParameterCollection.RangeCheck(String parameterName)

at System.Data.Odbc.OdbcParameterCollection.get_Item(String parameterName)

at WindowsApplication3.Form1.button1_Click(Object sender, EventArgs e) in d:\cis\windowsappliction3\form1.cs:line157

icommand.Parameters["@food"].Value=cfood;
ADO.net, how to use INSERT statement

Answer this question