• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 995
  • Last Modified:

problem with sqlString - asp.net c#

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
mwoolgar
Asked:
mwoolgar
  • 3
  • 3
  • 3
  • +1
1 Solution
 
mmarinovCommented:
Hi,

there is special words in your statement
try this

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

Regards,
B..M
0
 
Arthur_WoodCommented:
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
 
ThogekCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mwoolgarAuthor Commented:
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
 
ThogekCommented:
Can you post the newly-updated SqlString-building code?
0
 
Arthur_WoodCommented:
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
 
mwoolgarAuthor Commented:
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
 
mwoolgarAuthor Commented:
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
 
ThogekCommented:
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
 
Arthur_WoodCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now