none
VB2005 & MS SQL Server - Selecting DB record using WHERE by comparing with a string RRS feed

  • Question

  • I obtain a Company Code from a Cookie and it selects the correct Code OK using:

    Dim CoCode As String = Trim(Request.Cookies("CoIDCode").Value())

     

    I then attempt to use the string CoCode obtained above to extract the record as follows:

    Dim Obtain1 As String"SELECT UserID, FirstName, LastName "FROM Users_TBL WHERE _

    & CoCodeID = 'CoCode'  ORDER by LastName, FirstName"

     

    This returns an error "Incorrect syntax near the keyword 'FROM'. "  - I can't find any other Help information.

     

    I believe the problem is the format of the string 'CoCode' .  This worked OK in VB2002 but not in VB2005.

     

    Could anybody shed a light on this - I can't find any article that covers this scenario

     

    Many thanks

     

    Pat

     

    Tuesday, March 11, 2008 6:15 PM

All replies

  • Try debugging your application to see what is the value of CoCode and run the query from sql server management studio to see if you get an error there. Also, use parametrized queries instead of plain text so that your application is not open to sql injection attacks.
    Tuesday, March 11, 2008 7:17 PM
  • What you posted shouldn't even compile, let alone run.  I think you garbled something in transferring your code to the post.

     

    Something that will help you, and that you should be doing anyway:  use a parameter query, don't formulate SQL via string concatenation.  Using concatenation will fail if any variable you're using contains an apostrophe, whether by accident or on purpose.  (Google "SQL injection attack" for more on that last issue.)  Look in the .NET Framework documentation for SqlParameter and you'll find examples of the right way to do this.

    Tuesday, March 11, 2008 7:17 PM
  • If you want to use contatenation to create your SQL statement, then it should look like

     

    Dim Obtain1 As String"SELECT UserID, FirstName, LastName "FROM Users_TBL WHERE  CoCodeID = '" & CoCode.Replace("'","''") & "'  ORDER by LastName, FirstName"

     

    But I would suggest to use parameterized query to make it safe and avoid potential SQL injection issues.

     

     

    Wednesday, March 12, 2008 10:00 AM
    Moderator
  • OK, thanks for the responses - Guess I didn't make my problem very clear but VMazur seemed to more closely understand what I'm trying to do.  I'll try to explain once more:

     

    My Login page collects a Cookie which is used in a Web page to set the WHERE statement (CoCode = Cookie.value).

     

    I can achieve this using the sqldatasource select parameters in the .aspx page.

     

    Without going into further detail complications, I would prefer to code this in the .aspx.vb page in the pageload routine.

     

    Is this possible, or do I have to completely re-write the original VB2002 coding?

     

    Thanks again

     

    Pat 'D

    Thursday, March 13, 2008 8:53 AM