locked
sqldatasource.insert(); RRS feed

  • Question

  • User-771576004 posted

    I am getting error on page (at the bottom) but no asp error but nothing is getting inserted into the table, Does an error seem apparant?

            string dd1 = (DropDownList1.SelectedValue);
            string dd2 = (DropDownList2.SelectedValue);
            string dd3 = (DropDownList3.SelectedValue);
            string dd4 = (DropDownList4.SelectedValue);

            SqlDataSource1.InsertParameters.Clear();

    SqlDataSource1.InsertParameters.Add(new Parameter("dd1", System.TypeCode.String));
    SqlDataSource1.InsertParameters.Add(new Parameter("dd2", System.TypeCode.String));
    SqlDataSource1.InsertParameters.Add(new Parameter("dd3", System.TypeCode.String));
    SqlDataSource1.InsertParameters.Add(new Parameter("dd4", System.TypeCode.String));


    SqlDataSource1.InsertCommand = "insert into milestone (branch,ko,ks,customer) values (@dd1,@dd2,@dd3,@dd4)";
    SqlDataSource1.Insert();

    Tuesday, October 25, 2011 9:14 AM

Answers

  • User1020195037 posted

    That would be a javascript error. Double click the yellow triangle and you will get a browser popup window with the javascript error message.

    Also, have you tried setting a breakpoint and stepping through the process to see what values are going in to your parameters?

    You are not allowing nulls in any of your columns so any missing parameter would cause a sql insert error.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 25, 2011 11:14 AM

