ExecuteReader - cannot assign sql query result to a txtbox

[Musab]

Hi all,

I'm trying to assign a result from a SQL query to my textbox, can anyone please tell me what I am doing wrong?

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim str As String

cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Musab\My Documents\Visual Studio 2005\Projects\StudentDataBase\StudentDataBase\db2000.mdb")
cn.Open()
str = "Select EName FROM Table1 WHERE Tabel1.Num = " & TextBox1.Text & "'"
cmd = New OleDbCommand(str, cn)
dr = cmd.ExecuteReader
While dr.Read
test.Text = dr(1)
End While
cn.Close()

its complaining about the excutereader function.
Syntax Error in string in query expression 'Tabel1.Num = 222".

 

[MattP]

Add an apostrophe at the end of your first string. I'd check to make sure you've got a table named Tabel also.

...WHERE Table1.Num = '" & TextBox1.Text & "'"

 

[JohnH]

Ever heard about parameters? Putting parameter values in the query string manually is moot.

 

[Musab]

Thank you...that works str = "Select EName FROM Table1 WHERE Table1.Num = '" & TextBox1.Text & "'"

 

[MattP]

Good to hear that it's working for you. I'd take JohnH's advice and look into using parameters.

Cjard wrote an excellent post about why your solution is hard to read, generates higher overhead, and could be subject to an injection attack. You can find the post here.

 

[Musab]

I have been reading that..this is really amazing, didn't know about it before !

But I'm still having a problem adding another reader

----------------------

str = "Select EName, Salary FROM Table1 WHERE Table1.Num = '" & TextBox1.Text & " ' Table.Salary = '" & TextBox3.Text & "'"

----------------------

I don't know where I'm missing those little things like Cjard said. I also don't understand how to apply that on my access db.

 

[InertiaM]

Given that this is the second problem you have had with the way you are writing queries, I seriously suggest you get to grips with parameters. Paramerised queries (PQs) are much more efficient and, more importantly, much safer as they avoid SQL injection.

Although I know the answer to your problem, I'm not going to give it as you will not learn unless you change to parameters. When you change your query to use parameters, it would look something like

Select EName, Salary FROM Table1 WHERE Table1.Num = @NUM AND Table.Salary = @SALARY

You would then set up parameters @NUM and @SALARY, and give them values.

I'll leave the rest for you to work out

 

[Musab]

LOL...I also forgot the AND..ok I'll keep trying until it works...

thanks

 

[Musab]

I got it to work but not with parameters. VB.net is complaining about the '@' as illegal character or something

but this works
str = "Select EName, Salary"
str &= " FROM Table1"
str &= " WHERE Table1.Num = '" & Me.TextBox1.Text & "'"
str &= " AND Table1.Phone = '" & Me.TextBox4.Text & "'"

 

[cjard]

VB.net is complaining about the '@' as illegal character or something

That's a great, specific, searchable, understandable error message. "Oh, it just said thre was an error or something. I couldnt really be bothered writing it down"

I have been reading that..this is really amazing, didn't know about it before !

But I'm still having a problem [doing it my old way]

So you really can take a donkey to water..



Have a read of the DW2 link in my signature, section on Creating a SImple Data App

 

[Musab]

That's a great, specific, searchable, understandable error message. "Oh, it just said thre was an error or something. I couldnt really be bothered writing it down"

You r so funny,

'Character not Valid'

str = "Select EName, Salary"
str &= " FROM Table1"
str &= " WHERE Table1.Num = '" & @NUM & "'"
str &= " AND Table1.Phone = '" & @Phone & "'"

So you really can take a donkey to water..

Yeah If I don't know how to drive ! Teach me !

Have a read of the DW2 link in my signature, section on Creating a SImple Data App

ok thanks !

 

[cjard]

str = "Select EName, Salary"
str &= " FROM Table1"
str &= " WHERE Table1.Num = '" & @NUM & "'"
str &= " AND Table1.Phone = '" & @Phone & "'"

str = "Select EName, Salary"
str &= " FROM Table1"
str &= " WHERE Table1.Num = @NUM "
str &= " AND Table1.Phone = @Phone "


Just.. take a look at it for a long time and work out what you did wrong

 

[Musab]

It doesn't work but I got the idea,thanks ! I will keep missing with it.

 

[cjard]

No, dont.. youre wasting soo much time that could be better spent reading the Dw2 link..