After transferring to MySQL, date fieldvalues now show null

[janetopps]

I have a news website, with asp pages, which was on Access, and i upgraded to MySQL, i used Bullzip to transfer the data. It had about 1000 pages, which im now able to pull up on the public side. Im sorting out a few glitches though.

Since i upgraded from ms access database to MySQL, i have added about 4 articles to test the new setup. I note some fields aren't being added in the new mySql database for the new 4 records. When i ran the MySQK query analyser and checked to see what was in the database fields.. the "fldCreated" shows NULL, when it should be showing a date. In the query analyser, the dates BEFORE the new mysql database show:

nm_tbl_news n;
2008-10-15 08:28:31

but in the few articles i added after the transfer it doesn't have a date, it says NULL, which means the code adding the date to the database must be wrong.


I'm including some code from the newsadd.asp page, which is the page that is actually adding the news to the database and also some code from the include files on this page , which may be relevant


inc_api.asp
[code]

FUNCTION RFC822(dDate,iOffset)
    Dim d
    d = DateAdd("h",-iOffset,dDate)
    RFC822 = Left(WeekDayName(WeekDay(d)),3) & ", " & Right(String(2,"0") & Day(d),2) & " " & Left(MonthName(Month(d)),3) & " " & Year(d) & " " & Right(String(2,"0") & Hour(d),2) & ":" & Right(String(2,"0") & Minute(d),2) & ":" & Right(String(2,"0") & Second(d),2) & " " & "GMT"
END FUNCTION    

config.asp

<% Dim MyConn, APPLICATION_URL, arrLEVEL, SAFE_EXTENSIONS_ONLY, KEEP_LOGS, DB_TO_USE, ALL_ARTICLES_PAGE_SIZE, SET_EMAIL_COMP, SHOW_AUTHOR
   Dim SEARCH_METHOD, SEARCH_IN, SEARCH_MODE, SEARCH_FOR
 
   Session.LCID = 1033
 
 
 
        ' Set to 1 if you are using MS Access, set to 2 if you are using MS SQL database, 3 for MySQL
        DB_TO_USE = 3
 
 
    ' Assemble date value
    FUNCTION ASEMBLE_DATE_FORMAT(sDAY, sMONTH, sYEAR)
        Dim DATE_FORMAT, sDATE                   
 
        DATE_FORMAT = 1 ' US Locale
 
        SELECT CASE DATE_FORMAT
            CASE 1
                sDATE = sMONTH & "/" & sDAY  & "/" & sYEAR
            CASE ELSE
                sDATE = sMONTH & "/" & sDAY  & "/" & sYEAR
        END SELECT            
        ASEMBLE_DATE_FORMAT = sDATE
    END FUNCTION
 
 
 
    <% '// NO NEED TO MODIFY ANYTHING BELOW 
FUNCTION CNT_DATE(sDATE)
  IF IsDate(sDATE) = True THEN
 
    SELECT CASE DATE_FORMAT
        CASE 1        'YYYYMMDD
            CNT_DATE = Year(sDATE) & Right(Cstr(Month(sDATE) + 100),2) & Right(Cstr(Day(sDATE) + 100),2)
        CASE 2        'YYYY-MM-DD
            CNT_DATE = Year(sDATE) & "-" & Right(Cstr(Month(sDATE) + 100),2) & "-" & Right(Cstr(Day(sDATE) + 100),2)
        CASE 101    'mm/dd/yy 
            CNT_DATE = Right(Cstr(Month(sDATE) + 100),2) & "/" & Right(Cstr(Day(sDATE) + 100),2) & "/" & Right(Cstr(Year(sDATE)),2)
        CASE 102    'yy.mm.dd
            CNT_DATE = Right(Cstr(Year(sDATE)),2) & "." & Right(Cstr(Month(sDATE) + 100),2) & "." & Right(Cstr(Day(sDATE) + 100),2)
        CASE 103    'dd/mm/yy
            CNT_DATE = Right(Cstr(Day(sDATE) + 100),2) & "/" & Right(Cstr(Month(sDATE) + 100),2) & "/" & Right(Cstr(Year(sDATE)),2)
        CASE 104    'dd.mm.yy
            CNT_DATE = Right(Cstr(Day(sDATE) + 100),2) & "." & Right(Cstr(Month(sDATE) + 100),2) & "." & Right(Cstr(Year(sDATE)),2)
        CASE 105    'dd-mm-yy
            CNT_DATE = Right(Cstr(Day(sDATE) + 100),2) & "-" & Right(Cstr(Month(sDATE) + 100),2) & "-" & Right(Cstr(Year(sDATE)),2)
        CASE 110    'mm-dd-yy
            CNT_DATE = Right(Cstr(Month(sDATE) + 100),2) & "-" & Right(Cstr(Day(sDATE) + 100),2) & "-" & Right(Cstr(Year(sDATE)),2)
        CASE 111    'yy/mm/dd
            CNT_DATE = Right(Cstr(Year(sDATE)),2) & "/" & Right(Cstr(Month(sDATE) + 100),2) & "/" & Right(Cstr(Day(sDATE) + 100),2)    
        CASE 112    'yymmdd
            CNT_DATE = Right(Cstr(Year(sDATE)),2) & Right(Cstr(Month(sDATE) + 100),2) & Right(Cstr(Day(sDATE) + 100),2)
    END SELECT    
  ELSE
      CNT_DATE = Null
  END IF
