Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

What is wrong with my Insert string in my VB.NET program?  NEED HELP DESPERATELY!!

Posted on 2006-07-05
5
Medium Priority
?
282 Views
Last Modified: 2010-04-23
I am trying to run an insert string in order for my program to work, but I keep on getting an error that states the following..(SEE BELOW).  I am taking fields from an Excel spreadsheet and inserting them into a SQL Server database table.

SQLException was unhandled
Line 1: Incorrect syntax near ','.

Now I have been working on this problem all day and I do not see a problem in my Insert String Command.  What could possibly be the problem?  Below is my insert string for all to see.

tester = "insert into test_nip_valuation_nonproduction (LOB, PolicyNO, OccuranceNO, PolOccNo, " & _
                   "PolComm, LossDay, CAT, RiskState, FirstNotice, ValnMo, AccYr, PdIndem, PdALAE, " & _
                  "OSIndem, OSALAE, IncdIndem, IncdALAE, IncdLandALAE, Insured, LossDescription) " & _
                 "values('" & row.Item("LOB").ToString & "', '" & row.Item("Pol #").ToString & "', '" & _
                row.Item("Occ #").ToString & "', '" & row.Item("Pol Occ #").ToString & "', '" & _
                 row.Item("Pol Comm").ToString & "', '" & row.Item("Loss Day").ToString & "', " & _
                row.Item("CAT").ToString & ", " & _
                row.Item("RiskState").ToString & ", '" & row.Item("First Notice").ToString & "', " & _
                row.Item("Valn Mo").ToString & ", " & row.Item("Acc Yr").ToString & ", " & _
                row.Item("PdIndem").ToString & ", " & row.Item("PdALAE").ToString & ", " & _
                row.Item("OS Indem").ToString & ", " & row.Item("OS ALAE").ToString & ", " & _
                row.Item("IncdIndem").ToString & ", " & row.Item("IncdALAE").ToString & ", " & _
                row.Item("Incd L&ALAE").ToString & ", '" & row.Item("Insured").ToString & "', '" & _
                row.Item("Loss Description") & "')"
0
Comment
Question by:VBBRett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 17048133
Its very difficult to find something in the code why don't you modify it and test
say like this

        Dim newTester As String
        newTester = "insert into test_nip_valuation_nonproduction (LOB, PolicyNO, OccuranceNO, PolOccNo, " & _
                                  "PolComm, LossDay, CAT, RiskState, FirstNotice, ValnMo, AccYr, PdIndem, PdALAE, " & _
                                 "OSIndem, OSALAE, IncdIndem, IncdALAE, IncdLandALAE, Insured, LossDescription) "
       
        newTester &= String.Format("values ('{0}','{1}',{2} .....)", row.Item("LOB").ToString, row.Item("Pol #").ToString .Format....)

OR just
Use stringBuilder and concatenate each line so that if you have some error it gives you the correct line number rather than
 "Line 1: Incorrect Syntax"

0
 

Author Comment

by:VBBRett
ID: 17048193
Hello arif eqbal, thank you for chiming in.  I found the problem with my code, it was not in the code at all.  It was because I had records coming from the Excel spreadsheet that had a blank space as opposed to a null or a zero.  How would I deal with a problem like this if say I did have a blank space?  How would I code a fail safe way of beridding of this problem?  Thanks!
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 17048211
I didn't get you, can you elaborate a little
If excel is having blank cells or blank space in a cell it should not create a problem
0
 

Author Comment

by:VBBRett
ID: 17048216
It creates a problem for me with my insert statement.  I filled in the cell that was blank with a 0 and I had no problem with that particular record.
0
 
LVL 12

Accepted Solution

by:
Praveen Kumar earned 2000 total points
ID: 17048925
Use Convert.ToString() function

Ex:
Convert.ToString(row.Item("LOB")) it will not though any Exception if it is Null..
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question