SQLException in Windows forms application

Question

At the very begining please allow me to convey my regards and respect to everyone from StackOverFlow and all the users. You guys are doing a great job helping out people like us. Thankyou.

Now to my question: I am building a winforms application, for learning purpose, that: 1) Connects to a database and retrives results based on a query. 2) Query is generated by either: * The user clicking on various controls of the application or * The user writing the query herself and then executing it. 3) The results are displayed on the resultbox.

I am getting a SQLException of "Incorrect syntax near',' " The syntax generated is correct because: 1) I copied the generated query and executed it in SQL Management Studio. It executes there flawlessly. 2) I tried writing the query manually and then executing it but it still throws the same execption.

Please note that at the first stage of the appliaction development it had three controls only, a DataGridView control, a TextBox control and a Button control. At that stage I sucessfully generated results and displayed them. After I added the the extra features the application stopped working. I cannot understand why. I am using the same code I used in first stage of development, infact it is the same appliaction. I have hence made another application with the three basic controls mentioned earlier and copied the query generated by the primary appliaction and then executed it in the second application. It works there.

At the current stage the application throws the same execption if I use the generated query or if I write the query myself and then execute it. Here is my code. When user clicks on "Execute" button the LoadData method is called.

try
{

    string ConnectString = "Data Source=(local); Initial Catalog=AdventureWorks2008;User ID=; Password=;Integrated Security=SSPI";
    ConnectionObj.ConnectionString = ConnectString;//ConnectionObj is SQLConnection object defined in the same class as Loaddata()
    ConnectionObj.Open();
    ColumnNames = string.Empty;//ColumnNames is a String type defined in the same class as Loaddata()

    foreach (string Name in ColumnNamesCheckedListBox.CheckedItems)//ColumnNamesCheckedListBox is a CheckedListBox control which lets user select ColumnNames from the corresponding table
    {
        ColumnNames = ColumnNames + Name + ", ";
    }
    int Length = ColumnNames.Length;
    Length = Length - 2;//To remove the extra "," and "<space>" at the end of ColumnNames
    string TempQuery = ColumnNames.Remove(length);

    //User may use the query formed by the application or she may write her own query, hence using "string PossibleQuery"
    //SelectQueryDropDownList is a ComboBox control holding the items "Select" "Update" "Insert" and "Delete". Used to generate the DML clause in the query
    //Database_TreeView is a TreeView control that holds all the table names in the database. This is used to generate the "FROM" clause in the query
    //QueryBox is a TextBox control that displays the generated query and if required lets the user write her own query manually

    string PossibleQuery = SelectQueryDropDownList.SelectedItem.ToString() + TempQuery + " From " + Database_TreeView.SelectedNode.Name.ToString();
    QueryBox.TempQuery = PossibleQuery;
    string FinalQuery = QueryBox.Text; //incase the user modified the query at QueryBox manually

    SqlDataAdapter DA = new SqlDataAdapter(FinalQuery, ConnectString);
    SqlCommandBuilder CommandBuilder = new SqlCommandBuilder(DA);
    DataTable Table = new System.Data.DataTable();


    //EXCEPTION OCCURS AT THIS STATEMENT
    DA.Fill(Table);


    BindingSource Source = new BindingSource();
    Source.DataSource = Table;
    ResultBox.DataSource = Source;//ResultBox is a DataGridView control to display the results of the the query after execution (if any)
}
catch (Exception e)
{
MessageBox.Show(e.Message+"\nPlease try again","Error",MessageBoxButtons.OK);
}
finally
{
ConnectionObj.Close();
}

Here are the Screenshots: This is the query generated my the application. http://i60.photobucket.com/albums/h31/spiderclaws/Stack%20Over%20Flow/1ScreenShot2013-04-23at54453PM.png

This shows the exception generated. http://i60.photobucket.com/albums/h31/spiderclaws/Stack%20Over%20Flow/2ScreenShot2013-04-23at54550PM.png

Please help me out. Thanx. Regards, Gogol Prasad ([email protected]) P.S.- please excuse me for writing such a long post. I wanted it to be clear for everyone.

