I am getting an exception with vb.net when certain punctuations are used

Question

I am not a coder, I'm a teacher. My sole aim is to code a simple educational application for my students. I was into programming for like 20 years ago but it's all so foggy now. So with this example, vb.net is throwing an exception only when I use some punctuations while recording a text to sql database.

A screenshot of the code:

con.Open()

    cmd = con.CreateCommand
    cmd.CommandType = CommandType.Text
    cmd = New SqlCommand("Select * from Dictionary where Id = " & intIdCheck & "", con)


    Dim result As DialogResult = MessageBox.Show("This word already exists in the Dictionary. Do you want to edit this entry?", "Entry Exists!", MessageBoxButtons.YesNo)

    If result = vbYes Then

        cmd.CommandText = "Update Dictionary  
        SET English = '" & TextBox1.Text & "',
        Turkish1 = '" & TextBox2.Text & "',
        TUrkish2 = '" & TextBox3.Text & "',
        Turkish3 = '" & TextBox4.Text & "',
        Turkish4 = '" & TextBox5.Text & "',
        Turkish5 = '" & TextBox6.Text & "',
        Turkish6 = '" & TextBox7.Text & "',
        Noun = '" & CheckBox1.CheckState & "',
        Verb = '" & CheckBox2.CheckState & "',
        Adjective = '" & CheckBox3.CheckState & "',
        Adverb = '" & CheckBox4.CheckState & "',
        NounSnt1 = '" & RichTextBox1.Text & "',
        NounSnt2 = '" & RichTextBox2.Text & "',
        VerbSnt1 = '" & RichTextBox3.Text & "',
        VerbSnt2 = '" & RichTextBox4.Text & "',
        AdjAdvSnt1 = '" & RichTextBox5.Text & "',
        AdjAdvSnt2 = '" & RichTextBox6.Text & "'
        where Id = " & intIdCheck & ""

        cmd.ExecuteNonQuery()

        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
        TextBox7.Text = ""
        CheckBox1.Checked = CheckState.Unchecked
        CheckBox2.Checked = CheckState.Unchecked
        CheckBox3.Checked = CheckState.Unchecked
        CheckBox4.Checked = CheckState.Unchecked
        RichTextBox1.Text = ""
        RichTextBox2.Text = ""
        RichTextBox3.Text = ""
        RichTextBox4.Text = ""
        RichTextBox5.Text = ""
        RichTextBox6.Text = ""
        intIdCheck = 0

    Else

        Exit Sub

    End If

So, the richtextbox controls are for sentences and textboxes are for words. Exception is thrown only when I update the database table with a sentence which includes certain punctuations, like an apostorophe '. Does anyone know what this is about and may they also offer me an easy and simple solution, as I am not so pro at coding.

Thanks in advance.

P.S: I am using visual studio 2019 and it says T-SQL on the interface.

You need to use parameters in your query (instead of just joining the input values directly into the SQL text). This will prevent these syntax errors and also protect your code from a type of hacking known as SQL Injection. There are various tutorials online which can show you what to do, including this one https://mobile.codeguru.com/columns/vb/using-parameterized-queries-and-reports-in-vb.net-database-applications.htm — ADyson, Dec 19 '20 at 18:15
Although you didn't phrase the question the same way, your issue is similar to this one: [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) — ADyson, Dec 19 '20 at 18:18
Yes please do. That is the way you should write all your queries you write from VB.net — ADyson, Dec 19 '20 at 18:44
Never again in your programming life should you ever consider NOT using parameters when it comes to passing data to a database... http://bobby-tables.com — Caius Jard, Dec 19 '20 at 18:44

I am getting an exception with vb.net when certain punctuations are used

0 Answers0