Help Please - Microsoft OLE DB Provider for SQL Server error '80040e21'

I had to switch one website from access db to sql and i had many errors all over the site but managed to fix them one way or another but this erro i cant fix.

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

The requested properties cannot be supported.

/apartments_for_rent_in_bulgaria_bansko/forum.asp, line 127

The error is generated when it trys to open a recordset. Here is the code of the recordset.
Code: I am new to sql and dont know much of the syntax differenties between the access and sql so please i will appriciate if anyone helps me fix that.

Posted On: Tuesday 20th of November 2012 03:22:48 AM Total Views:  311
View Complete with Replies

Related Messages:

database structure help   (264 Views)
ok need a bit of help with creating a database for the structre of my company, the attached image is an example of the hierachy in my company (ignore the names) I'm ok with the staff table but my main problem is with the department table. This is how I had it at first DeptID (primary key) DeptName DeptHead (staff members id - foreign key) Level Parent In the image you should see the level number beneath the dept name, fairly obvious what I thought here. And again Parent refers to the parent field directly above, for instance in this case the parent of the Hats Department would be Candles, parent of Candles would be Bakery, parent of Bakery would be board..... now onto the problem.... With this example if I wanted to list in the hats department I could use an SQL statement roughly like this SELECT * FROM Staff,Department WHERE (Department.Dept=hats or Department.Parent=hats) AND (Staff.DeptID=Department.DeptID) now what this would return would be in the hats and fishmonger department....however I also need to list in Candles, Bakery and Board....don't think this is possible with my structure kinda stuck, anyone faced this problem before
Please help with Update query   (316 Views)
Hi ! Please help... How I can write a query in which I update some text in column. I have a column with long text(not only one word),in this text I only want to update x to y ,for example.
table design help needed   (205 Views)
I have a gamming website. I have pages which displays data from other clans. Members need to register and login to view full clan details. User who have not logged in can only view partial data. Now I need to keep track of the kind of hits each page received. That is I want to tell clan say X that these particular members viewed your page these many times and on these dates and these members who have not registered have viewed your page these many times and on these dates. I am using ASP with MS SQL. I would like some help on designing the tables and layout that is efficient.
help....can't even install SQL server in XP!   (314 Views)
Hi all, i need help for the installation.. SQL server 2000 gave me the error "The logon account cannot be validated for the SQL service . Verify that the user name and password entered are correct. The logon attempt failed." I used my windows XP administrator account username and password .. why it won't allowed me to install using a Domain user account instead of the local system account The funny thing is that i did installed SQL server before but the SQL server service cannot be logon when i tried to use i reformatted my HD. Please help.....i do not wish to reformat my HD again or use the local system account during the installation. Local System account vs Domain User account.. which is better
Query Problem.. Logic Problem need help   (252 Views)
Im trying to get the stored procedure to send back the rows which are only 'proposal's but i keep getting the other type (jobs) in this case in the C# Web App as well Can anyone see a problem with my code... thanks CREATE PROCEDURE SP_GetProposalListGenerator( @strPropID varchar(35), @strPDPM varchar(10), @strBusinessGroup varchar(40), @strLocation varchar(20), @strStudyType varchar(20), @strStatus varchar(6), @strProposalName varchar(35), -- @dtmProposalDateMin datetime, --not used -- @dtmProposalDateMax datetime, @strCompanyCity varchar(30), @strContact varchar(40), @strCompany varchar(40) ) AS --Declare string variables to be used to execute the command DECLARE @strCMDAND varChar(8000) DECLARE @strCMDOR varChar(8000) SET @strCMDOR = '' SET @strCMDAND = '' BEGIN SET @strCMDAND = @strCMDAND+'SELECT a.TITLE, a.CONTSUPREF, a.ZIP, a.COUNTRY, a.ADDRESS1, a.STATE, a.ADDRESS2, a.LINKACCT, a.NOTES, a.ACCOUNTNO, a.EXT, b.COMPANY, b.CONTACT, b.CITY, b.PHONE1, b.PHONE2, b.PHONE3, b.STATE as c1STATE, b.COUNTRY as c1COUNTRY FROM CONTSUPP a INNER JOIN CONTACT1 b on a.ACCOUNTNO = b.ACCOUNTNO WHERE ( ' END --Only add where condition if the search parameter is actually being searched for. FOR # 1 -> 10 /* IF (@strSOMETHING IS NOT NULL) BEGIN (scope { ) -if the parameter is used then SET @strCMDAND = @strCMDOR +'(x.SOMETHING LIKE ''%'' + @strSomething + ''%'') AND ' -add condition that the field will either be like the search parameter SET @strCMDOR = @strCMDOR + '(x.SOMETHING = NULL) AND ' -or the field will be completely null END ( } scope ) */ IF (@strPropID IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND +'(a.TITLE LIKE ''%'+ @strPropID +'%'') AND ' SET @strCMDOR = @strCMDOR + '((a.TITLE IS NULL) OR (a.TITLE = '''')) AND ' END IF (@strPDPM IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(a.ZIP LIKE ''%' +@strPDPM +'%'') AND ' SET @strCMDOR = @strCMDOR + '((a.ZIP IS NULL) OR (a.ZIP = '''')) AND ' END IF (@strBusinessGroup IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(a.ADDRESS1 LIKE ''%' +@strBusinessGroup +'%'') AND ' SET @strCMDOR = @strCMDOR + '((a.ADDRESS1 IS NULL) OR (a.ADDRESS1 = '''')) AND ' END IF (@strLocation IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '((a.COUNTRY LIKE ''%'+ @strLocation +'%'') OR (b.STATE LIKE ''%'+ @strLocation +'%'')) AND ' SET @strCMDOR = @strCMDOR + '((a.COUNTRY IS NULL) OR (a.COUNTRY ='''')) OR ((b.STATE IS NULL) OR (b.STATE = '''')) AND ' END IF (@strStudyType IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(a.ADDRESS2 LIKE ''%'+ @strStudyType +'%'') AND ' SET @strCMDOR = @strCMDOR + '((a.ADDRESS2 IS NULL) OR (a.ADDRESS2 = '''')) AND ' END IF (@strCompanyCity IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(b.CITY LIKE ''%'+ @strCompanyCity +'%'') AND ' SET @strCMDOR = @strCMDOR + '((b.CITY IS NULL) OR (b.CITY = '''')) AND ' END IF (@strStatus IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(a.EXT LIKE ''%'+ @strStatus +'%'') AND ' SET @strCMDOR = @strCMDOR + '((a.EXT IS NULL) OR (a.EXT = '''')) AND ' END IF (@strProposalName IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(a.CONTSUPREF LIKE ''%'+ @strProposalName +'%'') AND ' SET @strCMDOR = @strCMDOR + '((a.CONTSUPREF IS NULL) OR (a.CONTSUPREF = '''')) AND ' END --Not used in Jobs --IF ((@dtmProposalDateMin IS NOT NULL) OR (@dtmProposalDateMax IS NOT NULL)) BEGIN -- SET @strCMDAND = @strCMDAND + '(CONVERT(int,a.LASTDATE) >'+CONVERT(int,@dtmProposalDateMin)+') AND (CONVERT(int,a.LASTDATE) < '+CONVERT(int,@dtmProposalDateMax)+') AND ' -- SET @strCMDOR = @strCMDOR + '(a.LASTDATE IS NULL) AND ' --END IF (@strContact IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(b.CONTACT LIKE ''%'+ @strContact +'%'') AND ' SET @strCMDOR = @strCMDOR + '((b.CONTACT IS NULL) OR (b.CONTACT ='''')) AND ' END IF (@strCompanyCity IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(b.CITY LIKE ''%'+ @strCompanyCity +'%'') AND ' SET @strCMDOR = @strCMDOR + '((b.CITY IS NULL) OR (b.CITY ='''')) AND ' END IF (@strCompany IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND+ '(b.COMPANY LIKE ''%'+ @strCompany +'%'') AND ' SET @strCMDOR = @strCMDOR + '((b.COMPANY IS NULL) OR (b.COMPANY = '''')) AND ' END --Put the entire string together so that it can be executed. 1=1 is for the extra AND that will be at the end of the next condition --since we dont know when it will stop adding conditions SET @strCMDAND = @strCMDAND + '1=1) OR ('+@strCMDOR+'1=1) AND (a.RECTYPE = ''P'') AND (a.CONTACT = ''PROPOSAL'')' --the type will always be a job EXEC(@strCMDAND) GO
help extracting user info   (297 Views)
please can anyone help me i have a ipb forum some how i accidently delete my post i have a old backup from 3 week ago how can i extract the post from the backup NOTE: the only post that was deleted was post made by me none of the other member post was deleted i did not want to restore the entire post table beacause i would loose post in the past 3 weeks can anyone help me with a script or anything i can used to extract post of the post table where the poster id is 1 here is a sample INSERT INTO `ibf_posts` VALUES (2, 0, NULL, 1, 'albertldee', 1, 1, '' INSERT INTO `ibf_posts` VALUES (3, 0, NULL, 4, ' - ', 1, 1, ' INSERT INTO `ibf_posts` VALUES (4, 0, NULL, 6, 'demarcus', 1, 1, ' INSERT INTO `ibf_posts` VALUES (5, 0, NULL, 7, 'gottasteu', 1, 1, ' the post table has about 122000 line like above i just want to extract all the line that has albertldee like line number 1
Create Parameters DataType help   (162 Views)
Hi I am creating a Parameter for my Command Object. In my database, the field is of type ntext with a length of 16. When I use the .CreateParameter method (I am using the shortcut text here..), what is the DataTypeEnum for it Is it adVarChar 16 or what .Parameters.Append .CreateParameter("@strComments", adVarChar, adParamInput, 16) sanctos
select statement help   (258 Views)
I have a select query statement that is not producing the results i want. I am joining a table (B) to another table (A). Table A will always have one record, but table B may have 0 or more records. If table B has at least one record, one of them will be flaged in a column (bDefault = 1) and the rest (bDefault = 0). What i need to do is select from table A and if there are records in table B get the one with bDefault=1. This has proven to be beyond my skills. Below is the code I have tried so far. What happens when i run it is this: table B has three records for one member (6) and just one for another (3). the querry returns three rows for 6 and one for 3. How ever many records there are in table B for a given member is how many rows are returned by the query. I don't want that. i want to return only one row per member and if they have a row in table b, return only the row from table B where bDefault = 1 and if they don't have a row in table B, to ignore it. Here is the first code I am tried: SELECT P.nMID, P.nAge, (SELECT MI.sURLPath + 't_' + convert(varchar(10),MI.nID) + '.jpg' WHERE bDefault = 1 AND MI.nMID = P.nMID) as sURLPath FROM tblMember_profile P Left Outer JOIN tblMember_Images MI ON MI.nMID = p.nMID WHERE P.nMID IN ('7','6') ORDER BY P.nMID the 2nd querry here produced the same result: SELECT P.nMID, P.nAge, (SELECT MI.sURLPath + 't_' + convert(varchar(10),MI.nID) + '.jpg' WHERE bDefault = 1 AND MI.nMID = P.nMID) as sURLPath FROM tblMember_profile P JOIN tblMember_Images MI ON MI.nMID = p.nMID WHERE P.nMID IN ('7','6') ORDER BY P.nMID and this is the result: 6 22 NULL 6 22 /images/MemImages/t_36.jpg 6 22 NULL 7 27 /images/MemImages/t_51.jpg I tried this too: SELECT P.nMID, P.nAge, (MI.sURLPath + 't_' + convert(varchar(10),MI.nID) + '.jpg') as sURLPath FROM tblMember_profile P JOIN tblMember_Images MI ON MI.nMID = P.nMID WHERE MI.bDefault = 1 AND P.nMID IN ('6','7') ORDER BY P.nMID and it gave me the results i wanted except that if someone does not have a record in table 2, it does not display them at all. it looks like this: 6 22 /images/MemImages/t_36.jpg 7 27 /images/MemImages/t_51.jpg I know this is long and drawn out, but could someone please make a suggestion on how to deal with this
Urgent! Please help. Can't insert!   (241 Views)
I am running SQL2000 on W2k server. Everything was working fine untill sometime yesterday. in a sudden, I was not able to insert into my database table. I can do select, and I can even run sp_columns on the table, but when I try to INSERT, I get the following error ... Server: Msg 208, Level 16, State 3, Line 4 Invalid object name [tablename]. The insert statment should be working fine as they were run before the problem occurs! Has anyone ran into this before Any input is appreciated!
Need help to export data to EXcel   (428 Views)
iam developed a module. but i want to take data backup every week related to that module. for that i want to generate one button. by clicking that button i want to import tat data in to EXCEL file. any one can help me and save me from tis problem.
Hi members help   (277 Views)
Code: DECLARE @val VARCHAR(12) declare @p tinyint declare @s tinyint SET @val = '918.56JOA' set @p=len(@val)-1 set @s=len(@val) - charindex('.',@val)-3 select @p as p,@s as s select len(@val)-1 precisions select len(@val) - charindex('.',@val)-3 scale SELECT CONVERT(DECIMAL(@p,@s), LEFT(@val,6)) s iam trying to use this code to automatically get the precision and scale from the value but i get the following error please hep Error name Server: Msg 170, Level 15, State 1, Line 15 Line 15: Incorrect syntax near '@p'.
Joins and Query...please help   (338 Views)
I am trying to figure out how to Create a join on two tables and then query that join... Join SELECT * FROM DatabaseA INNER JOIN DatabaseB ON DatabaseA.Field3 = DatabaseB.Field7 THen once I have the join do I need to run another query pointing to the join SELECT * from DatabaseA.Field3 WHERE Field9 = "435" I am just a bit confused on the the process and what to do in SQL server. I am trying to access tha data on the fly in a 3rd party app....but running SQL statements... I guess qwhat I am asking is do I write two queries Connected to the DB, Join, Query the Join Or is there antoher way. Any thoughts woudl be very appreciated. THanks
MS Access to MS SQL - help   (153 Views)
, I am about to convert an MS Access database to MS SQL 2005, and I just wondered what equivalents should I use for each field type as I keep seeing mixed opinions and Im not sure at all . If anyone could let me know what these should be in MS SQL 2005 : - ID Autonumber (primary key in ms access) (No Null / No duplicates) - Date field (No Null) - Number field (to hold large numbers) (No Null) - Text field (up to 255 chars) (Nulls allowed) - Memo field (unlimited chars) (Nulls allowed) Your help would be so much appreciated Thankyou, Gia
Problem in runnin asp under sql server (2005)windows authentication.plz help!!   (353 Views)
i want to run asp under sqlserver 2005 windows authentication .for this i have written the connection string as:- set connadmin = Server.CreateObject("ADODB.Connection") strpath = Request.ServerVariables("APPL_PHYSICAL_PATH") connstr = "Driver={SQL Server}; Server=PERSONAL-AF5588\SQLEXPRESS;Database=cwcom;Trusted_Connectio n=Yes;" 'connstr="Provider=SQLOLEDB;Trusted_Connection=Yes ;Initial Catalog=cwcom;Data Source=PERSONAL-AF5588\SQLEXPRESS;" connadmin.cursorlocation = 3 But the erre is coming as Cannot open database "cwcom" requested by the login. The login failed. plz help me
Need help to write the query   (272 Views)
I need help to write a query. The details of which is given below employee table Empid EmpName EcatID ProjectID 1200 Rama 5 2 1201 Shiva 5 2 1202 arab 6 44 1207 Kumar 3 28 1208 Despande 6 2 1210 Divya 3 50 1230 praveen 1 44 1232 Raghu 1 28 1247 sumitra 1 2 1266 Manoj 1 39 1274 Siddu 2 39 Empskill Table EmpID CourseID SelfRating Evalrating 1201 10 4 - 1202 11 8 7 1202 100 8 - 1200 12 7 7 1207 99 10 9 1207 100 10 8 1207 101 8 8 1207 102 5 5 1207 103 6 4 1207 104 4 4 1274 104 8 8 Result Needed: Empid PMName No Of Subordinates TotalNoofSubjsratedbysubords TotalNoOfEvalRating 1230 praveen 1 2 1 1232 Raghu 1 6 6 1247 sumitra 2 2 1 1266 Manoj 1 1 1 EcatID =1 means he is a Project Manager a PM has a ProjectID, his sub-ordinates have the same ProjectID (thats how you locate them)
Query help needed   (181 Views)
I've got 3 tables. Contact1 (accountno, company, contact,...) Contact2 (accountno, lastconton, nextaction, meetdateon,...) ContHist (accountno, userid, notes,...) I want to query the company, contact, last contact date, next action, date for next meeting, the userid who last spoke to the person,and the relevant notes they added when speaking to the contact. So, I have this: Code: select distinct,, ch.userid , c2.lastconton, c2.nextaction , isnull(c2.meetdateon, c2.callbackon) as NextConDate , right(replace(replace(replace(cast(ch.notes as varchar(7000)), char(10), ' '), char(13), ''), char(9), ' '), 300) as Notes from contact1 c1 inner join contact2 c2 on c1.accountno = c2.accountno inner join conthist ch on c1.accountno = ch.accountno where ( c2.nextaction like '%Conceptual Pitch%' or c2.nextaction like '%Appointment set%' ) and ( c2.callbackon between dateadd(dd, -7, getdate()) and dateadd(dd, 30, getdate()) or c2.meetdateon between dateadd(dd, -7, getdate()) and dateadd(dd, 30, getdate()) ) order by This does ALMOST everything I want it to do... except for this one pesky fact: Every time a user adds notes to a contact history, it adds a new row to the ContHist table. I get (for example) 6 rows for "ACME Rockets" in this query since my user talked to ACME Rockets and made notes regarding this contact 6 times. Now, ContHist has a "lastdate" column in datetime format, but I cannot figure out how to only query against the most recent ContHist record for my 3 table join. How do I limit this query so I only get one record for ACME Rockets based upon the most recent "lastdate" field in ContHist
Simple SQL statment help required!!   (340 Views)
Please can someone tell me how I add a order by statment into the following SQL query Code: SQL_query = "SELECT * FROM players WHERE age = " & request.querystring("age") Cheers, Dan
Please help with setting up for OR statement queries   (422 Views)
, I have a problem with creating a simple querry that will look at multiple areas in my database and bring a result. Here's what I want: (search term) --> looks under the "products" table and retrives items based on the search term. Needs to look under SKU, manufacturer, title and description - all contained in the "products" table. Here's what I've got: Code: sQuery = "SELECT * FROM products WHERE keywords = ' '" If keywords "" Then 'Split up keywords into individual words keywordArray = Split(keywords, " ") 'Check to see if the keywords describe a SKU Dim i For i=0 to UBound(keywordArray) sQuery = sQuery & "SKU LIKE '" & keywordArray(i) & "'" sQuery = sQuery & " OR " Next 'Next check in the title For i=0 to UBound(keywordArray) sQuery = sQuery & "title LIKE '" & keywordArray(i) & "'" sQuery = sQuery & " OR " Next 'Finally, check the description For i=0 to UBound(keywordArray) sQuery = sQuery & "description LIKE '" & keywordArray(i) & "'" If i UBound(keywordArray) Then sQuery = sQuery & " OR " Next End If If manufacturer "" Then If keywords "" Then sQuery = sQuery & " AND " sQuery = sQuery & "manufacturer = '" & manufacturer & "'" End If sQuery = sQuery & ";" %> Keywords = " " Manufacturer = " " Query = " " No Products Found       Where are my faults Otherwise, can someone post a query system that will do what I need
Model Database!   (214 Views)
We detached the model database to move it to another drive, but now we cannot start the SQL Server Service. Can anyone out there help us
query help   (312 Views)
, I have the following table ClientID | Address | City | State __________________________________ 1 | something | Blah | WV 2 | someblah | Pitt | PA 2 | blahblah | Pitt | PA 3 | other | Mtow | SC 3 | someother | Mtow | SC 3 | somexxxx | Mtow | SC 3 | otherblah | Blah | WV i want the output as : ClientID | Address | City | State __________________________________ 1 | Blah, WV | Blah | WV 2 | Pitt, PA | Pitt | PA 2 | Pitt, PA2 | Pitt | PA 3 | Mtow, SC | Mtow | SC 3 | Mtow, SC2 | Mtow | SC 3 | Mtow, SC3 | Mtow | SC 3 | blah, WV | Blah | WV any suggestions ...