Use Variable in query

Question

I have an OleDbAdapter attached to a datagridview.
I wish to take the value in a textbox to use in a query to fill the datagridview.
Eg. The following works: David Carter in this instance is typed into the Query as a constant.
OleDbDataAdapter1.SelectCommand.CommandText = "Select * From InfringementRecords Where([INFStudent] = 'David Carter')"
But I don't want David Carter to be fixed in the query, I want to use a variable as entered into a textbox
Dim SelectName as string
SelectName = Textbox1.text
OleDbDataAdapter1.SelectCommand.CommandText = "Select * From InfringementRecords Where([INFStudent] = " & SelectName & ")"
That gives me an error:
Syntax error (missing operator) in query expression '([INFStudent] = David Carter)'.
Does anyone know how to use a variable in a query?
Visual Studio 2008.
Regards,
David

Satyapriya Nayak · Answer

Hi David Here is the vb code below:- Imports System.Data Imports System.Data.OleDb Public Class Form1 Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings(dsn) Dim con As OleDbConnection Dim com As OleDbCommand Dim oledbda As OleDbDataAdapter Dim ds As DataSet Dim str As String Private Sub Form1_Load(ByVal sender As System.Object ByVal e As System.EventArgs) Handles MyBase.Load bind() End Sub Sub bind() con = New OleDbConnection(ConnectionString) con.Open() Str = select * from employee com = New OleDbCommand(Str con) oledbda = New OleDbDataAdapter(com) ds = New DataSet() oledbda.Fill(ds employee) DataGridView1.DataSource = ds DataGridView1.DataMember = employee con.Close() End Sub Private Sub TextBox1_TextChanged(ByVal sender As System.Object ByVal e As System.EventArgs) Handles TextBox1.TextChanged con = New OleDbConnection(ConnectionString) con.Open() str = select * from employee where empname like ' & TextBox1.Text & %' com = New OleDbCommand(Str con) oledbda = New OleDbDataAdapter(com) ds = New DataSet() oledbda.Fill(ds employee) DataGridView1.DataSource = ds DataGridView1.DataMember = employee con.Close() End Sub End Class Thanks If it helps you plz mark it as Accepted answer

David Carter · Answer

Thank you very much. The line I needed was: OleDbDataAdapter1.SelectCommand.CommandText = Select * From InfringementRecords Where([INFStudent] = ' & SelectName & ') I was missing the ' after the = sign and before the ) The % seemed to be incorrect. Regards David

David Carter · Answer

Thanks for your efforts. I'm using VB. Regards David

Satyapriya Nayak · Answer

Hi David This is a simple application i did it of your requirement.Run the attachments. Code:- using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace Display_data_in_datagridview_with_textbox { public partial class Form1 : Form { string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings[dsn]; OleDbCommand com; OleDbDataAdapter oledbda; DataSet ds; string str; public Form1() { InitializeComponent(); } private void Form1_Load(object sender EventArgs e) { bindgrid(); } void bindgrid() { OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = select * from employee; com = new OleDbCommand(str con); oledbda = new OleDbDataAdapter(com); ds = new DataSet(); oledbda.Fill(ds employee); dataGridView1.DataSource = ds; dataGridView1.DataMember = employee; con.Close(); } private void textBox1_TextChanged(object sender EventArgs e) { OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = select * from employee where empname like ' + textBox1.Text + %'; com = new OleDbCommand(str con); oledbda = new OleDbDataAdapter(com); ds = new DataSet(); oledbda.Fill(ds employee); dataGridView1.DataSource = ds; dataGridView1.DataMember = employee; con.Close(); } } } Thanks If it helps you plz mark it as Accepted answer