Click here to Skip to main content
16,012,759 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
XML
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
C#
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

C#
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

Try:
C#
cmd.Parameters.Add("@Document", SqlDbType.Image);

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


Details here:
MSDN: SqlParameter.SqlDbType Property [^]
MSDN: SqlDbType Enumeration[^]
 
Share this answer
 
Look at your code:
C#
string commandText = "INSERT INTO Document VALUES(@FileName, @ContentType,@Size,@Document)";
commandText = commandText + "@Document";

You end up with SQL Command string:
SQL
INSERT INTO Document VALUES(@FileName, @ContentType,@Size,@Document)@Document
Which is clearly wrong!
Take out the second of these two lines...
 
Share this answer
 
Comments
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 asp.net.
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
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900