insert null into date fields

**holly** · Jun 19th, 2008, 06:18 AM

Hi I need help with the below code as no matter wot I try it keeps inserting
the default #01/01/1900#

Code:

  If Len(QuoteReq_char) = 0 Then
          SQL = SQL & ", '" & Null & "'"
   Else
        SQL = SQL & ", CONVERT(datetime,'" & Format(QuoteReq_char, "dd mmmm yyyy") & "', 103)"
    End If
    SQL = SQL & "," & TargetPrem_money
    SQL = SQL & ",'" & MakeStrSQLAcceptable(TargetInsurer_char) & "'"
    If Len(ToCarrier_char) = 0 Then
         SQL = SQL & ", '" & Null & "'"
    Else
        SQL = SQL & ", CONVERT(datetime,'" & Format(ToCarrier_char, "dd mmmm yyyy") & "', 103)"
    End If

thanks

the code is set in an insert stmt to sql server 2000...I have not posted the
whol routine as its big but if need to I will

**si_the_geek** · Jun 19th, 2008, 06:29 AM

Is there a reason you haven't checked what SQL is being created?

If you don't know how, see the article How can I find out why my SQL statement isn't working? from our Database Development FAQs/Tutorials (at the top of this forum)

Check (and think about) what this produces:

Code:

         SQL = SQL & ", '" & Null & "'"

..there are two big problems in that.

Also note that the way you Format the date values means that your code will only work under certain circumstances (where the database server understands English month names). For a way which works no matter what the settings are (on your PC, or in the database system), see the FAQ article How do I use values (numbers, strings, dates) in SQL statements?

**holly** · Jun 19th, 2008, 06:46 AM

Hi Si,

Code:

Insert into tabQMS_Quotes(AC_No, Status, Contact_No, Risk_No, RiskName_char, Renewal_date, Recieved_date, 
QuoteReference_char, DevExec_Char, ProductCode_char, ProductType_char, BrkComm_per, BDComm_per, 
QuotedPremium_cur, Currency_char, InceptionDate_date, PolicyEndDate_date, PolicyNo_char, Handler_char, 
QuoteHandler_Char, Terms_Note, RiskTransfer_char, creditTerms_char, Handling_Office, QuoteLogID_int, 
QABQuoteID_int, QMSQuoteID_Int, Live_Bit, LastLoggedDateTime_Date, FeedbackStatus_char, Feedback_note, 
QuoteRequired_date, TargetPremium_cur, TargetInsurer_char, ToCarrier_date, CarrierQuote_date, 
QuoteToBroker_date, InsurerPlaced_char, Carrier_char, CarrierSource_char, PremiumPlaced_cur) VALUES 
(89604,'Quote',559,1576,'STEVEN COX T/A GCR ENVIRONMENTAL', CONVERT(datetime,'17 June 2008', 103), 
CONVERT(datetime,'17 June 2008', 103),'BDQ141385', 'Simon Whitmarsh','ZC01','Manufacturing and Distribution',
'20','8',630.24,'GBP', CONVERT(datetime,'17/06/2008 00:00:00',103), CONVERT(datetime,'16/06/2009 00:00:00', 103)
,'BDQ141385','TONY SUCCAMORE','TONY SUCCAMORE','','YES','30 Days','BD',2843,1385,1777,-1,
 CONVERT(datetime,'19/06/2008 11:31:54',103),'','', '',0,'', '', CONVERT(datetime,'', 103), 
CONVERT(datetime,'', 103),'','Zurich','',0) SELECT @@IDENTITY

Here is the stmt that sql has created and as you can see this stmt

Code:

CONVERT(datetime,'', 103),

still puts 01/01/1900 into my code

thanks for your help its greatly appreciated!!

**si_the_geek** · Jun 19th, 2008, 07:01 AM

Why are you putting that in, rather than Null (which is very different to '' that you originally had)?

**holly** · Jun 19th, 2008, 07:13 AM

I know it sounds hard to beleive but when I debug the SQL stmt thats
what is created within the SQl string!!!

**si_the_geek** · Jun 19th, 2008, 07:35 AM

What code are you currently using?

What is the value of the apt variables (eg: QuoteReq_char) when it runs, and what is their data type?

**holly** · Jun 19th, 2008, 08:13 AM

The code is MSAccess 97 frontend and the backend is sqlserver2000.

the 'QuoteReq_char' variable is a string in the rountine and a datetime in sql.

Thanks

**FunkyDexter** · Jun 19th, 2008, 01:10 PM

