Post reply

Issue with Access string in MS SQL

  • April 7, 2005 at 5:22 pm

    #163220

    Hello,

    This is my first ever post here, so please be gentle with me.

    I have an issue where I have the following Database call that works great with an Access Database, but it has come time to upgrade to SQL server for the application and I get an error with the rsCategory.Open() part:

    CONNECTION_STRING = "Provider=SQLOLEDB;Data Source=(Server Source);database=(Database Name);uid=(DB User);pwd=(DB Password);"

    set rsCategory = Server.CreateObject("ADODB.Recordset")

    rsCategory.ActiveConnection = CONNECTION_STRING

    rsCategory.Source = "SELECT tblItems_Category_1.CategoryImageFile AS ParentCategoryImageFile, tblItems_Category_1.CategoryLabel AS ParentCategoryLabel, tblItems_Category_1.CategoryDesc AS ParentCategoryDesc, tblItems_Category_1.CategoryValue AS ParentCategoryValue, tblItems_Category.CategoryID, tblItems_Category.CategoryValue, tblItems_Category.ParentCategoryID, tblItems_Category.CategoryDesc, tblItems_Category.CategoryLabel, tblItems_Category.CategoryImageFile, Count(tblItems.ItemID) AS ItemCount  FROM (tblItems_Category LEFT JOIN tblItems_Category AS tblItems_Category_1 ON tblItems_Category.ParentCategoryID = tblItems_Category_1.CategoryID) RIGHT JOIN tblItems ON tblItems_Category.CategoryID = tblItems.CategoryIDkey  WHERE (((tblItems.Activated)='True'))  GROUP BY tblItems_Category_1.CategoryImageFile, tblItems_Category_1.CategoryLabel, tblItems_Category_1.CategoryDesc, tblItems_Category_1.CategoryValue, tblItems_Category.CategoryID, tblItems_Category.CategoryValue, tblItems_Category.ParentCategoryID, tblItems_Category.CategoryDesc, tblItems_Category.CategoryLabel, tblItems_Category.CategoryImageFile  HAVING (((tblItems_Category.ParentCategoryID)<>0))  ORDER BY tblItems_Category_1.CategoryValue, tblItems_Category.CategoryValue"

    rsCategory.CursorType = 0

    rsCategory.CursorLocation = 2

    rsCategory.LockType = 3

    rsCategory.Open()

    rsCategory_numRows = 0

    Can someone advise what I need to do to get MS SQL to read this as I am still banging my head against the monitor trying to work it out. Very much a big thank you in advance.

    Cheers,

    David

  • April 7, 2005 at 5:28 pm

    #551334

    >> I get an error

    You really need to provide the error text.

    >>tblItems.Activated)='True'

    This is the only suspect part I see. What is the datatype of tblItems.Activated ? Is it really a character column, or is it a bit datatype ? If it's a bit, SqlServer doesn't understand True/False as strings and you need to use zero for False or one for True

    tblItems.Activated = 1

     

  • April 7, 2005 at 6:03 pm

    #551336

    Yes I should of inserted the error, I keep forgetting to do that when I have erros.

    The error I get is:

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

    The requested properties cannot be supported.

    /applications/MyAppManager/inc_myapmanager.asp, line 83

    Line 83 is the:  rsCategory.LockType = 3

    I must also point out that I did a straight import of the Access Database in to SQL, and I am not sure if it has anything to do with it, but the tables are linked in the Access Database.

    Cheers,

    David

  • April 8, 2005 at 6:42 am

    #551449

    If your cursortype is 0 (forwardonly) change your locktype to 1 and test....







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply