Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    21

    Question Unanswered: ASP using stored procedure MS SQL Server 2008

    <%
    dim objParam, objCmd
    Set cmd = Server.CreateObject("ADODB.Command")

    Set cmd.ActiveConnection = sysdb
    objCmd.CommandText = "test2"
    objCmd.CommandType = adCmdStoredProc
    objCmd.ActiveConnection = 4

    Set objParam = objCmd.CreateParameter ("@count_authors",adinteger,adParamOutput,4)
    objCmd.Parameters.Append objParam
    ObjCmd.Execute
    %>

    Can anybody see anything wrong with this block of code or show me a very basic example of how to select and display information from SQL server 2008 using asp.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It helps if you describe what's going wrong.

    Right off the bat I see you're using named constants, and if you're using classic ASP that means you need to have an include somewhere that translates those constants into longs.

    Second, you're referencing sysdb but haven't included any code that shows you creating a new connection.

    We can't help you any further without you telling us what's actually happening, on what line, in what circumstances.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2009
    Posts
    21
    I have the connection string in a include which is being called. Basically what I'm trying to do is use a stored procedure to get information from a server side database(SQL server 2008). I'm new to asp so I'm not sure why this isn't working. I need to get 4 pieces of information to come up and have it loop. The company name, image, web site, and desc. Right now I'm just trying to get the unique field to show up on my page below. I am very new to asp so even somebody could give me an idea how to do that. It would be great I tried a lot of code from sites and it doesn't seem to be working.

  4. #4
    Join Date
    Sep 2009
    Posts
    21
    Ok, nvm we are going to do it another way not using stored procedures for now.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You should stick with sprocs.
    Code:
    <%
    
    Dim rs
    
    Set rs = Server.CreateObject("ADODB.Recordset")
    
    Set rs = ObjCmd.Execute
    
    While Not rs.EOF
      Response.Write(rs.Fields("name_of_first_field")
      Response.Write(rs.Fields("name_of_first_field")
    
      rs.MoveNext
    Loop
    Please note that this is air-code (i.e. written from memory and un-tested.
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2009
    Posts
    21
    We are having trouble on the stored procedure side when ever we make a select statement the return values are showing but we get an error in sql 2008, when we execute(actually run it to see if it works) the stored procedure, which says that the stored procedure couldn't be found although all of the fields we are looking for are coming up and also so is another field which says return_value = @return_value. Pretty new to this stuff so I'm not sure what that means either way it does say the procedure (dbo)(test2), which is my stored procedure on that database, couldn't be found even though it was executed and the fields information is coming up.
    Last edited by bingo_barns; 09-10-09 at 08:40.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    the sproc is in the dbo schema?
    You should specify that in your command text
    Code:
    objCmd.CommandText = "test2"
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2009
    Posts
    21
    Oh ya I did the problem is with whatever we are doing in the actual stored procedure not the asp code.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    $20 says you're not fully qualifying your sproc (as in your initial post), you haven't granted permission to execute the sproc to the user you're connecting as, or you're not connecting to the correct database.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Sep 2009
    Posts
    21
    I have information from the database coming out using straight asp code instead of a sproc the connection is definitely going to the right database.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    straight asp code?

    When the database tells you it can't find a stored procedure, that means it can't find the stored procedure. Fairly straight-forward.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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