We help IT Professionals succeed at work.

expects the parameter which was not supplied error

ocdc
ocdc asked
on
Certain browsers (Chrome 5, Safari 4) produce an error when a file is not uploaded or no data is provided.

Error is:
The parameterized query '(@FileId int,@RUFileName nvarchar(100),@RUFileType nvarc' expects the parameter '@RefLetterType', which was not supplied.


Source Error:

Line 119:            command.Parameters.Add("@CLContent", SqlDbType.VarBinary).Value = fileCLContent;
Line 120:            command.Connection.Open();
Line 121:            command.ExecuteNonQuery();
Line 122:
Line 123:        }

Source File: c:\web\HR\OApp\App\Files.aspx.cs    Line: 121
using (SqlCommand command = new SqlCommand())
        {
            command.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["JConnectionString1"].ConnectionString);
            command.CommandText = @"insert into [appfiles](FileId, RUFileName, RUFileType, RUFileSize, CLName, CLType, CFSize, RefLetterName, RefLetterType,RefLetterSize, ResText, ReferenceLetterText,CoverLetterText,ResContent,CoverLetterContent,RefContent)
            						values(@FileId, @RUFileName, @RUFileType, @RUFileSize, @CLName, @CLType,@CFSize,@RefLetterName,@RefLetterType,@RefLetterSize, @ResText, @ReferenceLetterText,@CoverLetterText,@ResContent,@CLContent,@RefContent)";

            command.Parameters.Add("@FileId", SqlDbType.Int).Value = int.Parse(Session["FileId"].ToString());
			
            command.Parameters.Add("@RUFileName", SqlDbType.NVarChar, 100).Value = Path.GetFileName(fuRes.PostedFile.FileName);
            command.Parameters.Add("@RUFileType", SqlDbType.NVarChar, 100).Value = fuRes.PostedFile.ContentType;
            command.Parameters.Add("@RUFileSize", SqlDbType.Int).Value = fuRes.PostedFile.ContentLength;

            command.Parameters.Add("@CLName", SqlDbType.NVarChar, 100).Value = Path.GetFileName(fuCLetter.PostedFile.FileName);
            command.Parameters.Add("@CLType", SqlDbType.NVarChar, 100).Value = fuCLetter.PostedFile.ContentType;
            command.Parameters.Add("@CFSize", SqlDbType.Int).Value = fuCLetter.PostedFile.ContentLength;
            

            command.Parameters.Add("@RefLetterName", SqlDbType.NVarChar, 100).Value = Path.GetFileName(fuRefLetter.PostedFile.FileName);
            command.Parameters.Add("@RefLetterType", SqlDbType.NVarChar, 100).Value = fuRefLetter.PostedFile.ContentType;
            command.Parameters.Add("@RefLetterSize", SqlDbType.Int).Value = fuRefLetter.PostedFile.ContentLength;

            
            // filecontent, convert from stream to byte array
            byte[] fileResContent = new byte[fuRefLetter.PostedFile.ContentLength];
            
            //fuRefLetter.PostedFile.InputStream.Read(fileResContent, 0, fuRefLetter.PostedFile.ContentLength);
            Stream File1Stream;
            File1Stream = fuRefLetter.PostedFile.InputStream;  
            File1Stream.Read(fileResContent, 0, fuRefLetter.PostedFile.ContentLength);  
            command.Parameters.Add("@RefContent", SqlDbType.VarBinary).Value = fileResContent;

            byte[] fileResContent = new byte[fuRes.PostedFile.ContentLength];
            fuRes.PostedFile.InputStream.Read(fileResContent, 0, fuRes.PostedFile.ContentLength);
            command.Parameters.Add("@ResContent", SqlDbType.VarBinary).Value = fileResContent;

            byte[] fileCLContent = new byte[fuCLetter.PostedFile.ContentLength];
            fuCLetter.PostedFile.InputStream.Read(fileCLContent, 0, fuCLetter.PostedFile.ContentLength);
            command.Parameters.Add("@CLContent", SqlDbType.VarBinary).Value = fileCLContent;
            command.Connection.Open(); 
            command.ExecuteNonQuery();

        }

Open in new window

Comment
Watch Question

Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
It is probably null, and you would probably not want to execute it if no file is selected, right?

I would probably check
if string.IsNullOrEmpty(fuRefLetter.PostedFile.FileName) return;

Author

Commented:
Thanks.  How can I use this in this context?
if string.IsNullOrEmpty(fuRefLetter.PostedFile.FileName) return;
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Before processing your code. Something like the code below. This has the same effect as this code without the nesting (if ! ...

        void processFile()
        {
            if (!string.IsNullOrEmpty(txtFileName.FileName)) {
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = ...
                    // etc.
                }
            }
        }

void LinkButton1_Click(object sender, EventArgs e)
        {
            processFile();
        }

        void processFile()
        { 
            if (string.IsNullOrEmpty(txtFileName.FileName)) return;
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = ...
                // etc.
            }
        }

Open in new window

Author

Commented:
similar error:
The parameterized query '(@FileId int,@ResFileName nvarchar(100),@ResFileType nvarc' expects the parameter '@ResFileType', which was not supplied.
 
if (!string.IsNullOrEmpty(fuRes.PostedFile.FileName))
        {
            using (SqlCommand command = new SqlCommand())
            {
...}

Open in new window

Author

Commented:
How do I make this a null value if no data is provided? Thanks.

 command.Parameters.Add("@CLName", SqlDbType.NVarChar, 100).Value = Path.GetFileName(fuCLetter.PostedFile.FileName);
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Typically, you would not execute the stored procedure if the important data (i.e. the file with which you are planning to do something) does not exist.

However, the error you are getting appears to be related to @ResFileType. Make sure you are adding the parameters with the same names as in your SQL Stored Procedure.

Author

Commented:
You were correct.
I updated the code:

The parameterized query '(@FileId int,@RUFileName nvarchar(100),@RUFileType nvarc' expects the parameter '@RUFileType', which was not supplied.

This is my error:  

The parameterized query '(@FileId int,@RUFileName nvarchar(100),@RUFileType nvarc' expects the parameter '@RUFileType', which was not supplied.


amenkes, Maybe I have an issue with the way I setup my web form.  I have 3 upload components that I want to use to upload binary files to the sql server.   I am not forcing the end user to use any of them.  If, for example, the end user only uses the one of the upload component, but not the other 2 components they will get an error message when using Chrome 4.0.5, or Safari 4.  Internet Explorer & Firefox record a "0" value in the RUFileSize field,   "application/octet-stream" in the RUFileType, and nothing in the RUFileName fields.  When they actually select a file to upload in each upload component, then there are not any errors.  

The code from the original post is all execute on  "protected void btnUpload_Click(object sender, EventArgs e)"

I thought that if I could get the upload components to pass a Null value, then that would solf the errors in certain types of browsers.  

How could I stop from executing the code when a file is not selected?
C# ASP.NET Developer
Top Expert 2010
Commented:
By using the
  if (!string.IsNullOrEmpty( fuCLetter.PostedFile.FileName)) {

  }

For example, suppose you had 3 controls
a, b, and c

if the sql function is going to be the same for all 3, I would put that in a function, something like:
void processFile(string filename)
{
  if (string.IsNullOrEmpty(filename)) return;
   // process sql here
}

then, you could do this:
processfile(a.PostedFile.Filename);
processfile(b.PostedFile.Filename);
processfile(c.PostedFile.Filename);

Author

Commented:
Thanks.