Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

problem with sqlString - asp.net c#

Posted on 2004-08-11
10
Medium Priority
?
991 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
Technology Partners: 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 150 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

721 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