Insert Null Values into Database

[violette]

Hi guys. here's my problem. When there is Null values in the excel file, the macro will give me an error 'type mismatch'. Please have a look at my code.

This code only works when there every field has value. it gives error if there is null value.

sqlStr = "INSERT INTO company_financial "
                    sqlStr = sqlStr & "(year_id, period_id, stock_code,mkt_cap,created_by,created_date)"
                    sqlStr = sqlStr & " VALUES('" & yearIdCom & "', '" & periodIdCom & "', '" & stockCodeCom & "', '" & marketCapCom & "', 'fistconv','" & Format(Now(), "yyyy-MM-dd HH:mm") & "')"

                    conn.Execute sqlStr
                    lastRow = lastRow + 1
                End If
                    Range("C3").Value = lastRow - 5

I tried separating each sql statement(which I'm not sure whether it is possible to do that or not) and it gives me error saying 'you have an error in your sql syntax'. Can somebody point out which part is my mistake? below is the code;

sqlStr = "INSERT INTO company_financial"
               sqlStr = sqlStr & " year_id = '" & yearIdCom & "',"
               sqlStr = sqlStr & " period_id = '" & periodIdCom & "',"
               sqlStr = sqlStr & " stock_code = '" & stockCodeCom & "',"
               
                    If tradeReceivable <> "Null" Then
                sqlStr = sqlStr & " receivable = '" & CDbl(tradeReceivable) & "',"
                    Else
                sqlStr = sqlStr & " receivable = Null ,"
                    End If
                
                    If revenueCom <> "Null" Then
                sqlStr = sqlStr & " revenue = '" & CDbl(revenueCom) & "',"
                    Else
                sqlStr = sqlStr & " revenue = Null ,"
                    End If
                
                    If TotLiability <> "Null" Then
                sqlStr = sqlStr & " total_liabilities = '" & CDbl(TotLiability) & "',"
                    Else
                sqlStr = sqlStr & " total_liabilities = Null ,"
                    End If
                
                    If shareholderEqyCom <> "Null" Then
                sqlStr = sqlStr & " shareholders_equity = '" & CDbl(shareholderEqyCom) & "',"
                    Else
                sqlStr = sqlStr & " shareholders_equity = Null ,"
                    End If
                
                    If totLiabToSE <> "Null" Then
                sqlStr = sqlStr & " total_liabilities_to_shareholders_equity = '" & CDbl(totLiabToSE) & "',"
                    Else
                sqlStr = sqlStr & " total_liabilities_to_shareholders_equity = Null ,"
                    End If
                
                    If curLiability <> "Null" Then
                sqlStr = sqlStr & " current_liabilities = '" & CDbl(curLiability) & "',"
                    Else
                sqlStr = sqlStr & " current_liabilities = Null ,"
                    End If
                
                    If currentAss <> "Null" Then
                sqlStr = sqlStr & " total_current_assets = '" & CDbl(currentAss) & "',"
                    Else
                sqlStr = sqlStr & " total_current_assets = Null ,"
                    End If
                
                    If netAttributableCom <> "Null" Then
                sqlStr = sqlStr & " net_attributable = '" & CDbl(netAttributableCom) & "',"
                    Else
                sqlStr = sqlStr & " net_attributable = Null ,"
                    End If
                
                    If inventory <> "Null" Then
                sqlStr = sqlStr & " inventories = '" & CDbl(inventory) & "',"
                    Else
                sqlStr = sqlStr & " inventories = Null ,"
                    End If
                
                    If fixedAsset <> "Null" Then
                sqlStr = sqlStr & " fixed_assets = '" & CDbl(fixedAsset) & "',"
                    Else
                sqlStr = sqlStr & " fixed_assets = Null ,"
                    End If
                
                    If operatingExpenses <> "Null" Then
                sqlStr = sqlStr & " operating_cost = '" & CDbl(operatingExpenses) & "',"
                    Else
                sqlStr = sqlStr & " operating_cost = Null ,"
                    End If
                
                    If operatingCashFlow <> "Null" Then
                sqlStr = sqlStr & " net_cash_operation = '" & CDbl(operatingCashFlow) & "',"
                    Else
                sqlStr = sqlStr & " net_cash_operation = Null ,"
                    End If
                
                    If ClosingPrice <> "Null" Then
                sqlStr = sqlStr & " price_close = '" & CDbl(ClosingPrice) & "',"
                    Else
                sqlStr = sqlStr & " price_close = Null ,"
                    End If
                
                    If priceEarningRatio <> "Null" Then
                sqlStr = sqlStr & " pe = '" & CDbl(priceEarningRatio) & "',"
                    Else
                sqlStr = sqlStr & " pe = Null ,"
                    End If
                
                    If earningPerShare <> "Null" Then
                sqlStr = sqlStr & " eps = '" & CDbl(earningPerShare) & "',"
                    Else
                sqlStr = sqlStr & " eps = Null ,"
                    End If
                
                    If peRatio <> "Null" Then
                sqlStr = sqlStr & " pe_relative_sector = '" & CDbl(peRatio) & "',"
                    Else
                sqlStr = sqlStr & " pe_relative_sector = Null ,"
                    End If
                
                    If altmanZ <> "Null" Then
                sqlStr = sqlStr & " altman_z_score = '" & CDbl(altmanZ) & "',"
                    Else
                sqlStr = sqlStr & " altman_z_score = Null ,"
                    End If
                
                    If paidUpCapital <> "Null" Then
                sqlStr = sqlStr & " paid_up_capital = '" & CDbl(paidUpCapital) & "',"
                    Else
                sqlStr = sqlStr & " paid_up_capital = Null ,"
                End If
                
                    If netPOL <> "Null" Then
                sqlStr = sqlStr & " net_profit_or_loss = '" & CDbl(netPOL) & "',"
                    Else
                sqlStr = sqlStr & " net_profit_or_loss = Null ,"
                End If
                
                    If incStock <> "Null" Then
                sqlStr = sqlStr & " dec_stock = '" & CDbl(incStock) & "',"
                    Else
                sqlStr = sqlStr & " dec_stock = Null ,"
                End If
                
                    'If netInvest = Null Then
                sqlStr = sqlStr & " dec_debtors = Null ,"
                    'Else
                'sqlStr = sqlStr & " dec_debtors = '" & CDbl(netInvest) & "',"
                'End If
                
                    'If netInvest = Null Then
                sqlStr = sqlStr & " inc_creditors = Null ,"
                    'Else
                'sqlStr = sqlStr & " inc_creditors = '" & CDbl(netInvest) & "',"
                'End If
                
                    If netInvest <> "Null" Then
                sqlStr = sqlStr & " net_investments = '" & CDbl(netInvest) & "',"
                    Else
                sqlStr = sqlStr & " net_investments = Null ,"
                End If
                
                    If cashNeqvalent <> "Null" Then
                sqlStr = sqlStr & " cash_and_equivalents = '" & CDbl(cashNeqvalent) & "',"
                    Else
                sqlStr = sqlStr & " cash_and_equivalents = Null ,"
                End If
                
                    If retainedPOL <> "Null" Then
                sqlStr = sqlStr & " retained_profit_or_loss = '" & CDbl(retainedPOL) & "',"
                    Else
                sqlStr = sqlStr & " retained_profit_or_loss = Null ,"
                End If
                
                    If EbitCom <> "Null" Then
                sqlStr = sqlStr & " ebit = '" & CDbl(EbitCom) & "',"
                    Else
                sqlStr = sqlStr & " ebit = Null ,"
                End If
                
                    If marketCapCom <> "Null" Then
                sqlStr = sqlStr & " mkt_cap = '" & CDbl(marketCapCom) & "',"
                    Else
                sqlStr = sqlStr & " mkt_cap = Null ,"
                End If
                
                    If salesToTotalAss <> "Null" Then
                sqlStr = sqlStr & " sales_to_assets = '" & CDbl(salesToTotalAss) & "',"
                    Else
                sqlStr = sqlStr & " sales_to_assets = Null ,"
                End If
                
                    If ReOTA <> "Null" Then
                sqlStr = sqlStr & " rota = '" & CDbl(ReOTA) & "',"
                    Else
                sqlStr = sqlStr & " rota = Null ,"
                End If
                
                    If ReOE <> "Null" Then
                sqlStr = sqlStr & " roe = '" & CDbl(ReOE) & "',"
                    Else
                sqlStr = sqlStr & " roe = Null ,"
                End If
                
                    If DebtEqy <> "Null" Then
                sqlStr = sqlStr & " debt_to_equity = '" & CDbl(DebtEqy) & "',"
                    Else
                sqlStr = sqlStr & " debt_to_equity = Null ,"
                End If
                
                    If AvergShares <> "Null" Then
                sqlStr = sqlStr & " average_shares = '" & CDbl(AvergShares) & "',"
                    Else
                sqlStr = sqlStr & " average_shares = Null ,"
                End If
                
                    If totalAss <> "Null" Then
                sqlStr = sqlStr & " total_assets = '" & CDbl(totalAss) & "',"
                    Else
                sqlStr = sqlStr & " total_assets = Null ,"
                End If
                
                    If sharesOutstdgCom <> "Null" Then
                sqlStr = sqlStr & " shares_outstanding = '" & CDbl(sharesOutstdgCom) & "',"
                    Else
                sqlStr = sqlStr & " shares_outstanding = Null ,"
                End If
                
                    If fiyeMonth <> "Null" Then
                sqlStr = sqlStr & " financial_year_end = '" & fiyeMonth & "',"
                    Else
                sqlStr = sqlStr & " financial_year_end = Null ,"
                End If

                sqlStr = sqlStr & " created_by = 'admin',"
                sqlStr = sqlStr & " created_date = '" & Format(Now(), "yyyy-MM-dd HH:mm") & "'"
                sqlStr = sqlStr & " WHERE year_id = '" & yearIdCom & "' AND period_id = '" & periodIdCom & "' AND stock_code = '" & stockCodeCom & "'"
          
                    conn.Execute sqlStr
                    lastRow = lastRow + 1
                End If

[ChrisPadgham]

you need to use the isnull or nz function for example

If not isnull(tradeReceivable)  Then
sqlStr = sqlStr & " receivable = '" & CDbl(tradeReceivable) & "',"
End If

if the value of tradeReceivable is null then you do not need to include it in the SQL it will be null by default
or

sqlStr = sqlStr = " receivable = '" & nz(tradeReceivable,0) & "',"

[hkdani]

Some database fields require input for each field: even if you don't have a value to input. So if you're using numbers, you might supply the number zero where a null field would show up. But sometimes the field requires input, but it requires a string value. So, you provide a space string value for that field, if you don't have any input for that particular field.

Dim strName as string

if strName = vbNullString then
     strName = " " '
     rst.Fields("Name") = strName
else
     rst.Fields("Name") = strName
End if

Something along those lines. You need to know which fields require a value. And you need to add a value that will work with that field that you can recognize later as a missing value, in case you to need to add a record to a table without having all the information at the time of the recordset update.

[BitBlt]

Actually, depending on what database you are using, the ANSI standard for inserting rows is:

INSERT into dbo.myTable 
(col_1, col_2, col_3)
VALUES
('1', '2', '3')   -- or numbers, or whatever the datatypes are for your columns

Your second example would come out looking something like:

INSERT into dbo.myTable 
col_1 = '1', 
col_2 = '2',
col_3 = '3'

This is incorrect syntax.

However, you can assemble your VALUES statement kind of like you tried to assemble the other parts. It might look something like this:

mySqlStmt = "INSERT into dbo.myTable "
mySqlStmt = mySqlStmt & "(col_1, col_2, col_3)"
mySqlStmt = mySqlStmt & " VALUES ("
if myCol_1 <> "NULL" then
' or test for however NULL is represented, either with isnull(myCol_1) or myCol_1 = vbNullString
    mySqlStmt = mySqlStmt & "'" & myCol_1 & "',"
else
    mySqlStmt = mySqlStmt & "NULL,"
end if
if myCol_2 <> "NULL" then
    mySqlStmt = mySqlStmt & "'" & myCol_2 & "',"
else
    mySqlStmt = mySqlStmt & "NULL,"
end if
if myCol_3 <> "NULL" then
    mySqlStmt = mySqlStmt & "'" & myCol_3 & "'"   ' Notice, no terminating comma for the last value!
else
    mySqlStmt = mySqlStmt & "NULL"
end if
' now be sure to enclose the "VALUES" clause with a close-parenthesis...
mySqlStmt = mySqlStmt & ")"

You could also get fancy and decide whether to include a column by assembling the column list at the same time as the values list, then stringing the whole thing together at the end.

Hope this is useful to you! Good luck!

[dspnhn]

i need to look at ur table structure first before assuming anything. so please post ur table structure first.