Trouble with sql statement

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 1201 Views - Last Post: 28 May 2013 - 03:28 PM Rate Topic: -----

#1 mattcash83   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 50
  • Joined: 23-June 12

Trouble with sql statement

Posted 27 May 2013 - 04:16 PM

I left a personal project alone for a few months, working, and when I cam back to it I suddenly could not get an INSERT INTO command to work properly. I have checked to make sure the table was not moved on my computer, and the connection takes correctly. I do not work with sql too often, and I feel I may be overlooking something.

Here is the code snippet with the command:

                Con.Open()
                Dim cmdWrite As OleDb.OleDbCommand = New OleDb.OleDbCommand _                                                                                                                                               ("INSERT INTO MUSIC1 (Song, Band, Genre, Album, fullpath, Allcaps) VALUES ('" & Song & "', '" & Band & "', '" _             & Genre &  "', '" & Album & "', '" & p & "', '" & Song.ToUpper & "')", Con)
                cmdWrite.ExecuteNonQuery()
                Con.Close()




And here is the output and exception:

INSERT INTO MUSIC1 (Song, Band, Genre, Album, fullpath, Allcaps) VALUES (' Down', ' 3 1 1', ' Alternative', ' Playlist The Very Best Of 3 1 1', 'C:\Users\Matt Cashion\Music\MUSIC\311\311 - 01 Down.mp3', ' DOWN')

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

I am aware of the spaces preceding some of the values, could this throw the exception? Any help is excepted, thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Trouble with sql statement

#2 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1730
  • View blog
  • Posts: 5,709
  • Joined: 25-September 09

Re: Trouble with sql statement

Posted 27 May 2013 - 04:57 PM

you're specifying 6 fields and only 5 values.

Also, why the ALLCAPS field?

You can just perform the ToUpper on song when you need to display it in all capitals instead of duplicating the same data for a second field.

This post has been edited by CharlieMay: 27 May 2013 - 05:17 PM

Was This Post Helpful? 0
  • +
  • -

#3 mattcash83   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 50
  • Joined: 23-June 12

Re: Trouble with sql statement

Posted 27 May 2013 - 06:48 PM

Wow, I don't know how but I am not inserting the band's name. That will def take care of the problem, thanks for pointing out the obvious! Sometimes try to worry so much over technicality that I overlook obvious. The all caps field is for a special search function I have dreamt up. Again, thanks!

Uh Oh, that was not it. I actually am providing all six values.

This post has been edited by mattcash83: 27 May 2013 - 06:54 PM

Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1730
  • View blog
  • Posts: 5,709
  • Joined: 25-September 09

Re: Trouble with sql statement

Posted 28 May 2013 - 04:10 AM

OK, so what is the exact exception message?

If this code is in a form_load event, you are probably only getting the First Class exception message so to alleviate that, put the cmdWrite.ExecuteNonQuery into a try catch block and display the exception in the catch.
Something like:
Try
  cmdWrite.ExecuteNonQuery
Catch ex as Exception
  msgbox(ex.message) 
Finally
  con.close
End Try


Run the code and report back with the actual error.
Was This Post Helpful? 0
  • +
  • -

#5 mattcash83   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 50
  • Joined: 23-June 12

Re: Trouble with sql statement

Posted 28 May 2013 - 07:16 AM

I am sorry, I do run a try catch block and have the message come in on a mag box, I thought I posted that part. It tells me I have an INSERT INTO syntax error on the line that contains my cmdexecute. So the way see this, my connection is good, but something is wrong with how I have the SQL structured, do you see anything wrong with the output?

This post has been edited by mattcash83: 28 May 2013 - 07:16 AM

Was This Post Helpful? 0
  • +
  • -

#6 Linesofcode   User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 62
  • Joined: 23-May 13

Re: Trouble with sql statement

Posted 28 May 2013 - 07:38 AM

This is why, using prepared statements are a better approach.

Dim query As String = "INSERT INTO music1 (Song, Band, Genre, Album, fullpath, Allcaps) VALUES (@song, @band, @genre, @album, @path, @caps)"

