none
Ms access database query in C# 2010 RRS feed

  • Question

  • string str1 = "";
                string str2 = "";
                str1 = "Insert into Table1(id, Disease,Symptoms,Drugs,Side effects,Genes,Proteins) Values (@id, @Disease,@Symptoms,@Drugs,@Side effects,@Genes,@Proteins)";

               str2 = "Insert into Table1(Disease clr, side effects/Symptoms clr,drugs clr,genes color,proteins clr) Values ('1', '5' ,' 4' , '2 ',' 3')";

                OleDbCommand com1 = new OleDbCommand(str1, connection);
                OleDbCommand com2 = new OleDbCommand(str2, connection);
                //connection.Open();
                com1.Parameters.Add("@id", OleDbType.Integer, 5);
                //com1.Parameters.Add("@id", OleDbType.VarChar, 200);
                
                com1.Parameters.Add("@Disease", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Symptoms", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Drugs", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Side effects", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Genes", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Proteins",OleDbType.VarChar,200);
                connection.Open();
                com1.Parameters["@id"].Value = System.Convert.ToInt32(textBox1.Text.ToString());
                //com1.Parameters["@id"].Value = textBox1.Text.ToString();
               
                com1.Parameters["@Disease"].Value = textBox2.Text.ToString();

                com1.Parameters["@Symptoms"].Value = textBox3.Text.ToString();
                com1.Parameters["@Drugs"].Value = textBox4.Text.ToString();
                com1.Parameters["@Side effects"].Value = textBox5.Text.ToString();            
                com1.Parameters["@Genes"].Value = textBox6.Text.ToString();            
                com1.Parameters["@Proteins"].Value = textBox7.Text.ToString();
                com1.ExecuteNonQuery();
                com2.ExecuteNonQuery();
                connection.Close();
                textBox1.Text = "";
                textBox2.Text = "";
                textBox3.Text = "";
                textBox4.Text = "";
                textBox5.Text = "";
                textBox6.Text = "";
                textBox7.Text = "";
                MessageBox.Show("Save Successfull ... ");

    go2go_suvo

    Tuesday, January 22, 2013 8:02 AM

Answers

  • Hi,

    Dont give space to @Side effects

    Try with the below code

    string str1 = "";
                string str2 = "";
                str1 = "Insert into Table1(id, Disease,Symptoms,Drugs,Side effects,Genes,Proteins) Values (@id, @Disease,@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins)";
    
               str2 = "Insert into Table1([Disease clr], [side effects/Symptoms clr],[drugs clr],[genes color],[proteins clr]) Values ('1', '5' ,' 4' , '2 ',' 3')";
    
                OleDbCommand com1 = new OleDbCommand(str1, connection);
                OleDbCommand com2 = new OleDbCommand(str2, connection);
                //connection.Open();
                com1.Parameters.Add("@id", OleDbType.Integer, 5);
                //com1.Parameters.Add("@id", OleDbType.VarChar, 200);
    
                com1.Parameters.Add("@Disease", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Symptoms", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Drugs", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Side effects", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Genes", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Proteins",OleDbType.VarChar,200);
                connection.Open();
               
                com1.ExecuteNonQuery();
                com2.ExecuteNonQuery();
                connection.Close();
                textBox1.Text = "";
                textBox2.Text = "";
                textBox3.Text = "";
                textBox4.Text = "";
                textBox5.Text = "";
                textBox6.Text = "";
                textBox7.Text = "";
                MessageBox.Show("Save Successfull ... ");

                          

    PS.Shakeer Hussain

    • Marked as answer by go2go_suvo Tuesday, January 22, 2013 8:48 AM
    Tuesday, January 22, 2013 8:08 AM

All replies

  • What is wrong with that code? It says .... error in insert statement...


    go2go_suvo

    Tuesday, January 22, 2013 8:02 AM
  • Hi,

    Dont give space to @Side effects

    Try with the below code

    string str1 = "";
                string str2 = "";
                str1 = "Insert into Table1(id, Disease,Symptoms,Drugs,Side effects,Genes,Proteins) Values (@id, @Disease,@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins)";
    
               str2 = "Insert into Table1([Disease clr], [side effects/Symptoms clr],[drugs clr],[genes color],[proteins clr]) Values ('1', '5' ,' 4' , '2 ',' 3')";
    
                OleDbCommand com1 = new OleDbCommand(str1, connection);
                OleDbCommand com2 = new OleDbCommand(str2, connection);
                //connection.Open();
                com1.Parameters.Add("@id", OleDbType.Integer, 5);
                //com1.Parameters.Add("@id", OleDbType.VarChar, 200);
    
                com1.Parameters.Add("@Disease", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Symptoms", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Drugs", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Side effects", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Genes", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Proteins",OleDbType.VarChar,200);
                connection.Open();
               
                com1.ExecuteNonQuery();
                com2.ExecuteNonQuery();
                connection.Close();
                textBox1.Text = "";
                textBox2.Text = "";
                textBox3.Text = "";
                textBox4.Text = "";
                textBox5.Text = "";
                textBox6.Text = "";
                textBox7.Text = "";
                MessageBox.Show("Save Successfull ... ");

                          

    PS.Shakeer Hussain

    • Marked as answer by go2go_suvo Tuesday, January 22, 2013 8:48 AM
    Tuesday, January 22, 2013 8:08 AM
  • Same problem happens...it says

    " Syntax error in INSERT INTO statement."


    go2go_suvo

    Tuesday, January 22, 2013 8:13 AM
  • Hi,

    In which sql statement your are receiving error str1 or str2 ?


    PS.Shakeer Hussain


    Tuesday, January 22, 2013 8:14 AM
  • com1.ExecuteNonQuery();

    go2go_suvo

    Tuesday, January 22, 2013 8:19 AM
  • now str1 inserts statement executes but for str2 values com2.ExecuteNonQuery()

    says----------

    Index or primary key cannot contain a Null value.


    go2go_suvo

    Tuesday, January 22, 2013 8:30 AM
  • It solves now,

    Thank u,Syed Shakeer Hussain


    go2go_suvo

    Tuesday, January 22, 2013 8:47 AM
  • HI,

     I want to add disease and symptoms in one column(Field1) of hypothesis table.

    what's wrong with that query?

    str20 = "Insert into hypothesis(Field1)  Values ( @Disease,@Symptoms)";

     com20.Parameters.Add("@Field1",OleDbType.VarChar,200);


    go2go_suvo

    Tuesday, January 22, 2013 4:22 PM