Solved

problem with sqlString - asp.net c#

Posted on 2004-08-11
10
971 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
  • 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now