insert null into date fields
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
Re: insert null into date fields
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?
Re: insert null into date fields
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!!
Re: insert null into date fields
Why are you putting that in, rather than Null (which is very different to '' that you originally had)?
Re: insert null into date fields
I know it sounds hard to beleive but when I debug the SQL stmt thats
what is created within the SQl string!!! :( :(
Re: insert null into date fields
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?
Re: insert null into date fields
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
;)
Re: insert null into date fields
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:-
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?
Re: insert null into date fields
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 !!
Re: insert null into date fields
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.
