Prepared statement '(@ID int...,' expects parameter @ID, which was not supplied

  • Thread starter Thread starter GolanH
  • Start date Start date
G

GolanH

i'm trying to use an sqlCommand as an UpdateCommand for a dataAdapter
which contains a changd Dataset.

Whenever i try to upadte - i generat a command with an Update
Statement from the original Select Command of the DataAdapter (using
DataReader which supplies the meta data = ColumnName, BaseTableNAme
etc.)

When i finaly write Da.Upadate(Ds,"tblName") i get an error:

"Prepared statement '(@ID int,@Name nvarchar(50),@Number
nvarchar(50),@Remarks ntext,' expects parameter @ID, which was not
supplied"


my code is:
private void CreateCommands(DataSet Ds, string MainTable, SqlCommand
cmd)
{
// Ds: The changed dataset which should be updated
// MainTable: the table to be updated (when having a
join in the select statement)
// cmd: the original Select command of the Dataset

// Prepare the strings for generating commands
string sUpd = "Update " + MainTable + " Set ";
string sIns = "Insert into " + MainTable + "(";
string sInsVal = "Values (";
string sDel = "Delete From " + MainTable + " Where " +
Ds.Tables[0].PrimaryKey + "= @" + Ds.Tables[0].PrimaryKey;
// meta Data from the Datareader
string sBaseColumnName="";
string sBaseTableName="";
string ColType;
int ColLength;
string sParamName = "";
string sPk = "";

SqlDbType Type = SqlDbType.Int;
SqlParameter parm;

// Creating the DataReader for extraction of the construct
SqlDataReader dr ;
dr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
DataTable dt = dr.GetSchemaTable();

// Go through the columns, get the data and create the statement
foreach (DataRow Dr in dt.Rows)
{
sBaseColumnName = Dr["BaseColumnName"].ToString();
sParamName = "@" + Dr["BaseColumnName"].ToString();
sBaseTableName = Dr["BaseTableName"].ToString();
ColType = Dr["DataType"].ToString();
ColLength = (int)Convert.ChangeType(Dr["ColumnSize"],typeof(int));

// assuming the increments are the PrimaryKeys
if (Dr["IsAutoIncrement"].ToString()=="True")
{
sPk = Dr["BaseColumnName"].ToString();
}

if (Ds.Tables[0].TableName.ToString() == sBaseTableName)
{
if (sPk != sBaseColumnName)
{
// dont add the primary key to the update statement
sUpd += sBaseColumnName + " = " + sParamName + ", " ;
sIns += sBaseColumnName + ", " ;
sInsVal += sParamName + ", ";

// This section generates the commandText "Update tbl set
ColName=@colName, ...
}

// Get the type of the column
switch (ColType)
{
case "System.Int32":
Type = SqlDbType.Int;
break;
case "System.String":
Type = SqlDbType.NVarChar;
break;
case "System.DateTime":
Type = SqlDbType.DateTime;
break;
}

// Create the Parameters
if (sPk == sBaseColumnName)
{

parm=new SqlParameter (sParamName , Type,
ColLength,sBaseColumnName);
parm.SourceVersion = DataRowVersion.Original;
cmdUpd.Parameters.Add(parm);
}
else
{
parm=cmdUpd.Parameters.Add(sParamName, Type,
ColLength,sBaseColumnName);
}


}
}

// Cut the last comma and complete the statement (with Where
pk=@Pk)
sUpd = sUpd.Substring(0,sUpd.Length-2) + " Where " + sPk + "= @" +
sPk;



cmdUpd.CommandText = sUpd;
cmdUpd.CommandType=CommandType.Text;
cmdUpd.Connection = Cn;
Da.UpdateCommand = cmdUpd;
// close the Datareader
dr.Close();


Da.Update(Ds,MainTable); // Here is the error


}
 
I would dump the final string you're trying to execute as the action query.
I expect somewhere in this convoluted code you've missed a quote or dropped
a space.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

GolanH said:
i'm trying to use an sqlCommand as an UpdateCommand for a dataAdapter
which contains a changd Dataset.

Whenever i try to upadte - i generat a command with an Update
Statement from the original Select Command of the DataAdapter (using
DataReader which supplies the meta data = ColumnName, BaseTableNAme
etc.)

When i finaly write Da.Upadate(Ds,"tblName") i get an error:

