ASP.NET

9-Jul-07 18:49

Hi, I'm trying to read a blob with the following code but got the error message"You have an error in your SQL syntax".

 Dim i As Integer<br />
            For i = 0 To ListBox2.Items.Count - 1<br />
                If ListBox2.Items(i).Selected Then<br />
                    Dim myCon As New OdbcConnection<br />
<br />
<br />
                    myCon = New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=myServer;Database=myDB;User=myUser;Password=myPass;Option=3;")<br />
                    Dim cmd As New OdbcCommand("SELECT * FROM myTable where Professions = 'ListBox2.Items(i).Text' ")<br />
<br />
                    myCon.Open()<br />
<br />
                    cmd.Connection = myCon<br />
<br />
                    Dim ds As New DataSet<br />
<br />
                    Dim ad As New OdbcDataAdapter(cmd)<br />
<br />
                    ad.Fill(ds)<br />
<br />
                    Me.ListBox2.DataSource = ds<br />
<br />
                    Me.ListBox2.DataTextField = "CategoryName"<br />
<br />
                    'Me.ListBox2.DataValueField = "CategoryID"<br />
<br />
                    Me.ListBox2.DataBind()<br />
                End If<br />
            Next<br />

I have looked up the Select statement and the where clause online to see if they are in the correct format. As far as I can see they appear to be in the correct format. Please help me determine the cause of the error, thank you in advance for your help.

-- modified at 1:44 Tuesday 10th July, 2007 Confused | :confused:

Sathesh Sakthivel9-Jul-07 19:00

Sathesh Sakthivel

9-Jul-07 19:00

Did you Run your Query in the SQL Server.

What error it shows?

Regards,

Satips. Rose | [Rose]

Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus

ASPnoob9-Jul-07 19:03

9-Jul-07 19:03

The error message that VS2003 shows is as follows.
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.41-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.Text' at line 1

Line 92: Dim ad As New OdbcDataAdapter(cmd)
Line 93:
Line 94: ad.Fill(ds)
Line 95:
Line 96: Me.ListBox2.DataSource = ds

Rhys Gravell9-Jul-07 19:46

9-Jul-07 19:46

Look more closely at the select statement you're constructing...
"SELECT * FROM myTable where Professions = 'ListBox2.Items(i).Text' "

You are not appending the Text property of ListBox2.Items(i), the WHERE clause is being set to the string literal 'ListBox2.Items(i).Text'.

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"

They say a little knowledge is a dangerous thing, but it's not one half so bad as a lot of ignorance."
Terry Pratchett

ASPnoob9-Jul-07 20:00

9-Jul-07 20:00

That's exactly what I wanted. The select statement is supposed to select my table where the name of the profession is equal to the text of the list item being selected. I'm trying to display the content of the field Resume Where the profession field is equal to list item being selected.

Rhys Gravell10-Jul-07 6:46

Re: Reading Blob [modified]

10-Jul-07 6:46

Look at it again. The query you're sending is;

SELECT * FROM myTable where Professions = 'ListBox2.Items(i).Text'

NOT

SELECT * FROM myTable where Professions = 'ExampleProfessionFromListBox'

ASPnoob10-Jul-07 8:25

10-Jul-07 8:25

Hi, in my database table the Resumes field stores resumes in blobs and each resume in the Resumes field has a corresponding profession which is stored in the Professions field. I am trying to enable users to search for resumes by selecting a single profession or many professions in the listbox. The select statement is supposed to select the resumes in mytable where the Professions field has the profession(s) that is/are equal to the text of the listitem(s) that the user has selected. Is this the wrong way to go about it? If it is how should I utilize the For-Next loop so that the listitem selected matches the profession in the Professions field? I tried your example but it didn't work, perhaps I did not know how to implement your example correctly. Please elaborate a little more and give another example. This is what I had done after seeing your example
("SELECT * FROM Contenttbl where Professions = 'ListBox2.Items(i).Text from ListBox2'")
Thank you again for your help.

-- modified at 14:39 Tuesday 10th July, 2007

Rhys Gravell10-Jul-07 10:12

Re: Reading Blob [modified]

10-Jul-07 10:12

You need to get the value of the Text property, (which is already a string type), and get that into your Sql string. Ideally you'd utilise stored procedures and pass in parameters, but we won't go there yet.

Your problem is here;
= 'ListBox2.Items(i).Text from ListBox2'

All this does is put the string value 'ListBox2.Items(i).Text from ListBox2' as your where clause. The statement;
ListBox2.Items(i).Text
...will get you the string value you want.

Try the below, (I usually use c# so it may not be syntactically spot on), and look at the difference between what you've got;
"SELECT * FROM Contenttbl where Professions = '" & ListBox2.Items(i).Text & "'"

ASPnoob10-Jul-07 11:44

10-Jul-07 11:44

Thank you so much for responding, I have tried your suggestion and it worked.

-- modified at 2:07 Wednesday 11th July, 2007

Rhys Gravell10-Jul-07 20:36