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

maintain values of a textbox on Postback

10-Jul-07 20:36

Sorry if this sounds patronising, but I'd say the important thing here is to see and understand the difference in both statements so do take the time to make sure you do, it'll be far more helpful in the long run Smile | :)

Archana New to Dotnet9-Jul-07 18:45

Archana New to Dotnet

9-Jul-07 18:45

Hi,
I have a textbox which gets value from a calender control.To avoid users from manually typing I have diabled the textbox,The problem is on Postback the Textbox value i.e the date value is getting cleared.

I have also tried it using Readonly Property same problem.

Can any one please give me a solution ,

Thanks is Advance.

Archana

MaheshSharma9-Jul-07 19:00

MaheshSharma

9-Jul-07 19:00

I thing you have you set the autopostback property of textbox set to false

cheers

Archana New to Dotnet9-Jul-07 19:06

Archana New to Dotnet

9-Jul-07 19:06

No ,I didnt set the Autopost back,Since Im getting the value from calender control the page is automatically getting refreshed.

MaheshSharma9-Jul-07 19:09

MaheshSharma

9-Jul-07 19:09

Set the autopostback = false in aspx page check it

kumar.a9-Jul-07 20:15

kumar.a

9-Jul-07 20:15

hi its very fine

N a v a n e e t h9-Jul-07 19:09

N a v a n e e t h

9-Jul-07 19:09

View state is maintaining value on postbacks. So try disabling viewstate for the textbox.

My Website | Ask smart questions

Michael Sync9-Jul-07 20:04

Michael Sync

9-Jul-07 20:04

How did you disable this textbox? There is a reason why i ask this question. There are some differences between disabling textbox explicitly and disabling parent container control (eg: Panel or DIV).

Thanks and Regards,
Michael Sync ( Blog: http://michaelsync.net)