How to compare TextBox.Text and DataBase fields

[Tenzen]

I try to compare TextBox.Text and DataBase fields.

I have an answer in TextBox.Text from User Control and table with several columns from Access. I want to check if in column is the same answer like in TextBox. How to do that? I added table to DataGridView but how to access to fields by ID and Column Name?

Or maybe there is a better way to compare that?

 

[Tenzen]

Or how to find something in database? Maybe using query?

 

[jmcilhinney]

If you don't actually want the user to view the data then you should just execute a simple query:

SELECT COUNT(*)
FROM MyTable
WHERE ID = @ID
AND Answer = @Answer

Executing that will return 1 is there's a match and 0 otherwise.

If you don;t know how to work with databases in VB.NET then I suggest that you follow the Data Walkthroughs link in my signature and do some reading.

 

[Tenzen]

Thank you for query and links.
Maybe one more quick question. How to execute that query from code? Can I use the variables to it? Like:
Dim MyTable As String
Dim ID As Integer
Dim Answer As String

 

[jmcilhinney]

Exactly how you execute the query depends on how you've designed your data access. Are you using a typed DataSet? If so then you'd add a query to it in the designer and then call the corresponding method in code. If not then you'd create an OleDbConnection and an OleDbCommand and then call ExecuteScalar on the command.

 

[Tenzen]

Yes. I have DataSet with my tables. I can add query to every table and call it like [..]tebleAdapter.fill()?
But I have a lot of tables and I think this would be better then adding query from desiner:

Dim oCn As New SqlConnection("data source=(local);database=Northwind;user id=me;")
Dim oCmd As New SqlCommand("SELECT CompanyName FROM Customers", oCn)

oCn.Open()

Dim oRdr As SqlDataReader
oRdr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

While oRdr.Read
ListBox1.Items.Add(oRdr(0))
End While

oRdr.Close()
oCmd.Dispose()
oCn.Close()
oCn.Dispose()

Write an entire row to a ListBox and from there would be easy or add filer and get answer.

but I have problem here with connection to my Access database file.
How to connect it if it is here:
C:\database.accdb
with password = ppp

I try that:

Dim oCn As New SqlConnection("data source=C:\database.accdb;database=database.accdb;password=ppp;Integrated Security=True")

but does not connect. How to do it correctly?

 

[jmcilhinney]

Why would writing code for each and every table be better than adding a query to the designer for each and every table? I don't see the advantage to writing code when you already have a typed DataSet.

Anyway, your problem is that you're suing SqlClient to access the database, which is only for SQL Server. For Access you use OleDb.

 

[Tenzen]

I see...

I wanted to use it for two reasons.

I need to find result from several column from several tables and I think I could do that:

Dim MyTable As String = "CompanyName"
Dim MyColumn As String = "Customers"

Dim oCmd As New SqlCommand("SELECT " & MyTable & " FROM " & MyColumn", oCn)

And I cannot control index after DataBindings

ListBox1.DataSource = MyTableBindingSource
ListBox1.DisplayMember = "MyColumn"
ListBox1.ValueMember = "MyColumn"

Me.MyTableTableAdapter.Fill(Me.MyDataSet.MyTable)

I cannot do that in DataBindings mode with I thikt that would be posible with that SQL Reader:

Dim i As integer = 0
TextBox1.Text = ListBox1.Items.Item(i)

Conversion from type 'DataRowView' to type 'String' is not valid.

And overloads errors.

Can I do something like this:

Dim Filter As String = "value"
Me.MyTableTableAdapter.FillWithMyFilter(Me.MyDataSet.MyTable)

 

[cjard]

I think it's getting towards the time that you post the problem youre trying to solve, not the problem you've hit implementing your imagined solution

Say what you've got, and where you are, and where you want to be.. And someone will help point you in the right direction

 

[Tenzen]

Yes. That in point of fact is teoretical reflection. I solved my problem. I was just wondering is there a better way to do it

And my question is how to use vb.net variables in queries.