I believe the problem is that a null in your code equates to blank, but that's not the same as a null in sql server. This:-

Code:

CONVERT(datetime,'', 103)

will get converted to the base date by sql server and that's 01/01/1900. Instead of passing in a blank you want to pass in an actual null. Change this:-

Code:

SQL = SQL & ", '" & Null & "'"

to this:-

Code:

SQL = SQL & ", Null"

That way SQL server will be process a null rather than a blank and will insert a null into the field. Do that for any other bit of code that's trying to put a null into the field too.

I am concerned that this:-

Code:

SQL = SQL & ", '" & Null & "'"

should give you a string of:-

Code:

,''

but you seem to be saying it's actually giving you:-

Code:

CONVERT(datetime,'', 103)

and I cant see where the Convert is coming from. Am I missing something?

**holly** · Jun 20th, 2008, 06:57 AM

Hi FunkyDexter,
thanks for replying..I have not tried you solution yet but I thought it may
be beneficial if I posted the whole routine...

Code:

Private Function CreateQMSRecord(rs As ADODB.Recordset) As Boolean
    Dim rsSet As ADODB.Recordset
    Dim rsScheme As ADODB.Recordset
    Dim rsRisk As ADODB.Recordset
    Dim RiskID_int As Long
    Dim CustID_int As Long
    Dim lngCountKey As Long
    Dim SQL_str As String
    Dim DevExec As String, BName As String
    Dim RenewalDate As String
    Dim ReceivedDate As String
    Dim Logged_datetime As String
    Dim ProductCode_char As String
    Dim Carrier_char As String
    Dim BrkComm_per As Integer
    Dim BDComm_per As Integer
    Dim NewQHandler As String
    Dim QmsQuoteID As Long
    Dim Feedback_char As String
    Dim Feedback_note As String
    Dim QuoteReq_char As String
    Dim TargetPrem_money As Single
    Dim TargetInsurer_char As String
    Dim ToCarrier_char As String
    Dim CarrierQuote_char As String
    Dim ToBroker_char As String
    Dim GrossIncIPT_money As Single
    Dim InsurerPlaced_char As String
    Dim ProductType_char As String
    'Dim Carrier_char As String
    Dim Source_char As String
    Dim Currency_char As String
    Dim PremPlaced_money As Single
    'Dim BDEQuotes()
    Dim RSNewQuoteID As ADODB.Recordset
    
    Set RSNewQuoteID = New ADODB.Recordset
    SQL_str = "SELECT Max([QMSQuoteID_int]) AS maxlogID from tabqms_quotes"
    RSNewQuoteID.Open SQL_str, gBass, adOpenDynamic, adLockPessimistic
    If Not RSNewQuoteID.BOF Then
        QmsQuoteID = RSNewQuoteID!maxlogID + 1
        Set RSNewQuoteID = Nothing
    End If
    
    'NewQHandler = rs!CompletedBy_Varchar
    Logged_datetime = rs!Logged_datetime
    
    Set rsSet = New ADODB.Recordset 'create new object
    SQL = "Insert into tabQMS_Quotes(AC_No, Status, Contact_No, Risk_No, RiskName_char, Renewal_date, Recieved_date, "
    SQL = SQL & "QuoteReference_char, DevExec_Char, ProductCode_char, ProductType_char, BrkComm_per, BDComm_per, "
    SQL = SQL & "QuotedPremium_cur, Currency_char, InceptionDate_date, PolicyEndDate_date, PolicyNo_char, Handler_char, "
    SQL = SQL & "QuoteHandler_Char, Terms_Note, RiskTransfer_char, creditTerms_char, Handling_Office, "
    SQL = SQL & "QuoteLogID_int, QABQuoteID_int, QMSQuoteID_Int, Live_Bit, LastLoggedDateTime_Date, FeedbackStatus_char,"
    SQL = SQL & " QuoteReq_char, Feedback_note, TargetPremium_cur, TargetInsurer_char, "
    SQL = SQL & " InsurerPlaced_char, Carrier_char, CarrierSource_char, PremiumPlaced_cur, TransactionType_Char)"
            
    If Len("" & rs!ac_no_varchar) > 0 Then
        SQL_str = "SELECT BDA_Main.A_C_No, BDA_Main.Name, BDA_Main.CommlExec FROM BDA_Main "
        SQL_str = SQL_str & " WHERE BDA_Main.A_C_No = " & rs!ac_no_varchar
        Set rsRisk = New ADODB.Recordset
        rsRisk.Open SQL_str, gLocal, adOpenStatic, adLockPessimistic
        
        If Not rsRisk.EOF Then
            If "" & rsRisk!CommlExec <> "" Then
            '    CheckBrokerDetails RSQMS5!A_C_NO
                DevExec = rsRisk!CommlExec
                BName = rsRisk!Name
            End If
        End If
    End If
    
    'check whether quoteid = tab_qms_riskdetails
    RiskID_int = CheckRiskDetails(rs!InsuredLastName_varchar, rs!QuoteID_int)
    CustID_int = CheckBrokerDetails(vbNullString & rs!ac_no_varchar)
    
    
    SQL = SQL & " VALUES ("
    SQL = SQL & "" & Val("" & gAccNo_str) & ""
    SQL = SQL & ",'" & rs!Status_varchar & "'"
    SQL = SQL & "," & CustID_int
    SQL = SQL & "," & RiskID_int
    SQL = SQL & ",'" & MakeStrSQLAcceptable(rs!InsuredLastName_varchar) & "'"
            
    RenewalDate = Format(rs!PolicyStartDate_DateTime, "dd mmmm yyyy")
    ReceivedDate = Format(Logged_datetime, "dd mmmm yyyy")
    
    SQL = SQL & ", CONVERT(datetime,'" & RenewalDate & "', 103)"
    SQL = SQL & ", CONVERT(datetime,'" & ReceivedDate & "', 103)"
    If IsNumeric(rs!PolID_varchar) Then
        SQL = SQL & ",'" & IIf("" & GetQuoteReferenceFromPrev(rs!QuoteID_int) <> "", GetQuoteReferenceFromPrev(rs!QuoteID_int), rs!PolID_varchar) & "'"
    Else
        SQL = SQL & ",'" & rs!PolID_varchar & "'"
    End If
    SQL = SQL & ", '" & DevExec & "'"
    
    'code for getting from Tony's tables when they reflect properly
    'SQL = "SELECT DISTINCT dbo.Tonyg_SchemeShop_Products.ProductPrefix_varchar, dbo.Tonyg_SchemeShop_Products.CommissionAmount_Decimal,"
    'SQL SQL & " dbo.Tonyg_SchemeShop_Products.BrokerDirectCommissionAmount_Decimal, dbo.Tonyg_SchemeShop_Products.ProductID_int,"
    'SQL = SQL & " dbo.Tonyg_SchemeShop_Products.CommissionType_varchar FROM dbo.Tonyg_SchemeShop_LogQuoteCalcs INNER JOIN"
    'SQL = SQL & " dbo.Tonyg_SchemeShop_Products ON dbo.Tonyg_SchemeShop_LogQuoteCalcs.ProductID_int = dbo.Tonyg_SchemeShop_Products.ProductID_int"
    'SQL = SQL & " WHERE (dbo.Tonyg_SchemeShop_LogQuoteCalcs.QuoteID_int = " & RS!QuoteID_int & ")"
    
    'For now - use the tabQMS_DefaultProductComms table
    SQL_str = "SELECT * FROM tabQMS_DefaultProductComms WHERE SchemeID = " & rs!SchemeID_int
    Set rsScheme = New ADODB.Recordset
    rsScheme.Open SQL_str, gBass, adOpenStatic, adLockOptimistic
    
    If Not rsScheme.BOF Then
        
        ProductCode_char = rsScheme!ProductCode_char
        ProductType_char = GetProductType(ProductCode_char)
        Carrier_char = rsScheme!Carrier_char
        If Val("" & rsScheme!BrokerComm_num) > 0 Then BrkComm_per = rsScheme!BrokerComm_num
        If Val("" & rsScheme!BDComm_num) > 0 Then BDComm_per = rsScheme!BDComm_num
    Else
        ProductType_char = ""
        ProductCode_char = ""
        Carrier_char = ""
        BrkComm_per = 0
        BDComm_per = 0
    End If
    
    SQL = SQL & ",'" & trim(ProductCode_char) & "'"
    SQL = SQL & ",'" & ProductType_char & "'"
    SQL = SQL & ",'" & BrkComm_per & "'"
    SQL = SQL & ",'" & BDComm_per & "'"
    SQL = SQL & "," & rs!GrossPremium_money
    SQL = SQL & ",'GBP'"
    
    SQL = SQL & ", CONVERT(datetime,'" & Format(rs!PolicyStartDate_DateTime, "dd/mm/yyyy hh:mm:ss") & "',103)"
    SQL = SQL & ", CONVERT(datetime,'" & Format(rs!PolicyEndDate_DateTime, "dd/mm/yyyy hh:mm:ss") & "', 103)"

    SQL = SQL & ",'" & rs!PolID_varchar & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(rs!CompletedBy_Varchar) & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(rs!CompletedBy_Varchar) & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(rs!UserNotes_varchar) & "'"
    SQL = SQL & ",'YES'"
    SQL = SQL & ",'30 Days'"
    SQL = SQL & ",'BD'"
    SQL = SQL & "," & rs!QuoteLogID_Int
    SQL = SQL & "," & rs!QuoteID_int
    SQL = SQL & "," & QmsQuoteID
    
    SQL = SQL & ",-1"
    SQL = SQL & ", CONVERT(datetime,'" & Format(Now, "dd/mm/yyyy hh:mm:ss") & "',103)"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(Feedback_char) & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(Feedback_note) & "'"
    'SQL = SQL & ",'" & MakeStrSQLAcceptable(QuoteReq_char) & "'"
    If Val("" & QuoteReq_char) = 0 Then
          SQL = SQL & ", '" & Null & "'"
    Else
        SQL = SQL & ", CONVERT(datetime,'" & Format(QuoteReq_char, "MM-DD-YYYY") & "', 103)"
    End If
    SQL = SQL & "," & TargetPrem_money
    SQL = SQL & ",'" & MakeStrSQLAcceptable(TargetInsurer_char) & "'"
    If Val("" & ToCarrier_char) = 0 Then
        SQL = SQL & ", '" & Null & "'"
    Else
         SQL = SQL & ", CONVERT(datetime,'" & Format(ToCarrier_char, "dd mmmm yyyy") & "', 103)"
    End If
    'If CarrierQuote_char = "" Then
    '     SQL = SQL & "," & vbNull
    'Else
    'SQL = SQL & ", CONVERT(datetime,'" & Format(CarrierQuote_char, "dd mmmm yyyy") & "', 103)"
    'End If
    'If ToBroker_char = "" Then
    '     SQL = SQL & "," & vbNull
    'Else
   ' SQL = SQL & ", CONVERT(datetime,'" & Format(ToBroker_char, "dd mmmm yyyy") & "', 103)"
    'End If
    SQL = SQL & ",'" & MakeStrSQLAcceptable(InsurerPlaced_char) & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(Carrier_char) & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(Source_char) & "'"
    SQL = SQL & "," & PremPlaced_money
    SQL = SQL & ",'" & rs!TransactionType_varChar & "'"
    
    SQL = SQL & ") SELECT @@IDENTITY"
    
    Set rsSet = gBass.Execute(SQL)

