Solved

problem with sqlString - asp.net c#

Posted on 2004-08-11
10
979 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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