Hi, I have recently moved an existing Access Database in to a SQL database as part of a work project. Unfortunately, being new to SQL databases in relation to ASP I have run in to a problem: We have an ASP application that previously connected to the Access database and worked fine. 1) I have manage to set up a the new connection to the SQL database using the following code and all appears fine: <% Dim con Set con = Server.CreateObject("ADODB.Connection") con.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = ***; User Id = ***; Password=***" %> 2) The issue arrives with the following lines of code. It is the last line that produces the error (the code worked previosly with the Access Database, but I am not sure how to get it to work with the SQL Database): <% Dim objRS, bolFound, strUsername Set objRS = Server.CreateObject("ADODB.Recordset") objRS.ActiveConnection = con objRS.Open "EmaiList", objConn, , , adCmdTable The error message received is: ADODB.Recordset error '800a0bb9' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. /dental_attachments/dental_attachments/EditUserAttachment.asp, line 60 Any advice would be greatly appreciated.. Thanks.
you have set the ActiveConnection to con yet use objConn for your active connection when executing the command. I don't believe it is necessary to set the ActiveConnection property at all. So you could try removing this line, and changing objConn to con. Another thing, your table name is "EmaiList", is this supposed to be "EmailList" ? *EDIT* Also I don't see you define adCmdTable ... I'm not sure if ASP has this predefined but to be safe just change it to 2 ... so: objRS.Open "EmailList", con, , , 2 Code (markup):
Hi camjohnson95, That is great; I removed the line you suggested and changed the other line to "objRS.Open "EmailList", con, , , 2". On top of this I changed some of the permissions on the SQL database (right clicked on the database > Properties > Permissions > selected the applicable user and granted the permissions for Connect, Delete, Execute, Select, Insert, Delete and Update). And that resolved the "ASP and SQL Database error message: ADODB.Recordset error '800a0bb9' Arguments" Thanks for your advice. Changing the lines you suggested also resolved some issues further on in the code. I noticed the mistake in the EmaiList / Emailist name; thanks. I am now receiving the message "ADODB.Recordset error '800a0cb3' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype. " The code that is causing the issue is below and it is the last line that is causing the issue. I'm not sure if this is just because it is code used to update an access database and should actually be replaced with SQL statments or if it is another issue: I have commented out the 3rd line (highlighted green) from the original code and added in the 4th line (highlighted red) as this resolved any previous issues. However, in this case it has not. Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") 'objRS.Open "EmailList", objConn, , adLockOptimistic, adCmdTable objRS.Open "EmailList", con, , , 2 Dim reg, reg1 reg1=Date reg = DateAdd("d", 94, Date) objRS("DateEntered") = (Reg1) Again; thanks to camjohns95 for resolving the first issue. If anyone has any advice regarding this issue, I would greatly appreciate it.
Okay so the syntax of the (recordset).open method is as follows: ObjRs.Open <source>, <connection>, <cursortype>, <locktype>, <commandtype> Code (markup): I generally use a cursortype of 1 (adOpenKeyset) and locktype of 3 (adLockOptimistic). Which I have never had problems with. This is as follows: objRS.Open "EmailList", con, 1, 3, 2 Code (markup): This should resolve your issue. The default CursorType (adOpenForwardOnly), does not allow the updating of records.