1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

ASP and SQL Database error message: ADODB.Recordset error '800a0bb9' Arguments

Discussion in 'C#' started by MarkDownie, Aug 2, 2010.

  1. #1
    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.
     
    MarkDownie, Aug 2, 2010 IP
  2. camjohnson95

    camjohnson95 Active Member

    Messages:
    737
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    60
    #2
    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):
     
    camjohnson95, Aug 3, 2010 IP
  3. MarkDownie

    MarkDownie Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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.
     
    MarkDownie, Aug 4, 2010 IP
  4. camjohnson95

    camjohnson95 Active Member

    Messages:
    737
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    60
    #4
    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.
     
    Last edited: Aug 4, 2010
    camjohnson95, Aug 4, 2010 IP
  5. MarkDownie

    MarkDownie Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Hi camjohnson95,

    Again; thanks for that. Your code resolved this issue.

    Thanks again.
     
    MarkDownie, Aug 5, 2010 IP