END FUNCTION
%>    

and this is the page that adds the news to the database, ive tried to only include relevant code, (or that which i think is relevant) as it is a long page, .

newsadd.asp

<!--#include file="inc_api.asp"-->
<!--#include file="config.asp"-->
 
    <%Dim MODE, MESSAGE, CATE_STR, SQL, RS, CATE, AGENT, ACTIVE, ALLOW_C, ALLOW_V, TITLE, SUMMARY, IMAGE, CONTENT, I, _
          arrCATE, ID, sDATE, eDATE, TID, TEMPLATE, A_C, A_V, APPROVE, HLITE, AGENT_STR, USE_VIEW
 
    MODE = Request.Form("mode")
    TID  = Trim(Request.QueryString("TID"))
 
 
    Call CREATE_SECURITY()    
    IF NOT Trim(arrLEVEL(27)) = "1" THEN MESSAGE = "<li />Sorry, you don't have access to this section."    
 
    '
    '<description> add new article to the DB </description>
    '
    IF MODE = "add" THEN
 
        With Request
            arrCATE     = Split(APO(.Form("cate")),",")
            AGENT    = APO(.Form("agent"))
            ACTIVE   = CONVERT_NUM(.Form("active"))
            ALLOW_C  = CONVERT_NUM(.Form("allow_c"))
            ALLOW_V  = CONVERT_NUM(.Form("allow_v"))
            TITLE    = APO(.Form("title"))
            SUMMARY  = APO(STRIP_CODE(.Form("summary")))
            USE_VIEW = APO(.Form("USE_VIEW"))
            IMAGE    = APO(.Form("image"))            
            CONTENT  = APO_LAX(.Form("content"))            
            IF DB_TO_USE = 3 THEN
                sDATE = .Form("sY") & "-" & .Form("sM") & "-" & .Form("sD")    
                eDATE = .Form("eY") & "-" & .Form("eM") & "-" & .Form("eD")    
            ELSE
                sDATE    = ASEMBLE_DATE_FORMAT(.Form("sD"),.Form("sM"),.Form("sY"))
                eDATE    = ASEMBLE_DATE_FORMAT(.Form("eD"),.Form("eM"),.Form("eY"))  
            END IF  
            HLITE    = CONVERT_NUM(.Form("hlite"))
        End With
 
        Call CHECK_INP()        
 
        IF MESSAGE = "" THEN            
            '
            '<description> add article </description>
            '               
                     ELSE
                SQL = "INSERT INTO nm_tbl_news (fldTITLE, fldCONTENT, fldSUMMARY, fldACTIVE, fldAID, fldPOSTED, fldEXPIRES, fldIMAGE, fldALLOW_COMMENTS, fldALLOW_VOTING, fldHIGHLIGHT, fldUSE_VIEW) VALUES ('" & TITLE & "','" & CONTENT & "','" & SUMMARY & "'," & ACTIVE & "," & AGENT & ",'" & sDATE & "','" & eDATE & "','" & IMAGE & "'," & ALLOW_C & "," & ALLOW_V & "," & HLITE & ", " & USE_VIEW & ")"
            END IF                
            Call OPEN_DB()
 
            MyConn.Execute SQL
 
            SQL = "SELECT ID FROM nm_tbl_news WHERE fldTITLE='" & TITLE & "' AND fldAID = " & AGENT & " ORDER BY ID DESC"
            Set RS = Server.CreateObject("ADODB.Recordset")
            RS.Open SQL, MyConn    
                 IF NOT RS.EOF THEN
                     ID = trim(RS("ID"))
                 END IF
            RS.Close 
 
            '
            '<description> add category association </description>
            '              
            FOR I = 0 to Ubound(arrCATE)
                IF IS_VALID_ID(Trim(arrCATE(I))) = True THEN
                    SQL = "INSERT INTO nm_tbl_news_cate (fldNEWS_ID, fldCATE_ID) VALUES (" & ID & "," & Trim(arrCATE(I)) & ")"
                    MyConn.Execute SQL
                END IF    
            NEXT 
 
            ' { Append New Log Lin } 
            Call APPEND_LOG(False, "Added new article ( " & TITLE & " ).", Session("ID"))            
 
            MyConn.close 
            Set MyConn = Nothing                                    
 
            Response.Redirect "news.asp"
            Response.End
        END IF
 
    ELSE
 
        Call OPEN_DB()
        '
        '<description> Create the category drop down </description>
        '        
 
        IF DB_TO_USE = 3 THEN
            SQL = "SELECT ID, fldNAME, ID as ID1, 0 as ID2, 0 as ID3 FROM nm_tbl_cate WHERE fldLEVEL = 1 UNION SELECT nm_tbl_cate.ID, CONCAT(PARENT.fldNAME , ' > ' , nm_tbl_cate.fldNAME) AS NAME, PARENT.id, nm_tbl_cate.ID, 0 FROM nm_tbl_cate, nm_tbl_cate AS PARENT WHERE (nm_tbl_cate.fldLEVEL = 2) AND (nm_tbl_cate.fldPID =PARENT.ID) UNION SELECT nm_tbl_cate.ID, CONCAT(PARENT.fldNAME , ' > ' , SUBPARENT.fldNAME , ' > ' , nm_tbl_cate.fldNAME) AS NAME, PARENT.id, SUBPARENT.id, nm_tbl_cate.ID FROM nm_tbl_cate, nm_tbl_cate AS PARENT, nm_tbl_cate AS SUBPARENT  WHERE (nm_tbl_cate.fldLEVEL = 3) AND (nm_tbl_cate.fldPID =PARENT.ID) AND (nm_tbl_cate.fldSID =SUBPARENT.ID) ORDER BY `fldName` ASC"
        ELSE        
                    END IF            
        Set RS = Server.CreateObject("ADODB.Recordset")
        RS.LockType   = 1
        RS.CursorType = 0
 
 
        RS.Open SQL, MyConn    
             WHILE NOT RS.EOF 
                CATE_STR = CATE_STR & "<option value='" & trim(RS("ID")) & "'>" & trim(RS("fldNAME")) & vbcrlf
                RS.MoveNext
             WEND
        RS.Close 
        '
 
                ELSE
                    IF Session("ID") = trim(RS("ID")) THEN
                        AGENT_STR = AGENT_STR & "<option selected='selected' style='background-color: #ffffea' value='" & trim(RS("ID")) & "'>" & trim(RS("fldNAME")) & vbcrlf
                    END IF                
                END IF
                RS.MoveNext
             WEND
        RS.Close
 
        IF IS_VALID_ID(TID) THEN 
 
            SQL = "SELECT fldTEMPLATE FROM nm_tbl_template WHERE ID = " & TID
            Set RS = Server.CreateObject("ADODB.Recordset")
            RS.LockType   = 1
            RS.CursorType = 0
            RS.Open SQL, MyConn    
                 IF NOT RS.EOF THEN
                     TEMPLATE = trim(RS("fldTEMPLATE"))
                 END IF
            RS.Close     
 
        END IF           
 
        SQL = "SELECT fldA_V, fldA_C, fldAPPROVE FROM nm_tbl_settings WHERE ID = 1"
        Set RS = Server.CreateObject("ADODB.Recordset")
        RS.LockType   = 1
        RS.CursorType = 0
        RS.Open SQL, MyConn    
             IF NOT RS.EOF THEN
                A_C     = Trim(RS("fldA_C"))
                A_V     = Trim(RS("fldA_V"))
                APPROVE = Trim(RS("fldAPPROVE"))
             END IF
        RS.Close 
 
        Set RS = Nothing        
        MyConn.Close
        Set MyConn = Nothing
 
    END IF    
 
 
    %>
 
 
 
    <table width="100%" align="center" cellpadding="2" cellspacing="0" border="0">
        <tr>
 
        </tr>
    </table>
    <br />
    <table width="100%" align="center" cellpadding="2" cellspacing="0" border="1" bordercolor="whitesmoke" style="border-collapse: collapse;">
        <tr bgcolor="whitesmoke">
            <td class="MainTitle">Add New Article</td>
            <td class="MainTitle" align="right">< <a href="templates.asp">Apply Template</a> ></td>
        </tr>
        <tr>
            <td colspan="2">
                <br />       
 
                <form action="newsadd.asp" method="post" name="frm">
                <table cellpadding="2" cellspacing="0" border="0" align="center" width="622">
                    <tr>
                        <td>Article&nbsp;By:</td>
                        <td align="right"><select name="agent" class="textbox" style="width: 450px;"><%= AGENT_STR %></select></td>
                    </tr>
                    <tr>
 
                                                               <td>Publish Date:</td>
                                        <td><select name="sM" id="sM"><%FOR I = 1 TO 12%><option<%If Trim(I) = Trim(Month(Date)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td>
                                        <td>/</td>
                                        <td><select name="sD" id="sD"><%FOR I = 1 TO 31%><option<%If Trim(I) = Trim(day(Date)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td>
                                        <td>/</td>
                                        <td><select name="sY" id="sY"><%FOR I = 2001 TO 2030%><option<%If Trim(I) = Trim(Year(Date)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td>
                                        <td></td>
                                    </tr></table>        
                                </td>
                                <td>
                                    <table align="right" cellpadding="2" cellspacing="0" border="0"><tr>
                                        <td>Expires Date:</td>
                                        <td><select name="eM" id="eM"><%FOR I = 1 TO 12%><option<%If Trim(I) = Trim(Month(Date)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td>
                                        <td>/</td>
                                        <td id="eD"><select name="eD" id="eD"><%FOR I = 1 TO 31%><option<%If Trim(I) = Trim(Day(Date)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td>
                                        <td>/</td>
                                        <td><select name="eY" id="eY"><%FOR I = 2001 TO 2030%><option<%If Trim(I) = Trim(Year(Date+365)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td>
                                        <td></td>
                                    </tr></table>        
                                </td>
                            </tr></table>
 
                        </td>                    
                    </tr>                    
 
                        </td>
                    </tr><tr>
                        <td colspan="2" align="right"><br /><input type="Submit" value="Add Article" onClick="return ValidateNewsArticle()" /><br /></td>
                    </tr></table>                                
                <input type="Hidden" name="mode" value="add" />
                </form>
                <br /><br />
            </td>
        </tr>
    </table>    
 
<!--#include file="inc_footer.asp"-->

I'd appreciate it if anyone could show me what to change and where. I'm not html literate, and realise the code above is probably not written in the best way, but im just concerned with getting it to function.

Much appreciated and thanks in advance

[Nicodemas]

On line 47 of your news_add.asp, there is no reference to a "fldCreated" in your SQL query. That means no value would be input to your table when this script fires off.

I have modified your file with important changes. See lines 18 - 23.

I created a new variable 'CREATE_DATE'.

The code on line 21 formats this date as "MM/DD/YYY"

Line 22 is just so I can get around modifying other files and thus complicating this for you.

Line 23 formats the date for MySQL insertion.

Skip to line 53 now. It now includes fldCreated.

Try this and let me know if it works.

<!--#include file="inc_api.asp"--> 
<!--#include file="config.asp"--> 
 
    <%Dim MODE, MESSAGE, CATE_STR, SQL, RS, CATE, AGENT, ACTIVE, ALLOW_C, ALLOW_V, TITLE, SUMMARY, IMAGE, CONTENT, I, _ 
          arrCATE, ID, sDATE, eDATE, TID, TEMPLATE, A_C, A_V, APPROVE, HLITE, AGENT_STR, USE_VIEW 
 
    MODE = Request.Form("mode") 
    TID  = Trim(Request.QueryString("TID")) 
 
 
    Call CREATE_SECURITY()     
    IF NOT Trim(arrLEVEL(27)) = "1" THEN MESSAGE = "<li />Sorry, you don't have access to this section."     
 
    ' 
    '<description> add new article to the DB </description> 
    ' 
    IF MODE = "add" THEN 
        ''// added by nicodemas
        twoDay      = day(date) :   if twoDay < 10 then twoDay = "0" & twoDay
        twoMonth    = month(date) : if twoMonth < 10 then twoMonth = "0" & twoMonth
        CREATE_DATE = ASEMBLE_DATE_FORMAT(twoDay,twoMonth,year(date)) 
        DATE_FORMAT = 2
        CREATE_DATE = CNT_DATE(CREATE_DATE)
 
        With Request 
            arrCATE     = Split(APO(.Form("cate")),",") 
            AGENT    = APO(.Form("agent")) 
            ACTIVE   = CONVERT_NUM(.Form("active")) 
            ALLOW_C  = CONVERT_NUM(.Form("allow_c")) 
            ALLOW_V  = CONVERT_NUM(.Form("allow_v")) 
            TITLE    = APO(.Form("title")) 
            SUMMARY  = APO(STRIP_CODE(.Form("summary"))) 
            USE_VIEW = APO(.Form("USE_VIEW")) 
            IMAGE    = APO(.Form("image"))             
            CONTENT  = APO_LAX(.Form("content"))             
            IF DB_TO_USE = 3 THEN 
                sDATE = .Form("sY") & "-" & .Form("sM") & "-" & .Form("sD")     
                eDATE = .Form("eY") & "-" & .Form("eM") & "-" & .Form("eD")     
            ELSE 
                sDATE    = ASEMBLE_DATE_FORMAT(.Form("sD"),.Form("sM"),.Form("sY")) 
                eDATE    = ASEMBLE_DATE_FORMAT(.Form("eD"),.Form("eM"),.Form("eY"))   
            END IF   
            HLITE    = CONVERT_NUM(.Form("hlite")) 
        End With 
 
        Call CHECK_INP()         
 
        IF MESSAGE = "" THEN             
            ' 
            '<description> add article </description> 
            '                
                     ELSE 
                SQL = "INSERT INTO nm_tbl_news (fldCreated, fldTITLE, fldCONTENT, fldSUMMARY, fldACTIVE, fldAID, fldPOSTED, fldEXPIRES, fldIMAGE, fldALLOW_COMMENTS, fldALLOW_VOTING, fldHIGHLIGHT, fldUSE_VIEW) VALUES ('"& CREATE_DATE &"', '" & TITLE & "','" & CONTENT & "','" & SUMMARY & "'," & ACTIVE & "," & AGENT & ",'" & sDATE & "','" & eDATE & "','" & IMAGE & "'," & ALLOW_C & "," & ALLOW_V & "," & HLITE & ", " & USE_VIEW & ")" 
            END IF                 
            Call OPEN_DB() 
 
            MyConn.Execute SQL 
 
            SQL = "SELECT ID FROM nm_tbl_news WHERE fldTITLE='" & TITLE & "' AND fldAID = " & AGENT & " ORDER BY ID DESC" 
            Set RS = Server.CreateObject("ADODB.Recordset") 
            RS.Open SQL, MyConn     
                 IF NOT RS.EOF THEN 
                     ID = trim(RS("ID")) 
                 END IF 
            RS.Close  
 
            ' 
            '<description> add category association </description> 
            '               
            FOR I = 0 to Ubound(arrCATE) 
                IF IS_VALID_ID(Trim(arrCATE(I))) = True THEN 
                    SQL = "INSERT INTO nm_tbl_news_cate (fldNEWS_ID, fldCATE_ID) VALUES (" & ID & "," & Trim(arrCATE(I)) & ")" 
                    MyConn.Execute SQL 
                END IF     
            NEXT  
 
            ' { Append New Log Lin }  
            Call APPEND_LOG(False, "Added new article ( " & TITLE & " ).", Session("ID"))             
 
            MyConn.close  
            Set MyConn = Nothing                                     
 
            Response.Redirect "news.asp" 
            Response.End 
        END IF 
 
    ELSE 
 
        Call OPEN_DB() 
        ' 
        '<description> Create the category drop down </description> 
        '         
 
        IF DB_TO_USE = 3 THEN 
            SQL = "SELECT ID, fldNAME, ID as ID1, 0 as ID2, 0 as ID3 FROM nm_tbl_cate WHERE fldLEVEL = 1 UNION SELECT nm_tbl_cate.ID, CONCAT(PARENT.fldNAME , ' > ' , nm_tbl_cate.fldNAME) AS NAME, PARENT.id, nm_tbl_cate.ID, 0 FROM nm_tbl_cate, nm_tbl_cate AS PARENT WHERE (nm_tbl_cate.fldLEVEL = 2) AND (nm_tbl_cate.fldPID =PARENT.ID) UNION SELECT nm_tbl_cate.ID, CONCAT(PARENT.fldNAME , ' > ' , SUBPARENT.fldNAME , ' > ' , nm_tbl_cate.fldNAME) AS NAME, PARENT.id, SUBPARENT.id, nm_tbl_cate.ID FROM nm_tbl_cate, nm_tbl_cate AS PARENT, nm_tbl_cate AS SUBPARENT  WHERE (nm_tbl_cate.fldLEVEL = 3) AND (nm_tbl_cate.fldPID =PARENT.ID) AND (nm_tbl_cate.fldSID =SUBPARENT.ID) ORDER BY `fldName` ASC" 
        ELSE         
                    END IF             
        Set RS = Server.CreateObject("ADODB.Recordset") 
        RS.LockType   = 1 
        RS.CursorType = 0 
 
 
        RS.Open SQL, MyConn     
             WHILE NOT RS.EOF  
                CATE_STR = CATE_STR & "<option value='" & trim(RS("ID")) & "'>" & trim(RS("fldNAME")) & vbcrlf 
                RS.MoveNext 
             WEND 
        RS.Close  
        ' 
 
                ELSE 
                    IF Session("ID") = trim(RS("ID")) THEN 
                        AGENT_STR = AGENT_STR & "<option selected='selected' style='background-color: #ffffea' value='" & trim(RS("ID")) & "'>" & trim(RS("fldNAME")) & vbcrlf 
                    END IF                 
                END IF 
                RS.MoveNext 
             WEND 
        RS.Close 
 
        IF IS_VALID_ID(TID) THEN  
 
            SQL = "SELECT fldTEMPLATE FROM nm_tbl_template WHERE ID = " & TID 
            Set RS = Server.CreateObject("ADODB.Recordset") 
            RS.LockType   = 1 
            RS.CursorType = 0 
            RS.Open SQL, MyConn     
                 IF NOT RS.EOF THEN 
                     TEMPLATE = trim(RS("fldTEMPLATE")) 
                 END IF 
            RS.Close      
 
        END IF            
 
        SQL = "SELECT fldA_V, fldA_C, fldAPPROVE FROM nm_tbl_settings WHERE ID = 1" 
        Set RS = Server.CreateObject("ADODB.Recordset") 
        RS.LockType   = 1 
        RS.CursorType = 0 
        RS.Open SQL, MyConn     
             IF NOT RS.EOF THEN 
                A_C     = Trim(RS("fldA_C")) 
                A_V     = Trim(RS("fldA_V")) 
                APPROVE = Trim(RS("fldAPPROVE")) 
             END IF 
        RS.Close  
 
        Set RS = Nothing         
        MyConn.Close 
        Set MyConn = Nothing 
 
    END IF     
 
 
    %> 
 
 
 
    <table width="100%" align="center" cellpadding="2" cellspacing="0" border="0"> 
        <tr> 
 
        </tr> 
    </table> 
    <br /> 
    <table width="100%" align="center" cellpadding="2" cellspacing="0" border="1" bordercolor="whitesmoke" style="border-collapse: collapse;"> 
        <tr bgcolor="whitesmoke"> 
            <td class="MainTitle">Add New Article</td> 
            <td class="MainTitle" align="right">< <a href="templates.asp">Apply Template</a> ></td> 
        </tr> 
        <tr> 
            <td colspan="2"> 
                <br />        
 
                <form action="newsadd.asp" method="post" name="frm"> 
                <table cellpadding="2" cellspacing="0" border="0" align="center" width="622"> 
                    <tr> 
                        <td>Article&nbsp;By:</td> 
                        <td align="right"><select name="agent" class="textbox" style="width: 450px;"><%= AGENT_STR %></select></td> 
                    </tr> 
                    <tr> 
 
                                                               <td>Publish Date:</td> 
                                        <td><select name="sM" id="sM"><%FOR I = 1 TO 12%><option<%If Trim(I) = Trim(Month(Date)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td> 
                                        <td>/</td> 
                                        <td><select name="sD" id="sD"><%FOR I = 1 TO 31%><option<%If Trim(I) = Trim(day(Date)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td> 
                                        <td>/</td> 
                                        <td><select name="sY" id="sY"><%FOR I = 2001 TO 2030%><option<%If Trim(I) = Trim(Year(Date)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td> 
                                        <td></td> 
                                    </tr></table>         
                                </td> 
                                <td> 
                                    <table align="right" cellpadding="2" cellspacing="0" border="0"><tr> 
                                        <td>Expires Date:</td> 
                                        <td><select name="eM" id="eM"><%FOR I = 1 TO 12%><option<%If Trim(I) = Trim(Month(Date)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td> 
                                        <td>/</td> 
                                        <td id="eD"><select name="eD" id="eD"><%FOR I = 1 TO 31%><option<%If Trim(I) = Trim(Day(Date)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td> 
                                        <td>/</td> 
                                        <td><select name="eY" id="eY"><%FOR I = 2001 TO 2030%><option<%If Trim(I) = Trim(Year(Date+365)) Then %> selected<%End If%> value="<%=I%>"><%=I%><%NEXT%></select></td> 
                                        <td></td> 
                                    </tr></table>         
                                </td> 
                            </tr></table> 
 
                        </td>                     
                    </tr>                     
 
                        </td> 
                    </tr><tr> 
                        <td colspan="2" align="right"><br /><input type="Submit" value="Add Article" onClick="return ValidateNewsArticle()" /><br /></td> 
                    </tr></table>                                 
                <input type="Hidden" name="mode" value="add" /> 
                </form> 
                <br /><br /> 
            </td> 
        </tr> 
    </table>     
 
<!--#include file="inc_footer.asp"--> 

[janetopps]

Thanks so much, N

I tried the code and when i tried adding the article i got this error message. Remember before i could add the article , but there was no date in the fldCreated showing in the Mysql database


Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'twoDay'

/Admin/ne_add.asp, line 19

and the code on line 19 is

With Request

Anyhow i think it important to mention something, this script was set to run on different databases, 1 for Access, 2 for MS sql and 3 for MySQL

It was working fine on Access. But i didn't show you the part for the Access database
You said " fldCreated" was missing. But it was also missing from the code used for Access, and it worked fine then. Let me show you, the complete code for that part, as i only showed you the MySQL one,

 '<description> add article </description>
            '               
            IF DB_TO_USE = 1 THEN
                SQL = "INSERT INTO nm_tbl_news (fldTITLE, fldCONTENT, fldSUMMARY, fldACTIVE, fldAID, fldPOSTED, fldEXPIRES, fldIMAGE, fldALLOW_COMMENTS, fldALLOW_VOTING, fldHIGHLIGHT, fldUSE_VIEW) VALUES ('" & TITLE & "','" & CONTENT & "','" & SUMMARY & "'," & ACTIVE & "," & AGENT & ",#" & sDATE & "#,#" & eDATE & "#,'" & IMAGE & "'," & ALLOW_C & "," & ALLOW_V & "," & HLITE & ", " & USE_VIEW & ")"
            ELSE
                SQL = "INSERT INTO nm_tbl_news (fldTITLE, fldCONTENT, fldSUMMARY, fldACTIVE, fldAID, fldPOSTED, fldEXPIRES, fldIMAGE, fldALLOW_COMMENTS, fldALLOW_VOTING, fldHIGHLIGHT, fldUSE_VIEW) VALUES ('" & TITLE & "','" & CONTENT & "','" & SUMMARY & "'," & ACTIVE & "," & AGENT & ",'" & sDATE & "','" & eDATE & "','" & IMAGE & "'," & ALLOW_C & "," & ALLOW_V & "," & HLITE & ", " & USE_VIEW & ")"
 
            END IF 

As you can see, fldCreated was missing from the Access one too. But the data that i transferred using Bullzip, shows the fldCreated field with dates in. The only ones showing "null" are the 4 articles i added since ive had MySQL

Anyway, using the code above, it didn't add the article to the database, but returned the error above.

I don't think this is related, but when i transferred the data, i couldn't transfer using Bullzip, without leaving out the "default" values, whatever those are. When i unchecked that box, i could transfer the data. I don't think that has anything to do with this, but just in case, i thought i'd let you know.


an update: I just checked my access database, and it didn't have a "fldCreated" column, so i'm confused now. Did the transfer create this extra column or what?

Many thanks

[Nicodemas]

to solve the variable undefined errors, just create Dim statements for all teh variables I added to the top of the page, underneath the include statements.
See if that clears up the issue