VB.net / Oracle select statement from textbox input into datagrid

Question

I have a textbox and a datagrid. I am trying to enter a last name into the textbox and have that return the entire record from that last name into the datagrid from an oracle database. The code below currently returns all records in the table and is not searching by the last name.

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim myString As String
    Dim myArg As String
    Dim mysql As String
    Dim myConn As New OleDb.OleDbConnection()

    ' Create new connection
    myString = "Provider=msdaora;Data Source=XXXX;User Id=XXXX;Password=XXXX;"
    myConn.ConnectionString = myString
    myArg = TextBox1.Text
    mysql = "select * from table1 where lastname like '%'"
    Try
        myConn.Open()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

    Dim myDataadapter As New OleDb.OleDbDataAdapter(mysql, myConn)

    Dim myDs As New DataSet

    myDataadapter.Fill(myDs, "customer")

    Dim prmLName As New OleDb.OleDbParameter()
    prmLName.Direction = ParameterDirection.Input
    prmLName.Size = "10"
    prmLName.Value = TextBox1.Text


    DataGridView1.DataSource = myDs
    DataGridView1.DataMember = "customer"
End Sub
End Class

Jim O'Neil · Accepted Answer · 2012-09-26 06:16:13Z

1

Private Sub Search1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search1.Click
   Dim connectionstring As String = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=dps; User=root;Password=xxxxx;Option=3;"
   Dim conn As New OdbcConnection(connectionstring)
   conn.Open()

   Dim da As New OdbcDataAdapter("select * from Student where Grade ='" & Label1.Text & "' and lastName like '%" & txtLastName.Text & "%'", conn)
   Dim ds As New DataSet
   da.Fill(ds)
   DataGridView1.DataSource = ds.Tables(0)
   DataGridView1.Refresh()

   conn.Close()

edited Sep 26, 2012 at 6:16

Jim O'Neil

23.8k7 gold badges46 silver badges69 bronze badges

answered Sep 26, 2012 at 5:55

Saito Najumi

111 bronze badge

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

Comments

Sandeep Pathak · Accepted Answer · 2011-11-14 05:40:03Z

0

You didn't pass parameter value for the lastname , so Its taking it as a null and returning all the records :

mysql = "select * from table1 where lastname like '%"

Here you would do like :-

 mysql = "select * from table1 where lastname like '%" & txtLastName.Text & "%'"

I will also suggest you to pass parameter by SqlCommand.Parameters Property to avoid SQL Injection.

edited Nov 14, 2011 at 5:40

answered Nov 14, 2011 at 5:34

Sandeep Pathak

10.8k8 gold badges47 silver badges59 bronze badges

Comments

Sai Kalyan Kumar Akshinthala · Accepted Answer · 2011-11-14 05:39:25Z

0

You should write your select query as follows.

mysql = "select * from table1 where lastname like '%" & myArg & "'"

answered Nov 14, 2011 at 5:39

Sai Kalyan Kumar Akshinthala

11.9k8 gold badges45 silver badges68 bronze badges

1 Comment

Christopher Over a year ago

Thanks for your answer as well skk!

Collectives™ on Stack Overflow

VB.net / Oracle select statement from textbox input into datagrid

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related