SQLITE错误E/SQLiteLog在“monthly”附近:语法错误

Hi i've been getting a SQL error that says E/SQLiteLog﹕ (1) near "monthly": syntax error

你好，我收到了一个SQL错误，它说E/SQLiteLog(1)接近“monthly”:语法错误。

i believe it may have something todo with my create Table statement

我相信这可能与我的create Table语句有关

  private static final String CREATE_TABLE2 = "CREATE TABLE " + TABLE_NAME2 + " (" + UID +
        " INTEGER PRIMARY KEY AUTOINCREMENT, " + POST_CODE + " VARCHAR(255)," + FIRST_LINE +
        " VARCHAR(255)," + TOWN + " VARCHAR(255)," + COUNTY + " VARCHAR(255)," + ROOM_NO +
        " INTEGER ," + ASKING_PRICE + " INTEGER ," + CURRENT_OFFER + " INTEGER ," + AGREED_PRICE +
        " INTEGER ," + AGENT_NAME + " VARCHAR(255)," + AGENT_PHO_NUM + " VARCHAR(255)," + ESTATE_AGENT_NAME +
        " VARCHAR(255), " + REFURB_COST + " VARCHAR(255), " + TIME_OF_APPOINTMENT + " VARCHAR(255), "
        + DONE_UP_VALUE + " INTEGER ," +WEEKLY_ROOM_RATE+ " INTEGER ," +EXPECTED_RENT + " INTEGER ," + MAX_OFFER + " INTEGER ,"
        + DISCOUNT_PERCENT + " DECIMAL(3,2) ," + LOAN_TO_VALUE + " DECIMAL(3,2) ,"+ MORTGAGE_Interest + "DECIMAL(3,2) ," +
        MAX_MORTGAGE + " INTEGER ," + DESPOSIT_NEEDED + " INTEGER ," + DESPOSIT_REPAYMENT_MONTHLY+ " INTEGER ," + REPAYMENT_ANNAUL + " INTEGER ,"
        +STAMP_DUTY_PER + " DECIMAL(3,2) ," + STAMP_DUTY_ACT + " INTEGER ," +INSURENCES+ " INTEGER ," + TOTAL_PURCHASE_COSTS +
        " INTEGER ," + TOTAL_MONEY_NEEDED + " INTEGER ," + MORTGAGE_REPAYMENT + " INTEGER ," +
        BILLS_UTILS + " INTEGER ," + TOTAL_COST_PER_MONTH + " INTEGER ," + TOTAL_REFURB_COST + " INTEGER ," + TOTAL_PROJECT_COST +
        " INTEGER ,"+ TOTAL_PROFIT + " INTEGER ,"+ RENTAL_PROFIT + " INTEGER);";

this is my insertData method for adding to my database

这是添加到数据库的insertData方法

public long insertData2(String posCode, String firstLine, String town, String county, int roomNum,
                       int askingPrice, int currentOffer, int agreedPrice, String agentName,
                       String agentPhone, String estateAgentNam, int refurb, String Time, int doneUp,int expectedRent
                        , int weeklyRent, int maxOffer,double discountPercent, double LoanToValue,Double mortgageInterest
                        , int maxMOrtgage , int despositNeeded, int despositRepayment, int despositRepaymentAnnual, double stampDutyPer,
                       int stampDutyAct , int insurances, int totalPurchaseCost,int moneyNeeded, int mortgageRepayments,int billsUtils,
                       int costPerMonth, int refurbCost , int projectCost, int totalProfit, int rentProfit) {

    ContentValues contentValues = new ContentValues();
    contentValues.put(POST_CODE, posCode);
    contentValues.put(FIRST_LINE, firstLine);
    contentValues.put(TOWN, town);
    contentValues.put(COUNTY, county);
    contentValues.put(ROOM_NO, roomNum);
    contentValues.put(ASKING_PRICE, askingPrice);
    contentValues.put(CURRENT_OFFER, currentOffer);
    contentValues.put(AGREED_PRICE, agreedPrice);
    contentValues.put(AGENT_NAME, agentName);
    contentValues.put(AGENT_PHO_NUM, agentPhone);
    contentValues.put(ESTATE_AGENT_NAME, estateAgentNam);
    contentValues.put(REFURB_COST, refurb);
    contentValues.put(TIME_OF_APPOINTMENT, Time);
    contentValues.put(DONE_UP_VALUE,doneUp);
    contentValues.put(WEEKLY_ROOM_RATE,weeklyRent);
    contentValues.put(EXPECTED_RENT,expectedRent);
    contentValues.put(MAX_OFFER,maxOffer);
    contentValues.put(DISCOUNT_PERCENT,discountPercent);
    contentValues.put(LOAN_TO_VALUE,LoanToValue);
    contentValues.put(MORTGAGE_Interest,mortgageInterest);
    contentValues.put(MAX_MORTGAGE,maxMOrtgage);
    contentValues.put(DESPOSIT_NEEDED,despositNeeded);
    contentValues.put(DESPOSIT_REPAYMENT_MONTHLY,despositRepayment);
    contentValues.put(REPAYMENT_ANNAUL,despositRepaymentAnnual);
    contentValues.put(STAMP_DUTY_PER,stampDutyPer);
    contentValues.put(STAMP_DUTY_ACT,stampDutyAct);
    contentValues.put(INSURENCES,insurances);
    contentValues.put(TOTAL_PURCHASE_COSTS,totalPurchaseCost);
    contentValues.put(TOTAL_MONEY_NEEDED,moneyNeeded);
    contentValues.put(MORTGAGE_REPAYMENT,mortgageRepayments);
    contentValues.put(BILLS_UTILS,billsUtils);
    contentValues.put(TOTAL_COST_PER_MONTH,costPerMonth);
    contentValues.put(TOTAL_REFURB_COST,refurbCost);
    contentValues.put(TOTAL_PROJECT_COST,projectCost);
    contentValues.put(TOTAL_PROFIT,totalProfit);
    contentValues.put(RENTAL_PROFIT,rentProfit);




    long id = db.insert(TABLE_NAME2, null, contentValues);
    return id;


}

