asked on

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") & "')"

arif_eqbal

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"

VBBRett

ASKER

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!

arif_eqbal

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

VBBRett

ASKER

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.

ASKER CERTIFIED SOLUTION

Praveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial