Solved

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

Posted on 2006-07-05
5
280 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 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

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

734 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