Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL exception when field has two words or a space

Author  Topic 

chrstdvd
Starting Member

3 Posts

Posted - 2014-09-21 : 08:49:20
Win 7, VS 2013 Express for Desktop, C #

I get exception 80131904 when there basically says Syntax error near 'Checking" when field is '[My Checking]' or the second word if other than checking.

I am writing a checkbook program that adds accounts as tables to the database. The create table sql works fine.

After the table is created I want to enter the first transaction as the opening balance and amount and have the Category be the Account Name in brackets. [oneword] = no exception and the line is entered. [one word] gets an exception.

Here is the sql just before execute:
insert into My Checking(Id, Date, CheckNr, Payee, [Category], Payment, Clr, Deposit, Balance)values(1,'1/1/2014','','Opening Balance',[My Checking],'','','1500.',0)

I have had the code so it says '[My Checking]' in the sql get same results. I have tried it by "[" + Name + "]" and get the same thing.
Here is the Function:

private void AddOpenBal(string Name, string open, string bal) //OFFENDING FUNCTION FOR MULTIPLE WORDS IN TABLE NAME
{
string connetionString = null;
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
string sql = null;
GetLastId(Name);
newId = lastId + 1;
string cknr = "";
string pmt = "";
string c = "";
object name = Name;
string payee = "Opening Balance"; ;
string dep = bal;
//bal = need to get a search for provious balance then this becomes 'prevbal + dep - pmt'
bal = "0";
connetionString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|" +
"\\Checkbook.mdf;Integrated Security=True;Connect Timeout=30";

connection = new SqlConnection(connetionString);

sql = "insert into " + Name + "(Id, Date, CheckNr, Payee, [Category], Payment, Clr, Deposit, Balance)" +
"values(" + newId + ",'" + open + "','" + cknr + "','" + payee + "'," + CatName + ",'" + pmt + "','" + c + "','" + dep + "'," + bal + ")";
try
{
connection.Open();
adapter.InsertCommand = new SqlCommand(sql, connection);
adapter.InsertCommand.ExecuteNonQuery();
Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

}

Any ideas? Thanks

dmc

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-21 : 08:57:27
firstly, you should be using parameterised SQL to avoid injection attacks etc... - this is best practice and easier to trace your parameters too.

secondly, your table name contains a space - really, they shouldn't. you should perhaps place the square brackets around it:

INSERT INTO [My Checking]

with your code:

"INSERT INTO [" + Name + "]} ....


Go to Top of Page

chrstdvd
Starting Member

3 Posts

Posted - 2014-09-21 : 10:29:25
tech 1, thanks for the answer. You hit the nail on the head. It is two words in the table name that causes this. I changed the string to:
sql = "INSERT INTO [" + Name + "] (Id, Date, CheckNr, Payee, Category, Payment, Clr, Deposit, Balance)" +
"values(" + newId + ",'" + open + "','" + cknr + "','" + payee + "'," + CatName + ",'" + pmt + "','" + c + "','" + dep + "'," + bal + ")";

Now I get an exception: Invalid Column Name 'My Checking'

I know nothing about "parameterised SQL to avoid injection attacks".



dmc
Go to Top of Page

chrstdvd
Starting Member

3 Posts

Posted - 2014-09-21 : 12:23:51
quote:
Originally posted by tech_1

firstly, you should be using parameterised SQL to avoid injection attacks etc... - this is best practice and easier to trace your parameters too.

secondly, your table name contains a space - really, they shouldn't. you should perhaps place the square brackets around it:

INSERT INTO [My Checking]

with your code:

"INSERT INTO [" + Name + "]} ....



I could not figure out how to reply to my own reply, but anyway, I studied Parameterized SQL here:
http://www.dotnetperls.com/sqlparameter

And then rewrote the function:

private void AddOpenBal(string Name, string open, string bal)
{
SqlCommand command = new SqlCommand();
string connetionString = null;
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
string sql = null;
GetLastId(Name);
newId = lastId + 1;
string cknr = "";
string pmt = "";
string c = "";
string parm = "[" +Name+"]";
string payee = "Opening Balance"; ;
string dep = bal;
//bal = need to get a search for provious balance then this becomes 'prevbal + dep - pmt'
bal = "0";

connetionString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|" +
"\\Checkbook.mdf;Integrated Security=True;Connect Timeout=30";
using (connection = new SqlConnection(connetionString))
{

using (command = new SqlCommand("INSERT INTO " + parm + " (Id, Date, CheckNr, Payee, Category, Payment, Clr, Deposit, Balance)" +
"values(" + newId + ",'" + open + "','" + cknr + "','" + payee + "', + '" + parm + "' ,'" + pmt + "','" + c + "','" + dep + "'," + bal + ")")) ;
{
command.Parameters.Add(new SqlParameter("@Name", parm));
}

try
{
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
Close();
}

catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}

It works. I do not know why it works, or if I even needed to use 'parm'. But after 4 days of failure, I thank you for sending me in a different direction.

dmc
Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-21 : 12:47:04
no worries, glad I could help.
from your code snippet above, you are still using SQL injection. below is the code you should be using - excuse some typo's perhaps as I was doing this in notepad. I have also tidied up certain things


GetLastId(Name);
newId = lastId + 1;
string cknr = "";
string pmt = "";
string c = "";
string parm = "[" +Name+"]";
string payee = "Opening Balance"; ;
string dep = bal;
//bal = need to get a search for provious balance then this becomes 'prevbal + dep - pmt'
bal = "0";

string connetionString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|" +
"\\Checkbook.mdf;Integrated Security=True;Connect Timeout=30";


string sqlStmt = "INSERT INTO " + parm + " (Id, Date, CheckNr, Payee, Category, Payment, Clr, Deposit, Balance) values (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)";

using (SqlCommand command = new SqlCommand(sqlStmt, new SqlConnection(connectionString)))
{
command.Parameters.Add(new SqlParameter("@p1", newId));
command.Parameters.Add(new SqlParameter("@p2", open));
command.Parameters.Add(new SqlParameter("@p3", cknr));
command.Parameters.Add(new SqlParameter("@p4", payee));
command.Parameters.Add(new SqlParameter("@p5", parm));
command.Parameters.Add(new SqlParameter("@p6", pmt));
command.Parameters.Add(new SqlParameter("@p7", c));
command.Parameters.Add(new SqlParameter("@p8", dep));
command.Parameters.Add(new SqlParameter("@p9", bal));


try
{
command.Connection.Open()
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
}
}




Go to Top of Page
   

- Advertisement -