CODEWITHSUNDEEP
c#oracle-database
Fatemeh Hojjati
Fatemeh Hojjati

Reputation: 59

Invalid SQL statement

While I'm running the following code I get bellow error :

Invalid SQL Statement.

OleDbConnection con = new OleDbConnection();
con.ConnectionString = "Provider=MSDAORA;Data Source=data;
        Password=fatemeh;User ID=personnel_temp";
string sqlcommand = "MAXID NUMBER" +
                    "MAXID=1175" +
                    "SELECT GROUP_ID INTO MAXID FROM GROUPS"+
                    "MAXID:=MAXID+1" +
                    "INSERT INTO GROUPS(GROUP_ID, GROUP_NAME,DEPT_ID)
                    VALUES(MAXID,"
                    + textBox1.Text + 
                   ", SELECT DEPT_ID FROM PERSONNEL_TEMP.DEPARTMENT INNER 
   JOIN SHIFTS_N.GROUPS ON DEPARTMENT.DEPARTMENT_ID=GROUPS.DEPT_ID)";
                 OleDbDataAdapter oda = new OleDbDataAdapter(sqlcommand,con);
 con.Open();
 oda.Fill(dt);
 con.Close();

Upvotes: 0

Views: 649

Answers (3)

Sachu
Sachu

Reputation: 7766

    OleDbConnection con = new OleDbConnection();
     con.ConnectionString = "Provider=MSDAORA;Data Source=data;
     Password=fatemeh;User ID=personnel_temp";
     con.Open();
     string sqlcommand = "SELECT MAX(GROUP_ID) FROM GROUPS";
     OleDbCommand command = new OleDbCommand(sqlcommand, con);
     Int32 MAXID = (Int32) command.ExecuteScalar();
     MAXID++;
      sqlcommand = "INSERT INTO GROUPS(GROUP_ID, GROUP_NAME,DEPT_ID) "+
                        "VALUES("+MAXID+",'"
                        + textBox1.Text + 
                       "', SELECT DEPT_ID FROM PERSONNEL_TEMP.DEPARTMENT WHERE "
 +"DEPARTMENT_NAME='"+COMBOBOX1.TEXT()+"')";

  command = new OleDbCommand(sqlcommand, con);
  command.ExecuteNonQuery();
      // OleDbDataAdapter oda = new OleDbDataAdapter(sqlcommand,con);

    // oda.Fill(dt);
     con.Close();

I found two issues in your query

  1. like other two answers there is no spaces. You need to add spaces.
  2. In the insert statement you are inserting a string. It should enclosed in '

Upvotes: 3

Ben
Ben

Reputation: 2523

Currently your query looks something like this:

"MAXID NUMBERMAXID=1175SELECT GROUP_ID INTO MAXID FROM GROUPSMAXID:=MAXID+1..."

What the main problem is that you need to do is add spaces, either at the beginning or end of each line; and you need to surround your string input from your text box with '; like so:

"MAXID NUMBER " +
"MAXID=1175 " +
"SELECT GROUP_ID INTO MAXID FROM GROUPS "+
"MAXID:=MAXID+1 " +
"INSERT INTO GROUPS(GROUP_ID, GROUP_NAME,DEPT_ID)
VALUES(MAXID, '"
+ textBox1.Text + 
"', SELECT DEPT_ID FROM PERSONNEL_TEMP.DEPARTMENT INNER JOIN SHIFTS_N.GROUPS ON DEPARTMENT.DEPARTMENT_ID=GROUPS.DEPT_ID)";

A good practice would be to type it all out on one line first, then break it up into additional lines.

One thing you try to see what might be going wrong when you execute your command, you can either write to console the command if you are running a console application:

Console.Writeline(sqlcommand);

Or show a Messagebox if you are using a winforms (or something similar) application:

MessageBox.Show(sqlcommand);

This will print out your command on one line so you can see where you have made errors in your syntax.

Upvotes: 1

Ashley McVeigh
Ashley McVeigh

Reputation: 35

Correct me if I'm wrong but I think you have to have spaces in your strings otherwise they just join together as big words without spaces.

try:

"MAXID NUMBER " + "MAXID=1175 " + "SELECT GROUP_ID INTO MAXID FROM GROUPS "+ "MAXID:=MAXID+1 " +

etc.

Upvotes: 1

Related Questions