I am creating a very basic application but its not working. I am pretty much it must be something stupid and I am looking at this too closely. So I am seeking a second option..
Here is my query:
Private callsQuery As String = "SELECT CL.CALL_NUMBER,UA.FIRST_NAME, UA.SURNAME, LOC.NAME as LOCATION, CL.SHORT_PROBLEM " _ & "FROM CL_CALL_LOGGING AS CL " _ & "INNER JOIN AR_User_Attributes AS UA " _ & "ON UA.Ref = CL.User_Ref " _ & "INNER JOIN AR_LOCATIONS as LOC " _ & "ON LOC.LOCATION_REF = UA.LOCATION_REF " _ & "WHERE CL.Resolve_Time IS NULL " _ & "AND CL.CURRENT_GROUP ='" & selectedGroup & "'"
selectedGroup is a public property pointing to a variable. before the SQL is run the variable is passed a value from the query string.
The problem is ti isnt working, when debugging, If I view the string it looks like this:
Private callsQuery As String = "SELECT CL.CALL_NUMBER,UA.FIRST_NAME, UA.SURNAME, LOC.NAME as LOCATION, CL.SHORT_PROBLEM " _ & "FROM CL_CALL_LOGGING AS CL " _ & "INNER JOIN AR_User_Attributes AS UA " _ & "ON UA.Ref = CL.User_Ref " _ & "INNER JOIN AR_LOCATIONS as LOC " _ & "ON LOC.LOCATION_REF = UA.LOCATION_REF " _ & "WHERE CL.Resolve_Time IS NULL " _ & "AND CL.CURRENT_GROUP = ''"
So it is not receiving the variable for some reason. I have also tried this with a viewstate variable with identical results. It shouldn't be an issue applying a variable in the string, maybe I have just been looking at this too long.
Hopefully a few suggestions from you guys and fresh eyes tomorrow can make this go away 
Cheers folks
Add a quickwatch or watch to your selectedGroup variable. You'll likely find that it's an empty string, which is where you need to make your fix.
Also, look up the StringBuilder class. Not only is it the most efficient way of building strings, it also make your code so much more readable.
When debugging if I view the selectedGroup variable I can see the group number in there. I know about stringbuilder but I was under the impression it uses slightly more resources. This app is mainly going to be accessed via a mobile device so I am trying to keep it as resource starved as possible.
Here is the main body of code. The only bit not shown is a little validation method making sure the selectedGroup property isnt passing back any crap
I have discounted that as a problem by bypassing it on a few of the tests. So the query or code is to blame I suppose
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Assign the selected group to the property/variable
selectedGroup = Request.QueryString("Group")
'If INVALID input
If selectedGroup = "00" Then
'create error label
Dim err As New Label
err.Text = "Invalid group selected, Please go back and select your group from the dropdown list. <br /><br />"
err.ForeColor = Drawing.Color.Red
'create back button
Dim lnkErr As New HyperLink
lnkErr.Text = "Back"
lnkErr.NavigateUrl = "~/index.aspx"
pnMain.Controls.Add(err)
pnMain.Controls.Add(lnkErr)
Else
'Create DB connection
Dim dbAccess As New MobileInfra.DataAccess
'Assign query
dbAccess.CommandText = callsQuery
Try
Dim dsCalls As New Data.DataSet
'assign datasource
dsCalls = dbAccess.ExecuteDataSet
'Create gridview to display
Dim gvCalls As New GridView
gvCalls.DataSource = dsCalls
gvCalls.ID = "gvCalls"
gvCalls.RowHeaderColumn = False
gvCalls.AutoGenerateColumns = False
gvCalls.GridLines = GridLines.Horizontal
'Create Columns
Dim CALL_NUMBER As New BoundField
CALL_NUMBER.DataField = "CALL_NUMBER"
Dim col1 As DataControlField = CALL_NUMBER
Dim FIRST_NAME As New BoundField
FIRST_NAME.DataField = "FIRST_NAME"
Dim col2 As DataControlField = FIRST_NAME
Dim SURNAME As New BoundField
SURNAME.DataField = "SURNAME"
Dim col3 As DataControlField = SURNAME
Dim LOCATION As New BoundField
LOCATION.DataField = "LOCATION"
Dim col4 As DataControlField = LOCATION
gvCalls.Columns.Add(CALL_NUMBER)
gvCalls.Columns.Add(FIRST_NAME)
gvCalls.Columns.Add(SURNAME)
gvCalls.Columns.Add(LOCATION)
'databind
gvCalls.DataBind()
pnMain.Controls.Add(gvCalls)
Catch ex As Exception
End Try
End If
End Sub
Where is the selectedGroup declared? Does it fall out of scope at any point?
Just created a page with the function below and accessed: Default.aspx?Group=17
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Assign the selected group to the property/variable
Dim selectedGroup As String = Request.QueryString("Group")
'If INVALID input
If selectedGroup = "00" Then
'create error label
Response.Write("ERROR")
Else
'Create DB connection
Dim callsQuery As String = "SELECT CL.CALL_NUMBER,UA.FIRST_NAME, UA.SURNAME, LOC.NAME as LOCATION, CL.SHORT_PROBLEM " _
& "FROM CL_CALL_LOGGING AS CL " _
& "INNER JOIN AR_User_Attributes AS UA " _
& "ON UA.Ref = CL.User_Ref " _
& "INNER JOIN AR_LOCATIONS as LOC " _
& "ON LOC.LOCATION_REF = UA.LOCATION_REF " _
& "WHERE CL.Resolve_Time IS NULL " _
& "AND CL.CURRENT_GROUP ='" & selectedGroup & "'"
Try
Response.Write(callsQuery.ToString())
Catch ex As Exception
End Try
End If
End Sub
And the output was: SELECT CL.CALL_NUMBER,UA.FIRST_NAME, UA.SURNAME, LOC.NAME as LOCATION, CL.SHORT_PROBLEM FROM CL_CALL_LOGGING AS CL INNER JOIN AR_User_Attributes AS UA ON UA.Ref = CL.User_Ref INNER JOIN AR_LOCATIONS as LOC ON LOC.LOCATION_REF = UA.LOCATION_REF WHERE CL.Resolve_Time IS NULL AND CL.CURRENT_GROUP ='17'
..as expected.
You haven't declared the selectedGroup variable within the function so at best guess the variable isn't in scope. Is it declared elsewhere with the wrong access modifier?
I'm heading out for the afternoon but I can perhaps assist more this evening. Just strip it down to bare bones and build it back up again.
For a quick test, change the variable name to queryGroup and limit the scope to that PageLoad method only. That will help identify/elimate scope issues.
Hope you get it sorted, mate.
I originally had
private _group as string Private Property selectedGroup() as string etc...
Which as you rightly pointed out is out of scope. I have moved the declaration within the page_load and it is working. I knew it would be soemthing stupid!
Thanks very much indeed for having a look. I only do this kind of work on and off so I get rusty and little things can trip me up.
I owe you one. :beer:
No worries, mate.
I rarely develop nowadays too and it's often scope that scuppers me. If you're using a variable debugging generally gives you and "instance not set" error which usually identifies scope as the issue.
Glad it's sorted.
:beer:
Just a little pointer. Use parameterised queries instead of string mashing to build your SQL... your code is probabily wide open to SQL Injection attack.
I do validation on all input before it gets anywhere near the SQL. I am actually going to make them into stored procs though, to make use of the DB cache facility.