none
Ms access database query in C# 2010 RRS feed

  • Question

  • Hi,

    Plz help me with this query---

    string str1 = "Insert into Table2( Disease,Symptoms,Drugs,Sideeffects,Genes,Proteins,[Diseaseclr],sideeffects/Symptomsclr],[drugsclr],[genescolor],[proteinsclr]) Values (@Disease,@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins,'1', '5' ,' 4' , '2 ',' 3')";

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

    While inserting disease and symptoms value in Table1, i want to insert these values in  hypothesis table also

    where Field1 column contains disease and symptoms name both &

    noun1 coumn contains only disease names.


    go2go_suvo

    Tuesday, January 22, 2013 4:29 PM

All replies

  • Hi,

    Plz help me with this query---

    string str1 = "Insert into Table2( Disease,Symptoms,Drugs,Sideeffects,Genes,Proteins,[Diseaseclr],sideeffects/Symptomsclr],[drugsclr],[genescolor],[proteinsclr]) Values (@Disease,@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins,'1', '5' ,' 4' , '2 ',' 3')";

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

    While inserting disease and symptoms value in Table2, i want to insert these values in  hypothesis table also

    where Field1 column contains disease and symptoms name both &

    noun1 column contains only disease names.


    go2go_suvo

    • Merged by Alexander Sun Thursday, January 24, 2013 2:09 AM Duplicate
    Tuesday, January 22, 2013 4:59 PM
  • Any1 plz help me......

    go2go_suvo

    Tuesday, January 22, 2013 5:00 PM
  • Hi,

    Not understood what you are looking? Please post clearly


    PS.Shakeer Hussain

    Tuesday, January 22, 2013 5:04 PM
  • While inserting values of diseases,drugs,genes...etc to Table 2, i want to add disease and drug items like in this format (disease,drugs) in Field1 column of  hypothesis table and add disease item to noun1 column of same hypothesis table.

    Help me with this..


    go2go_suvo

    Tuesday, January 22, 2013 5:09 PM
  • Hi,

    You can combine two columns as a below code.

    string strDiseaseAndSymptons=textBoxDisease.Text+textBoxSymponts.Text; string str20 = "Insert into hypothesis(Field1) Values ( @DiseaseAndSymptoms)";

    SqlCommand cmd=new SqlCommand(str20,conn);

    conn.Open() command.Parameters.AddWithValue("@DiseaseAndSymptoms", strDiseaseAndSymptons); cmd.ExecuteNonQuery();




    PS.Shakeer Hussain

    Tuesday, January 22, 2013 5:20 PM
  • Thanks for ur reply,

    But it stores only diseases value, i want to store (Disease,symptoms) in Field1 of hypothesis table.


    go2go_suvo

    Tuesday, January 22, 2013 5:34 PM
  • (Diseases,Symptoms) in this format........

    go2go_suvo

    Tuesday, January 22, 2013 5:36 PM
  • Ok

    string strDiseaseAndSymptons="("+textBoxDisease.Text+","+textBoxSymponts.Text+")"; string str20 = "Insert into hypothesis(Field1) Values ( @DiseaseAndSymptoms)";

    SqlCommand cmd=new SqlCommand(str20,conn);

    conn.Open() command.Parameters.AddWithValue("@DiseaseAndSymptoms", strDiseaseAndSymptons); cmd.ExecuteNonQuery();


    PS.Shakeer Hussain

    Tuesday, January 22, 2013 5:45 PM
  • Hi,

    It works. But there is another problem....

    If hypothesis table contains diseases,symptoms,drugs,sideeffects,genes,proteins like this

    ID Field1ID  R1               R2

    45         disease                   47 46

    46 symptoms  45

    47           drugs  48

    48 sideeffects  47

    49          genes                     50

    50 proteins 49

    After inserting these names how can i store value of R1 & R2 like above with MS access database query like before in C#?


    go2go_suvo

    Tuesday, January 22, 2013 6:46 PM
  • Here it is.....


    go2go_suvo

    Tuesday, January 22, 2013 6:52 PM
  • How these value can be saved in database with MS access Query?

    go2go_suvo

    Tuesday, January 22, 2013 6:56 PM
  • Hello,

    My suggestion is shown below, create the needed objects, connection and command, execute one query after the other and check the results if by chance one insert fails should the next one continue (only you know this).

    You need to take care of the parameter setup in regards to setting each parameter to the correct type along with setting values.

    Please note when dealing with MS-Access parameter placeholders can be a question mark symbol yet it is best to name them as shown below as this is a better way to go. Any ways this gives you a pattern to work from.

    using System.Data.OleDb;
    public class Form1
    {
    	private void Button1_Click(object sender, System.EventArgs e)
    	{
    		using (OleDbConnection cn = new OleDbConnection("Your connection string"))
    		{
    			using (OleDbCommand cmd = new OleDbCommand("Insert into Table2 (Disease, Symptoms, Drugs, Sideeffects, Genes, Proteins,[Diseaseclr], sideeffects/Symptomsclr], [drugsclr],[genescolor], [proteinsclr]) Values (@Disease,@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins,'1', '5' ,' 4' , '2 ',' 3')", cn))
    			{
    				cmd.Parameters.Add(new OleDbParameter("@Disease", OleDbType.LongVarWChar));
    				cmd.Parameters.Add(new OleDbParameter("@Symptoms", OleDbType.LongVarWChar));
    				cmd.Parameters.Add(new OleDbParameter("@Drugs", OleDbType.LongVarWChar));
    				cmd.Parameters.Add(new OleDbParameter("@Sideeffects", OleDbType.LongVarWChar));
    				cmd.Parameters.Add(new OleDbParameter("@Genes", OleDbType.LongVarWChar));
    				cmd.Parameters.Add(new OleDbParameter("@Proteins", OleDbType.LongVarWChar));
    
    				cmd.Parameters[0].Value = "Your value";
    				cmd.Parameters[1].Value = "Your value";
    				cmd.Parameters[2].Value = "Your value";
    				cmd.Parameters[3].Value = "Your value";
    				cmd.Parameters[4].Value = "Your value";
    				cmd.Parameters[5].Value = "Your value";
    
    				cn.Open();
    
    				var Affected = cmd.ExecuteNonQuery();
    				if (Affected != 1)
    				{
    					//
    					// Abort
    					//
    				}
    
    				cmd.Parameters.Clear();
    
    				cmd.CommandText = "Insert into hypothesis(Field1)  Values ( @Disease,@Symptoms)";
    
    				cmd.Parameters.Add(new OleDbParameter("@Disease", OleDbType.LongVarWChar));
    				cmd.Parameters.Add(new OleDbParameter("@Symptoms", OleDbType.LongVarWChar));
    
    				cmd.Parameters[0].Value = "Your value";
    				cmd.Parameters[1].Value = "Your value";
    
    				Affected = cmd.ExecuteNonQuery();
    				if (Affected != 1)
    				{
    					//
    					// Abort
    					//
    				}
    			}
    		}
    	}
    
    	public Form1()
    	{
    
    		//INSTANT C# NOTE: Converted event handler wireups:
    		Button1.Click += new System.EventHandler(Button1_Click);
    	}
    }


    KSG

    Tuesday, January 22, 2013 7:55 PM
  • how can we solve with ms access query?

    While inserting disease,symptoms,drugs,side effects, genes ,proteins names how can we store R1 & R2 value according to id?


    go2go_suvo

    • Merged by Alexander Sun Wednesday, January 30, 2013 8:49 AM Duplicate
    Wednesday, January 23, 2013 4:11 AM
  • any1 plz help me????

    go2go_suvo

    Wednesday, January 23, 2013 4:15 AM
  • Hi,

    You can use below code

    string strDiseaseAndSymptons=textBoxDisease.Text+textBoxSymponts.Text;
    
    int R1=Convert.ToInt32(textBoxR1.Text);
    
    int R2=Convert.ToInt32(textBoxR2.Text);
    
    string str20 = "Insert into hypothesis(Field1,R1,R2)  Values ( @DiseaseAndSymptoms,@R1,@R2)";
    
    SqlCommand cmd=new SqlCommand(str20,conn);
    
    conn.Open()
    
    command.Parameters.AddWithValue("@DiseaseAndSymptoms", strDiseaseAndSymptons);
    command.Parameters.AddWithValue("@R1", R1);
    
    command.Parameters.AddWithValue("@R2", R2);
    cmd.ExecuteNonQuery();
    
    


    PS.Shakeer Hussain

    Wednesday, January 23, 2013 6:19 AM
  • Thanks for ur reply,

    but u misjudged it as i am not fully clear to yourself.....


    go2go_suvo

    Wednesday, January 23, 2013 6:34 AM
  • Hi,

    After inserting Filed1, you want to Insert R1 and R2 columns Then you have to use Update statement.


    PS.Shakeer Hussain

    Wednesday, January 23, 2013 6:37 AM
  • This is that code.....

    it shows the error..."missing semicolon at the end of sql statement". But i can't see any missing semicolon..

    How the error can be recovered?

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.OleDb;
    namespace add
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=C:\Users\shivam\Documents\Visual Studio 2010\Projects\add\add\bin\hypo13.mdb");
           
            private void textBox1_TextChanged(object sender, EventArgs e)
            {

            }

            private void Form1_Load(object sender, EventArgs e)
            {

            }

            private void button1_Click(object sender, EventArgs e)
            {
                //if (textBox1.Text == "")
                //{
                //    MessageBox.Show("rrrrr ....");
                //    textBox1.Focus();
                //    return;
                //}
                if (textBox2.Text == "")
                {
                    MessageBox.Show("You must give an account Name ....");
                    textBox2.Focus();
                    return;
                }

                if (textBox3.Text == "")
                {
                    MessageBox.Show("You must give an account number/ID ....");
                    textBox3.Focus();
                    return;
                }
                if (textBox4.Text == "")
                {
                    MessageBox.Show("You must give an account Name ....");
                    textBox4.Focus();
                    return;
                }


                if (textBox5.Text == "")
                {
                    MessageBox.Show("You must give an account number/ID ....");
                    textBox5.Focus();
                    return;
                }
                if (textBox6.Text == "")
                {
                    MessageBox.Show("You must give an account Name ....");
                    textBox6.Focus();
                    return;
                }
                if (textBox7.Text == "")
                {
                    MessageBox.Show("You must give an account Name ....");
                    textBox7.Focus();
                    return;
                }
                string str1 = "";
                string str2 = "";
                string str20="";
                string str21="";
               // str1 = "Insert into Table1(id, Disease,Symptoms,Drugs,Sideeffects,Genes,Proteins) Values (@id, @Disease,@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins)";

               //str2 = "Insert into Table1(Disease clr, sideeffects/Symptoms clr,drugs clr,genes color,proteins clr) Values ('1', '5' ,' 4' , '2 ',' 3')";
               // str1 = "Insert into Table2( Disease,Symptoms,Drugs,Sideeffects,Genes,Proteins) Values ( @Disease,@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins)";
                //insert into table2(col1,col2,col3) select col1,col2,col3 from table1
                str1 = "Insert into Table2( Disease,Symptoms,Drugs,Sideeffects,Genes,Proteins,[Diseaseclr], [sideeffects/Symptomsclr],[drugsclr],[genescolor],[proteinsclr]) Values ( @Disease,@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins,'1', '5' ,' 4' , '2 ',' 3')";
                //str20 = "Insert into hypothesis(Field1) select Disease,Drugs from Table2";
                str21 = "Insert into hypothesis(noun1)  Values ( @Disease)";
                string str22 = "Insert into hypothesis(noun2)  Values ( @Symptoms)";
               // str2 = "Insert into Table2([Diseaseclr], [sideeffects/Symptomsclr],[drugsclr],[genescolor],[proteinsclr]) Values ('1', '5' ,' 4' , '2 ',' 3')";

                OleDbCommand com1 = new OleDbCommand(str1, connection);
                OleDbCommand com2 = new OleDbCommand(str2, connection);
                //OleDbCommand com20 = new OleDbCommand(str20, connection);
                //OleDbCommand com21 = new OleDbCommand(str21, connection);
                //OleDbCommand com22 = new OleDbCommand(str22, 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("@Sideeffects", 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["@Sideeffects"].Value = textBox5.Text.ToString();            
                com1.Parameters["@Genes"].Value = textBox6.Text.ToString();            
                com1.Parameters["@Proteins"].Value = textBox7.Text.ToString();

                string strDiseaseAndSymptons = "(" + textBox2.Text + "," + textBox3.Text + ")";
                string strDisease = textBox2.Text;
                string strSymptoms = textBox3.Text;
                string strDrugs = textBox4.Text;
                string strsideeffects = textBox5.Text;
                string strGenes = textBox6.Text;
                string strProteins = textBox7.Text;

                str20 = "Insert into hypoth(Field1,noun1,noun2)  Values ( @DiseaseAndSymptoms,@Disease,@Symptoms)";
                OleDbCommand com20 = new OleDbCommand(str20, connection);
                com20.Parameters.AddWithValue("@DiseaseAndSymptoms", strDiseaseAndSymptons);
                com20.Parameters.AddWithValue("@Disease", strDisease);
                com20.Parameters.AddWithValue("@Symptoms", strSymptoms);

                //@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins

                string str30 = "Insert into Rela(Field1ID,CLR) Values (@Disease,'1')";
                string str31 = "Insert into Rela(Field1ID,CLR) Values (@Symptoms,'5')";
                string str32 = "Insert into Rela(Field1ID,CLR) Values (@Drugs,'4')";
                string str33 = "Insert into Rela(Field1ID,CLR) Values (@Sideeffects,'5')";
                string str34 = "Insert into Rela(Field1ID,CLR) Values (@Genes,'2')";
                string str35 = "Insert into Rela(Field1ID,CLR) Values (@Proteins,'3')";
                OleDbCommand com30s = new OleDbCommand(str30, connection);
                OleDbCommand com31s = new OleDbCommand(str31, connection);
                OleDbCommand com32s = new OleDbCommand(str32, connection);
                OleDbCommand com33s = new OleDbCommand(str33, connection);
                OleDbCommand com34s = new OleDbCommand(str34, connection);
                OleDbCommand com35s = new OleDbCommand(str35, connection);
                
                
                com30s.Parameters.AddWithValue("@Field1ID", strDisease);
                com31s.Parameters.AddWithValue("@Field1ID", strSymptoms);
                com32s.Parameters.AddWithValue("@Field1ID", strDrugs);
                com33s.Parameters.AddWithValue("@Field1ID", strsideeffects);
                com34s.Parameters.AddWithValue("@Field1ID", strGenes);
                com35s.Parameters.AddWithValue("@Field1ID", strProteins);
                



                string str40 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str30 + "'";
                string str41 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str31 + "'";
                string str42 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str32 + "'";
                string str43 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str33 + "'";
                string str44 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str34 + "'";
                string str45 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str35 + "'";
               
                string str50 = "Insert into Rela(R1, R2) Values (@str41, @str42) where (Rela.[Field1] ='" + str40 + "'";
                string str51 = "Insert into Rela(R1) Values (@str40) where (Rela.[Field1] ='" + str41 + "'";
                string str52 = "Insert into Rela(R1) Values (@str43) where (Rela.[Field1] ='" + str42 + "'";
                string str53 = "Insert into Rela(R1) Values (@str42) where (Rela.[Field1] ='" + str43 + "'";
                string str54 = "Insert into Rela(R1) Values (@str45) where (Rela.[Field1] ='" + str44 + "'";
                string str55 = "Insert into Rela(R1) Values (@str44) where (Rela.[Field1] ='" + str45 + "'";

               


                

                OleDbCommand com50s = new OleDbCommand(str50, connection);
                OleDbCommand com51s = new OleDbCommand(str51, connection);
                OleDbCommand com52s = new OleDbCommand(str52, connection);
                OleDbCommand com53s = new OleDbCommand(str53, connection);
                OleDbCommand com54s = new OleDbCommand(str54, connection);
                OleDbCommand com55s = new OleDbCommand(str55, connection);

                com50s.Parameters.AddWithValue("@R1", str41);
                com50s.Parameters.AddWithValue("@R2", str42);
                com51s.Parameters.AddWithValue("@R1", str40);
                com52s.Parameters.AddWithValue("@R1", str43);
                com53s.Parameters.AddWithValue("@R1", str42);
                com54s.Parameters.AddWithValue("@R1", str45);
                com55s.Parameters.AddWithValue("@R1", str44);
                




                




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

                com30s.ExecuteNonQuery();
                com31s.ExecuteNonQuery();
                com32s.ExecuteNonQuery();
                com33s.ExecuteNonQuery();
                com34s.ExecuteNonQuery();
                com35s.ExecuteNonQuery();


                



                com50s.ExecuteNonQuery();
                com51s.ExecuteNonQuery();
                com52s.ExecuteNonQuery();
                com53s.ExecuteNonQuery();
                com54s.ExecuteNonQuery();
                com55s.ExecuteNonQuery();



               // com21.ExecuteNonQuery();
                //com22.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

    Wednesday, January 23, 2013 6:37 AM
  • Hi,

    click on Error Message, It will redirect to that line.


    PS.Shakeer Hussain


    Wednesday, January 23, 2013 6:40 AM
  • No error....only a box appears shows OLedb exception was unhandled. 

    missing semicolon at the end of sql statement.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.OleDb;
    namespace add
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=C:\Users\shivam\Documents\Visual Studio 2010\Projects\add\add\bin\hypo13.mdb");
           
            private void textBox1_TextChanged(object sender, EventArgs e)
            {

            }

            private void Form1_Load(object sender, EventArgs e)
            {

            }

            private void button1_Click(object sender, EventArgs e)
            {
                //if (textBox1.Text == "")
                //{
                //    MessageBox.Show("rrrrr ....");
                //    textBox1.Focus();
                //    return;
                //}
                if (textBox2.Text == "")
                {
                    MessageBox.Show("Y");
                    textBox2.Focus();
                    return;
                }

                if (textBox3.Text == "")
                {
                    MessageBox.Show("");
                    textBox3.Focus();
                    return;
                }
                if (textBox4.Text == "")
                {
                    MessageBox.Show("");
                    textBox4.Focus();
                    return;
                }


                if (textBox5.Text == "")
                {
                    MessageBox.Show("");
                    textBox5.Focus();
                    return;
                }
                if (textBox6.Text == "")
                {
                    MessageBox.Show("");
                    textBox6.Focus();
                    return;
                }
                if (textBox7.Text == "")
                {
                    MessageBox.Show(" ....");
                    textBox7.Focus();
                    return;
                }
                string str1 = "";
                string str2 = "";
                string str20="";
                string str21="";
                  str1 = "Insert into Table2( Disease,Symptoms,Drugs,Sideeffects,Genes,Proteins,[Diseaseclr], [sideeffects/Symptomsclr],[drugsclr],[genescolor],[proteinsclr]) Values ( @Disease,@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins,'1', '5' ,' 4' , '2 ',' 3')";
                //str20 = "Insert into hypothesis(Field1) select Disease,Drugs from Table2";
                str21 = "Insert into hypothesis(noun1)  Values ( @Disease)";
                string str22 = "Insert into hypothesis(noun2)  Values ( @Symptoms)";
               // str2 = "Insert into Table2([Diseaseclr], [sideeffects/Symptomsclr],[drugsclr],[genescolor],[proteinsclr]) Values ('1', '5' ,' 4' , '2 ',' 3')";

                OleDbCommand com1 = new OleDbCommand(str1, connection);
                OleDbCommand com2 = new OleDbCommand(str2, connection);
               
                com1.Parameters.Add("@Disease", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Symptoms", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Drugs", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Sideeffects", 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["@Sideeffects"].Value = textBox5.Text.ToString();            
                com1.Parameters["@Genes"].Value = textBox6.Text.ToString();            
                com1.Parameters["@Proteins"].Value = textBox7.Text.ToString();

                string strDiseaseAndSymptons = "(" + textBox2.Text + "," + textBox3.Text + ")";
                string strDisease = textBox2.Text;
                string strSymptoms = textBox3.Text;
                string strDrugs = textBox4.Text;
                string strsideeffects = textBox5.Text;
                string strGenes = textBox6.Text;
                string strProteins = textBox7.Text;

                str20 = "Insert into hypoth(Field1,noun1,noun2)  Values ( @DiseaseAndSymptoms,@Disease,@Symptoms)";
                OleDbCommand com20 = new OleDbCommand(str20, connection);
                com20.Parameters.AddWithValue("@DiseaseAndSymptoms", strDiseaseAndSymptons);
                com20.Parameters.AddWithValue("@Disease", strDisease);
                com20.Parameters.AddWithValue("@Symptoms", strSymptoms);

                //@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins

                string str30 = "Insert into Rela(Field1ID,CLR) Values (@Disease,'1')";
                string str31 = "Insert into Rela(Field1ID,CLR) Values (@Symptoms,'5')";
                string str32 = "Insert into Rela(Field1ID,CLR) Values (@Drugs,'4')";
                string str33 = "Insert into Rela(Field1ID,CLR) Values (@Sideeffects,'5')";
                string str34 = "Insert into Rela(Field1ID,CLR) Values (@Genes,'2')";
                string str35 = "Insert into Rela(Field1ID,CLR) Values (@Proteins,'3')";
                OleDbCommand com30s = new OleDbCommand(str30, connection);
                OleDbCommand com31s = new OleDbCommand(str31, connection);
                OleDbCommand com32s = new OleDbCommand(str32, connection);
                OleDbCommand com33s = new OleDbCommand(str33, connection);
                OleDbCommand com34s = new OleDbCommand(str34, connection);
                OleDbCommand com35s = new OleDbCommand(str35, connection);
                
                
                com30s.Parameters.AddWithValue("@Field1ID", strDisease);
                com31s.Parameters.AddWithValue("@Field1ID", strSymptoms);
                com32s.Parameters.AddWithValue("@Field1ID", strDrugs);
                com33s.Parameters.AddWithValue("@Field1ID", strsideeffects);
                com34s.Parameters.AddWithValue("@Field1ID", strGenes);
                com35s.Parameters.AddWithValue("@Field1ID", strProteins);
                



                string str40 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str30 + "'";
                string str41 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str31 + "'";
                string str42 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str32 + "'";
                string str43 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str33 + "'";
                string str44 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str34 + "'";
                string str45 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str35 + "'";
               
                string str50 = "Insert into Rela(R1, R2) Values (@str41, @str42) where (Rela.[Field1] ='" + str40 + "'";
                string str51 = "Insert into Rela(R1) Values (@str40) where (Rela.[Field1] ='" + str41 + "'";
                string str52 = "Insert into Rela(R1) Values (@str43) where (Rela.[Field1] ='" + str42 + "'";
                string str53 = "Insert into Rela(R1) Values (@str42) where (Rela.[Field1] ='" + str43 + "'";
                string str54 = "Insert into Rela(R1) Values (@str45) where (Rela.[Field1] ='" + str44 + "'";
                string str55 = "Insert into Rela(R1) Values (@str44) where (Rela.[Field1] ='" + str45 + "'";

               


                

                OleDbCommand com50s = new OleDbCommand(str50, connection);
                OleDbCommand com51s = new OleDbCommand(str51, connection);
                OleDbCommand com52s = new OleDbCommand(str52, connection);
                OleDbCommand com53s = new OleDbCommand(str53, connection);
                OleDbCommand com54s = new OleDbCommand(str54, connection);
                OleDbCommand com55s = new OleDbCommand(str55, connection);

                com50s.Parameters.AddWithValue("@R1", str41);
                com50s.Parameters.AddWithValue("@R2", str42);
                com51s.Parameters.AddWithValue("@R1", str40);
                com52s.Parameters.AddWithValue("@R1", str43);
                com53s.Parameters.AddWithValue("@R1", str42);
                com54s.Parameters.AddWithValue("@R1", str45);
                com55s.Parameters.AddWithValue("@R1", str44);
                




                




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

                com30s.ExecuteNonQuery();
                com31s.ExecuteNonQuery();
                com32s.ExecuteNonQuery();
                com33s.ExecuteNonQuery();
                com34s.ExecuteNonQuery();
                com35s.ExecuteNonQuery();


                



                com50s.ExecuteNonQuery();
                com51s.ExecuteNonQuery();
                com52s.ExecuteNonQuery();
                com53s.ExecuteNonQuery();
                com54s.ExecuteNonQuery();
                com55s.ExecuteNonQuery();



               // com21.ExecuteNonQuery();
                //com22.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

    Wednesday, January 23, 2013 6:43 AM
  • It shows in that line " com50s.ExecuteNonQuery();"

    go2go_suvo

    Wednesday, January 23, 2013 6:45 AM
  • No error....only a box appears shows OLedb exception was unhandled. 

    missing semicolon at the end of sql statement.

    What is wrong with that code????

    string str1 = "";
                string str2 = "";
                string str20="";
                string str21="";
                  str1 = "Insert into Table2( Disease,Symptoms,Drugs,Sideeffects,Genes,Proteins,[Diseaseclr], [sideeffects/Symptomsclr],[drugsclr],[genescolor],[proteinsclr]) Values ( @Disease,@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins,'1', '5' ,' 4' , '2 ',' 3')";
                //str20 = "Insert into hypothesis(Field1) select Disease,Drugs from Table2";
                str21 = "Insert into hypothesis(noun1)  Values ( @Disease)";
                string str22 = "Insert into hypothesis(noun2)  Values ( @Symptoms)";
               // str2 = "Insert into Table2([Diseaseclr], [sideeffects/Symptomsclr],[drugsclr],[genescolor],[proteinsclr]) Values ('1', '5' ,' 4' , '2 ',' 3')";

                OleDbCommand com1 = new OleDbCommand(str1, connection);
                OleDbCommand com2 = new OleDbCommand(str2, connection);
               
                com1.Parameters.Add("@Disease", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Symptoms", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Drugs", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Sideeffects", 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["@Sideeffects"].Value = textBox5.Text.ToString();            
                com1.Parameters["@Genes"].Value = textBox6.Text.ToString();            
                com1.Parameters["@Proteins"].Value = textBox7.Text.ToString();

                string strDiseaseAndSymptons = "(" + textBox2.Text + "," + textBox3.Text + ")";
                string strDisease = textBox2.Text;
                string strSymptoms = textBox3.Text;
                string strDrugs = textBox4.Text;
                string strsideeffects = textBox5.Text;
                string strGenes = textBox6.Text;
                string strProteins = textBox7.Text;

                str20 = "Insert into hypoth(Field1,noun1,noun2)  Values ( @DiseaseAndSymptoms,@Disease,@Symptoms)";
                OleDbCommand com20 = new OleDbCommand(str20, connection);
                com20.Parameters.AddWithValue("@DiseaseAndSymptoms", strDiseaseAndSymptons);
                com20.Parameters.AddWithValue("@Disease", strDisease);
                com20.Parameters.AddWithValue("@Symptoms", strSymptoms);

                //@Symptoms,@Drugs,@Sideeffects,@Genes,@Proteins

                string str30 = "Insert into Rela(Field1ID,CLR) Values (@Disease,'1')";
                string str31 = "Insert into Rela(Field1ID,CLR) Values (@Symptoms,'5')";
                string str32 = "Insert into Rela(Field1ID,CLR) Values (@Drugs,'4')";
                string str33 = "Insert into Rela(Field1ID,CLR) Values (@Sideeffects,'5')";
                string str34 = "Insert into Rela(Field1ID,CLR) Values (@Genes,'2')";
                string str35 = "Insert into Rela(Field1ID,CLR) Values (@Proteins,'3')";
                OleDbCommand com30s = new OleDbCommand(str30, connection);
                OleDbCommand com31s = new OleDbCommand(str31, connection);
                OleDbCommand com32s = new OleDbCommand(str32, connection);
                OleDbCommand com33s = new OleDbCommand(str33, connection);
                OleDbCommand com34s = new OleDbCommand(str34, connection);
                OleDbCommand com35s = new OleDbCommand(str35, connection);
                
                
                com30s.Parameters.AddWithValue("@Field1ID", strDisease);
                com31s.Parameters.AddWithValue("@Field1ID", strSymptoms);
                com32s.Parameters.AddWithValue("@Field1ID", strDrugs);
                com33s.Parameters.AddWithValue("@Field1ID", strsideeffects);
                com34s.Parameters.AddWithValue("@Field1ID", strGenes);
                com35s.Parameters.AddWithValue("@Field1ID", strProteins);
                



                string str40 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str30 + "'";
                string str41 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str31 + "'";
                string str42 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str32 + "'";
                string str43 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str33 + "'";
                string str44 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str34 + "'";
                string str45 = "select distinct [Field1] from Rela where (Rela.[Field1ID] ='" + str35 + "'";
               
                string str50 = "Insert into Rela(R1, R2) Values (@str41, @str42) where (Rela.[Field1] ='" + str40 + "'";
                string str51 = "Insert into Rela(R1) Values (@str40) where (Rela.[Field1] ='" + str41 + "'";
                string str52 = "Insert into Rela(R1) Values (@str43) where (Rela.[Field1] ='" + str42 + "'";
                string str53 = "Insert into Rela(R1) Values (@str42) where (Rela.[Field1] ='" + str43 + "'";
                string str54 = "Insert into Rela(R1) Values (@str45) where (Rela.[Field1] ='" + str44 + "'";
                string str55 = "Insert into Rela(R1) Values (@str44) where (Rela.[Field1] ='" + str45 + "'";
          
         
                OleDbCommand com50s = new OleDbCommand(str50, connection);
                OleDbCommand com51s = new OleDbCommand(str51, connection);
                OleDbCommand com52s = new OleDbCommand(str52, connection);
                OleDbCommand com53s = new OleDbCommand(str53, connection);
                OleDbCommand com54s = new OleDbCommand(str54, connection);
                OleDbCommand com55s = new OleDbCommand(str55, connection);

                com50s.Parameters.AddWithValue("@R1", str41);
                com50s.Parameters.AddWithValue("@R2", str42);
                com51s.Parameters.AddWithValue("@R1", str40);
                com52s.Parameters.AddWithValue("@R1", str43);
                com53s.Parameters.AddWithValue("@R1", str42);
                com54s.Parameters.AddWithValue("@R1", str45);
                com55s.Parameters.AddWithValue("@R1", str44);
                
                com1.ExecuteNonQuery();
                com20.ExecuteNonQuery();

                com30s.ExecuteNonQuery();
                com31s.ExecuteNonQuery();
                com32s.ExecuteNonQuery();
                com33s.ExecuteNonQuery();
                com34s.ExecuteNonQuery();
                com35s.ExecuteNonQuery();          
                
                com50s.ExecuteNonQuery();
                com51s.ExecuteNonQuery();
                com52s.ExecuteNonQuery();
                com53s.ExecuteNonQuery();
                com54s.ExecuteNonQuery();
                com55s.ExecuteNonQuery();

                connection.Close();
                //textBox1.Text = "";
                textBox2.Text = "";
                textBox3.Text = "";
                textBox4.Text = "";
                textBox5.Text = "";
                textBox6.Text = "";
                textBox7.Text = "";
                MessageBox.Show("Save Successfull ... ");


    go2go_suvo

    • Merged by Alexander Sun Thursday, January 24, 2013 2:14 AM Duplicate
    Wednesday, January 23, 2013 7:03 AM
  • It shows in that line " com50s.ExecuteNonQuery();"

    go2go_suvo

    Wednesday, January 23, 2013 7:05 AM
  • Wednesday, January 23, 2013 2:37 PM
  • Wednesday, January 23, 2013 2:38 PM
  • Post follow-up messages to original thread:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/6db04244-37d3-4627-ae16-21f62b7e4f60


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 23, 2013 2:39 PM