Exception Occur in VB.NET code

Question

I was doing VB.NET and use it to connect my SQL database.
Right now I want to insert the data into my database, yet after I finish my code it comes out this error:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

What does it means and what do I do wrong in my code?

Here is my code:

Imports System.Data.SqlClient
Public Class AddNew
Public con As New SqlConnection("Data Source=localhost;Initial Catalog=Tuition_Information;Integrated Security=True")
Public cmd As New Data.SqlClient.SqlCommand

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        cmd.CommandType = System.Data.CommandType.Text
        cmd.CommandText = "Insert Into Student_Info Value ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & ComboBox1.SelectedValue & "','" & ComboBox2.SelectedValue & "','" & TextBox7.Text & "','" & TextBox8.Text & "')"
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        MsgBox("New Student Added", MsgBoxStyle.Information, "add")

    Catch ex As Exception
        MessageBox.Show("Error occured, please try again.")
    End Try

End Sub

My Form looks like this:

Add New Form

My Database looks like this:

Not using parameters will do that to you, along with sql injection problems. Assuming SelectedValue is not null is a problem, too. It's VALUES, not VALUE. It's always safer to specify the field list to match the value list. — LarsTech, Apr 08 '16 at 20:52
[Student registration](http://stackoverflow.com/q/332365/11683), that is... — GSerg, Apr 08 '16 at 20:56

David T. Macknet · Accepted Answer · 2016-04-08T21:56:27.790

You're going to blow things up if you go about it the way you've got listed. What you need to do is to parameterize the query in some fashion - easiest way would be to create a stored procedure with named parameters. I say this will blow up because you do not account for the case wherein someone's name contains an apostrophe (and you can't tell me you won't have an O'Shannon or something), nor do you account for when a student is already registered. Something about that student must make them unique - like, maybe, Social Security Number? Which I assume you'll have during registration?

At that point, you could do something like:

create procedure SaveStudent
    @Student_Name nvarchar(128) ,
    @Home_Address nvarchar(256) ,
    @Contact_Number varchar(32) ,
    @Social_Security_Number char(11)
    ...
as

set nocount on
set rowcount 0

if exists ( select * from dbo.Students where Social_Security_Number = @Social_Security_Number )
    begin
        raiserror('Student with SSN %s already exists.', 16, 1, @Social_Security_Number) with nowait
    end
else
    begin
        insert into dbo.Students (
            Student_Name ,
            Home_Address ,
            Contact_Number ,
            Social_Security_Number ,
            ... )
        select
            @Student_Name ,
            @Home_Address ,
            @Contact_Number ,
            @Social_Security_Number ,
            ...
    end

go

grant execute on SaveStudent to WhateverApplicationUserYouAreUsing

With that stored procedure in place, you'd then be able to bind those parameters, and you wouldn't be vulnerable to someone maliciously entering data, nor would you fall over when you get the first name with an apostrophe in it.

Obviously, you'd have to then bind to the stored procedure, which would mean preparing parameters & setting their values. But you'd be in better shape, and not vulnerable to the Little Bobby Tables problem.

Exception Occur in VB.NET code

1 Answers1