Go Premium for a chance to win a PS4. Enter to Win

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
?
285 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:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 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