I use SQLite as my database engine and my program should insert a new user into my database. But it gives me the error: SQL logic error or missing database near "group": syntax error.
I can't see anything wrong in my statement. There is my SQL string and my DB structure:
public void CreateAccount(string uid, string name, string aGroup, string oaL, string oaP, string oaLW, string oaPW,
string intrL, string intrP, string mailL, string mailP, string c1, string c2, string c3, string c4, string c5, string c6, string c7, string c8, string c9, string c10, string c11, string c12, string c13, string c14, string c15, string c16, string c17, string c18, string c19, string c20, string c21, string c22, string c23, string c24, string c25, string c26, string c27, string c28, string c29, string c30, string w1, string w2, string w3, string w4, string w5, string w6, string w7, string w8, string w9, string w10, string w11, string w12, string w13, string w14, string w15, string w16, string w17, string w18, string w19, string w20, string w21, string w22, string w23, string w24, string w25, string w26, string w27, string w28, string w29, string w30 )
{
SQLiteConnection sql_con;
sql_con = new SQLiteConnection("Data Source=DB.db;Version=3;");
SQLiteCommand addUserStr = new SQLiteCommand("INSERT INTO usersDb (uid, agentname, group, oaLogin, oaPw, webOaLogin, webOaPwd, intrLogin, intrPwd, gmailLogin, gmailpwd, nCode1, nCode2, nCode3, nCode4, nCode5, nCode6, nCode7, nCode8, nCode9, nCode10, nCode11, nCode12, nCode13, nCode14, nCode15, nCode16, nCode17, nCode18, nCode19, nCode20, nCode21, nCode22, nCode23, nCode24, nCode25, nCode26, nCode27, nCode28, nCode29, nCode30, wCode1, wCode2, wCode3, wCode4, wCode5, wCode6, wCode7, wCode8, wCode9, wCode10, wCode11, wCode12, wCode13, wCode14, wCode15, wCode16, wCode17, wCode18, wCode19, wCode20, wCode21, wCode22, wCode23, wCode24, wCode25, wCode26, wCode27, wCode28, wCode29, wCode30) VALUES (@uidParam, @nameParam, @grParam, @oaLoginParam,@oaPwParam,@oaWebLParam,@oaWebPwParam,@intrLParam,@intrPwParam,@mailLParam,@mailPwParam,@c1Param,@c2Param,@c3Param,@c4Param,@c5Param,@c6Param,@c7Param,@c8Param,@c9Param,@c10Param,@c11Param,@c12Param,@c13Param,@c14Param,@c15Param,@c16Param,@c17Param,@c18Param,@c19Param,@c20Param,@c21Param,@c22Param,@c23Param,@c24Param,@c25Param,@c26Param,@c27Param,@c28Param,@c29Param,@c30Param,@w1Param,@w2Param,@w3Param,@w4Param,@w5Param,@w6Param,@w7Param,@w8Param,@w9Param,@w10Param,@w11Param,@w12Param,@w13Param,@w14Param,@w15Param,@w16Param,@w17Param,@w18Param,@w19Param,@w20Param,@w21Param,@w22Param,@w23Param,@w24Param,@w25Param,@w26Param,@w27Param,@w28Param,@w29Param,@w30Param);", sql_con);
addUserStr.Parameters.AddWithValue("@uidParam", uid); addUserStr.Parameters.AddWithValue("@nameParam", name);
addUserStr.Parameters.AddWithValue("@grParam", aGroup);
addUserStr.Parameters.AddWithValue("@oaLoginParam", oaL); addUserStr.Parameters.AddWithValue("@oaPwParam", oaP); addUserStr.Parameters.AddWithValue("@oaWebLParam", oaLW);
addUserStr.Parameters.AddWithValue("@oaWebPwParam", oaPW); addUserStr.Parameters.AddWithValue("@intrLParam", intrL); addUserStr.Parameters.AddWithValue("@intrPwParam", intrP);
addUserStr.Parameters.AddWithValue("@mailLParam", mailL); addUserStr.Parameters.AddWithValue("@mailPwParam", mailP);
//codecard (regular)
addUserStr.Parameters.AddWithValue("@c1Param", c1); addUserStr.Parameters.AddWithValue("@c2Param", c2); addUserStr.Parameters.AddWithValue("@c3Param", c3); addUserStr.Parameters.AddWithValue("@c4Param", c4);
addUserStr.Parameters.AddWithValue("@c5Param", c5); addUserStr.Parameters.AddWithValue("@c6Param", c6); addUserStr.Parameters.AddWithValue("@c7Param", c7); addUserStr.Parameters.AddWithValue("@c8Param", c8);
addUserStr.Parameters.AddWithValue("@c9Param", c9); addUserStr.Parameters.AddWithValue("@c10Param", c10); addUserStr.Parameters.AddWithValue("@c11Param", c11); addUserStr.Parameters.AddWithValue("@c12Param", c12);
addUserStr.Parameters.AddWithValue("@c13Param", c13); addUserStr.Parameters.AddWithValue("@c14Param", c14); addUserStr.Parameters.AddWithValue("@c15Param", c15); addUserStr.Parameters.AddWithValue("@c16Param", c16);
addUserStr.Parameters.AddWithValue("@c17Param", c17); addUserStr.Parameters.AddWithValue("@c18Param", c18); addUserStr.Parameters.AddWithValue("@c19Param", c19); addUserStr.Parameters.AddWithValue("@c20Param", c20);
addUserStr.Parameters.AddWithValue("@c21Param", c21); addUserStr.Parameters.AddWithValue("@c22Param", c22); addUserStr.Parameters.AddWithValue("@c23Param", c23); addUserStr.Parameters.AddWithValue("@c24Param", c24);
addUserStr.Parameters.AddWithValue("@c25Param", c25); addUserStr.Parameters.AddWithValue("@c26Param", c26); addUserStr.Parameters.AddWithValue("@c27Param", c27); addUserStr.Parameters.AddWithValue("@c28Param", c28);
addUserStr.Parameters.AddWithValue("@c29Param", c29); addUserStr.Parameters.AddWithValue("@c30Param", c30);
//codecard (web)
addUserStr.Parameters.AddWithValue("@w1Param", w1); addUserStr.Parameters.AddWithValue("@w2Param", w2); addUserStr.Parameters.AddWithValue("@w3Param", w3); addUserStr.Parameters.AddWithValue("@w4Param", w4);
addUserStr.Parameters.AddWithValue("@w5Param", w5); addUserStr.Parameters.AddWithValue("@w6Param", w6); addUserStr.Parameters.AddWithValue("@w7Param", w7); addUserStr.Parameters.AddWithValue("@w8Param", w8);
addUserStr.Parameters.AddWithValue("@w9Param", w9); addUserStr.Parameters.AddWithValue("@w10Param", w10); addUserStr.Parameters.AddWithValue("@w11Param", w11); addUserStr.Parameters.AddWithValue("@w12Param", w12);
addUserStr.Parameters.AddWithValue("@w13Param", w13); addUserStr.Parameters.AddWithValue("@w14Param", w14); addUserStr.Parameters.AddWithValue("@w15Param", w15); addUserStr.Parameters.AddWithValue("@w16Param", w16);
addUserStr.Parameters.AddWithValue("@w17Param", w17); addUserStr.Parameters.AddWithValue("@w18Param", w18); addUserStr.Parameters.AddWithValue("@w19Param", w19); addUserStr.Parameters.AddWithValue("@w20Param", w20);
addUserStr.Parameters.AddWithValue("@w21Param", w21); addUserStr.Parameters.AddWithValue("@w22Param", w22); addUserStr.Parameters.AddWithValue("@w23Param", w23); addUserStr.Parameters.AddWithValue("@w24Param", w24);
addUserStr.Parameters.AddWithValue("@w25Param", w25); addUserStr.Parameters.AddWithValue("@w26Param", w26); addUserStr.Parameters.AddWithValue("@w27Param", w27); addUserStr.Parameters.AddWithValue("@w28Param", w28);
addUserStr.Parameters.AddWithValue("@w29Param", w29); addUserStr.Parameters.AddWithValue("@w30Param", w30);
try
{
sql_con.Open();
addUserStr.ExecuteNonQuery();
MessageBox.Show("Vartotojas " + name + " sukurtas sÄ—kmingai");
sql_con.Close();
}
catch (Exception ex)
{
MessageBox.Show("Err:" + ex.Message);
sql_con.Close();
//throw new Exception(ex.Message);
}
}
CREATE TABLE "usersDb" (
"uid" TEXT(256),
"agentname" TEXT(100),
"group" TEXT(100),
"oaLogin" TEXT(100),
"oaPw" TEXT(100),
"webOaLogin" TEXT(100),
"webOaPwd" TEXT(100),
"intrLogin" TEXT(100),
"intrPwd" TEXT(100),
"gmailLogin" TEXT(100),
"gmailpwd" TEXT(100),
"nCode1" TEXT(10),
"nCode2" TEXT(10),
"nCode3" TEXT(10),
"nCode4" TEXT(10),
"nCode5" TEXT(10),
"nCode6" TEXT(10),
"nCode7" TEXT(10),
"nCode8" TEXT(10),
"nCode9" TEXT(10),
"nCode10" TEXT(10),
"nCode11" TEXT(10),
"nCode12" TEXT(10),
"nCode13" TEXT(10),
"nCode14" TEXT(10),
"nCode15" TEXT(10),
"nCode16" TEXT(10),
"nCode17" TEXT(10),
"nCode18" TEXT(10),
"nCode19" TEXT(10),
"nCode20" TEXT(10),
"nCode21" TEXT(10),
"nCode22" TEXT(10),
"nCode23" TEXT(10),
"nCode24" TEXT(10),
"nCode25" TEXT(10),
"nCode26" TEXT(10),
"nCode27" TEXT(10),
"nCode28" TEXT(10),
"nCode29" TEXT(10),
"nCode30" TEXT(10),
"wCode1" TEXT(10),
"wCode2" TEXT(10),
"wCode3" TEXT(10),
"wCode4" TEXT(10),
"wCode5" TEXT(10),
"wCode6" TEXT(10),
"wCode7" TEXT(10),
"wCode8" TEXT(10),
"wCode9" TEXT(10),
"wCode10" TEXT(10),
"wCode11" TEXT(10),
"wCode12" TEXT(10),
"wCode13" TEXT(10),
"wCode14" TEXT(10),
"wCode15" TEXT(10),
"wCode16" TEXT(10),
"wCode17" TEXT(10),
"wCode18" TEXT(10),
"wCode19" TEXT(10),
"wCode20" TEXT(10),
"wCode21" TEXT(10),
"wCode22" TEXT(10),
"wCode23" TEXT(10),
"wCode24" TEXT(10),
"wCode25" TEXT(10),
"wCode26" TEXT(10),
"wCode27" TEXT(10),
"wCode28" TEXT(10),
"wCode29" TEXT(10),
"wCode30" TEXT(10),
PRIMARY KEY ("uid" ASC)
);
Any ideas guys?
group
is a keyword in SQLite, so you need to escape it. Any of the following should work:
"INSERT INTO usersDb (uid, agentname, `group`, ...)"
"INSERT INTO usersDb (uid, agentname, [group], ...)"
"INSERT INTO usersDb (uid, agentname, \"group\", ...)"
... or rename the column to something that isn't a keyword, preferrably...
See more on this question at Stackoverflow