davidlee9023

Hey I am a newbe about ado.net
Would you guys check this code up for me



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
ListBox3.Items.Clear()
ListBox2.Items.Clear()
ListBox4.Items.Clear()





Dim myconnstring1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Application.Info.DirectoryPath & "\db1.mdb;Jet OLEDBBig Smileatabase Password=sa;"
Dim myinsertquery1 As String = "SELECT contents,code FROM list WHERE listname like '" & ComboBox1.Text & "' "
Dim myconnection1 As OleDbConnection = New OleDbConnection(myconnstring1)
Dim mycommand1 As OleDbCommand = New OleDbCommand(myinsertquery1, myconnection1)


myconnection1.Open()


Dim myreader1 As OleDbDataReader = mycommand1.ExecuteReader


Try

Do While myreader1.Read

ListBox4.Items.Add(myreader1.Item(1).ToString)



ListBox3.Items.Add(myreader1.Item(1).ToString + " " + myreader1.Item(2).ToString)
ListBox2.Items.Add(myreader1.Item(2).ToString)

Loop
Catch ex As OleDbException

MessageBox.Show(ex.Message)

End Try
myreader1.Close()
myconnection1.Close()

End Sub



Re: Visual Basic Language Ado.net oledb query problem!

kleinma

Why don't you try stating what the actual problem is that you are having. It is much easier for someone to provide an answer if they know exactly what your issue is.






Re: Visual Basic Language Ado.net oledb query problem!

davidlee9023

OK!

Dim myinsertquery1 As String = "SELECT contents,code FROM list WHERE listname like '" & ComboBox1.Text & "' "

My question is This parts!
I used this query in ado It worked well, but I tried it in ado.net It dosen,t work!

What shoud i do







Re: Visual Basic Language Ado.net oledb query problem!

Riquel Dong – MSFT

Hi Dacidlee9023,

Based on your post, my understanding of your question is that you need to use Like operator to determine whether a specific character string matches a specified pattern.

I recommend you use the parameters to implement the query. This will improve the readability of your code. Here is the code snippet to use Like operator with the paramaters to query the data. If you have any further questions, please tell me.

Code Block

Imports System.Data.OleDb

Public Class Form1

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

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb")

Dim sql As OleDbCommand = New OleDbCommand("SELECT * FROM Shippers WHERE CompanyName like @cname", con)

Dim ds As DataSet = New DataSet()

Dim DataAdapter1 As OleDbDataAdapter = New OleDbDataAdapter()

sql.Parameters.AddWithValue("@cname", TextBox1.Text)

con.Open()

DataAdapter1.SelectCommand = sql

DataAdapter1.Fill(ds, "Shippers")

DataGridView1.DataSource = ds

DataGridView1.DataMember = "Shippers"

con.Close()

End Sub

End Class

Best regards,

Riquel






Re: Visual Basic Language Ado.net oledb query problem!

Adam D. Turner

I would suggest not using a parameter. It does not enhance the readability of your code, provides no benefit, and is an incorrect approach. It defeats the purpose of the LIKE operator.

You most definitely do not want to use a wildcard in your SELECT *. Big performance killer. You do not want to fill an entire dataset for one field. Your approach with the datareader is fine.

My suggestion would be to implement the following:

Dim myinsertquery1 As String = "SELECT contents, code FROM dbo.List WHERE listname LIKE '%" & ComboBox1.Text & "%'"

Adam