Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7

    Unanswered: MSaccess 2003 to SQL 2005

    have a web site and I'm up size to SQL 2005 was msaccess2003 got the DATA into

    you type some text in a text box on a web page
    it then create this SQL

    SELECT Prisoner.* FROM Prisoner WHERE [PRNNumber] & ' ' & [SoNumber] & ' ' & [Surname] & ' ' & [FirstName] & ' ' & [Gender] & ' ' & [Ethnicity] Like "%stephan%" Order By Prisoner.Surname


    as i dont know what feild to check I just check them all

    which work great in msaccess2003

    but in SQL 2005

    I get this error

    Microsoft OLE DB Provider for SQL Server error '80040e14'

    Invalid column name '%stephan%'.

    /society/FContact.asp, line 80



    here the code bit that does the work
    Code:
    if Request.Form("submit") = "GO" then
    SearchFor = Request.Form("Contact")
    FindThis  = "%" & replace(Request.Form("Search")," " ,"%") & "%"
    elseif Request.QueryString("feildname")<>"" then
    SearchFor = Request.QueryString("feildname")
    FindThis  = "%" & replace(Request.QueryString("search")," " ,"%") & "%"
    end if
    IF SearchFor = "All" then
          WHERESQL = " WHERE [PRNNumber] & ' ' & [SoNumber] & ' ' & [Surname] & ' ' & [FirstName] & ' ' & [Gender] & ' ' & [Ethnicity] Like """ & FindThis & """"
    else
          WHERESQL = " WHERE Prisoner." & SearchFor & " Like """ & FindThis & """"
    end if
    SQL = ""
    SQL = SQL & "SELECT Prisoner.*"
    SQL = SQL & " FROM Prisoner "
    SQL = SQL & " " & WhereSQL
    SQL = SQL & " Order By Prisoner.Surname"
    
    Set rsFind = Server.CreateObject("ADODB.Recordset")
    
    
    rsFind.Open SQL & ";", connstring, adOpenKeyset, adLockPessimistic, adCmdText

    can some point down the right track please
    Last edited by myle; 10-22-08 at 02:36.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    concatenation in SQL does not use the ampersand (&), it uses the plus (+) operator.
    Code:
    SELECT 'ABC' + 'DEF' + 'HIJ'
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Best to print out the value of the SQL string - easier to read than the code building it up.

    I would very, very strongly recommend you parametrise your statements now you are using SQL Server as the BE. JET SQL is pretty well uninjectable (at least nothing especially damaging can be done). T-SQL is a whole different matter. Please google "SQL Injection"and have a read.

    Personally I would use stored procedures but you don't even need to do that:
    http://weblogs.sqlteam.com/jeffs/arc.../21/10728.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Thanks to both
    have taken both comment on board

    this is the working SQL

    SELECT Prisoner.*, PRNNumber + ' ' + SoNumber + ' ' + Surname + ' ' + FirstName + ' ' + Gender + ' ' + Ethnicity AS Findit FROM Prisoner WHERE (PRNNumber + ' ' + SoNumber + ' ' + Surname + ' ' + FirstName + ' ' + Gender + ' ' + Ethnicity LIKE '%%') Order By Prisoner.Surname
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Best to print out the value of the SQL string - easier to read than the code building it up.
    Sorry - just realised this is exactly what you did. Obviously not easy enough for poots...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Having recently started using sequel server in lieu of access, I think you will like stored procedures.

    Here's an example of a simple sp that pulls a set of data based on a parameter and return the query results to a dataset.

    Front End
    Code:
    If rsD.Tables.Contains("Entries") Then rsD.Tables.Remove("Entries")
    Using connection As New SqlConnection(strCONsql)
        Dim adapter As New SqlDataAdapter()
        Dim sTemp As String = "TS_LoadEntries" & strParameters & ", @GroupKey=" & Str(fnDisplayed("PersonGroup"))
        adapter.SelectCommand = New SqlCommand(sTemp, connection)
        adapter.Fill(rsD, "Entries")
    End Using
    I have publicly defined:
    Public strCONsql As String = My.Settings.ConStringSQL
    Public rsD As New DataSet


    Back End
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[TS_LoadEntries]
    /*
    Author:	Sutay, Todd
    Create date: 19 Sept 2008
    
    Description:
    Pull the Entry info to which the software will populate the pivot table
    
    Application:
    Time Sheet (Project Hours Tracking) - tables prefixed with TS_
    
    Changes:
    Description				Date				User
    Created procedure		19 Sept 2008		Todd Sutay
    */
    	@WeekKey AS Int,
    	@PersonKey AS Int,
    	@GroupKey AS Int
    
    AS
    	DECLARE
    	@return_value As Int
    
    BEGIN
    	
        SET NOCOUNT ON
    	SELECT @return_value = COUNT_BIG(DISTINCT tE.EntryKey) 
    	FROM TS_tblEntry tE 
    	WHERE (tE.WeekKey) = @WeekKey And (tE.PersonKey) = @PersonKey;
        
    	IF @return_value = 0
    	BEGIN
    		EXEC @return_value = [TS_CreateNewWeek] @PersonKey, @WeekKey, @GroupKey
    	END
    
    	SET NOCOUNT ON
    	SELECT tE.ProjectKey, tE.GroupKey, tE.EntryKey, tE.EntryHours
    	FROM TS_tblEntry tE 
    	WHERE ((tE.WeekKey) = @WeekKey) And ((tE.PersonKey) = @PersonKey)
    	ORDER BY tE.ProjectKey, tE.GroupKey;
    END
    Last edited by tcace; 10-22-08 at 22:54.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •