VBA ADODB CommandString Error

Question

So I've got an interesting problem. I'm working with VB modifier (VBA) in Microsoft GP2013, and all I want to do is reference a table in one of our databases, grabbing total amount of call hours based on the service call number (job number) entered. To do so, I have opened an SQL connection, with an SQL command to do so. The problem is, I consistently get conversion errors regardless of how I convert the data coming in, and the data the SQL command is referencing. Here is the error I get:

Conversion failed when converting the varchar value 'Rack build' to data type int.

Here is the command string:

cmdString.CommandText = "SELECT SUM(TRXHRUNT)/100 from CTI_Timetrack_Open_and_Closed where TRXDSCRN = " & CallNumber.Value & ""

Where CallNumber.Value is an integer being populated by our form. I've wrapped my head around this all day, and the answer is probably very simple. I am looking for any advice to alleviate this error. Thanks.

What data type is TRXHRUNT -- are you attempting to sum a varchar? — sgeddes
– sgeddes, Commented Apr 22, 2015 at 22:29
Either TRXHRUNT or TRXDSCRN has a value of 'Rack build' for one of the records. — Millie Smith
– Millie Smith, Commented Apr 23, 2015 at 0:57

Millie Smith · Accepted Answer · 2015-04-23 01:25:21Z

If CallNumber.Value is really an int, then one of your records has a value of 'Rack build' for TRXDSCRN.

Try this select:

SELECT * FROM CTI_Timetrack_Open_and_Closed WHERE TRXDSCRN = 'Rack build';

If there are actually integer values in that column, and you really do want to search for an integer there, you will need to pass it as a string by adding single quotes around CallNumber.Value:

cmdString.CommandText = "SELECT SUM(TRXHRUNT)/100 from CTI_Timetrack_Open_and_Closed where TRXDSCRN = '" & CallNumber.Value & "'"

You really should be using command parameters though to pass the CallNumber.Value in. Otherwise somebody could wreak havoc on your database by passing the following string in for the CallNumber (if it accepts strings):

'; DELETE FROM CTI_Timetrack_Open_and_Closed;--close command with comment

You can use parameters to solve that SQL injection and others using this:

cmdString.CommandText = "SELECT SUM(TRXHRUNT)/100 from CTI_Timetrack_Open_and_Closed where TRXDSCRN = ?";

Dim Pm As ADODB.Parameter
Set Pm = cmdString.CreateParameter("parentid", adNumeric, adParamInput)
Pm.Value = CallNumber.Value
cmdstring.Parameters.Append Pm

I know that TRXHRUNT is not the problem, because if you try to sum on a varchar, you get the following error:

Msg 8117, Level 16, State 1, Line 2

Operand data type varchar is invalid for sum operator.

Collectives™ on Stack Overflow

VBA ADODB CommandString Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related