Runtime Error 80040e14

[violette]

I got an error when i run my program. it says;
you have an error in your sql syntax;check the manual that corresponds to your mySQL server version for the right syntax to use near 'VALUES ('2011',5,5131,678,'342',809 at line 1

what does it mean? here's my code. can someone point out which part is wrong?

If stockCodeCom <> "" Then

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

[urtrivedi]

before value remove that extra comma after report_period_end_month

[violette]

but it still return the same error...

[MeSampath]

Hi,

As per the posted code ")" are missing. please check that.

Thank you,

but it still return the same error...

[violette]

still got the same error but this time it says; you have an error in your sql syntax;check the manual that corresponds to your mySQL server version for the right syntax to use near line 1. it does not shows any value like the previous one

[MeSampath]

What is your MySQL server version? Check this link

Try to use the "`" [tilde key in the left corner below Esc key] char instead of "'" [single quote] in the query.

Thank you

still got the same error but this time it says; you have an error in your sql syntax;check the manual that corresponds to your mySQL server version for the right syntax to use near line 1. it does not shows any value like the previous one

[urtrivedi]

If you have any text box on the form then
add following line before execution

txt1.text=sqlStr

 conn.Execute sqlStr

Then copy that query run it in sql analyzer and also post that generated query here also

Your code must look like following
NOTICE RED BOLD UNDERLINE, I HAVE REMOVED 2 COMMAS AND REPLACED BY CLOSING PARANTHESIS )

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

[sknake]

You should be using parameterized SQL. Its a lot easier to read queries and ensure you're not having datatype mismatches.

Imports System.Data.SqlClient
Imports System.Text

Public Class Form1

  'Create Table TestTable
  '(
  '  Column1 varchar(100) PRIMARY KEY NOT NULL
  ')

  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Using conn As SqlConnection = New SqlConnection("Data Source=SCOTTIE-DEV2;Initial Catalog=TestDB;Integrated Security=True")
      conn.Open()
      Using cmd As SqlCommand = conn.CreateCommand()
        Dim sb As New StringBuilder
        sb.AppendLine("Insert Into TestTable (Column1) Values (@Value1)")
        sb.AppendLine("Insert Into TestTable (Column1) Values (@Value2)")

        cmd.CommandText = sb.ToString()
        cmd.Parameters.Add("@Value1", SqlDbType.VarChar).Value = "First Row"
        cmd.Parameters.Add("@Value2", SqlDbType.VarChar).Value = "Second Row"
        Dim i As Integer = cmd.ExecuteNonQuery()
        MessageBox.Show("Rows inserted: " + i.ToString())
      End Using
    End Using
  End Sub
End Class

[violette]

What is your MySQL server version? Check this link

Try to use the "`" [tilde key in the left corner below Esc key] char instead of "'" [single quote] in the query.

Thank you

hi, ny mysql server is the 5.0.45-community.

i've done like what utrivedi have posted and the code works. just a simple mistake makes the code failed. >.<

i'm trying to implement like sknake have suggested. still working on it. :) thank you guys

[adam_k]

Just for future reference, your error was here:

"'," & netInvest & "'," & netInvest & "'," & netInvest & "','" &

you are missing single quotes.

It will be a lot easier for you to troubleshoot errors of this type by using debug.print sqlStr (or how you call the string with the statement) and reading the output or even better trying it in your db.

[WaltP]

Try formatting your code into a sequence of readable lines

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

This way you can actually see what you're doing and spot errors a lot faster. Like missing parentheses.

Your code must look like following

Jeez I hope not! That's an unreadable mess!!!!