cmd.Parameters.Add  parameter always required?

Posted on 2008-10-14
Last Modified: 2013-12-17
I'm inserting a record in a MS-access database.   I'm doing it in the c# section of an 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); 
         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);

Open in new window

Question by:bjorkn
  • 3
  • 3
LVL 53

Expert Comment

ID: 22718360
Did you check this:
if ((TextBox)(FormView1.FindControl("TextBox4"))).Text  = "" then
   cmd.Parameters.Add("@Myndatexti",OleDbType.Char, 50).Value = dbNull;
cmd.Parameters.Add("@Myndatexti",OleDbType.Char, 50).Value = ((TextBox)(FormView1.FindControl("txtMyndaTexti"))).Text;
end if

Author Comment

ID: 22723606
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.

LVL 53

Expert Comment

ID: 22728431
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.
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.


Author Comment

ID: 22729683
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?
LVL 53

Accepted Solution

Dhaest earned 250 total points
ID: 22729726
cmd.Parameters.Add("@Myndatexti",OleDbType.Char, 50).Value = DBNull.Value;

Author Closing Comment

ID: 31505994
Not exactly what I was looking for but solved the problem so far

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now