Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

cmd.Parameters.Add  parameter always required?

Posted on 2008-10-14
6
Medium Priority
?
6,660 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 750 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

610 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