OleDBCommand SQL Syntax help

[kAnne]

Hi,

Im quite familar with VB.NET since my transition from VB6.

However, Im having trouble using a variable name in the SQL syntax for the OleDBCommand.

I want to be able to select a table using a variable rather than a set name.

e.g

dim tablename as string

SELECT * FROM tablename

well, you get the general idea!

Thanks for any help

 

[albertkhor]

Hi,

Im quite familar with VB.NET since my transition from VB6.

However, Im having trouble using a variable name in the SQL syntax for the OleDBCommand.

I want to be able to select a table using a variable rather than a set name.

e.g

dim tablename as string

SELECT * FROM tablename

well, you get the general idea!

Thanks for any help

Sure you can do that~
Dim SQL as String = "SELECT * FROM " & tablename
Dim cmdSelect As New OleDbCommand(SQL, sqlCeConn)

 

[kAnne]

Sure you can do that~
Dim SQL as String = "SELECT * FROM " & tablename
Dim cmdSelect AsNew OleDbCommand(SQL, sqlCeConn)[/QUOTE]

Thanks for your reply, but...it wont work..I think its me being a bit lame!

Right, ill post how ive done it already and hopefully some lovely person will tell what the hell is wrong!!

cmd = New OleDbCommand("SELECT * from & tablename WHERE subject = & subject", con)

My error trapping is telling me I have an invalid FROM clause.

 

[mechwarrior3]

Sure you can do that~
Dim SQL as String = "SELECT * FROM " & tablename
Dim cmdSelect AsNew OleDbCommand(SQL, sqlCeConn)[/QUOTE]

Thanks for your reply, but...it wont work..I think its me being a bit lame!

Right, ill post how ive done it already and hopefully some lovely person will tell what the hell is wrong!!

cmd = New OleDbCommand("SELECT * from & tablename WHERE subject = & subject", con)

My error trapping is telling me I have an invalid FROM clause.

Your line of code is very close. It should read:
"SELECT * from" & tablename & "WHERE subject = " & subject

I believe that will now work.

 

[DavidT_macktool]

Build your select statement as a string - spaces count...


Dim SelSTMT As String = "SELECT * from " & tablename & " WHERE subject = " & subject
cmd = New OleDbCommand(SelSTMT, con)

 

[kAnne]

Dim SelSTMT As String = "SELECT * from " & tablename & " WHERE subject = " & subject
cmd = New OleDbCommand(SelSTMT, con)

Now im getting a syntax error in FROM clause error message

 

[DavidT_macktool]

Where do you assign values to tablename and subject. Are they Strings?
Dim tablename As String
Dim subject As String

tablename = ???
subject = ???
Let's see that code.

 

[kAnne]

Well, it gets confusing because im using fscommand to communicate with Flash in order to display questions and answers from a database onto a new form.

Ill show you what I have so far...... (forgive me if its crap, ive never used flash and VB togther before!)

con = New OleDbConnection("Provider=Microsoft.Jet.OleDB.4.0;Data Source= C:\cp3343.mdb;")
            Try
                [COLOR=DarkGreen]'open the connection[/COLOR]
                con.Open()
                'SQL Query
                e.command = "quiz_cat"
                tablename = e.args
                e.command = "quiz_subject"
                subject = e.args
                Dim SelSTMT As String = "SELECT * from " & tablename & " WHERE subject = " & subject
                cmd = New OleDbCommand(SelSTMT, con)

               [COLOR=DarkGreen] 'Send the SQL and build an OleDbDataReader.[/COLOR]
                reader = cmd.ExecuteReader
              While reader.Read()
                    F2.question.Text = reader(2)
                    F2.answer1.Text = reader(3)
                    F2.answer2.Text = reader(4)
                    F2.answer3.Text = reader(5)
                End While
                reader.Close()
                con.Close()

 

[DavidT_macktool]

My guess is the values for tablename and subject are messing with you.

Add this to see what you are actually assigning to those strings.

MsgBox(tablename)
MsgBox(subject)

 

[kAnne]

I think your right...im getting nothing back.

 

[DavidT_macktool]

http://www.vbdotnetforums.com/showthread.php?t=3446

or

http://www.flashfanatiker.de/blog/archives/000032.html#more

 

[kAnne]

Thanks for your help (and links!). I'll try and figure it out somehow

 

[TechGnome]

Assuming that Subject is a string as well... It HAS TO be like this:

Dim SelSTMT As String = "SELECT * from " & tablename & " WHERE subject = '" & subject & "'"

That's double quote " and a single quote ' and another double quote " on the end of that.

Better than messageboxing the table name and subject, also messagebox.show the resulting SQL after you have built it.

-tg

 

[kAnne]

Finally!!!! Thankyou so much TechGnome...I can go and eat now (this things been driving me crazy, and all over a few quotes!)