All replies

  • User1020195037 posted

    You are not setting the values of the parameters you are passing to the database. You should change your Parameter adding code to this:

    SqlDataSource1.InsertParameters.Add(new Parameter("@dd1", System.Data.DbType.String, dd1));
    ...

    This lets you set the value of the parameter as it is added, then the sql parameters will be correctly substituted sql side.

    Tuesday, October 25, 2011 9:38 AM
  • User-771576004 posted

           SO should it be this way? 

            string dd1 = (DropDownList1.SelectedValue);
            string dd2 = (DropDownList2.SelectedValue);
            string dd3 = (DropDownList3.SelectedValue);
            string dd4 = (DropDownList4.SelectedValue);

            SqlDataSource1.InsertParameters.Clear();

     

    SqlDataSource1.InsertParameters.Add(new Parameter("@dd1", System.Data.DbType.String, dd1));
    SqlDataSource1.InsertParameters.Add(new Parameter("@dd2", System.Data.DbType.String, dd2));
    SqlDataSource1.InsertParameters.Add(new Parameter("@dd3", System.Data.DbType.String, dd3));
    SqlDataSource1.InsertParameters.Add(new Parameter("@dd4", System.Data.DbType.String, dd4));

    SqlDataSource1.InsertParameters["DD1"].DefaultValue = dd1;
    SqlDataSource1.InsertParameters["DD2"].DefaultValue = dd2;
    SqlDataSource1.InsertParameters["DD3"].DefaultValue = dd3;
    SqlDataSource1.InsertParameters["DD4"].DefaultValue = dd4;

     

    SqlDataSource1.InsertCommand = "insert into milestone (branch,ko,ks,customer) values (@dd1,@dd2,@dd3,@dd4)";
    SqlDataSource1.Insert();

    Tuesday, October 25, 2011 9:50 AM
  • User1020195037 posted

    You don't need the lines where you are setting the DefaultValue. When you add the new Parameter, you are setting the parameter name first, the value type second, and the value last.

    Tuesday, October 25, 2011 9:53 AM
  • User-771576004 posted

    I put in your lines, which were slightly diff then mine, took out the defaultvalue lines, but no record is getting inserted, just error on page, no asp error is showing.

    the table in sqlServer is like this

    CREATE TABLE Milestone (

      ID int identity(1,1) NOT NULL PRIMARY KEY,
      Branch varchar(max)   NOT NULL default '',
      KO varchar(max)   NOT NULL default 'f',
      KS varchar(max)   NOT NULL,
      Customer varchar(50)   NOT NULL,
      PR_Received datetime  NOT NULL,
      PR_MIPR varchar(50)   NOT NULL,
      RFP_CONTRACT varchar(50)   NOT NULL,
      Descrip nvarchar(max) NOT NULL)

     

    Tuesday, October 25, 2011 10:16 AM
  • User-771576004 posted

            I have commented out the insert part where insertion is done. It does not like the label assignment at the bottom, is where it craps out

            string dd1 = (DropDownList1.SelectedValue);
            string dd2 = (DropDownList2.SelectedValue);
            string dd3 = (DropDownList3.SelectedValue);
            string dd4 = (DropDownList4.SelectedValue);

            SqlDataSource1.InsertParameters.Clear();

    //SqlDataSource1.InsertParameters.Add("dd1",sy );

    //SqlDataSource1.InsertParameters.Add(new Parameter("dd1", System.TypeCode.String));
    //SqlDataSource1.InsertParameters.Add(new Parameter("dd2", System.TypeCode.String));
    //SqlDataSource1.InsertParameters.Add(new Parameter("dd3", System.TypeCode.String));
    //SqlDataSource1.InsertParameters.Add(new Parameter("dd4", System.TypeCode.String));

     

    SqlDataSource1.InsertParameters.Add(new Parameter("@dd1", System.Data.DbType.String, dd1));
    SqlDataSource1.InsertParameters.Add(new Parameter("@dd2", System.Data.DbType.String, dd2));
    SqlDataSource1.InsertParameters.Add(new Parameter("@dd3", System.Data.DbType.String, dd3));
    SqlDataSource1.InsertParameters.Add(new Parameter("@dd4", System.Data.DbType.String, dd4));

     

     

    SqlDataSource1.InsertCommand = "insert into milestone (branch,ko,ks,customer) values (@dd1,@dd2,@dd3,@dd4)";
    //SqlDataSource1.Insert();

    Label1.Visible = true;
    Label1.Text = SqlDataSource1.InsertParameters["dd4"].ToString();

    Tuesday, October 25, 2011 10:26 AM
  • User1020195037 posted

    What is the exact error you are getting? That would really help track down the exact problem.

    Sql Parameters are usually prefixed with the @ character. Try changing your code to this:

    Label1.Text = SqlDataSource1.InsertParameters["@dd4"].ToString();

     

    Tuesday, October 25, 2011 10:30 AM
  • User-771576004 posted

    It simply say 'error on page' in the status area of the browser.

    When I had my orig code, the label would output the parameter defaultvalue, insert statement would work, but NO RECORD would get inserted.

    Tuesday, October 25, 2011 10:57 AM
  • User1020195037 posted

    That would be a javascript error. Double click the yellow triangle and you will get a browser popup window with the javascript error message.

    Also, have you tried setting a breakpoint and stepping through the process to see what values are going in to your parameters?

    You are not allowing nulls in any of your columns so any missing parameter would cause a sql insert error.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 25, 2011 11:14 AM
  • User-771576004 posted

    Now where getting somewhere, it was a null value issue(why do I even have them defined that way?)

    label1 being = to SqlDataSource1.InsertParameters["@dd1"].ToString();  shows @dd1.

    So the nul values are my downfall.  The fields you see are only a few of many and I am building the form for inputting.

    Does this mean for testing, I can't insert a record unless all the fields have values?( and there are alot!)

     

     

     

    Tuesday, October 25, 2011 11:37 AM
  • User1020195037 posted

    If you have columns defined as NOT NULL, they must have a value when you insert a row or no row will be created. You can change them to allow nulls (except your primary key columns). I reworked the code a little bit. See if this helps:

            string dd1 = DropDownList1.SelectedValue;
            string dd2 = DropDownList2.SelectedValue;
            string dd3 = DropDownList3.SelectedValue;
            string dd4 = DropDownList4.SelectedValue;
    
            SqlDataSource1.InsertParameters.Add("@dd1", dd1);
            SqlDataSource1.InsertParameters.Add("@dd2", dd2);
            SqlDataSource1.InsertParameters.Add("@dd3", dd3);
            SqlDataSource1.InsertParameters.Add("@dd4", dd4);
    
            SqlDataSource1.Inserting += new SqlDataSourceCommandEventHandler(SqlDataSource1_Inserting);
     
            SqlDataSource1.InsertCommand = "insert into milestone (branch,ko,ks,customer) values (@dd1,@dd2,@dd3,@dd4)";
            SqlDataSource1.Insert();
    
            
        }
    
        protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
        {
            Label1.Text = e.Command.Parameters["@dd1"].Value.ToString();
        }

    The Inserting event gives you access to the actual parameters values instead of just the default value. This should let you populate your label. Also, you could check for null values and cancel the event at this point if you wanted.

    Tuesday, October 25, 2011 11:58 AM
  • User-771576004 posted

    Thanks again for your input. I am not familiar with "thSqlDataSource1.Inserting" mechanism or what it does.

    the other quest I have is: it is acceptable for many of these fields to be blank as user need not fill all of them out, could I have a default value of space(1) for each of the fields ( a blank space to avoid the null issue), I tried that as you see in my code but to no avail.

    Tuesday, October 25, 2011 12:43 PM
  • User1020195037 posted

    SqlDataSource1_Inserting fires when you call SqlDataSource1.Insert(). It has direct access to the parameters whereas the other methods are only dealing with the Default Values of the parameters.

    Technically you can change the table columns to allow nulls or insert blank spaces, but as far as meeting the application requirements that is up to you.

    Tuesday, October 25, 2011 12:49 PM
  • User-771576004 posted

    If I have the below script, will the branch field be automatically populated with zoo when a record is created? I am testing in mgmt studio.

    CREATE TABLE Milestone (

      ID int identity(1,1) NOT NULL PRIMARY KEY,
      Branch varchar(max)   default 'zoo' NOT NULL ,
      KO varchar(max)   default ' ' NOT NULL ,
      KS varchar(max)   default ' ' NOT NULL,
      Customer varchar(50)   default ' ' NOT NULL,
      PR_Received datetime default ' '  NOT NULL)

    Tuesday, October 25, 2011 1:18 PM
  • User1020195037 posted

    That should put work, putting your default values into columns where no value is supplied during insert.

    Tuesday, October 25, 2011 2:23 PM