Newbie! Convert String to Null

[mcauliff]

I'm coding my first VB.Net program.

The process is to read a .csv file and write the records to a SQL Server 2005 table.

I'm getting an error when a column is empty. I need to put a NULL in the column.

In the code below; Currentline(10) is either empty or has a value. If the value is presented, no error. If empty, then it throws an error.

This is the error message
"Incorrect syntax near ','."

I understand that it is because it is empty. I could check the field;

If CurrentLine(10).Equals("") Then
How do I set the String to NULL
End If
Using sr As New StreamReader("test.csv")
Do
'CurrentLine = sr.ReadLine
CurrentLine = sr.ReadLine.Split(",")
' First row of file is a header row
If CurrentLine(0) <> "Address" Then
strSQL = "Insert CHQ_Resources(Address, AnnualTargetHours, " & _
"BaseCurrency, CertificationCycle, CertificationHours, City, Country, " & _
"EmailAddress, EmployeeType, ExpenseApprover, FirstName, FirstPayroll, " & _
"HireDate, HourlyCostRate, HoursPerDay, Language, LastName, Location, NTUserId, OfficeNumber, " & _
"OfficeNumberExt, PayrollCycle, PayrollLagDays, PostalCode, Province, " & _
"ResourceText1, ResourceText2, ResourceText3, ResourceType, ReportsTo, " & _
"TerminationDate, TimeApprover, Title, UserDefinedResourceId, WebPassword, " & _
"ResourceText6, TimeZone) " & _
"Values ('" & CurrentLine(0) & "', " & CurrentLine(1) & ", '" & CurrentLine(2) & _
"', 'W', '0', '" & CurrentLine(3) & "', '" & CurrentLine(4) & "', '" & CurrentLine(5) & _
"', '" & CurrentLine(6) & "', '" & CurrentLine(23) & "', '" & CurrentLine(7) & "', '" & CurrentLine(8) & _
"', '" & CurrentLine(9) & "', " & CurrentLine(10) & ", '8', 'Eng','" & CurrentLine(11) & _
"', '" & CurrentLine(12) & "', '" & CurrentLine(13) & "', '" & CurrentLine(14) & _
"', '" & CurrentLine(15) & "', '" & CurrentLine(16) & "', '120', '" & CurrentLine(17) & _
"', '" & CurrentLine(18) & "', '" & CurrentLine(19) & "', '" & CurrentLine(20) & _
"', '" & CurrentLine(21) & "', '" & CurrentLine(22) & "', '" & CurrentLine(23) & _
"', '" & CurrentLine(24) & "', '" & CurrentLine(23) & "', '" & CurrentLine(25) & "', '" & CurrentLine(26) & _
"', 'compuware" & CurrentLine(26) & "', '" & CurrentLine(27) & "', '" & CurrentLine(28) & "')"

 

[bb9786]

The easiest way I can think of doing it is setting that column (FirstName) to allow nulls. Then you should be able to pass an empty string to it. There might be a better way of handling this though. I'm new to Sql myself.

 

[gmmastros]

SQL Server has some built-in tools for importing data. I guarantee that you will NOT be able to write an application that performs better than the tools that Microsoft wrote.

Specifically, do a little research on SQL Server Integration Services (SSIS) and also on Bulk Insert.

Then again.... if you want to write the application in VB just to get some practice, then it's not a bad idea.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mcauliff]

Thank you for the replies

The column is set to allow nulls. The issue is that the field from the table is a string and SQL doesn't allow a Null to be place in a string.

The program will execute on a web server, not on a database server where SSIS is located. There is more processing to be done after the csv is copied to the SQL table.

 

[gmmastros]

The problem is empty data. Since you are building a SQL String in code, it ends up looking like this...

Insert Into Table(Col1, Col2) Values(,)

If you want to insert nulls, then you need...

Insert Into Table(Col1, Col2) Values(NULL,NULL)

But, a better way to do this would be to use a command object and parameters. This will be (slightly) better for performance, but will also protect you from SQL Injection and weird data.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Sorwen]

My 2 cents. Should say that VB.net doesn't have Nulls. A value is Empty or Nothing, but not Null. Just like dictionaries at Microsoft must have quite a few Empty or Nothing pages, but that is a whole other discussion.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!