Solved

cmd.Parameters.Add  parameter always required?

Posted on 2008-10-14
6
6,635 Views
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 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
Comment
Question by:bjorkn
  • 3
  • 3
6 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 22718360
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
 

Author Comment

by:bjorkn
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.

0
 
LVL 53

Expert Comment

by:Dhaest
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.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:bjorkn
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?
0
 
LVL 53

Accepted Solution

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

Author Closing Comment

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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 start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 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

17 Experts available now in Live!

Get 1:1 Help Now