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

Question

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);

This looks like you're starting to build something that will be crazy-vulnerable to sql injection attacks. — Joel Coehoorn
– Joel Coehoorn, Commented Sep 16, 2016 at 22:18

Joel Coehoorn · Accepted Answer · 2016-09-26 00:31:18Z

1

ADODB is ancient and deprecated. 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();
}

edited Sep 26, 2016 at 0:31

answered Sep 16, 2016 at 22:16

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user6063812 Over a year ago

Thank you very much indeed Joel. You can tell the last time I programmed was 15 years ago and not even then on these technologies. I will change my connection to ADO.Net further to your recommendation. Much appreciated.

user6063812 Over a year ago

What I am actually trying to do is to loop through a table and build up a JSON string with a list of ID's and after every 40 iterations send the JSON request and process the 40 JSON items returned using an update with a where clause with details contained in the 40 returned JSON requests. And repeat that every 40 records read. I know I need 2 queries - one is just a select distinct for the loop and the other an update where. Can you possibly provide the basic outline like you've done with the select - I'm getting syntax errors with your code. Hence using ADODB being more familiar.

Joel Coehoorn Over a year ago

You're likely missing some imports (ie: system.data, system.data.sqlclient).

Ilyes · Accepted Answer · 2016-09-16 22:03:49Z

1

You caan update your table as follow :

Syntax: MSDN

Update YourTable
Set Column = Value
Where Condition

cmdInsert.CommandText = "Update ExchangeTypes Set MarketSelectionId =  VALUE 
Where MarketSelectionId = '?' ";

edited Sep 16, 2016 at 22:03

answered Sep 16, 2016 at 21:57

Ilyes

15k5 gold badges31 silver badges61 bronze badges

1 Comment

user6063812 Over a year ago

Thank you Sami. I got it working which is a great step forward. However looks like my inexperience in choice of connection has been exposed and i need to change to more modern options.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related