"Prepared statement '(@ID int,@Name nvarchar(50),@Number
nvarchar(50),@Remarks ntext,' expects parameter @ID, which was not
supplied"


my code is:
private void CreateCommands(DataSet Ds, string MainTable, SqlCommand
cmd)
{
// Ds: The changed dataset which should be updated
// MainTable: the table to be updated (when having a
join in the select statement)
// cmd: the original Select command of the Dataset

// Prepare the strings for generating commands
string sUpd = "Update " + MainTable + " Set ";
string sIns = "Insert into " + MainTable + "(";
string sInsVal = "Values (";
string sDel = "Delete From " + MainTable + " Where " +
Ds.Tables[0].PrimaryKey + "= @" + Ds.Tables[0].PrimaryKey;
// meta Data from the Datareader
string sBaseColumnName="";
string sBaseTableName="";
string ColType;
int ColLength;
string sParamName = "";
string sPk = "";

SqlDbType Type = SqlDbType.Int;
SqlParameter parm;

// Creating the DataReader for extraction of the construct
SqlDataReader dr ;
dr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
DataTable dt = dr.GetSchemaTable();

// Go through the columns, get the data and create the statement
foreach (DataRow Dr in dt.Rows)
{
sBaseColumnName = Dr["BaseColumnName"].ToString();
sParamName = "@" + Dr["BaseColumnName"].ToString();
sBaseTableName = Dr["BaseTableName"].ToString();
ColType = Dr["DataType"].ToString();
ColLength = (int)Convert.ChangeType(Dr["ColumnSize"],typeof(int));

// assuming the increments are the PrimaryKeys
if (Dr["IsAutoIncrement"].ToString()=="True")
{
sPk = Dr["BaseColumnName"].ToString();
}

if (Ds.Tables[0].TableName.ToString() == sBaseTableName)
{
if (sPk != sBaseColumnName)
{
// dont add the primary key to the update statement
sUpd += sBaseColumnName + " = " + sParamName + ", " ;
sIns += sBaseColumnName + ", " ;
sInsVal += sParamName + ", ";

// This section generates the commandText "Update tbl set
ColName=@colName, ...
}

// Get the type of the column
switch (ColType)
{
case "System.Int32":
Type = SqlDbType.Int;
break;
case "System.String":
Type = SqlDbType.NVarChar;
break;
case "System.DateTime":
Type = SqlDbType.DateTime;
break;
}

// Create the Parameters
if (sPk == sBaseColumnName)
{

parm=new SqlParameter (sParamName , Type,
ColLength,sBaseColumnName);
parm.SourceVersion = DataRowVersion.Original;
cmdUpd.Parameters.Add(parm);
}
else
{
parm=cmdUpd.Parameters.Add(sParamName, Type,
ColLength,sBaseColumnName);
}


}
}

// Cut the last comma and complete the statement (with Where
pk=@Pk)
sUpd = sUpd.Substring(0,sUpd.Length-2) + " Where " + sPk + "= @" +
sPk;



cmdUpd.CommandText = sUpd;
cmdUpd.CommandType=CommandType.Text;
cmdUpd.Connection = Cn;
Da.UpdateCommand = cmdUpd;
// close the Datareader
dr.Close();


Da.Update(Ds,MainTable); // Here is the error


}
Click to expand...
 
Golan:
When i tested your function against a couple of my tables, the function first never pickup up the primary key.

i would get a select statment like this
"Update BusinessTask Set BusinessTaskID = @BusinessTaskID, TellerSignonRequired = @TellerSignonRequired, AllowOffline = @AllowOffline, TellerTransaction = @TellerTransaction, ReportName = @ReportName Where = @" <--- missing pk

for me to reproduce your error I would need you table definition

plus since you are using a basic update statement “ I would suggest in this case using the commandbuilder

SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", nwindConn);
SqlCommandBuilder custCB = new SqlCommandBuilder(custDA);
custCB.QuotePrefix = "[";
custCB.QuoteSuffix = "]";

DataSet custDS = new DataSet();

nwindConn.Open();
custDA.Fill(custDS, "Customers");

// Code to modify data in the DataSet here.

// Without the SqlCommandBuilder, this line would fail.
custDA.Update(custDS, "Customers");
nwindConn.Close();



David
 
Back
Top