Get the results from a SQL SUM command in VB

Question

0.00/5 (No votes)

See more:

I'm trying to get the SQL server to execute the summation of a field, rather than my VB code. This first code segment works great for me. The second throws an exception at the bold/underlined text. I can't figure out what the dbReader should be looking for as a result.

Thanks,

What I have tried:

' Sum up the material costs
            cmd = "SELECT WOBOM_AMATCST FROM WOBOM WHERE WOBOM_WOPRE='" + wopre + "' AND WOBOM_WOSUF='" + wosuf + "'"
            dbcmd.CommandText = cmd
            dbcmd.Connection = nconn
            dbReader = dbcmd.ExecuteReader()
            If dbReader.HasRows Then
                While (dbReader.Read)
                    MatlCost = MatlCost + dbReader("WOBOM_AMATCST")
                End While
            End If

cmd = "SELECT SUM(WOBOM_AMATCST) FROM WOBOM WHERE WOBOM_WOPRE='" + wopre + "' AND WOBOM_WOSUF='" + wosuf + "'"
            dbcmd.CommandText = cmd
            dbcmd.Connection = nconn
            dbReader = dbcmd.ExecuteReader()
            If dbReader.HasRows Then
                While (dbReader.Read)
                    MatlCost = MatlCost + dbReader("WOBOM_AMATCST")
                End While
            End If

Posted 4-Nov-20 3:21am

Beanmeister

Updated 4-Nov-20 3:37am

Add a Solution

Comments

Richard Deeming 4-Nov-20 9:34am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

dbcmd.CommandText = "SELECT SUM(WOBOM_AMATCST) FROM WOBOM WHERE WOBOM_WOPRE = @wopre AND WOBOM_WOSUF = @wosuf"
dbcmd.Parameters.AddWithValue("@wopre", wopre)
dbcmd.Parameters.AddWithValue("@wosuf", wosuf)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-11-04T03:38:00

Solution 1

Use parameters to remove the SQL Injection[^] vulnerability from your code.

You also can't reference a field by name when the query doesn't specify a name for the field.

VB.NET

dbcmd.CommandText = "SELECT SUM(WOBOM_AMATCST) FROM WOBOM WHERE WOBOM_WOPRE = @wopre AND WOBOM_WOSUF = @wosuf"
dbcmd.Parameters.AddWithValue("@wopre", wopre)
dbcmd.Parameters.AddWithValue("@wosuf", wosuf)

dbcmd.Connection = nconn
dbReader = dbcmd.ExecuteReader()
While (dbReader.Read)
    MatlCost = MatlCost + dbReader(0)
End While

NB: Don't store connection, command, and data reader objects in class-level fields. Instead, create them when needed, and wrap them in Using blocks to ensure they're properly disposed of when you've finished with them.
Using Statement - Visual Basic | Microsoft Docs[^]

Posted 4-Nov-20 3:38am

Richard Deeming

Comments

Beanmeister 4-Nov-20 10:30am

First of all, thank you for the help and the education. However, when I execute this VB code, the parameters don't seem to be getting inserted. I get an error when executing the ExecutedReader command saying the SQL command doesn't recognize @wopre, instead of my actual variable.

Richard Deeming 4-Nov-20 10:32am

Are you using an OleDbCommand rather than a SqlCommand?

For OLEDB, you may need to use ? instead of the parameter names in the command text:

dbcmd.CommandText = "SELECT SUM(WOBOM_AMATCST) FROM WOBOM WHERE WOBOM_WOPRE = ? AND WOBOM_WOSUF = ?"
dbcmd.Parameters.AddWithValue("@wopre", wopre)
dbcmd.Parameters.AddWithValue("@wosuf", wosuf)

Beanmeister 4-Nov-20 10:50am

I'm actually using Pervasive SQL. The ? works great. Again, thanks for the education.

Beanmeister 5-Nov-20 15:34pm

So I ran into an additional problem, probably because of my lack of knowledge regarding the parameterized query. The above logic is inside of a loop and the wopre and wosuf change every loop. However, the results indicate the values aren't changing in the commandtext.

Thoughts?

Richard Deeming 6-Nov-20 3:38am

Probably because you're reusing the same command instance for every iteration of your loop. Try clearing the parameters before adding them:

dbcmd.CommandText = "SELECT SUM(WOBOM_AMATCST) FROM WOBOM WHERE WOBOM_WOPRE = ? AND WOBOM_WOSUF = ?"
dbcmd.Parameters.Clear()
dbcmd.Parameters.AddWithValue("@wopre", wopre)
dbcmd.Parameters.AddWithValue("@wosuf", wosuf)