• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6663
  • Last Modified:

cmd.Parameters.Add parameter always required?

I'm inserting a record in a MS-access database.   I'm doing it in the c# section of an asp.net site

I use this code:

cmd.Parameters.Add("@Myndatexti",OleDbType.Char, 50).Value = ((TextBox)(FormView1.FindControl("txtMyndaTexti"))).Text;

The thing is that the column isn't required in the database  but the c#-code requires it,   so if the txtMyndatexti is empty I get an error   "No value given for one or more required parameters"
but the txtMyndatexti  has to be able to be empty

Is that not possible when we use this code?

cmd.Parameters.Add("@Myndatexti",OleDbType.Char, 50).Value = ((TextBox)(FormView1.FindControl("txtMyndaTexti"))).Text;

I used befor another method where I put :

dbRow["Myndatexti"] = ((TextBox)(FormView1.FindControl("txtMyndaTexti"))).Text;

and that works just fine whether  the textbox is empty or not.  

For some other reasons I needed to change the code and use the prior one cmd.Parameter.add(.....)
which has these rather boring sideeffects.

Does anyone know how to handle this?

string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+GetConnectionString();
 
         string strSQL = "INSERT into Frettir (CreatedBy,CreatedOn,Title,Description,Starts,Ends,CatId,SectionId,ArticleExt,Myndatexti,MyndUrAlbumi,NrMyndar) values(?,?,?,?,?,?,?,?,?,?,?,?)";
         OleDbConnection conn = new OleDbConnection(strConn); 
         conn.Open();
 
         OleDbCommand cmd = new OleDbCommand();
 
         cmd = new OleDbCommand(strSQL,conn );
 
         cmd.Parameters.Add("@CreatedBy",OleDbType.Integer).Value = notandi;
        cmd.Parameters.Add("@CreatedOn",OleDbType.Date, 50).Value = DateTime.Now;
          cmd.Parameters.Add("@Title",OleDbType.Char, 50).Value = ((TextBox)(FormView1.FindControl("TitleTextBox"))).Text.ToString();
        cmd.Parameters.Add("@Description",OleDbType.Char, 50).Value = ((RadEditor)(FormView1.FindControl("ContentTextBox"))).Html;
         cmd.Parameters.Add("@Starts",OleDbType.Date).Value = dstartdate;
            cmd.Parameters.Add("@Ends",OleDbType.Date).Value = finaldate;
          cmd.Parameters.Add("@CatId",OleDbType.Integer).Value = Convert.ToInt32(((DropDownList)(FormView1.FindControl("DropDownList3"))).SelectedValue); 
          cmd.Parameters.Add("@SectionId",OleDbType.Integer).Value = 1;
 
        string ext = Uploader.NewsPictures.UploadExtension(this);
        if (ext.Length > 0)
        {
            ((TextBox)(FormView1.FindControl("TextBox4"))).Text = ext;
        }
 
        if (((TextBox)(FormView1.FindControl("TextBox4"))).Text != "" & ((TextBox)(FormView1.FindControl("TextBox4"))).Text != "0")
        {
            cmd.Parameters.Add("@ArticleExt",OleDbType.Char, 50).Value = ((TextBox)(FormView1.FindControl("TextBox4"))).Text.ToString();
            
 
        }
       
        cmd.Parameters.Add("@Myndatexti",OleDbType.Char, 50).Value = ((TextBox)(FormView1.FindControl("txtMyndaTexti"))).Text;
        cmd.Parameters.Add("@MyndUrAlbumi",OleDbType.Boolean).Value = ((CheckBox)(FormView1.FindControl("ChkBoxMyndUrAlbumi"))).Checked;
        //dbRow["Myndatexti"] = ((TextBox)(FormView1.FindControl("txtMyndaTexti"))).Text;
        //dbRow["MyndUrAlbumi"] = ((CheckBox)(FormView1.FindControl("ChkBoxMyndUrAlbumi"))).Checked;
        if ( ((TextBox)(FormView1.FindControl("TextBox1"))).Text == "")
        {
            ((TextBox)(FormView1.FindControl("TextBox1"))).Text = "0";
            
        }
        
        cmd.Parameters.Add("@NrMyndar",OleDbType.Integer).Value = Convert.ToInt32(((TextBox)(FormView1.FindControl("TextBox1"))).Text);
        //dbRow["NrMyndar"] = Convert.ToInt32(((TextBox)(FormView1.FindControl("TextBox1"))).Text);
 
    
 
 
 
        
         cmd.ExecuteNonQuery();

Open in new window

0
bjorkn
Asked:
bjorkn
  • 3
  • 3
1 Solution
 
DhaestCommented:
Did you check this:
if ((TextBox)(FormView1.FindControl("TextBox4"))).Text  = "" then
   cmd.Parameters.Add("@Myndatexti",OleDbType.Char, 50).Value = dbNull;
else
cmd.Parameters.Add("@Myndatexti",OleDbType.Char, 50).Value = ((TextBox)(FormView1.FindControl("txtMyndaTexti"))).Text;
end if
0
 
bjorknAuthor Commented:
Yes I know this works but I would have liked to have it in one line instead of repeating the code.  Like this it can cause errors in maintenance.  that is you change it in one place and forget the other.

0
 
DhaestCommented:
It won't be possible, because your stored procedure always expects a number of parameters. When you don't pass them all, you'll get an error from your database.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
bjorknAuthor Commented:
It turns out that the dbNull isn't accepted in the line:

cmd.Parameters.Add("@Myndatexti",OleDbType.Char, 50).Value = dbNull;

I get this error

Error      1      'System.DBNull' is a 'type' but is used like a 'variable'      

Any ideas?
0
 
DhaestCommented:
cmd.Parameters.Add("@Myndatexti",OleDbType.Char, 50).Value = DBNull.Value;
0
 
bjorknAuthor Commented:
Not exactly what I was looking for but solved the problem so far
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now