[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error trying to add row to table with ASP.NET

Posted on 2005-05-09
9
Medium Priority
?
292 Views
Last Modified: 2010-04-16
I am building my website with ASP.NET C#, and am getting an error when submitting the form data.  The error occurs after the mytable.Rows.Add(myRow); line and states there is a syntax error in the Insert statement.  My code is below, with the line  that errors out marked.  As far as I can see, the table structure is the same as my Select statement.  All the columns show the correct form data, but I can't get it into the table.  Please help.

ptslv
------------------

                            private void btnSubmit_Click(object sender, System.EventArgs e)
            {
                  bool bIsError=false;
                  OleDbConnection con = new OleDbConnection(strConnection);
                  string strSQL;
                  DataSet ds = new DataSet();
                  strSQL="SELECT Top 1 [DateSubmit],[LastN],[FirstN],[MI],[Org],[Phone],[Email],[Role],[Tng],[Tng_Complete],[Title],[Title_Months],[Use],[Use_Months],[Computer],[Education],[Comments] FROM Personnel";

                                                try
                  {
                        
                        OleDbDataAdapter da = new OleDbDataAdapter(strSQL,con);
                        da.Fill(ds);
                        DataTable mytable =ds.Tables[0];
                        DataRow myRow ;
                        myRow = mytable.NewRow();
                        string n ;
                        foreach(DataColumn c in mytable.Columns)
                        {
                              n = c.ColumnName;
                              if(n=="AutoID")
                              {
                                    continue;
                              }
                              myRow[n]=ControlTypeCheck.CheckControlType(Page.FindControl(n),n);
                        }
                        mytable.Rows.Add(myRow);
                        OleDbCommandBuilder cb = new OleDbCommandBuilder(da); **** ERRORS OUT HERE***
                        int count1 =da.Update(ds);
                        int count=count1;
                  
                  }
                  catch(Exception err)
                  {
                        string str=err.Message;
                        bIsError=true;
                  }
                  finally
                  {
                        con.Close();
                  }
0
Comment
Question by:ptslv
8 Comments
 

Author Comment

by:ptslv
ID: 13962975
Sorry, the error is at the following line in the code:

int count1 =da.Update(ds);

ptslv
0
 
LVL 13

Expert Comment

by:dungla
ID: 13964942
Can you show me the error message? I think just because of your query, you just select TOP 1

You don't need to create new DataTable like that DataTable mytable =ds.Tables[0];, you can use the code below instead of

DataRow myRow = ds.Tables[0].NewRow();
foreach(DataColumn c in ds.Tables[0].Columns)
{
             if(c.ColumnName=="AutoID")
                     continue;
             myRow[c.ColumnName]=ControlTypeCheck.CheckControlType(Page.FindControl(c.ColumnName),c.ColumnName);
}
ds.Tables[0].Rows.Add(myRow);
strSQL = "SELECT [DateSubmit],[LastN],[FirstN],[MI],[Org],[Phone],[Email],[Role],[Tng],[Tng_Complete],[Title],[Title_Months],[Use],[Use_Months],[Computer],[Education],[Comments] FROM Personnel";
da.SelectCommand = new OleDbCommand(strSQL, con);
ds.EnforceConstraints = false;
da.Update(ds);
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 13967104
You'll need these to bind the data adapter with the command builder:

da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.UpdateCommand = cb.GetUpdateCommand();

Bob
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:ptslv
ID: 13967842
The code I have now worked great until I changed the fields in the table changed and I had to make adjustments.

ptslv
0
 

Author Comment

by:ptslv
ID: 13979089
dungla,

I tried what you said and got the following error: {"Update requires a valid InsertCommand when passed DataRow collection with new rows." }

Here's my code:

private void Submit_Click(object sender, System.EventArgs e)
{
     bool bIsError=false;
     OleDbConnection con = new OleDbConnection(strConnection);
     string strSQL;
     DataSet ds = new DataSet();
     strSQL="SELECT [Last],[First] FROM Personnel";

     try
     {
          OleDbDataAdapter da = new OleDbDataAdapter(strSQL,con);
          da.Fill(ds);

          DataRow myRow = ds.Tables[0].NewRow();
      foreach(DataColumn c in ds.Tables[0].Columns)
      {
           if(c.ColumnName=="AutoID")
            continue;
           myRow[c.ColumnName]=ControlTypeCheck.CheckControlType(Page.FindControl(c.ColumnName),c.ColumnName);
      }
            ds.Tables[0].Rows.Add(myRow);
            da.SelectCommand = new OleDbCommand(strSQL, con);
            ds.EnforceConstraints = false;
            da.Update(ds);
     }
     catch(Exception err)
     {
      string str=err.Message;
      bIsError=true;
     }
     finally
    {
      con.Close();
    }
}
            
0
 

Author Comment

by:ptslv
ID: 13990842
Does anyone have any idea why my code (SEE FIRST COMMENT) would error out with "Syntax Error in Insert Statement" after changing field names?  I have triple-checked my field names in the table and everything matches.  The code worked before the change.

ptslv.
0
 

Author Comment

by:ptslv
ID: 13999894
I have fixed this problem without revising my code.  There were reserved words as field names in the table.  The fields were renamed.  I am going to ask for cancellation of this question.  Thanks for the responses, tho.
0
 

Accepted Solution

by:
OzzMod earned 0 total points
ID: 14059401
Closed, 250 points refunded.
OzzMod
Community Support Moderator (Graveyard shift)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month19 days, 16 hours left to enroll

873 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