Trying to list data in ms sql using vb.net

Question

Below is the code that I'm using in ms access to list the data according to what is selected in combobox and what is input in textbox.

My problem is there no commandbuilder in ms sql like in ms access, because it fails when I try:

dim mscombuilder as new sqlcommandbuilder

Here is the code:

''#list school
If ComboBox1.SelectedItem = "School" Then
    Dim connectionString As String = "Data Source=SENBONZAKURA\SQLEXPRESS;Initial Catalog=testing;User ID=SenbonZakura\Rew; Trusted_Connection=True;"

    Dim selectCommand As String
    Dim sqlcon As New SqlConnection(connectionString)

    selectCommand = "select * from student WHERE (SCHOOL='" & TextBox1.Text & "')"

    Me.dataAdapter = New OleDbDataAdapter(selectCommand, connection)
    Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)

    Dim table As New DataTable()   
    table.Locale = System.Globalization.CultureInfo.InvariantCulture

    Me.dataAdapter.Fill(table)  
    Me.BindingSource1.DataSource = table

    Dim data As New DataSet()  
    data.Locale = System.Globalization.CultureInfo.InvariantCulture

    DataGridView1.DataSource = Me.BindingSource1   
    Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.White

    Me.DataGridView1.AutoResizeColumns( _
           DataGridViewAutoSizeColumnsMode.AllCells)

-There is no such thing as commandbuilder in the options that will appear. Do you know of any alternative of commandbuilder in ms sql?

Answer 1

I hate to put you down like this, but there are just so many things wrong here:

• You can't mix the system.data.sqlclient classes and the system.data.oledb classes! Pick one provider or the other, not both.
• SCHOOL='" & TextBox1.Text & "')" NEVER substitute user input directly into a query like that! It's a major security hole. What if I enter ';DROP TABLE Student;-- into your TextBox1?
• You don't properly check that your connection will be closed. If an exception is thrown or anything else unexepected occurs it will be left open, eventually causes your database to choke.
• Don't set your bind your datasource to your grid until after you set styles for the grid. Otherwise you might end up drawing your grid twice.
• Don't hard-code your connection string in a local variable. At least factor it out into a common data layer somewhere.

You want something more like this:

''#list schools
If ComboBox1.SelectedItem = "School" Then

    Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.White
    Me.DataGridView1.AutoResizeColumns( _
         DataGridViewAutoSizeColumnsMode.AllCells)

    Using cn As New SqlConnection(LoadConnectionStringFromConfigFile()), _
          cmd As New SqlCommand("SELECT * FROM Student WHERE School= @School")

        cn.Open()
        cmd.Parameters.AddWithValue("@School", TextBox1.Text)

        DataGridView1.DataSource = cmd.ExecuteReader()

        DataGridView1.DataBind() ''#ASP.Net only - don't do this line in winforms
    End Using

And if you really want to do it right, you'll factor out all code that talks to the database to a completely separate class, and then your databinding code is even simpler:

''#list schools
If ComboBox1.SelectedItem = "School" Then

    Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.White
    Me.DataGridView1.AutoResizeColumns( _
         DataGridViewAutoSizeColumnsMode.AllCells)

    DataGridView1.DataSource = MyDataClassVar.GetStudentsInSchool(TextBox1.Text)