Insert a file path string into SQL database via C# code

Question

In SQL:

INSERT INTO [dbo].[tblFiles]
           ([FullFilePath]
           ,[LastModified])
     VALUES
           ('P:\test\test.csv', 
           null)

This will store the full path in the database :) However, I need to do this in code.

    SqlConnection connection = new SqlConnection(DatabaseHelper.ConnectionString);
    connection.Open();

    SqlCommand command = new SqlCommand( "stpInsertFile", connection);
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("@filepath", System.Data.SqlDbType.VarChar));
    command.Parameters["@filepath"].Value = article.FullFilePath;

    command.Parameters.Add(new SqlParameter( "@LastModified", System.Data.SqlDbType.DateTime));
    command.Parameters["@LastModified"].Value = article.LastModified;


    int newArticleID = Convert.ToInt32((decimal)command.ExecuteNonQuery());

    command.Dispose();
    connection.Close();
    connection.Dispose();

    return newArticleID;

With this all I get is 'P' in the full path column. So I tried using LINQ and got the same result.

   public int InsertArticleUsingLINQ(tblFile article) {
            DataClassesDataContext context = new DataClassesDataContext();

                tblFile newFileEntry = new tblFile();
                newFileEntry.FullFilePath = article.FullFilePath;
                newFileEntry.LastModified = article.LastModified;
                context.tblFiles.InsertOnSubmit(newFileEntry);
                context.SubmitChanges();

                return newFileEntry.ID;

        }

I'm not doing anything with the string before passing it to the database insert functions. I read that you need to escape the backslash but it seems to be escaping on the quote. Also read that you need an @ symbol before the sql but how do you add this to a parameter?

What value is article.FullFilePath holding? Maybe it goes wrong before you insert it. — user2153378
– user2153378, Commented Oct 2, 2014 at 11:17
You may need to add quotes when you set command.Parameters["@filepath"].Value — Nicolas R
– Nicolas R, Commented Oct 2, 2014 at 11:19
It doesn't like single quotes or double quotes using ADO. However the LINQ code does work now. Thanks. — JohnnyBizzle
– JohnnyBizzle, Commented Oct 2, 2014 at 12:24

Paolo · Accepted Answer · 2014-10-02 12:12:51Z

1

warning: since you didn't share the stored procedure code this is just a wild guess.

did you set the size of the @filePath parameter in the definition of your stored procedure?

if you declare it as:

create procedure stpInsertFile
    @filepath     varchar,
    @LastModified datetime
as
...

then you parameter is created as varchar(1) because of the default behaviour of varchar datatype and that would produce the result you get.
please check reference documentation for char and varchar datatype on ms website.

edited Oct 2, 2014 at 12:12

answered Oct 2, 2014 at 12:03

Paolo

2,2731 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JohnnyBizzle Over a year ago

Yep that was it. An easy mistake to make?

apomene · Accepted Answer · 2014-10-02 11:19:32Z

1

if stpInsertFile is a Stored Procedure you will have to set in your code:

...
 command.CommandType = CommandType.StoredProcedure;

else you have to set the query string in your command:

 SqlCommand command = new SqlCommand( "INSERT INTO [dbo].[tblFiles] ([FullFilePath] [LastModified])  VALUES  (@filepath,@LastModified)", connection);
...

answered Oct 2, 2014 at 11:19

apomene

14.4k10 gold badges51 silver badges76 bronze badges

Comments

JohnnyBizzle · Accepted Answer · 2014-10-02 12:53:00Z

0

Of the above options, the ADO code did not copy the full path, even with manually adding quotes. All I ever got was one character in the database.

On further inspection, the parameter was of type Varchar(1) !!! I changed this to Varchar(255) an it worked.

Note, the LINQ to SQL function did insert the full path as this was not using the Stored Procedure.

Apologies for the mistake.

answered Oct 2, 2014 at 12:53

JohnnyBizzle

9793 gold badges17 silver badges32 bronze badges

Collectives™ on Stack Overflow

Insert a file path string into SQL database via C# code

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related