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

Sarang Rokade
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() );
}

James Anderton

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();
}

Rena
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;
nelson br
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();

LittleRock

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.

admoises
What is the stack-trace Can you post the full exception message
Schadix
the first posted in this thread is all content. thank for your help.
Thomas.AT
thanks for your help!
Tapan
Can you provide us the solution, so we can debug it for you
Nabeel Shahid
I do not know how to add attachment to a thread. so I send the DB to your gmail mailbox. thx alot.

Ramana Kumar

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();
}

cu-blenge
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.
QATester01
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!
Nathan Dolly
System.FormatException: input string was not in a correct format. which format i need to define in db CageName is Text.
ADO.net, how to use INSERT statement

Answer this question

ADO.net, how to use INSERT statement

Hot Topic

.NET Development

ADO.net, how to use INSERT statement

ADO.net, how to use INSERT statement

Sarang Rokade

James Anderton

Rena

nelson br

LittleRock

admoises

Schadix

Thomas.AT

Tapan

Nabeel Shahid

Ramana Kumar

cu-blenge

QATester01

Nathan Dolly