i've been looking at tutorials and checking online but i just can't seem to see where im going wrong can anyone please help?

我一直在看教程和网上查询，但我似乎不知道我哪里出错了，谁能帮忙吗?

UPDATE FULL error message

更新完整的错误消息

total refurb cost=0 Total Money Needed=1501 Insurences=1 Stamp duty actual=0 EstateAgentName=test rental profit=11 Mortgage Interest=1.0 AgreedPrice=0 AddressFirstLine=test mortage Repayment=0 stamp duty percent=0.0 Total project cost=1503 AskingPrice=3000 total cost per month=1 max_offer=-24 RefurbCost=0 total profit=3565275 desposit needed=0 discount_percent=0.0 doneUpValue=3566778 county=test PostCode=test loan to value=1.0 NumberOfRooms=3 bills utils=1 Town=stevenage timeOfAppointment=27 AgentName=27 Max Mortgage=-24 Repayment Annual=0 Expected rent=12 AgentPhoneNumber=test

更新:2007-2-13阅读:更新:2007-2-13阅读:更新:2007-2-13阅读:更新:2007-2-13阅读:更新:2007-2-13阅读:更新:阅读:更新:2007-2-13阅读:更新:2007-2-13阅读:更新:阅读AgentName=27 Max Mortgage=-24 Annual=0 Expected rent=12 AgentPhoneNumber=test

android.database.sqlite.SQLiteException: near "monthly": syntax error (code 1): , while compiling: INSERT INTO Viewed(repayment monthly,CurrentOffer,Total Purchase costs,weekly room rate,total refurb cost,Total Money Needed,Insurences,Stamp duty actual,EstateAgentName,rental profit,Mortgage Interest, AgreedPrice,AddressFirstLine, mortage Repayment,stamp duty percent,Total project cost,AskingPrice,total cost per month,max_offer,RefurbCost,total profit,desposit needed,discount_percent,doneUpValue,county,PostCode,loan to value,NumberOfRooms,bills utils,Town,timeOfAppointment, AgentName,Max Mortgage,Repayment Annual,Expected rent, AgentPhoneNumber) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

android.database.sqlite。SQLiteException:接近“月”:语法错误(代码1):，编译时:插入查看(每月还款，现金支付，总购买成本，每周房费，总翻新成本，所需资金，保险，印花税实际，房地产，租赁利润，抵押贷款利息，议定价格，地址优先线，抵押贷款，印花税，项目总成本，贷款价格，每月总成本，最高报价AgentName,马克斯抵押贷款、还款年度预期租金,AgentPhoneNumber)值(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

1 个解决方案

#1

Basically you didn't construct your sql correctly which happens near "monthly" in your long string of concatenated elements.

基本上，您没有正确地构造sql，这在长串连接元素的“monthly”附近发生。

There are so many wrongs in this sql:

这个sql中有很多错误:

INSERT INTO Viewed(repayment monthly,CurrentOffer,Total Purchase
costs,weekly room rate,total refurb cost,Total Money Needed, ...)

You are using spaces for column names.

您正在为列名使用空格。

I have two tips you can use to build easier sql.

我有两个技巧可以帮助您构建更简单的sql。

First use a MySql client like Workbench to build your table more easily with the help of its GUI tools (but make sure the data types are compatible with Sqlite). When the query is about to be executed, you can see the raw sql which you can later copy-paste to your project.
首先，使用类似于Workbench的MySql客户端，在GUI工具的帮助下更容易地构建表(但是要确保数据类型与Sqlite兼容)。当查询即将执行时，您可以看到原始sql，您可以稍后将其复制到项目中。

Don't use String concatenation like that. Use String.format.

不要像那样使用字符串连接。使用String.format。

String sql = String.format(
          "CREATE TABLE %s (%s,%s)",
          TABLE_NAME2,
          UID + " INTEGER PRIMARY KEY AUTOINCREMENT",
          POST_CODE + " VARCHAR(255)");

#1