user6063812 user6063812 - 2 months ago 12
C# Question

Correct MS SQL Update statement syntax C# with ADODB.Parameters?

Here is the code which is working for Insert - I just need help to convert it into Update with the values and the where clause syntax.

ADODB.Command cmdInsert = new ADODB.Command();
cmdInsert.ActiveConnection = conn;

cmdInsert.CommandText = "INSERT INTO ExchangeTypes(MarketSelectionId) VALUES (?)";

//Update statement to be modelled:
//cmdUpdate.CommandText = "Update ExchangeTypes SET
//LayOdds = '" & layOdds & "'" & ",
//Size='" & laySize & "' WHERE
//MarketId='" & marketid & "'" and SelectionId='" & selectionid & "'"

cmdInsert.CommandType = ADODB.CommandTypeEnum.adCmdText;

// Append the parameters
ADODB.Parameter paramMS = cmdInsert.CreateParameter(
"MarketSelectionId", // Parameter name
ADODB.DataTypeEnum.adVarChar, // Parameter type (adVarChar)
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
200, // Max size
umarketiduselectionid); // Parameter value

cmdInsert.Parameters.Append(paramMS);

object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmdInsert.Execute(out nRecordsAffected, ref oParams,
(int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);

Answer

ADODB is ancient and depcrecated. It exists solely for backwards compatibility with old vb6-era code as you port it forward. It should NEVER be used for new code.

Try this using ADO.Net for your insert instead:

using (var conn As new SqlConnection("connection string here"))
using (var cmd As new SqlCommand("INSERT INTO ExchangeTypes(MarketSelectionId) VALUES (@MarketSelectionId)", conn)
{
    cmd.Parameters.Add("@MarketSelectionId", SqlDbType.VarChar, 200).Value = umarketiduselectionid;

    conn.Open();
    cmd.ExecuteNonQuery();
}

and then the UPDATE query would look like this:

using (var conn As new SqlConnection("connection string here"))
using (var cmd As new SqlCommand("Update ExchangeTypes SET LayOdds = @LayOdds, Size = @LaySize WHERE MarketId= @MarketId AND SelectionId = @SelectionID ", conn)
{
    //Guessing at your column types/lengths here
    cmd.Parameters.Add("@LayOdds", SqlDbType.VarChar, 200).Value = layOdds;
    cmd.Parameters.Add("@LaySize", SqlDbType.Int).Value = laySize;
    cmd.Parameters.Add("@MarketId", SqlDbType.Int).Value = marketid;
    cmd.Parameters.Add("@SelectionId", SqlDbType.Int).Value = selectionid;

    conn.Open();
    cmd.ExecuteNonQuery();
}