Dim cmd As New OleDbCommand(query, Con) 
cmd.Parameters.Add("@song", OleDbType.VarChar).Value = Song
cmd.Parameters.Add("@band", OleDbType.VarChar).Value = band
' Etc
cmd.ExecuteNonQuery()

Was This Post Helpful? 1
  • +
  • -

#7 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1730
  • View blog
  • Posts: 5,709
  • Joined: 25-September 09

Re: Trouble with sql statement

Posted 28 May 2013 - 08:09 AM

I totally agree with Linesofcode parameters would be a much better way to go.

That being said, have you copied the INSERT statement that your concatenation produced and ran it as a Query in Access to see if Access hilights the area in question with the syntax error?
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1730
  • View blog
  • Posts: 5,709
  • Joined: 25-September 09

Re: Trouble with sql statement

Posted 28 May 2013 - 08:24 AM

Wrap the word Band inside of square brackets. Apparently, it is a keyword or something. I'll have to look it up.

Oddly enough this doesn't happen in Access.

INSERT INTO MUSIC1(Song, [Band], Genre .....

Was This Post Helpful? 1
  • +
  • -

#9 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: Trouble with sql statement

Posted 28 May 2013 - 08:29 AM

Is BAND a reserved word? list here.

I was thinking that the OP may have included the word GROUP somewhere along the line, but this would imply that he is not showing us his actual code (no offence!). Or that the song includes an apostrophe - another reason to use prepared statements.

There may be an embedded carriage return in the song, but this shouldn't cause it to fail.

This post has been edited by andrewsw: 28 May 2013 - 08:31 AM
Reason for edit:: CR

Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1730
  • View blog
  • Posts: 5,709
  • Joined: 25-September 09

Re: Trouble with sql statement

Posted 28 May 2013 - 08:35 AM

Yes, it is.

In Access click the help icon and enter Band keyword and look at what Help has to say.

Quote

Access database engine reserved words
The Access database engine runs in different modes, depending on whether it is called from Access, data access objects, the Microsoft OLE Provider for the Access database engine, or the Microsoft Access ODBC driver. It can be run in either ANSI mode or non-ANSI (traditional) mode.

Because using these two modes results in two slightly different sets of reserved words, a query that uses a reserved word might work in one mode and fail in another mode.

The following is a list of reserved words to avoid when choosing identifier names.

snip...
-B
BAND
BEGIN
BETWEEN
BINARY

Edit: Didn't need to show all the A's and such.

This post has been edited by CharlieMay: 28 May 2013 - 08:47 AM

Was This Post Helpful? 2
  • +
  • -

#11 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: Trouble with sql statement

Posted 28 May 2013 - 08:40 AM

Synonyms for BAND aren't too useful:

combo, ensemble, orchestra, philharmonic, symphony, troupe

;)
Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1730
  • View blog
  • Posts: 5,709
  • Joined: 25-September 09

Re: Trouble with sql statement

Posted 28 May 2013 - 08:46 AM

I'd probably just use BandName as I don't like wrapping keywords. ;)
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: Trouble with sql statement

Posted 28 May 2013 - 08:52 AM

View PostCharlieMay, on 28 May 2013 - 03:46 PM, said:

I'd probably just use BandName as I don't like wrapping keywords. ;)

I'm with you, wrapping is an unnecessary pain. People wrap EVERY table and field-name in PHP with back-ticks, which is even more of a pain.
Was This Post Helpful? 0
  • +
  • -

#14 lar3ry   User is offline

  • Coding Geezer
  • member icon

Reputation: 314
  • View blog
  • Posts: 1,296
  • Joined: 12-September 12

Re: Trouble with sql statement

Posted 28 May 2013 - 10:44 AM

View Postandrewsw, on 28 May 2013 - 09:40 AM, said:

Synonyms for BAND aren't too useful:

combo, ensemble, orchestra, philharmonic, symphony, troupe

I'd go with artist or performer.
Was This Post Helpful? 0
  • +
  • -

#15 mattcash83   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 50
  • Joined: 23-June 12

Re: Trouble with sql statement

Posted 28 May 2013 - 01:38 PM

Wow guys, great call on the reserved word! Don't really get into SQL or access too much, this was actually just a project to get into it with. Now I know to look for reserved words! Again, thanks to all.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2