Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi All,
 
I need a sample application for File Upload to SQL satabase.
I am using asp.net c#.net 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())
            {
                OpenConnection(connection);
                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;
                cmd.ExecuteNonQuery();
 
                connection.Close();
            }
        }
 
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 22:55pm
Rate this: bad
good
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...
  Permalink  
Comments
jaipal0908 at 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 asp.net.
Prasad_Kulkarni at 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 at 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 at 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 at 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
INSERT INTO myTable VALUES(@ID, @C3, @C2)
then
@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 at 5-Jun-12 5:08am
   
Good answer. 5!
jaipal0908 at 5-Jun-12 6:02am
   
Thank you all
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try:
cmd.Parameters.Add("@Document", SqlDbType.Image);
instead of
cmd.Parameters.Add("@Document", SqlDbType.VarBinary);
 
Details here:
MSDN: SqlParameter.SqlDbType Property [^]
MSDN: SqlDbType Enumeration[^]
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 355
1 _Amy 235
2 Peter Leow 185
3 Andreas Gieriet 180
4 Dave Kreskowiak 155
0 OriginalGriff 7,540
1 Sergey Alexandrovich Kryukov 6,462
2 Maciej Los 3,849
3 Peter Leow 3,653
4 CHill60 2,712


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 5 Jun 2012
Copyright © CodeProject, 1999-2014
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