problem with SELECT statement and WHERE

i am using visual web developer 2005 and SQL server 2005 with VB as the code behind

i am using the following code to retrieve data from the database and display in textboxes and it works fine

Dim tblData As New Data.DataTable()
Dim conn As New Data.SqlClient.SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;IntegratedSecurity=True;User Instance=True")
Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM Table1 ", conn)
Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)
conn.Close()
TextBox4.Text = tblData.Rows(0).Item("name").ToString()
TextBox5.Text = tblData.Rows(0).Item("age").ToString()
TextBox6.Text = tblData.Rows(0).Item("email").ToString()

now i want to display data in the text boxes that match certain criteria.i modified the code like this but it did not work

Dim tblData As New Data.DataTable()
Dim conn As New Data.SqlClient.SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;IntegratedSecurity=True;UserInstance=True")
Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM Table1 WHERE name=peter", conn)
Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)
conn.Close()
TextBox4.Text = tblData.Rows(0).Item("name").ToString()
TextBox5.Text = tblData.Rows(0).Item("age").ToString()
TextBox6.Text = tblData.Rows(0).Item("email").ToString()

it gave the result as Invalid column name 'peter'.

but if i give as SELECT * FROM Table1 WHERE id=1result is displayed correctly

but id is the primary key whereas name is not a primary key.

please help me how to display the values corresponding to the name field

i would think your "Select * from table1 where name = peter"
should be
"Select * From Table1 where name = 'peter'"

notice the single quotes on around peter as your querying a char field

HTH,
AjaxButter

|||

Try putting single quotes around peter like this 'peter'. The SQLAdapter is interrepting the string peter as a column name and not a value. The reason it works with the id is because id is most likely a numeric type value (IE: int or decimal, etc.) so single quotes are not needed.

Dapanther

|||

AjaxButter,

Beat again by my slow typing LOL

Dapanther

|||

if peter is just a substring and i want all the rows containing peter as a substring in name field what will i do

please help me

|||

If you need peter as a substring then there are the follwoing possiblities.

1. the name is "xyz peter"

For this the querry would be Select * From table1 where name like '%peter'

2. the name is "Peter xyz"

Here the querry would be like this Select * From table1 where name like 'Peter %'

3. The name could be "abc Peter xyz"

The querry would be as follows: Select * From table1 where name like '%peter%'

It all depends upon what you require. The % is a wild string search.

Please mark this as answer if the post help you.