[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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

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
VBBRett
Asked:
VBBRett
  • 2
  • 2
1 Solution
 
arif_eqbalCommented:
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
 
VBBRettAuthor Commented:
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
 
arif_eqbalCommented:
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
 
VBBRettAuthor Commented:
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
 
Praveen KumarArchitectCommented:
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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now