StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs there a better way to avoid error when using ADODB.Command with parameters?
text
Body
copied!<p>I had some existing VBA code that was building queries using string concatenation. I updated the code to use a command object with a parameter instead, but then started getting an error every once in a while that said <code>String data, right truncation</code>. After looking into it, I found out that if my collection of strings started with a string smaller than the rest, it would fail when it got to a longer string. To make it work, I just added some code to sort the collection from largest to smallest. Easy enough for this example since I know I won't have more than a few strings, but if I ever have a larger collection of strings, it might be an issue.</p> <p>Is there any other way around this besides sorting the collection? Below is the section of code that causes the issue.</p> <pre><code>Dim lineNumbers As New Collection Dim cnDb as new ADODB.Connection Dim cmd as new ADODB.Command Dim rs as new ADODB.RecordSet lineNumbers.Add "001-3""-5116323-ABA" lineNumbers.Add "001-1""-5116327-ABA-1 1/2""C" ' If not sorted, it fails when it tries this one lineNumbers.Add "001-1""-5116327-ABA" lineNumbers.Add "001-1""-5116327-ABA-1""C" sQry = "SELECT COUNT(commondatalink) FROM commondata WHERE reportedlineno = ?" cmd.ActiveConnection = cnDb cmd.CommandText = sQry cmd.CommandType = adCmdText For i = 1 To lineNumbers.Count Set rs = cmd.Execute(, Array(lineNumbers(i))) ' Error thrown here on unsorted list If rs(0) > 1 Then ' do something... End If Next i </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload