MS Access vs MS SQL 2005

To lessen the IF statements in ASP after connection has already come back, can some of these decisions be made right in the SQL database?  I am asking as you can convert IF statements to CASE WHEN ... THEN ... ELSE ... END logic in SQL.

http://technet.microsoft.com/en-us/library/ms181765.aspx

4 Guys From Rolla (always cool for ASP tutorials): http://www.4guysfromrolla.com/webtech/102704-1.shtml

Thanks for your feedback.

I have tried adding SET DATEFORMAT DMY, but my whole INSERT Query below wouldn't work. Nothing is added to the database. When I remove - ExpDate, PublishedDate, -  '" & expDate & "," & pubDate & "- all other data is inserted into a table. See the my changes to the code below:

      SET DATEFORMAT DMY
      
      if IsDate(objRecordSet("ExpDate")) then
                  expDate = "'" &  objRecordSet("ExpDate") & "'"
            else
                  expDate = "null"
            end if
      
            if IsDate(objRecordSet("PublishedDate")) then
                  pubDate = "'" & objRecordSet("PublishedDate") & "'"
            else
                  pubDate = "null"
            end if
            
      set dbConn = server.createobject("ADODB.Connection")
      dbConn.Open "Driver={SQL Native Client};Server=sqldatabase;Database=db_13;Uid=db_13_dbo;Pwd=password;"

      dbConn.Execute("Insert Into Files (Title, SectionId, UserId, Description, Comments, CheckOut, MajorVersion, MinorVersion, URL, DLCId, CreateDate, ExpDate, PublishedDate, Message, BaseFileId, PreviousFileId, CheckOutDate, CurrentFile, CreatorId, LastPopBy, intLinks, extLinks, iLinkCount, eLinkCount) values ('" & Replace(objRecordSet("Title"), "'", "''") & "'," & objRecordSet("SectionId") & "," &  uId & ",'" & objRecordSet("Description") & "','" & cmt & "',1," & ver & ",0,'-', 1 ,GetDate()," & expDate & "," & pubDate & ",'" & Replace(objRecordSet("Message"),"'","''") &  "'," & bFileId & "," & objRecordSet("FileId") & ",GetDate(), 1," & objRecordSet("CreatorId") & "," & objRecordSet("LastPopBy") & ",'" &  replace(objRecordSet("intLinks"), "'", "") & "','" & replace(objRecordSet("extLinks") , "'", "") & "'," & objRecordSet("iLinkCount") & "," & objRecordSet("eLinkCount") & ")")


In regards to SELECT CASE replacing IF-ELSE statement, I am not sure that this is an issue. I think its more to do with the connection to the database. In MS Access you can open the connection only once and it will stay open until you add dbConn.Close, Set dbConn = Nothing for example. This doesnt seem to be the case with SQL. With every query (and there are quite a few of them on one page) I need to open up the database connection otherwise it wouldnt work at all.

The last query on my  page has the connection open but it doesnt execute:
fid = Request("fid")
BaseFileId = " & objRecordSet("BaseFileId") and FileId = " & objRecordSet("FileId")

set dbConnNew = server.createobject("ADODB.Connection")
dbConnNew.Open "Driver={SQL Native Client};Server=sqldatabase;Database=db_13;Uid=db_13_dbo;Pwd=password;"


        Const adOpenStatic = 3
        Const adLockOptimistic = 3
        Const adUseClient = 3      
            
             set objNewFile = Server.CreateObject("Adodb.RecordSet")
            objNewFile.CursorLocation = adUseClient
        objNewFile.open "Select Max(FileId) as File from Files where BaseFileId = " & fid, dbConn, adOpenStatic, adLockOptimistic
            'set objNewFile = dbConnNew.Execute("Select Max(FileId) as File from Files where BaseFileId = " & fid), dbConnNew, adOpenStatic, adLockOptimistic)

                  if not objNewFile.eof then
                        newF = objNewFile("File")
                  end if

            set maxVer = dbConn.Execute("Select MaxNoOfVer from AppConfig")
            if not maxVer.Eof then
                  verAllowed = maxVer("MaxNoOfVer")
                  set objVerCnt = dbConn.Execute(verExist)
                  if objVerCnt(0) > verAllowed and verAllowed > 1 then
                        dbConn.Execute(delVerQry)
                  end if
                  objVerCnt.Close
            End If
            maxVer.Close
            
            dbConn.CommitTrans

Your date strings must be formatted either in the "reverse" US format (old style) or the ISO format (new style and preferred in SQL Server 2008).
So:
            if IsDate(objRecordSet("ExpDate")) then
                  expDate = "'" &  Format(objRecordSet("ExpDate"), "m\/d\yyyy") & "'"
            else
                  expDate = "null"
            end if

or:
            if IsDate(objRecordSet("ExpDate")) then
                  expDate = "'" &  Format(objRecordSet("ExpDate"), "yyyy\/mm\/dd") & "'"
            else
                  expDate = "null"
            end if

/gustav

Also note that SQL literal string values are single quoted.  Try sending in date wrapped with # to represent date to see if it helps.  My link to angelIII solution was regarding the date format so you can see that as added detail to cactus suggestion.

expDate = "#" &  objRecordSet("ExpDate") & "#"

You would still want to format the date to format as indicated, then have "#" on either side.  I just copied what you had above an example of what I meant.

You are mixing up things here. #-marks are for Access/JET only. In SQL Server, date strings are in single quotes:

  '8/18/2008'

So - given the functions above - this:

.. GetDate()," & expDate & "," & pubDate & ",'" & ..

should not be altered as the single quotes have been applied.

/gustav

You are right cactus_data.  I hate going back and forth in syntax, it drives me nuts sometimes.  And I haven't had enough coffee as usual. :)  You keep helping.  I will just cheer on from the sidelines...

It's not easy with these dates - and if you use ADO FindFirst in Access, did you know that it will fail if not the ISO format is used? Quite a surprise!

The trend is the ISO format. And now, study the SQL Server 2008 documentation, it is used everywhere.

/gustav

Thanks guys, much appreciated!

The following:  expDate = "'" &  Format(objRecordSet("ExpDate"), "m\/d\yyyy") & "'"

will display month first: m\/d\yyyy, shouldnt I use d\/m\yyyy instead? But how do I add hours, minutes and seconds, would this be d\/m\yyyy hh:mm:ss?

Also, do you have any idea why my set dbConnNew = server.createobject("ADODB.Connection") wouldnt connect in the last query?

MSSQL stores value differently than we read them (YYYY/MM/DD) when inserting you neet to convert the date going into SQL as a Date and when selecting you need to convert it back:

SELECT CONVERT(VARCHAR(10), ExpDate, 103) AS [DD/MM/YYYY]

I have tried all of this, but still have the problems with the code I've posted above. I have now tried so many different things and I feel I am now back to square one. ;o(

SET DATEFORMAT DMY - wouldn't help
SELECT CONVERT(VARCHAR(10), ExpDate, 103) AS [DD/MM/YYYY] - is not helping either

Could it be because my SQL query above:

dbConn.Execute("Insert Into Files (Title, SectionId, UserId, Description, Comments, CheckOut, MajorVersion, MinorVersion, URL, DLCId, CreateDate, ExpDate, PublishedDate, Message, BaseFileId, PreviousFileId, CheckOutDate, CurrentFile, CreatorId, LastPopBy, intLinks, extLinks, iLinkCount, eLinkCount) values ('" & Replace(objRecordSet("Title"), "'", "''") & "'," & objRecordSet("SectionId") & "," &  uId & ",'" & objRecordSet("Description") & "','" & cmt & "',1," & ver & ",0,'-', 1 ,GetDate()," & expDate & "," & pubDate & ",'" & Replace(objRecordSet("Message"),"'","''") &  "'," & bFileId & "," & objRecordSet("FileId") & ",GetDate(), 1," & objRecordSet("CreatorId") & "," & objRecordSet("LastPopBy") & ",'" &  replace(objRecordSet("intLinks"), "'", "") & "','" & replace(objRecordSet("extLinks") , "'", "") & "'," & objRecordSet("iLinkCount") & "," & objRecordSet("eLinkCount") & ")")

Can I use "GetDate()" and Replace(objRecordSet("Message"),"'","''")  in MSSQL query? If not, what else can I do?

Also, I still have problem rewriting this to Case Statement, can someone please help:

if not objRecordSet.eof then
      if objRecordSet("BaseFileId") = 0 then
            qry  = "Select Max(MajorVersion) from Files where FileId = " & fid & " or BaseFileId = " & fid
            verQry = "Select Count(*) As versionCount from Files where (FileId = " & fid & " or BaseFileId = " & fid & ") and MajorVersion = " & ver
            delVerQry = "Delete From Files Where (FileId = " & fid & " or BaseFileId = " & fid & ") and MajorVersion = (Select Min(MajorVersion) from Files where FileId = " & fid & " or BaseFileId = " & fid &  ")"
            verExist = "Select Count(*) As versionCount from Files where FileId = " & fid & " or BaseFileId = " & fid
      else
            qry  = "Select Max(MajorVersion) from Files where FileId = " & fid & " or BaseFileId = " & objRecordSet("BaseFileId")
            verQry = "Select Count(*) As versionCount from Files where ( FileId = " & fid & " or BaseFileId = " & objRecordSet("BaseFileId") & ") and MajorVersion = " & ver
            delVerQry = "Delete From Files Where (FileId = " & fid & " or BaseFileId = " & objRecordSet("BaseFileId") & "or (FileId = " & objRecordSet("BaseFileId") & " and  BaseFileId = 0)) and MajorVersion = (Select Min(MajorVersion) from Files where FileId = " & fid & " or BaseFileId = " & objRecordSet("BaseFileId") & " or (FileId = " & objRecordSet("BaseFileId") & " and  BaseFileId = 0))"
            verExist = "Select Count(*) As versionCount from Files where  FileId = " & fid & " or BaseFileId = " & objRecordSet("BaseFileId")
      end if

Ahh still didn't solve this problem, but will have to close this question.

Gustav, I will partially accept your solution as it give me some pointers at least.

expDate = "'" &  Format(objRecordSet("ExpDate"), "m\/d\yyyy hh\:nn\:ss AM/PM") & "'"

Thanks.