Solved

problem with sqlString - asp.net c#

Posted on 2004-08-11
10
983 Views
Last Modified: 2009-07-29
Hi

I have an asp.net [c#] page that queries a database which worked fine until I entered a new field called Deactivated.

The error message suggest there is something wrong with the sql string, but for the life of me I can't see why it is wrong.
Any help would be most appreciated.

Many thanks in advance

Regards

Miles

ERROR::
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'Deactivated'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.workingdatagrid_aspx.myDataGrid_Update(Object Sender, DataGridCommandEventArgs e) in C:\Inetpub\wwwroot\uap\workingdatagrid.aspx:line 63

CODE::

<%@ Page Language="c#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

  // public string ConnString = "server=uap;database=pap;uid=sa;pwd=z00;";
     public string ConnString = "Server=(local); user id=sa;password=;initial catalog = UAP;";
    void BindData()
    {
        //-- Using the Try statement, we attempt to connect to our
        //-- database, execute a SqlDataAdapter to store our data,
        //-- populate a dataset and then bind that dataset
        //-- to our DataGrid.
       try
       {
            SqlConnection SqlConn = new SqlConnection(ConnString);
            string SqlString = "SELECT uapID, DealershipName, AutoExchangeClientID, Deactivated FROM uapForm";
            SqlDataAdapter SqlComm = new SqlDataAdapter(SqlString, SqlConn);
            DataSet customerData = new DataSet();
            SqlComm.Fill(customerData, "uapForm");
   
            myDataGrid.DataSource = customerData;
            myDataGrid.DataBind();
   
            SqlConn.Close();
            SqlComm.Dispose();
            SqlConn.Dispose();
       }
       
       //-- If we are not able to connect, display a friendly error
       catch (Exception e)
       {
            ErrorLabel.Text = "Not able to connect to database. See description below: <P>";
            ErrorLabel.Text += e.ToString();
       }
   
    }
   
    void myDataGrid_Update (object Sender, DataGridCommandEventArgs e)
    {
        //-- Take the data from each textbox in our editable item
        //-- and assign that text to a string variable
         string uapID = Convert.ToString(e.Item.Cells[0].Text);
         string DealershipName = ((TextBox) e.Item.Cells[1].Controls[0]).Text;
             string AutoExchangeClientID = ((TextBox) e.Item.Cells[2].Controls[0]).Text;
         string Deactivated  = ((TextBox) e.Item.Cells[3].Controls[0]).Text;
             
                         
        //-- Again, using the Try statement, attempt to connect to our database
        //-- and make an update with the data from our datagrid    
         SqlConnection SqlConn = new SqlConnection(ConnString);
         try
         {
                SqlConn.Open();
                string SqlString = "UPDATE uapForm ";
                    SqlString += "SET DealershipName = '" + DealershipName.Replace("'", "''") + "', ";
                   //error - third character from end - comma was removed
                           //SqlString += "TradingName = '" + TradingName.Replace("'", "''") + "', ";
                    SqlString += "AutoExchangeClientID = '" + AutoExchangeClientID.Replace("'", "''") + "' ";
                              SqlString += "Deactivated  = '" + Deactivated.Replace("'", "''") + "' ";  
                              SqlString += " WHERE uapID = '" + uapID + "'";
                SqlCommand SqlComm = new SqlCommand(SqlString, SqlConn);
                SqlComm.ExecuteNonQuery();
                SqlConn.Close();
                SqlComm.Dispose();
                SqlConn.Dispose();
          }
         
          //-- If for some reason we cannot connect, display a friendly error.
          catch (Exception exc)
          {
                ErrorLabel.Text = "Not able to connect to database. <br>Please See description below:<P> <P>";
                ErrorLabel.Text += exc.ToString();
          }
         
         //-- Remove the edit focus
         myDataGrid.EditItemIndex = -1;
         //-- Rebind our datagrid
         BindData();
    }
   
    void myDataGrid_Cancel(object Sender, DataGridCommandEventArgs e)
    {
        //-- Remove the edit focus
         myDataGrid.EditItemIndex = -1;
         //-- Rebind our datagrid
         BindData();
    }
   
    void myDataGrid_Edit(object Sender, DataGridCommandEventArgs e)
    {
         //-- Set the edit focus to the item that was selected
         myDataGrid.EditItemIndex = (int) e.Item.ItemIndex;
         //-- Rebind our datagrid
         BindData();
    }
   
    void Page_Load (object Sender, EventArgs e)
    {
       //-- If the page is not posting back, bind our datagrid
       if (!Page.IsPostBack)
       {
         BindData();
       }
    }

</script>
0
Comment
Question by:mwoolgar
[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
  • 3
  • +1
10 Comments
 
LVL 28

Expert Comment

by:mmarinov
ID: 11774481
Hi,

there is special words in your statement
try this

SELECT [uapID], [DealershipName], [AutoExchangeClientID], [Deactivated] FROM uapForm

Regards,
B..M
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 11775222
my guess is that the error is being thrown in this code:

string SqlString = "UPDATE uapForm ";
                    SqlString += "SET DealershipName = '" + DealershipName.Replace("'", "''") + "', ";
                   //error - third character from end - comma was removed
                       //SqlString += "TradingName = '" + TradingName.Replace("'", "''") + "', ";
                    SqlString += "AutoExchangeClientID = '" + AutoExchangeClientID.Replace("'", "''") + "' ";
                         SqlString += "Deactivated  = '" + Deactivated.Replace("'", "''") + "' ";  
                         SqlString += " WHERE uapID = '" + uapID + "'";


and probably because of the presence of a '' in the modified text of the variable Deactivated ( in Deactivated.Replace("'", "''") ).  what are the possible values for the variable Deactivated, in this statement?

AW
0
 
LVL 15

Expert Comment

by:Thogek
ID: 11775513
I think Arthur_Wood's on the right track.  The error occurred within a call to SqlCommand.ExecuteNonQuery, which is how you call an UPDATE command, not a SELECT command.

If you're using VS.NET, perhaps you could add a breakpoint at the line Arthur_Wood quoted above, and determine the value fo the SqlString variable right after this line executes.

Thought:
Should
    SqlString += "AutoExchangeClientID = '" + AutoExchangeClientID.Replace("'", "''") + "' ";
be updated to add a comma (between AutoExchangeClientID and Deactivated in the SQL) like so
    SqlString += "AutoExchangeClientID = '" + AutoExchangeClientID.Replace("'", "''") + "', ";
?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mwoolgar
ID: 11778943
Thanks for the reply guys.
Unfortunately adding an extra coma in the AutoExchangeClientID just gave another error:

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.edit_datagrid2_aspx.myDataGrid_Update(Object Sender, DataGridCommandEventArgs e) in C:\7418BegASPNETdb\webroot\ch02\edit_datagrid2.aspx:line 65

As you can see in the code I had already had this problem.
Unfortunately I don't have VS.NET ;-(
0
 
LVL 15

Expert Comment

by:Thogek
ID: 11779159
Can you post the newly-updated SqlString-building code?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 11780319
so you are doing this in Notepad, or WebMatrix?

Yes, please post to current state of the String building code as you now have it.


AW
0
 

Author Comment

by:mwoolgar
ID: 11781635
Hi

Things have not changed that much. Deactivated is simply a 1 or 0 depending if it is deactivated or not.

The current string is:

<script runat="server">

         {
                SqlConn.Open();
                string SqlString = "UPDATE uapForm ";
                SqlString += "SET DealershipName = '" + DealershipName.Replace("'", "''") + "', ";
                SqlString += "AutoExchangeClientID = '" + AutoExchangeClientID.Replace("'", "''") + "' ";
      SqlString += "Deactivated  = '" + Deactivated.Replace("'", "''") + "', ";  
      SqlString += " WHERE uapID = '" + uapID + "'";
                SqlCommand SqlComm = new SqlCommand(SqlString, SqlConn);
                SqlComm.ExecuteNonQuery();
                SqlConn.Close();
                SqlComm.Dispose();
                SqlConn.Dispose();
          }
         
    </script>

cheers

Miles

0
 

Author Comment

by:mwoolgar
ID: 11784267
Hi All

I've solved it myself. The AutoExchangeClientID line was missing a comma near the end whilst the Deactivated line had to have a comma removed.

 SqlString += "SET DealershipName = '" + DealershipName.Replace("'", "''") + "', ";
 SqlString += "AutoExchangeClientID = '" + AutoExchangeClientID.Replace("'", "''") + "', ";
 SqlString += "Deactivated  = '" + Deactivated.Replace("'", "''") + "' ";  

cheers

Miles
0
 
LVL 15

Expert Comment

by:Thogek
ID: 11785137
Hmm.  Comparing the above code snippets, it sounds like when you added the comma I suggested, you may have put it in the wrong line.  :-)

Oh, well.  Fixed is fixed, eh?
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 50 total points
ID: 11788259
if Deactivates if a 1 or 0, then there is no need for the Replace functin cann, and you should NOT enclose the value in '...', so change this:

 SqlString += "Deactivated  = '" + Deactivated.Replace("'", "''") + "' ";  

to:

 SqlString += "Deactivated  = " + Deactivated ;

and you should be good-to-go.

AW  
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

734 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