Groups

    What is wrong with this SQL statement

    the guys over at MSDN could not figure it out, im using the code in VB.net

    17 Comments

    Original Poster

    cmd.CommandText = cmd.CommandText = "SELECT … cmd.CommandText = cmd.CommandText = "SELECT tblArtists.ArtistID,tblArtists.Artist from tblArtists WHERE tblArtists.category= '" & cbmusiccategory.Text & "'"


    is the code.



    Private Sub cbmusiccategory_SelectedIndexChanged(ByVal sender As … Private Sub cbmusiccategory_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbmusiccategory.SelectedIndexChanged Dim cnn As OleDbConnection = New OleDbConnection cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Hunter\Desktop\Student resources\CDs.mdb" cnn.open() Dim cmd As OleDbcommand = New OleDbcommand cmd.CommandText = cmd.CommandText = "SELECT tblArtists.ArtistID,tblArtists.Artist from tblArtists WHERE tblArtists.category= '" & cbmusiccategory.Text & "'" cmd.commandtype = CommandType.Text cmd.connection = cnn Dim da As OleDbDataAdapter = New OleDbDataAdapter Dim dsartist As DataSet = New DataSet da.SelectCommand = cmd 'the line below has the error of Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. da.Fill(dsartist, "ArtistsTable") lbCds.DataSource = dsartist.Tables("ArtistsTable") lbCds.DisplayMember = "Artist" lbCds.ValueMember = "ArtistID" cnn.close() cnn.dispose() End Sub


    this is the sub that fails I get the error. "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

    tried everywhere and and no-one knows.

    Have you tried just running the statement via management studio? to make sure that this returns the results.

    If the code is as what you posted then...

    cmd.CommandText = cmd.CommandText = "SELECT tblArtists.ArtistID,tblArtists.Artist from tblArtists WHERE tblArtists.category= '" & cbmusiccategory.Text & "'"

    should really be...

    cmd.CommandText = "SELECT tblArtists.ArtistID,tblArtists.Artist from tblArtists WHERE tblArtists.category= '" & cbmusiccategory.Text & "'"

    without the extra @cmd.CommandText [email protected]

    Also, not sure about OLEDB (I use rdo mostly) but what does @cmd.commandtype = [email protected] do?

    Original Poster

    tomblack258;3905777

    Have you tried just running the statement via management studio? to make … Have you tried just running the statement via management studio? to make sure that this returns the results.



    hi there tom, strong first post and welcome to HUKD.

    I dont have management studio, but when i play the code and use the dropdown list from my combobox, it times out and i get the "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." error message.

    this leads me to believe the SQL is incorrect, ive checked for spelling mistakes and there are all spelt correctly.

    Original Poster

    AyrshireBacon;3905801

    If the code is as what you posted then...cmd.CommandText = … If the code is as what you posted then...cmd.CommandText = cmd.CommandText = "SELECT tblArtists.ArtistID,tblArtists.Artist from tblArtists WHERE tblArtists.category= '" & cbmusiccategory.Text & "'"should really be...cmd.CommandText = "SELECT tblArtists.ArtistID,tblArtists.Artist from tblArtists WHERE tblArtists.category= '" & cbmusiccategory.Text & "'"without the extra @cmd.CommandText [email protected], not sure about OLEDB (I use rdo mostly) but what does @cmd.commandtype = [email protected] do?


    ha ha sick guy, it worked repped. I

    thanks as well Tom.:thumbsup:

    I have repped both and you have both gone up one bar. its the power i have on this site.

    surprising what you need to apply McDonalds

    black gerbil1;3905822

    ha ha sick guy, it worked repped. I thanks as well Tom.:thumbsup:I have … ha ha sick guy, it worked repped. I thanks as well Tom.:thumbsup:I have repped both and you have both gone up one bar. its the power i have on this site.



    TYVM BG1.

    The general rule about coding round here is that no-one can spot their own mistakes UNTIL you show someone else... :thumbsup:

    Original Poster

    AyrshireBacon;3905848

    TYVM BG1.The general rule about coding round here is that no-one can spot … TYVM BG1.The general rule about coding round here is that no-one can spot their own mistakes UNTIL you show someone else... :thumbsup:


    yeah I know, ive been looking at it for the past few hours and didn't know what was wrong, thought id let someone else look at it, i thought it would be a basic error which i just couldn't see.

    :thumbsup:
    jellybaby22;3905856

    one bar...that all...wow...weak.......:)


    lol what you got brah.

    Original Poster

    i got 42k.

    Banned

    black gerbil1;3906089

    i got 42k.



    Only another 34K ]to catch up with good members then.

    Such weak rep.


    BWAHAHAHAHA

    guv;3906135

    Only another 34K ]to catch up with good members then.Such weak … Only another 34K ]to catch up with good members then.Such weak rep.BWAHAHAHAHA



    :w00t::w00t:

    Banned

    I have no rep, but I have a life

    black gerbil1;3905752

    cmd.CommandText = cmd.CommandText = "SELECT … cmd.CommandText = cmd.CommandText = "SELECT tblArtists.ArtistID,tblArtists.Artist from tblArtists WHERE tblArtists.category= '" & cbmusiccategory.Text & "'"



    Also switch over to using parameters for the condition - avoids injection nastiness.

    your database belong to er----------------

    Original Poster

    alot of trolls on here today.

    I agree

    black gerbil1;3907493

    alot of trolls on here today.



    indeed, how are you:-D
    tis the only way to increase your post count
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Magazine competitions - Issue 39 @ tvchoicemagazine.co.uk55
      2. Win 1 of 3 x £1000 Selfridges vouchers with Virgin Trains109
      3. Win a Free minion usb stick1010
      4. totalev.com comp - win an electric car2121

      See more discussions