Vb.Net: How to execute SQL query inisde datagrid?

Question

I have load a csv file using the code below

Dim fi
Dim conn
Dim adapter1 As New OleDbDataAdapter
Dim ds As New DataSet

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    fi = New FileInfo("C:\path\Book1.csv")
    conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fi.DirectoryName)
    conn.Open()
    adapter1.SelectCommand = New OleDbCommand("SELECT * FROM " & fi.Name, conn)
    adapter1.Fill(ds, "DATA")
    DataGridView1.DataSource = ds.Tables(0).DefaultView
End Sub

Now, I want to execute a SQL query like create, select, update, etc inside the gridview. For example, I want to create a new column "test" and fill that column with the values from column "a" and "b". But I get error. Can you please correct the code?

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    Try
        DataGridView2.Columns.Add("test", "test")
        adapter1.SelectCommand = New OleDbCommand("Update Datagridview1 SET test = a + b", conn)
        Dim dsA As New DataSet
        adapter1.Fill(dsA, "DATA")
        DataGridView2.DataSource = dsA.Tables(0).DefaultView
    Catch exp As Exception
        MsgBox("Error : " & exp.Message)
    End Try
End Sub

Error message: he Microsoft Jet database engine could not find the object 'Datagridview1.txt'. Make sure the object exists and that you spell its name and the path name correctly. — Nok Imchen
– Nok Imchen, Commented May 2, 2013 at 10:43
Do you want to create a new column on the database table or just on the datagridview? — Steve
– Steve, Commented May 2, 2013 at 10:44

Steve · Accepted Answer · 2013-05-02 10:55:33Z

1

I cannot test this code now, but usually you change the schema of the DataTable linked to the DataGridView to reflect the new column, and, in your specific case set the Expression property of the newly created column

Dim dv As DataView = CType(DataGridView2.DataSource, DataView)
Dim dc = dv.Table.Columns.Add("test", System.Type.GetType("System.String"))
dc.Expression = "[A] + [B]"

This strongly depends on the datatype of the columns A and B. Here I suppose that they are both strings

answered May 2, 2013 at 10:55

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Vb.Net: How to execute SQL query inisde datagrid?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related