[Edit] Here is the info as requested: Please note that I am querying Person.BusinessEntityContact as per the screenshot...

FinalQuery=Select BusinessEntityID, PersonID, ContactTypeID From Person.BusinessEntityContact

StackTrace info:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at ADOBasicsWinFormsApp.Form1.loaddata() in C:\Users\Gogol\Documents\Visual Studio 2010\Projects\ADOBasicsConsoleApp\ADOBasicsWinFormsApp\Form1.cs:line 684

What was the exception? Can you post the stack trace or exception text? — Ramesh Durai
– Ramesh Durai, Commented Apr 23, 2013 at 14:10
The exception says "incorrect syntax", so post the contents of FinalQuery variable and you get the answer in no time — Steve
– Steve, Commented Apr 23, 2013 at 14:18
I am outside at the moment. I will post the StackTrace info and the FinalQuery content as soon as i get back to my system. Thanx for the reply to everyone. — Gogol
– Gogol, Commented Apr 23, 2013 at 14:31
although you could visit the screen shot link in the meantime...it shows the generated query in the QueryBox — Gogol
– Gogol, Commented Apr 23, 2013 at 14:32

Steve · Accepted Answer · 2013-04-23 14:47:56Z

2

Looking at your image seems that a space is missing between SELECT and the first field

So add a space between the SelectedItem and TempQuery

 string PossibleQuery = SelectQueryDropDownList.SelectedItem.ToString() + " " +
                        TempQuery + " From " + Database_TreeView.SelectedNode.Name.ToString();

(I assume that SelectQueryDropDownList contains the word SELECT)

Apart from that I would suggest to use a StringBuilder to construct your column names

StringBuilder cols = new StringBuilder()
// A check here is required to avoid empty selections
foreach (string Name in ColumnNamesCheckedListBox.CheckedItems)
   cols.Append(Name + ",");
if(cols.Length > 0) cols.Length -= 2;
string TempQuery = cols.ToString();

Also the SqlConnection object created at the start of the method is not passed in to your DataAdapter and never used elsewhere, the SqlDataAdapter receive your connectionstring and so it manages the connection itself, meaning it opens the connection and it closes the connection. So you could remove it.

edited Apr 23, 2013 at 14:47

answered Apr 23, 2013 at 14:24

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gogol Over a year ago

thanx a ton!! Yes I was missing a space between "select" and the columnames....it solved the problem. Also thanx for the additional info about the connectObj and Stringbuilder. This problem has been solved. I am replying from my phone. I will mari the thread as answered/solved/closed tomorrow when I visit the cyber-cafe.

Gogol Over a year ago

Thankyou everybody for the help. The application works like a charm now. :)

Rohrbs · Accepted Answer · 2013-04-23 14:14:12Z

1

Here it is

int length = ColumnNames.Length;
length = length - 2;
string TempQuery = ColumnNames.Remove(length);

Change your local Length variable to be lowercase l.

Or you could rewrite it to get rid of the local variable:

string TempQuery = ColumnNames.Remove(ColumnNames.Length - 2);

answered Apr 23, 2013 at 14:14

Rohrbs

1,85513 silver badges11 bronze badges

6 Comments

Steve Over a year ago

Right, but this could not the be the source of the SqlException. This is probably a typo because the code could not compile (unless a global variable named length exists....)

Rohrbs Over a year ago

Bad parameter name was causing a failure to remove the trailing comma from the column selection, so Incorrect syntax near','

Steve Over a year ago

As you know length != Length. Where is defined length?. Without a lowercase length defined somewhere the code doesn't compile

Rohrbs Over a year ago

Yes, I do know that. Who knows what he has defined local to the class? Until that section of code is fixed, and considering it's the only place where a string containing a comma is being manipulated, I'm sticking by my answer. :)

Rohrbs Over a year ago

Ok, well it was not good looks. Local variables should always start with a lower case letter; plus as Steve mentioned, it would not compile like that. Steve's answer below seems to be the correct one.

|

Collectives™ on Stack Overflow

SQLException in Windows forms application

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related