Click here to Skip to main content
12,700,838 members (31,418 online)
Rate this:
Please Sign up or sign in to vote.
See more: C# SQL-Server SQL-Server-2008 , +
Hi All,

I need a sample application for File Upload to SQL satabase.
I am using and sql server.

I am getting an error in Utilities class like this.
Please can anyone help me.
Server Error in '/' Application.
Incorrect syntax near '@Document'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@Document'.

Source Error:

Line 68:                 cmd.Parameters["@Size"].Value = size;
Line 69:                 cmd.Parameters["@Document"].Value = fileData;
Line 70:                 cmd.ExecuteNonQuery();
Line 71: 
Line 72:                 connection.Close();

Source File: C:\Users\CBS\documents\visual studio 2010\Projects\Mcases\Mcases\Utilities.cs    Line: 70

Stack Trace:

Here is utilities.cs class
public static void SaveFile(string name, string contentType, int size, byte[] fileData)
            using (SqlConnection connection = new SqlConnection())
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandTimeout = 0;

                string commandText = "INSERT INTO Document VALUES(@FileName, @ContentType,@Size,@Document)";
                commandText = commandText + "@Document";
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add("@FileName", SqlDbType.VarChar);
                cmd.Parameters.Add("@ContentType", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@size", SqlDbType.Int);
                cmd.Parameters.Add("@Document", SqlDbType.VarBinary);

                cmd.Parameters["@FileName"].Value = name;
                cmd.Parameters["@ContentType"].Value = contentType;
                cmd.Parameters["@Size"].Value = size;
                cmd.Parameters["@Document"].Value = fileData;


And this is my button click code to save file in Database

protected void btnUpload_Click(object sender, EventArgs e)
            HttpFileCollection files = Request.Files;
            foreach (string fileTagName in files)
                HttpPostedFile file = Request.Files[fileTagName];
                if (file.ContentLength > 0)
                    // Due to the limit of the max for a int type, the largest file can be
                    // uploaded is 2147483647, which is very large anyway.
                    int size = file.ContentLength;
                    string name = file.FileName;
                    int position = name.LastIndexOf("\\");
                    name = name.Substring(position + 1);
                    string contentType = file.ContentType;
                    byte[] fileData = new byte[size];
                    file.InputStream.Read(fileData, 0, size);

                    Utilities.SaveFile(name, contentType, size, fileData);
Posted 4-Jun-12 23:55pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Look at your code:
string commandText = "INSERT INTO Document VALUES(@FileName, @ContentType,@Size,@Document)";
commandText = commandText + "@Document";

You end up with SQL Command string:
INSERT INTO Document VALUES(@FileName, @ContentType,@Size,@Document)@Document
Which is clearly wrong!
Take out the second of these two lines...
jaipal0908 5-Jun-12 5:07am
Thank you,
Suppose if the table in Database contains 8 columns,can we insert only 4 or 5 like(I mean only some columns) into the table from
Prasad_Kulkarni 5-Jun-12 5:25am
Yes we can. Just make nullable property true of columns in which you don't want to add any values..
OriginalGriff 5-Jun-12 5:28am
Yes - as Prasad says, you can omit nullable columns. You can also omit columns if they are not nullable if you have provided a default value in the field definition.
jaipal0908 5-Jun-12 5:35am
In my table design for I checked for some columns to allow Nulls but if i try to insert into other columns its not inserting,getting below error--

Column name or number of supplied values does not match table definition.

Can you tell me what should be the problem with my code.
OriginalGriff 5-Jun-12 5:44am
It's a simple mistake: unless you tell SQL which columns you want teh data to go into, it tries to assign them in index order. So if you table has
ID, C1, C2, C3
and you use
@ID will go to ID
@C3 will go to C1
@C2 will go to C2
null will go to C3
Always name the columns, particularly if you aren't going to assign them all:
INSERT INTO myTable (ID, C3, C2) VALUES(@ID, @C3, @C2)
It is good practice to do that at all times, since it makes your code more robust in the event of table changes.
VJ Reddy 5-Jun-12 5:08am
Good answer. 5!
jaipal0908 5-Jun-12 6:02am
Thank you all
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

cmd.Parameters.Add("@Document", SqlDbType.Image);

instead of
cmd.Parameters.Add("@Document", SqlDbType.VarBinary);

Details here:
MSDN: SqlParameter.SqlDbType Property [^]
MSDN: SqlDbType Enumeration[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170118.1 | Last Updated 5 Jun 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100