Return status through ODBC

I'm tring to get an ASP page to excute a transaction and return a status.
The SQL statment excutes correcly but I can't figure out how to return a value to VB through the ODBC driver.

I could live withe a pass/fail status but would like to return a more informative result status.

Any ideas?

Harold

Here's the SQL:
DECLARE @FROMBATCH varchar(20)
DECLARE @TOBATCH varchar(20)
DECLARE @SOPNUMBE varchar(20)
DECLARE @COUNT integer
DECLARE @SANE integer
SET @FROMBATCH='<FROMBATCH>'
SET @TOBATCH='<TOBATCH>'
SET @SOPNUMBE='<ORDER>'
SET @SANE=0
SET @COUNT=0
PRINT @SANE + ' ' + @COUNT
--Verify From Batch
set @COUNT=(SELECT COUNT(*) FROM [SY00500] WHERE [SERIES] = 3 AND [ORIGIN] = 1 AND [BCHSOURC]='Sales Entry' AND [APPROVL] = 0 AND BACHNUMB=@FROMBATCH)
PRINT @SANE + ' ' + @COUNT
if @COUNT <> 1 BEGIN
    SELECT "BAD FROM BATCH"
    RETURN
  END
SET @SANE=@SANE+@COUNT
--Verify To Batch
SET @COUNT=(SELECT COUNT(*) FROM [SY00500] WHERE [SERIES] = 3 AND [ORIGIN] = 1 AND [BCHSOURC]='Sales Entry' AND [APPROVL] = 0 AND BACHNUMB=@TOBATCH)
PRINT @SANE + ' ' + @COUNT
if @COUNT <> 1 BEGIN
    SELECT "BAD TO BATCH"
    RETURN
  END
SET @SANE=@SANE+@COUNT
--Verify Order
SET @COUNT=(SELECT COUNT(*) FROM [SOP10100] WHERE BACHNUMB=@FROMBATCH AND [SOPNUMBE]=@SOPNUMBE)
PRINT @SANE + ' ' + @COUNT
if @COUNT <> 1 BEGIN
    SELECT "BAD ORDER"
    RETURN
  END
SET @SANE=@SANE+@COUNT
IF @SANE = 3 BEGIN
  PRINT 'BEGIN'
  DECLARE @SoTotal Money
  DECLARE @DexRowId Integer
  BEGIN TRAN
  SET NOCOUNT OFF
  --Lock Transaction
  SET @SANE=-4
  SET @DexRowId = (SELECT [DEX_ROW_ID] FROM [SOP10100] WHERE BACHNUMB=@FROMBATCH AND [SOPNUMBE]=@SOPNUMBE)
  IF (@@ERROR <> 0 )GOTO QUITWITHROLLBACK

  SET @SANE=-5
  INSERT tempdb.dbo.DEX_LOCK  ([session_id], [row_id], [table_path_name])  VALUES ('9999',@DexRowId,'TWO.dbo.SOP10100')
  IF (@@ERROR <> 0) GOTO QUITWITHROLLBACK

  SET @SANE=-6
  SET @SoTotal  = (SELECT [ACCTAMNT]   FROM [SOP10100] WHERE BACHNUMB=@FROMBATCH AND [SOPNUMBE]=@SOPNUMBE)
  IF (@@ERROR <> 0) GOTO QUITWITHROLLBACK

  SET @SANE=-7
  UPDATE [SY00500] SET [NUMOFTRX]=[NUMOFTRX] - 1, [BCHTOTAL]=[BCHTOTAL] - @SoTotal
  ,[CNTRLTRX]=[CNTRLTRX] -1, [CNTRLTOT]=[CNTRLTOT] - @SoTotal
  WHERE [SERIES] = 3 AND [ORIGIN] = 1 AND [BCHSOURC]='Sales Entry' AND [APPROVL] = 0 AND BACHNUMB=@FROMBATCH
  IF (@@ERROR <> 0) GOTO QUITWITHROLLBACK

  SET @SANE=-8
  UPDATE [SY00500] SET [NUMOFTRX]=[NUMOFTRX] + 1 ,[BCHTOTAL]=[BCHTOTAL] + @SoTotal
  ,[CNTRLTRX]=[CNTRLTRX] + 1, [CNTRLTOT]=[CNTRLTOT] + @SoTotal
   WHERE [SERIES] = 3 AND [ORIGIN] = 1 AND [BCHSOURC]='Sales Entry' AND [APPROVL] = 0 AND BACHNUMB=@TOBATCH
  IF (@@ERROR <> 0) GOTO QUITWITHROLLBACK

  SET @SANE=-9
  UPDATE [SOP10100] SET [BACHNUMB] =@TOBATCH WHERE BACHNUMB=@FROMBATCH AND [SOPNUMBE]=@SOPNUMBE
  IF (@@ERROR <> 0) GOTO QUITWITHROLLBACK

  --Release Transaction
  DELETE FROM tempdb.dbo.DEX_LOCK WHERE row_id = @DexRowId AND table_path_name = 'TWO.dbo.SOP10100'
  COMMIT TRAN
  PRINT 'COMMIT'
  SELECT 0
  RETURN
QUITWITHROLLBACK:
  ROLLBACK TRAN
  SELECT @SANE
  RETURN
END
**************
And the ASP script: in this case I get 'Operation is not allowed when the object is closed' on the objRec.Eof

  SQL=Replace(SQL,"[TWO]", DBASE )
  SQL=Replace(SQL,"<FROMBATCH>", FromBatch )
  SQL=Replace(SQL,"<TOBATCH>", ToBatch )
  SQL=Replace(SQL,"<ORDER>", Order )
  Set objRec = CreateObject("ADODB.RecordSet")
  on error resume next
  objRec.Open SQL, DSNstring
  if Not objRec.Eof then
    Response.Write "<br>E=" & objRec.Fields(0).value & "<br>"
    objRec.close
  end if
  if err <> 0 Then
    Response.Write "<br>" & err.Description & "<br>"
  End If
LVL 3
kinseyAsked:
Who is Participating?
 
MichaelSFullerConnect With a Mentor Commented:
You need a connection object. Alter your code to reflect the following. Also make sure you have the DSN created on the web server, and your ODBC drivers match those on your development or test server.

'declare variables
Dim objConn

'Instanciate
Set objConn = Server.CreateObject("ADODB.Connection")

'set attribute of entity
objConn.ConnectionString = "DSNstring"

'Open a connection
objConn.Open

'open the recordset passing it the open connection
objRec.Open strSQL, objConn

0
 
kinseyAuthor Commented:
I have no problems excuting the SQL via opjCon.execute() or objRec.open().
The problem is getting the SQL to return a value when it is anything other then a SELECT.
I run SELECT queries, UPDATEs, and INSERTs, etc.  all the time, mostly with objRec.Open().
 I just can't figure out how to get somthing back when the SQL is a script rather than a single statment.
0
 
kinseyAuthor Commented:
I'll give MichaelSFuller the points.
 At least he reponded.

Turns out you just can't do what I was trying to do.  I wound up using an ADODB.Connection and having to split up the SQL statment into several seperate call to objConn.excute sandwiched between objConn.beginTran and objConn.commit, testing the result after each objConn.execute.

It would be a lot cleaner execute one SQL statement and return one result. Then the VB code would be more general and reusable.
0
 
MichaelSFullerCommented:
kinsey-

Sorry I missed the question, I was looking at it wrong. So now that I see what your looking for allow me to answer it correctly. My question is why couldn't you wrap it in a stored procedure with an output param and several input params? I would create one command object, one output parameter object, and as many as needed input parameter objects and then execute the procedure. Then display those result to via the response.write() method.

Is this what you are looking for?

Mike
0
 
kinseyAuthor Commented:
Stored procedures would work, but I'm not comfortable generating them in the database.
The database is from a commercial application and there are support issues if you start monkeying around with there tables and stored procedures.

I suppose I could put a stored procedure in tempdb or something and replace it on the fly. Or maybe a stored procedure that could take an SQL statement as a parameter? Is that possible? I.E. a stored procedure I could pass the entire statement above to?

 I would like an option that would allow the program to work with programmatically generated SQL .

Harold
0
All Courses

From novice to tech pro — start learning today.