Thanks for your help !!

**FunkyDexter** · Jun 20th, 2008, 07:30 AM

Taking a quick look, I can't see anywhere where you give quoteReq_char a value so it will always be blank.

Assuming you are giving it a value, then changing this:-

Code:

If Val("" & QuoteReq_char) = 0 Then
          SQL = SQL & ", '" & Null & "'"    Else
        SQL = SQL & ", CONVERT(datetime,'" & Format(QuoteReq_char, "MM-DD-YYYY") & "', 103)"
    End If

to

Code:

If Val("" & QuoteReq_char) = 0 Then
          SQL = SQL & ", Null "
    Else
        SQL = SQL & ", CONVERT(datetime,'" & Format(QuoteReq_char, "MM-DD-YYYY") & "', 103)"
    End If

you'll also need to do something similar with ToCarrier_char. The point is you're building up a string. If you build a vb null (which is an empty string) into it you'll just get a blank so your string will look something like 'SomeVal', '', 'SomeOtherVal'. But if you pass "NULL" as a string then the string will read 'SomeVal', NULL, 'SomeOtherVal' and that's what sql server wants. Does that make sense?

I'm not familiar with this:-
If Val("" & QuoteReq_char) = 0
to check for an empty string but I think it should work. I'd suggest:-
If QuoteReq_char = ""
or
If len(QuoteReq_char) = 0
are more readable though.

Thread: insert null into date fields

Thread Tools

Display

insert null into date fields

Re: insert null into date fields

Re: insert null into date fields

Re: insert null into date fields

Re: insert null into date fields

Re: insert null into date fields

Re: insert null into date fields

Re: insert null into date fields

Re: insert null into date fields

Re: insert null into date fields

Posting Permissions