0

My error is:

System.Data.OleDb.OleDbException: 'Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.'

Here is my code, and i checked to make sure all those fields exist in my access datatbase and they do. some are out of order but that shouldnt be an issue right?

command.CommandText = "if not  exists ( select PatientID from patienttable where PatientID = " + PatientID.Text + " ) begin INSERT INTO patienttable VALUES (" + Convert.ToInt32(PatientID.Text) + "','" + OldID.Text + "','" 
                    + PtLastName.Text 
                    + "','" + PtPreviousLastName.Text + "','" + PtFirstName.Text + 
                    "','" + HomeAddress1.Text + "','" 
                    + HomeAddress2.Text + "','" + HomeCity.Text
                    + "','" + HomeStateProvinceRegion.Text + "','" 
                    + HomeZip.Text + "','" + Country.Text + "','" 
                    + Citizenship.Text + PtHomePhone.Text + "','"
                    + EmergencyPhoneNumber.Text + "','" +
                     PtHomeFax.Text+  "','"  +Pager.Text+ "','" + EmailAddress.Text 
                    + "','" + PtSS.Text + "','" 
                    + DOB.Text + "','" + Gender.Text + "','"
                    + EthnicAssociation.Text + "','" + Religion.Text + EthnicAssociation.Text + "','" + MaritalStatus.Text + "','"
                    + "','" + EmploymentStatus.Text + "','" + HospitalMR.Text + "','" + DateofExpire.Text + "','" + Referral.Text + "','" + CurrentPrimaryHCPId.Text + "','" + Specialist1.Text + "','" + Specialist2.Text + "','" + Specialist3.Text + "','" + Specialist4.Text + "','" + OriginalStaffHCP.Text + "','" + CurrentStaffHCP.Text +  "','" + Active.Checked + "','"
                    + Comments.Text + "','" + DateEntered.Text 
                    
                    + "','" + BusinessPhone.Text + "','" + BusinessFax.Text + "','" + SubscriberRelationship.Text + "','" + EmployerID.Text + "','" + NextOfKinID.Text + "','" + PtMiddleInitial.Text + "','" + NextOfKinRelationshipToPatient.Text + "','" + NoticeOfPracticePrivacy.Checked + "','" + NPPDate.Text + "','" + Suffix.Text + "') end else begin update patienttable  set" +

                 "PatientID=" + PatientID.Text +
",OldID="+ "'"+ OldID.Text +"'"+
",PtLastName='" + PtLastName.Text + "'" +
",PtPreviousLastName='" + PtPreviousLastName.Text + "'" +
",PtFirstName='" + PtFirstName.Text +"'" +
",HomeAddress1='" + HomeAddress1.Text +  "'" +
",HomeAddress2='" + HomeAddress2.Text +   "'" +
",HomeCity='" + HomeCity.Text +   "'" +
",HomeStateProvinceRegion='" + HomeStateProvinceRegion.Text +"'" +
",HomeZip='" + HomeZip.Text +"'" +
",Country='" + Country.Text +"'" +
",Citizenship='" + Citizenship.Text +"'" +
",PtHomePhone='" + PtHomePhone.Text +"'" +
",EmergencyPhoneNumber='" + EmergencyPhoneNumber.Text +   "'" +
",PtHomeFax='" + PtHomeFax.Text +  "'" +
",Pager='" + Pager.Text +      "'" +
",EmailAddress='" + EmailAddress.Text +                       "'" +
",PtSS='" + PtSS.Text +       "'" +
",DOB='" + DOB.Text +        "'" +
",Gender='" + Gender.Text +     "'" +
",EthnicAssociation='" + EthnicAssociation.Text +                  "'" +
",Religion='" + Religion.Text +   "'" +
",MaritalStatus='" + MaritalStatus.Text +                      "'" +
",EmploymentStatus='" + EmploymentStatus.Text +                   "'" +
",HospitalMR='" + HospitalMR.Text + "'" +
",DateofExpire='" + DateofExpire.Text +                       "'" +
",Referral='" + Referral.Text +   "'" +
",CurrentPrimaryHCPId    ='" + CurrentPrimaryHCPId.Text +                "'" +
",Specialist1='" + Specialist1.Text +"'" +
",Specialist2='" + Specialist2.Text +"'" +
",Specialist3='" + Specialist3.Text +"'" +
",Specialist4='" + Specialist4.Text +"'" +
",OriginalStaffHCP='" + OriginalStaffHCP.Text +                   "'" +
",CurrentStaffHCP=  '" + CurrentStaffHCP.Text +                    "'" +
",Active='" + Active.Text +     "'" +
",Comments='" + Comments.Text +   "'" +
",DateEntered='" + DateEntered.Text +"'" +
",BusinessPhone='" + BusinessPhone.Text +                      "'" +
",BusinessFax='" + BusinessFax.Text +"'" +
",SubscriberRelationship='" + SubscriberRelationship.Text +             "'" +
",EmployerID='" + EmployerID.Text + "'" +
",NextOfKinID='" + NextOfKinID.Text +"'" +
",PtMiddleInitial='" + PtMiddleInitial.Text +                    "'" +
",NextOfKinRelationshipToPatient   ='" + NextOfKinRelationshipToPatient.Text +     "'" +
",NoticeOfPracticePrivacy='" + NoticeOfPracticePrivacy.Text +            "'" +
",NPPDate='" + NPPDate.ToString() +    "'" +
",Suffix='" + Suffix.Text + "'" +
" where PatientID = " + PatientID.Text+

