Error in query or database

Question

Hi guys I have this problem in my string query in inserting data in c#. I know that my query is correct, because when I type some random words it saves. But when I typed the correct data in textbox it keeps getting error in the syntax. and my codes below

try
        {
            string connStr = "server = 127.0.0.1; uid = root; " + "pwd =; database = scco";
            string Query = " insert into scco.m_information (accno,tom,sname,gname,mname,gender,cno,father,mother,bday,age,email,educattain,cstatus,preadd,proadd,yres,residency,toj,comname,comadd,ccno,mincome,oincome,moincome,daccepted,ICS,BOD,DOP,NOS,AOS,YA,spname,spdad,spmom,stoj,scname,scadd,sccno,smi,nc1,stat1,kind1,inc1,allow1,nc2,stat2,kind2,inc2,allow2,nc3,stat3,kind3,inc3,allow3,nc4,stat4,kind4,inc4,allow4,nc5,stat5,kind5,inc5,allow5,nc6,stat6,kind6,inc6,allow6,befname,befrel,refname,refcno) values ('" + this.txtID.Text + "','" + tom + "', '" + this.txtSname.Text + "', '" + this.txtGname.Text + "', '" + this.txtMname.Text + "', '" + gender + "', '" + this.txtCno.Text + "', '" + this.txtDad.Text + "', '" + this.txtMom.Text + "', '" + this.dBirth.Text + "', '" + this.txtAge.Text + "', '" + this.txtEmail.Text + "', '" + this.cmbEducAttain.Text + "', '" + cstatus + "', '" + this.rtbPreAdd.Text + "', '" + this.rtbProAdd.Text + "', '" + this.txtYRes.Text + "', '" + residency + "','" + toj + "', '" + this.rtbComp.Text + "', '" + this.rtbCadd.Text + "', '" + this.txtCCno.Text + "', '" + this.txtMincome.Text + "', '" + this.txtOSincome.Text + "', '" + this.txtIncome.Text + "','" + this.dAccepted.Text + "', '" + this.txtIcs.Text + "', '" + this.txtBod.Text + "', '" + this.txtdop.Text + "', '" + this.txtnos.Text + "', '" + this.txtaos.Text + "', '" + this.txtya.Text + "','" + this.txtSpName.Text + "', '" + this.txtSFname.Text + "', '" + this.txtSMname.Text + "', '" + stoj + "', '" + this.rtbpscomname.Text + "', '" + this.rtbspcomadd.Text + "', '" + this.txtspccno.Text + "', '" + this.txtspminc.Text + "', '" + this.txtChild1.Text + "', '" + this.cmbCStatus1.Text + "', '" + this.cmbKind1.Text + "', '" + this.txtA1.Text + "', '" + this.txtI1.Text + "', '" + this.txtChild2.Text + "', '" + this.cmbCStatus2.Text + "', '" + this.cmbKind2.Text + "', '" + this.txtA2.Text + "', '" + this.txtI2.Text + "', '" + this.txtChild3.Text + "', '" + this.cmbCStatus3.Text + "', '" + this.cmbKind3.Text + "', '" + this.txtA3.Text + "', '" + this.txtI3.Text + "', '" + this.txtChild4.Text + "', '" + this.cmbCStatus4.Text + "', '" + this.cmbKind4.Text + "', '" + this.txtA4.Text + "', '" + this.txtI4.Text + "', '" + this.txtChild5.Text + "', '" + this.cmbCStatus5.Text + "', '" + this.cmbKind5.Text + "', '" + this.txtA5.Text + "', '" + this.txtI5.Text + "', '" + this.txtChild6.Text + "', '" + this.cmbCStatus6.Text + "', '" + this.cmbKind6.Text + "', '" + this.txtA6.Text + "', '" + this.txtI6.Text + "','" + this.rtbBefName.Text + "','" + this.rtbBefRel.Text + "','" + this.rtbRefName.Text + "','" + this.rtbRefCno.Text + "');";
            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlCommand MyCommand = new MySqlCommand(Query, conn);
            MySqlDataReader MyReader;



            conn.Open();
            MyReader = MyCommand.ExecuteReader();


            while (MyReader.Read())
            {

            }
            conn.Close();

See this picture below.

Here's the output

Hello, and welcome to Stack Overflow. This is a question most of those who starts venturing into the world of SQL have. I'd suggest that you start reading some books that will catapult you in the right direction. Suggestions: https://www.amazon.com/SQL-Dummies-Allen-G-Taylor/dp/1118607961 and http://shop.oreilly.com/product/0636920023951.do — Eric Wu, Sep 14 '16 at 04:50

score 2 · Answer 1 · answered Sep 14 '16 at 04:28

2

To me it looks like something in your data has an apostrophe in it, so when it is being appended to your query string it is thinking that it is a SQL single quote. You will have to escape or translate the apostrophe first.

answered Sep 14 '16 at 04:28

Ben Krueger

1,476
1
14
20

score 2 · Answer 2 · answered Sep 14 '16 at 04:36

2

Use parameters to avoid SQL injection. Read below or the awesome comic above!

Good luck!

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx

answered Sep 14 '16 at 04:36

Kyle Moffat

163
1
2
17

score -1 · Answer 3 · answered Sep 15 '16 at 00:51

-1

add MyReader.Close() above conn.Close().

answered Sep 15 '16 at 00:51

Wiguna R

157
5
19

Error in query or database

3 Answers3