Solved

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

Posted on 2006-07-05
5
278 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
  • 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:
Mamtha1982 earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

810 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