Click here to Skip to main content
15,311,717 members
Please Sign up or sign in to vote.
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
Updated 4-Nov-20 3:37am
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

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[^]
   
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)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900