" end ";
Sara Rodriguez
  • 1
  • 1
  • 1
    Two words: SQL Injection. Obligatory [XKCD comic](https://xkcd.com/327/). – ProgrammingLlama Dec 10 '20 at 03:37
  • 1
    Print your SQL and try on SSMS – MichaelMao Dec 10 '20 at 03:38
  • it said the error was in the first line for the first + sign – Sara Rodriguez Dec 10 '20 at 03:44
  • 1
    **Read the duplicate - that is what you must do.** But also look at the contents of `command.CommandText` (and as @MichaelMao says run it in Access) since I looked at the code for 10 seconds and spotted the mismatched `'`. – mjwills Dec 10 '20 at 03:46
  • where is the mismatched ' i also cant find a duplicate im sorry its my first class learning c# and i've been trying to fix this error for hours – Sara Rodriguez Dec 10 '20 at 03:51
  • This seems likely to be wrong: `Citizenship.Text + PtHomePhone.Text` – ProgrammingLlama Dec 10 '20 at 03:53
  • I noticed thank you but it still gives an error. it says the first + sign in the first line has an error – Sara Rodriguez Dec 10 '20 at 03:55
  • But note the bigger problem here: if `txtUserName.Text` contains `"Picard"`, then a query built using `"SELECT Id FROM Users WHERE Name = '" + txtUserName.Text + "'"` will work fine and produce `"SELECT Id FROM Users WHERE Name = 'Picard'"`, but if `txtUserName.Text` contains `"T'Pol"`, then the resulting query will be `"SELECT Id FROM Users WHERE Name = 'T'Pol'"`, which isn't valid SQL because the string ends at `'T'`. The solution to this is parameterized SQL queries. – ProgrammingLlama Dec 10 '20 at 03:55
  • John are you implying to remove the single ' in the " " ? so instead it should be "SELECT Id FROM Users WHERE Name = '" + txtUserName.Text + " " Im sorry im trying very hard to understand – Sara Rodriguez Dec 10 '20 at 03:58
  • No. I'm saying that a wider problem is that any user input containing `'` will break your code. Rather, users can change their input to a carefully crafted SQL injection attack, so long as they escape the string first with a `'`. Even if you fix this current issue, user input will at best break your SQL query, and at worst be a carefully crafted attack which leaves your entire database vulnerable. – ProgrammingLlama Dec 10 '20 at 04:05
  • john i understand the error that it could cause if someone types " T ' Pol " but this is just s small project for school im just trying to fix the error which is underlined red for the first plus sign + on the first line – Sara Rodriguez Dec 10 '20 at 04:05
  • OK, well please can you provide the resulting SQL query as text (in addition to the code you're using to build it). Thanks! – ProgrammingLlama Dec 10 '20 at 04:05
  • im sorry im new to this. it googled how to do that and i changed the query results to print to text and when i hit execute it said "query completed with errors" but when i check the documents folder i dont see any files (i made the destination there) I noticed the patientID next to the convert.toint32 is also underlined red – Sara Rodriguez Dec 10 '20 at 04:15
  • Put a breakpoint on the line under the end of "end "; and then hover over `command.CommandText` and click the magnifying glass. – ProgrammingLlama Dec 10 '20 at 04:19
  • Do you want me to do this in visual studio or SSMS – Sara Rodriguez Dec 10 '20 at 04:29
  • I'm assuming you meant visual studio. heres the text when i hit the maginifying glass. I manually inputed information for all the fields – Sara Rodriguez Dec 10 '20 at 04:35
  • if not exists ( select PatientID from patienttable where PatientID = 1232 ) begin INSERT INTO patienttable VALUES (1232','Sara','Garcia','Rodriguez','Beca','111 True St','','Hammond','IN','46327','USA','legal210753453','2134335444','219273','234','sdfshj@gmail.com','2323333333','Wednesday, December 9, 2020','Female','Hispanic','ChristianHispanic','Single','','employed','12345','Wednesday, December 9, 2020','Beca','234','tom','Jerry','Erika','Jon','John','Current Staff HCP','False','Comments','Wednesday, December 9, 2020','Business Phone','3246546','Sister','1234','123','a','mother','True', – Sara Rodriguez Dec 10 '20 at 04:36
  • 'Wednesday, December 9, 2020','Suffix') end else begin update patienttable setPatientID=1232,OldID='Sara',PtLastName='Garcia',PtPreviousLastName='Rodriguez',PtFirstName='Beca',HomeAddress1='111 True St',HomeAddress2='',HomeCity='Hammond',HomeStateProvinceRegion='IN',HomeZip='46327',Country='USA',Citizenship='legal',PtHomePhone='210753453',EmergencyPhoneNumber='2134335444',PtHomeFax='219273',Pager='234',EmailAddress='sdfshj@gmail.com',PtSS='2323333333',DOB='Wednesday, December 9, 2020',Gender='Female',EthnicAssociation='Hispanic',Religion='Christian',MaritalStatus='Single', – Sara Rodriguez Dec 10 '20 at 04:38
  • Referral='Beca',CurrentPrimaryHCPId ='234',Specialist1='tom',Specialist2='Jerry',Specialist3='Erika',Specialist4='Jon',OriginalStaffHCP='John',CurrentStaffHCP= 'Current Staff HCP',Active='',Comments='Comments',DateEntered='Wednesday, December 9, 2020',BusinessPhone='Business Phone',BusinessFax='3246546',SubscriberRelationship='Sister',EmployerID='1234',NextOfKinID='123',PtMiddleInitial='a',NextOfKinRelationshipToPatient ='mother',NoticeOfPracticePrivacy='',NPPDate='System.Windows.Forms.DateTimePicker, Value: 12/9/2020 10:25:46 PM',Suffix='Suffix' where PatientID = 1232 end – Sara Rodriguez Dec 10 '20 at 04:39
  • `INSERT INTO patienttable VALUES (1232',` Look at that bit. Carefully. – mjwills Dec 10 '20 at 06:08

0 Answers0