ERROR : PARAMETER_? has no default value

Chanki

I am trying to call a INSERT INTO query but I am getting a Parameter error

Parameter_ has no default value...

private void button2_Click(object sender, System.EventArgs e)

{

try

{

//create SQL query to insert row

oleDbDataAdapter2.InsertCommand.CommandText =

"INSERT INTO Company (" +

"Client, [Action], Stake," +

"Market, OpeningPrice, ClosingPrice, [OpeningDate], [ClosingDate]" + ") VALUES ('" +

listBox1.Text + "','" +

listBox2.Text + "', " +

textBoxStake.Text + " , '" +

textBoxMarket.Text + "' , " +

textBoxOpenPrice.Text + " , " +

textBoxClosePrice.Text + " , " +

textBoxOpenDate.Text + " , " +

textBoxCloseDate.Text + ")";

//notify user that query is being sent

textBoxStatusQuery.Text += "\r\nSending query: " +

oleDbDataAdapter2.InsertCommand.CommandText +

"\r\n";

//send query

oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();

textBoxStatusQuery.Text += "\r\nQuery successful\r\n";

}

catch (System.Data.OleDb.OleDbException oleException)

{

Console.WriteLine(oleException.StackTrace);

textBoxStatusQuery.Text += oleException.ToString();

}

}

I have checked the insertcommand generated by VS8 and copied it underneath

// oleDbSelectCommand2

//

this.oleDbSelectCommand2.CommandText = "SELECT IDPortfolio, Client, OpeningDate, ClosingDate, [Action], Stake, Market" +

", OpeningPrice, ClosingPrice\r\nFROM Company";

//

// oleDbInsertCommand2

//

this.oleDbInsertCommand2.CommandText = "INSERT INTO `Company` (`Client`, `OpeningDate`, `ClosingDate`, `Action`, `Stake`," +

" `Market`, `OpeningPrice`, `ClosingPrice`) VALUES ( , , , , , , , )";

this.oleDbInsertCommand2.Parameters.AddRange(new System.Data.OleDb.OleDbParameter[] {

new System.Data.OleDb.OleDbParameter("Client", System.Data.OleDb.OleDbType.VarWChar, 0, "Client"),

new System.Data.OleDb.OleDbParameter("OpeningDate", System.Data.OleDb.OleDbType.Date, 0, "OpeningDate"),

new System.Data.OleDb.OleDbParameter("ClosingDate", System.Data.OleDb.OleDbType.Date, 0, "ClosingDate"),

new System.Data.OleDb.OleDbParameter("Action", System.Data.OleDb.OleDbType.VarWChar, 0, "Action"),

new System.Data.OleDb.OleDbParameter("Stake", System.Data.OleDb.OleDbType.Integer, 0, "Stake"),

new System.Data.OleDb.OleDbParameter("Market", System.Data.OleDb.OleDbType.VarWChar, 0, "Market"),

new System.Data.OleDb.OleDbParameter("OpeningPrice", System.Data.OleDb.OleDbType.Long, 0, "OpeningPrice"),

new System.Data.OleDb.OleDbParameter("ClosingPrice", System.Data.OleDb.OleDbType.Long, 0, "ClosingPrice")});

//

This was generated by Visual Studio,

And also I would like to know what would be the command to update the database too as I have the same function on another form and its working fine (addclient) but the database does not get updated - it says query succesfull, but no change on the back end. Any idea

Thanks guys

CommonGenius.com

In general you should not execute the InsertCommand of a DataAdapter directly. DataAdapters are used to automatically handle moving data into and out of a DataSet. If you want to execute an insert command directly, create your own Command object.

Your problem is caused by the fact that the generated command had parameter objects added to it, but you are not setting the values of these parameters before you execute the command; you are concatenating the values into the CommandText (as a side note, concatenating values from GUI elements, or any unverified source, directly into a SQL statement is a very bad idea, as it is vulnerable to SQL injection attacks). As a simple solution, try setting the values of the parameters on the command object instead of recreating the CommandText, e.g. oleDbDataAdapter1.InsertCommand.Parameters("Client").Value = listBox1.Text (this also protects against SQL injection). However, as I said above, you should probably rethink the way you are using your objects.

Chanki

Thanks for you reply mate,

I have found a way to work around the problem, but cheers for the advices

K