none
INSERT INTO RRS feed

  • Question

  • Hi there, i have a problem with sql statement in insert into clausule. My values of insert into must be DataTable row or textBox.text, but i dont know how to do that. When i compile this code the c# gives me SYNTAX Error every time. Why?

     

    DataSet dataSet;

    DataTable dataTable;

    DataRow dataRow;

    OleDbConnection dataConnection;

    OleDbDataAdapter dataAdapter;

    string dataCommand = "select * from osiedla";

    int i = 0;

    dataSet = new DataSet();

    dataConnection = new OleDbConnection("provider = vfpoledb.1;data source = c:\\initio\\slowniki.dbc;");

    dataAdapter = new OleDbDataAdapter(dataCommand, dataConnection);

    dataAdapter.Fill(dataSet, "osiedla");

    dataTable = dataSet.Tables["osiedla"];

    dataRow = dataTable.NewRow();

    dataRow["c_osiedle"] = textBox1.Text;

    dataTable.Rows.Add(dataRow);

    string str = "INSERT INTO osiedla (c_osiedle, c_indeks) VALUES(@osiedleParam, @indeksParam)";

    OleDbCommand cmdInsert = new OleDbCommand(str,dataConnection);

    cmdInsert.Parameters.Add("@osiedleParam",textBox1.Text);

    cmdInsert.Parameters.Add("@indeksParam",textBox2.Text);

     

    dataConnection.Open();

    cmdInsert.ExecuteNonQuery();

    dataConnection.Close();

    Saturday, May 3, 2008 4:08 PM

Answers

  • You get an error because you cannot use named parameters with OLEDB provider. They work with SQL Server Managed provider. In a case of OLEDB your code should be like

     

    DataSet dataSet;

    DataTable dataTable;

    DataRow dataRow;

    OleDbConnection dataConnection;

    OleDbDataAdapter dataAdapter;

    string dataCommand = "select * from osiedla";

    int i = 0;

    dataSet = new DataSet();

    dataConnection = new OleDbConnection("provider = vfpoledb.1;data source = c:\\initio\\slowniki.dbc;");

    dataAdapter = new OleDbDataAdapter(dataCommand, dataConnection);

    dataAdapter.Fill(dataSet, "osiedla");

    dataTable = dataSet.Tables["osiedla"];

    dataRow = dataTable.NewRow();

    dataRow["c_osiedle"] = textBox1.Text;

    dataTable.Rows.Add(dataRow);

    string str = "INSERT INTO osiedla (c_osiedle, c_indeks) VALUES(?, ?)";

    OleDbCommand cmdInsert = new OleDbCommand(str,dataConnection);

    cmdInsert.Parameters.Add("@osiedleParam",textBox1.Text);

    cmdInsert.Parameters.Add("@indeksParam",textBox2.Text);

     

    dataConnection.Open();

    cmdInsert.ExecuteNonQuery();

    dataConnection.Close();

     

    Since named parameters do not exist in your case anymore, you also need to be sure that the order of the declared parameters in your code matches order of the parameters in your SQL statement
    Monday, May 5, 2008 9:46 AM
    Moderator

All replies

  • Hello,

     

    Would you mind to post the error you're having problem with ?

     

    Best Regards.

     

    Saturday, May 3, 2008 5:51 PM
  • You get an error because you cannot use named parameters with OLEDB provider. They work with SQL Server Managed provider. In a case of OLEDB your code should be like

     

    DataSet dataSet;

    DataTable dataTable;

    DataRow dataRow;

    OleDbConnection dataConnection;

    OleDbDataAdapter dataAdapter;

    string dataCommand = "select * from osiedla";

    int i = 0;

    dataSet = new DataSet();

    dataConnection = new OleDbConnection("provider = vfpoledb.1;data source = c:\\initio\\slowniki.dbc;");

    dataAdapter = new OleDbDataAdapter(dataCommand, dataConnection);

    dataAdapter.Fill(dataSet, "osiedla");

    dataTable = dataSet.Tables["osiedla"];

    dataRow = dataTable.NewRow();

    dataRow["c_osiedle"] = textBox1.Text;

    dataTable.Rows.Add(dataRow);

    string str = "INSERT INTO osiedla (c_osiedle, c_indeks) VALUES(?, ?)";

    OleDbCommand cmdInsert = new OleDbCommand(str,dataConnection);

    cmdInsert.Parameters.Add("@osiedleParam",textBox1.Text);

    cmdInsert.Parameters.Add("@indeksParam",textBox2.Text);

     

    dataConnection.Open();

    cmdInsert.ExecuteNonQuery();

    dataConnection.Close();

     

    Since named parameters do not exist in your case anymore, you also need to be sure that the order of the declared parameters in your code matches order of the parameters in your SQL statement
    Monday, May 5, 2008 9:46 AM
    Moderator