Getting error on opening SqlDataReader

Hi - frustration abounds...

Working in VS2008, VB.NET

Need to do a select query against a table using the userid from the .net membership collection once logged in. The purpose is to capture additional fields (and post to session variables) using the guid as the matching field in the select statement. All 'appears' to be proper, but when I open the page, I get this error (source in code window):

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'fd7'.
Source Error:

Line 39:         Dim dsc As New SqlCommand(strSQL, conn)
Line 40:         Dim myReader As SqlDataReader
Line 41:         myReader = dsc.ExecuteReader()
Line 42:
Line 43:         If myReader.HasRows Then

Source File: C:\Lansolutions\Clients\Epsten\ACTNow\Main.aspx.vb    Line: 41

If I do a trace, the strSql variable is correct, and includes the GUID value (which maps to the correct value in the table). If the Try/End Try are in place, it ignores, as it should, but don't get values.  Suspect a 'connection open' issue, but not sure why.

So, something is not going right, and I'm not sure where...

Any ideas?
Thanks - Ben


'next, we need to get the other info for the user
        ' check username/password against a database table
        Dim EmpID As Integer
        Dim PMID As Integer
        Dim LastName As String
        Dim Firstname As String
        Dim PrimaryID As Boolean
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ARDDBConnectionString").ToString())
        ' get row back based on userid
        conn.Open()
        Dim strSQL As String = ("Select * From PropertyManagerEmployees Where PMUserId = " & currentUserId.ToString())
        'Try
        Dim dsc As New SqlCommand(strSQL, conn)
        Dim myReader As SqlDataReader
        myReader = dsc.ExecuteReader()

        If myReader.HasRows Then
            'Iterate through the results
            While myReader.Read()
                EmpID = myReader.GetInt32(1)
                Session("EmpID") = EmpID
                PMID = myReader.GetInt32(2)
                Session("PMID") = PMID
                LastName = myReader.GetString(3)
                Firstname = myReader.GetString(4)
                Session("UserName") = Firstname & " " & LastName
                PrimaryID = myReader.GetBoolean(5)
            End While
        End If
        'Catch
        'End Try
        ' Close the connection (will automatically close the reader)
        conn.Close()

Open in new window

SDBenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

guru_samiCommented:
That isn't a good coding practice. Inline Sql is prone to Sq lInjection. You should at least be using Parametrized  query like:

Dim strSQL As String = "Select * From PropertyManagerEmployees Where PMUserId=@UserId"
Dim dsc As New SqlCommand(strSQL, conn)
dsc.Parameters.AddWithValue("@UserId",currentUserId.ToString())
Dim myReader As SqlDataReader
'rest of your code

Open in new window

SDBenAuthor Commented:
Thanks for the comment - trying to get things working first, then will do safety cleanup, which is definitely needed.

Anyway, found my issue - really dumb - forgot to surround the guid tostring value with delimiters. Oh, well...

Appreciate the look.
Ben
xizwyckCommented:
Try parameterizing your query.

Dim dsc As New SqlCommand("Select * From PropertyManagerEmployees Where PMUserId = @currentUserId", conn)

dsc.Parameters.AddWithValue("currendUserId", currentUserId.ToString())

But if the error is near fd7, sounds like an unescaped character, which the code above would solve.

That or PropertyManagerEmployees is a view that has an error.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Miguel OzSenior Software EngineerCommented:
Your code seems Ok, but I would like to see your connection string. Chances are there is a syntax error there some how.
Also on line 10 use:
Dim conn As New SqlConnection(WebConfigurationManager.ConnectionStrings("ARDDBConnectionString").ConnectionString)

In asp.net we use WebConfigurationManager not ConfigurationManager

SDBenAuthor Commented:
The various comments are welcomed - always more to learn. Error was due to the guid not being delimited, so that the first '-' in the id caused an exception. Parameterization takes care of that, of course.

Like most, I learn by playing, and "using" code found different places... hadn't know about WebConfigurationManager vs ConfigurationManager - thanks!

Ben

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScubagoCommented:
If you want to do this without parameters, you should place your guid between single quotes N'.....' so TSQL knows your passing a string
SDBenAuthor Commented:
Found my error, but the other comments were helpful in other areas.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.