ASP using stored procedure MS SQL Server 2008

bingo_barns · #1 (**permalink**) 09-08-09, 19:40

<%
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.

Teddy · #2 (**permalink**) 09-08-09, 23:42

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.

bingo_barns · #3 (**permalink**) 09-09-09, 06:25

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.

bingo_barns · #4 (**permalink**) 09-09-09, 12:45

Ok, nvm we are going to do it another way not using stored procedures for now.

gvee · #5 (**permalink**) 09-10-09, 04:35

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.

bingo_barns · #6 (**permalink**) 09-10-09, 07:35

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.

gvee · #7 (**permalink**) 09-10-09, 08:33

the sproc is in the dbo schema?
You should specify that in your command text

Code:

objCmd.CommandText = "test2"

bingo_barns · #8 (**permalink**) 09-10-09, 08:36

Oh ya I did the problem is with whatever we are doing in the actual stored procedure not the asp code.

Teddy · #9 (**permalink**) 09-10-09, 08:59

$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.

bingo_barns · #10 (**permalink**) 09-10-09, 09: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.

Teddy · #11 (**permalink**) 09-10-09, 10:14

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech