Link to home
Start Free TrialLog in
Avatar of Saroj13
Saroj13

asked on

How to update record in the database using stored procedure classic asp, ado ?

Hi,

I need to write this code in global.asa. GetLastDateandTime is working fine. I need to put that in the session. If user is present then we get the data from the database. This function is working fine. If user is not present then we are using update function. My update function is not working. Page is down. I am not sure how to fix the update function.

Sub S[b]ession_OnStart[/b]

Dim objConn, objRs, strConn

Set objRs = Server.CreateObject("ADODB.Recordset")

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

             

strConn = "test;"

 

objConn.Open strConn

dim strUser

strUser = request.servervariables("logon_user")

dim strDateAndTime = Now()       

If (strUser <> "" ) then

    Session("LastDateTime") = GetLastDateandTime(strUser, objRs,objConn)

else
[b]
   UpdateLastDateAndTime(strUser, strDateAndTime, objRs,objConn);[/b]

End If

 

Set objRs = Nothing

Set objConn = Nothing

End If

 

End Sub

 

 

Function GetLastDateandTime( ByVal strUser, ByRef objRs, ByRef objConn)

'--------------------------------------------------------------------------

'-------------------------------------------------------------------------

    Dim cmd

             

       Set cmd = Server.CreateObject("ADODB.Command")                            

       Set cmd.ActiveConnection = objConn

 

       cmd.CommandText = "usp_GetLastDateandTime"

       cmd.CommandType = adCmdStoredProc

 

    cmd.Parameters("@User") = strUser

 

       Set objRs = cmd.Execute

    

 

   'You can now access the record set

    if (not objRs.EOF) THEN

        LastDateTime = objRs("LastDateTime")

    end if

 

       GetLastDateandTime = LastDateTime               '// Return array

      

       'dispose your objects

    objRs.Close

    SET objRs = Nothing

 

    objConn.Close

    SET objConn = Nothing

End Function

 

 

F[b]unction UpdateLastDateAndTime(ByVal strUser, ByVal strDateAndTime,  ByRef objRs, ByRef objConn)

 

Dim cmd      

 

       Set cmd = Server.CreateObject("ADODB.Command")

       'Set up command

       cmd.ActiveConnection = objConn                                             

cmd.CommandText = "usp_UpdateLastDateAndTime"

       cmd.CommandType = adCmdStoredProc

       cmd.Parameters.Append cmd.CreateParameter("@DateAndTime", adLongVarWChar,adParamInput, 50,strDateAndTime )

       cmd.Parameters.Append cmd.CreateParameter("@User",adLongVarWChar,adParamInput ,50 ,strUser ) 

       cmd.Execute               

      

      

       objRs.Close

    SET objRs = Nothing

 

    objConn.Close

    SET objConn = Nothing

    SET cmd = Nothing

             

End Function
[/b]

****************
.asp page
<% Response.Write(Session("LastVisitedDateTime")) %>

Open in new window

Avatar of Scott Fell
Scott Fell
Flag of United States of America image

https://www.experts-exchange.com/questions/29074225/Launch-stored-procedure-SQL-Server-from-classic-asp-VB-page.html
https://www.experts-exchange.com/questions/28234393/vbscript-getting-a-recordset-back-from-a-stored-procedure.html
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
   ' Set up DB connection to use, set the type of SQL command
   	.ActiveConnection = connection_string
   	.CommandType = adCmdStoredProc
   	.CommandText = "stored_proc_name" '
   
   	.Parameters.Append .CreateParameter("@1",adVarWChar, adParamInput, 30)
   	.Parameters("@1") = variable
   

   set rs = .Execute
End With

  result= rs(0)

set cmd = nothing
set rs = nothing

Open in new window

This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.