SQL Statement Error in C#

[Pluto87]

Hello, I am having a trouble getting this piece of code to work. When users click on the "New" button, they enter the information into the text boxes and click "save". The data is suppose to be saved in SEQUEL. But once the user execute, there is a database error. I believe it is in the sql statements.



string strSQL = "";

DataTable Fields = new DataTable();


switch (ForAction.ToString())
{
case "New":
strSQL = "Insert into Test.Data ";
strSQL += " (";
strSQL += " Test_Number, ";
strSQL += " Test_Code ";
strSQL += "Values ( ";
strSQL += " " + Test_Number.Text.ToString().ToUpper().Trim() + ", ";
strSQL += " " + Test_Code.Text.ToString().ToUpper().Trim() + " ";

strSQL += ")";

Can someone see what is wrong? Thanks for the help in advance!

 

[SgtJarrow]

To answer your question....you are missing the red line indicated below:

strSQL += "     Test_Code ";
[red]strSQL += "     )";[/red]
strSQL += "Values ( ";

You need to close the column list at the top of your statement.

More importantly, you might want to consider taking your SQL code out of your application and putting them into stored procedures on your SQL server instead. You are possibly leaving yourself open to SQL Injection attacks and a whole collection of other problems that might arise from coding in this manner.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[Pluto87]

Thanks for your suggestion. But it is still giving me the same database error.

Yes, thank you again for your suggestion, although, this window application is only a test application and we will not be using this publicly. But I still need this piece of code fix.

Thanks!

 

[SgtJarrow]

OK....another reason for stored procedures is your second problem.

I am pretty sure that either Test_Number and/or Test_Code are text values and not integers or numeric values. In that case, you must wrap the value in single quotes. So you probably need the following:

 strSQL = "Insert into Test.Data ";
                                strSQL += "     (";
                                strSQL += "     Test_Number, ";
                                strSQL += "     Test_Code ";
                                strSQL += "     )";
                                strSQL += "Values ( ";
                                strSQL += "      '" + Test_Number.Text.ToString().ToUpper().Trim() + "', ";
                                strSQL += "      '" + Test_Code.Text.ToString().ToUpper().Trim() + "' ";

                                 strSQL += ")";
[red]    MessageBox.Show(strSQL);[/red]

If either Test_Number and/or Test_Code is TRULY a numeric value, remove the single quotes.

You also might want to add the red line above for validating purposes. You said this is a windows app and this line will display the SQL statement before executing it...You can use this to visually see if the statement is being created properly. Or set a break point at the last line that is building the strSQL line and then check your values during runtime to ensure the SQL statement is correct...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[Pluto87]

Works perfectly!! Thanks so much!