bulk upload into sql

Question

I am using this code to try and upload .csv file into sql. It works when the path is hard coded but if I try to add a parameter result from a text box it results in the NewLine in Constant error. What do I need to do to rectify this issue.

using (SqlCommand cmd = new SqlCommand(@"BULK INSERT Alpha.dbo.Beta
         FROM  '"+FileUpload_TextBox.Text+"'
         WITH
         (
           FIELDTERMINATOR=',',
           ROWTERMINATOR='\n',
           FIRSTROW=2
         )
         ", MyConnection))

the red squiggles appear under the +"' and upon hovering over it it appears NewLine Constant Error — AC3, Jan 28 '14 at 09:11

score 1 · Answer 1 · answered Jan 28 '14 at 08:55

1

Use following code:

FileUpload_TextBox.Text.Replace(Enviroment.NewLine,"")

It will remove new line chars from string

answered Jan 28 '14 at 08:55

Piotr Stapp

19,392
11
68
116

Alon Gubkin · Answer 2 · 2014-01-28T09:11:02.990

1

You should never specify parameters that way. Use SQL parameters - it might solve your issue on the way:

using (SqlCommand cmd = new SqlCommand(@"BULK INSERT Alpha.dbo.Beta
         FROM @FilePath
         WITH
         (
           FIELDTERMINATOR=',',
           ROWTERMINATOR='\n',
           FIRSTROW=2
         )
         ", MyConnection))
{
    cmd.Parameters.AddWithValue("@FilePath", FileUpload_TextBox.Text);
    ...
}

If it still doesn't work, use @Garath answer when adding the parameter:

cmd.Parameters.AddWithValue("@FilePath", 
    FileUpload_TextBox.Text.Replace(Enviroment.NewLine,""));

edited Jan 28 '14 at 09:11

answered Jan 28 '14 at 08:57

Alon Gubkin

56,458
54
195
288

When I use the above code that you first give it says underlines it in green saying it is obsolete and has been deprecated. When I input the second code it says the 'Environment' does not exist in the current context? – AC3 Jan 28 '14 at 09:09
I've updated the answer to fix the obsolete issue. About the second issue, are you sure you have `using System;`? – Alon Gubkin Jan 28 '14 at 09:11
There are now no errors in the build but when I run the code it breaks and results in an error message saying incorrect syntax near @FilePath – AC3 Jan 28 '14 at 09:17
Alon, this is what I was looking for. Thanks v-much for your patience. – AC3 Jan 28 '14 at 09:47

bulk upload into sql

2 Answers2