Integer variable is acquiring a string value of "ad" somewhere along the line, can anyone see where?

Question

Here is my code:

I should get output of the department id (did) as an integer and the templatefilename (result) that is required.

The errors I get are: Conversion from string "ad" to type 'Integer' is not valid. I'm fairly new to asp.net and cannot see where the did variable picks up the "ad" string.

Any help would be greatly appreciated.

Thanks

Answer 1

When you construct the query to the table departmentsgroupings, you're changing the value of sql, but you aren't creating a new SqlCommand. This means that cmd still contains the old SQL statement (the query to the Modules table) which, when executed, returns "ad".

To fix this, change your code as follows:

sql = ("select departmentsid from departmentsgroupings where groupingid =" & pageid & "")
Set cmd = New SqlCommand(sql, conn)    
did = (cmd.ExecuteScalar)

You may have expected the change you made to sql to get passed on automatically to the SqlCommand -- but it doesn't work that way.

Edit: Your code, as written, is vulnerable to SQL injection attacks. If you don't know what these are, you need to read the first answer to this:

http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain

To protect yourself against these kinds of attacks, use parameterized queries.

Answer 2

The mistake is in these lines:

sql = ("select departmentsid from departmentsgroupings where groupingid =" & pageid & "")
did = (cmd.ExecuteScalar)     <---- Wrong command executed here.

You presumably meant to execute the code in sql, not cmd again.

Answer 3

Looking at your SQL run (below)

Dim sql As String = ("select modules.templatefilename from modules, grouping where grouping.moduleid = modules.id and grouping.id = " & pageid & "")

You are selecting the templatefilename.

Furthur down you are then doing a scalar on the results of that SQL, which looking at it will return the templatefilename, which will be a string.

Also you delcared result is a string, which is also wrong.

result = (cmd.ExecuteScalar) 

This is incorrect, as you will be doing a Scalar on a string. You need to pass the new piece of SQL to the cmd, and re-create a new SqlCommand object off that sql.