Error in SQL Line in C#

Question

3.50/5 (2 votes)

See more:

I am getting error in Invalid Expression Term "," in

SqlConnection sqlconn = new SqlConnection("Data Source=ARSALAN-PC;Initial Catalog=latestsensordata;Integrated Security=True");
SqlDataAdapter sa = new SqlDataAdapter();

string query = "insert into Table_1(SensorValue,Time) values(" +hexValue+, +DateTime.Now.ToString("HH:MM:ss")+")";

sa.InsertCommand = new SqlCommand(query, sqlconn);

Posted 16-Jan-13 23:38pm

ontheline89

Updated 16-Jan-13 23:40pm

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2013-01-16T23:44:00

Solution 1

If you double click on the error in the IDE it will take you straight to the problem.
You have a comma after hexValue+ - You're actually missing some double quotes

Posted 16-Jan-13 23:44pm

CHill60

OriginalGriff · Accepted Answer · 2013-01-16T23:50:00

Solution 3

First off, don't do it like that: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Secondly, fixing the first one will solve the second: you need some more double quotes.

C#

string query = "insert into Table_1(SensorValue,Time) values(" +hexValue+, +DateTime.Now.ToString("HH:MM:ss")+")";

Becomes

C#

string query = "insert into Table_1(SensorValue,Time) values(" +hexValue+ ", " +DateTime.Now.ToString("HH:MM:ss")+")";

But do use parametrised queries:

C#

string query = "insert into Table_1(SensorValue,Time) values(@SV, @TM)";
sa.InsertCommand = new SqlCommand(query, sqlconn);
sa.InsertCommand.Parameters.AddWithValue("@SV", hexValue);
sa.InsertCommand.Parameters.AddWithValue("@TM", DateTime.Now.ToString("HH:MM:ss"));

BTW: It is a bad idea to use the same name for a field as a datatype - it can cause problems with some queries. Change "Time" to "SpotTime" or something else which describes it better.

[edit] :doh: Copy and Paste is great - when you remember to update the variable names... "@SV" duplicate changed to "@TM" to match the SELECT statement - OriginalGriff[/edit]

Posted 16-Jan-13 23:50pm

OriginalGriff

Updated 17-Jan-13 0:07am

v2

Comments

ontheline89 17-Jan-13 6:01am

I am still getting error
Must declare the Scalar Variable at @SV

OriginalGriff 17-Jan-13 6:08am

:doh:!
I forgot to update the variable name @SV to @TM to match the select statement when I copy and pasted the line start. Fixed now, sorry about that...

ontheline89 17-Jan-13 6:24am

I have two columns in SQL Server
SensorValue ( int )
Time (nchar)

OriginalGriff 17-Jan-13 6:35am

It is a bad idea to store date and / or time values as strings - it means you have to convert them to work with them. Store them in your DB as a Time datatype instead (then you can compare them a lot more easily if you need to later)

ontheline89 17-Jan-13 6:21am

but it still giving the same error.
Must declare the Scalar Variable at @SV.

OriginalGriff 17-Jan-13 6:31am

Copy and paste your exact code - it should work (most people do something like this all the time)

ontheline89 17-Jan-13 6:33am

#region DisplayData

private void DisplayData(MessageType type, string msg)
{
richTextBox1.Invoke(new EventHandler(delegate
{

string substring = string.Empty;
if (msg.Length > 57)
substring = msg.Substring(54, 2);

int x;

bool f = Int32.TryParse(substring, System.Globalization.NumberStyles.HexNumber, null, out x);
if (f)
{

string hexValue = x.ToString();
richTextBox1.AppendText(hexValue);

try
{

SqlConnection sqlconn = new SqlConnection("Data Source=ARSALAN-PC;Initial Catalog=latestsensordata;Integrated Security=True");
SqlDataAdapter sa = new SqlDataAdapter();

string query = "insert into Table_1(SensorValue) values(" +hexValue+")";

sa.InsertCommand = new SqlCommand(query, sqlconn);

sqlconn.Open();
try
{
sa.InsertCommand.ExecuteNonQuery();
}
catch (FormatException ex) { MessageBox.Show(ex.Message); }
MessageBox.Show("Record Inserted");
sqlconn.Close();
}
catch (SqlException ea) { MessageBox.Show(ea.Message); }

}

OriginalGriff 17-Jan-13 6:37am

No, copy and paste the exact code that causes the error message - not the code that doesn't! :laugh:

ontheline89 17-Jan-13 6:30am

If i use
string query = "insert into Table_1(SensorValue) values(" +hexValue+")";

Then sensor value is stored in database.

OriginalGriff 17-Jan-13 6:33am

Yes - but it leave you open to damage. For example, if hexvalue contains data which can be interpreted as SQL commands then your database can be deleted by a user half way round the world.
Google for "Bobby Tables" - and don't assume it is a joke. It is, but it's also very, very true.

ontheline89 17-Jan-13 6:36am

you are absolutely right, i dont have expertise in databases and c# thats why i am confused why i am not getting time with values.

ontheline89 17-Jan-13 6:39am

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO.Ports;
using System.Media;
using System.Data.SqlClient;

namespace WindowsFormsApplication2
{
public partial class Form1 : Form

{
public Form1()
{

InitializeComponent();
serialPort1.Open();
}
public void playSimpleSound()
{
SoundPlayer simpleSound = new SoundPlayer(@"c:\smokealarm.wav");
simpleSound.Play();
}

private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'latestsensordataDataSet1.Table_1' table. You can move, or remove it, as needed.
this.table_1TableAdapter1.Fill(this.latestsensordataDataSet1.Table_1);
// TODO: This line of code loads data into the 'latestsensordataDataSet.Table_1' table. You can move, or remove it, as needed.
this.table_1TableAdapter.Fill(this.latestsensordataDataSet.Table_1);
//configuring the serial port
serialPort1.PortName = "COM14";
serialPort1.BaudRate = 115200;
serialPort1.DataBits = 8;
serialPort1.Parity = Parity.None;
serialPort1.StopBits = StopBits.One;
serialPort1.DataReceived += new SerialDataReceivedEventHandler(serialPort1_DataReceived);

}

public enum MessageType { Incoming, Outgoing, Normal, Warning, Error };

private byte[] HexToByte(string msg)
{
//remove any spaces from the string
msg = msg.Replace(" ", "");
//create a byte array the length of the
//divided by 2 (Hex is 2 characters in length)
byte[] comBuffer = new byte[msg.Length / 2];
//loop through the length of the provided string
for (int i = 0; i < msg.Length; i += 2)
//convert each set of 2 characters to a byte
//and add to the array
comBuffer[i / 2] = (byte)Convert.ToByte(msg.Substring(i, 2), 16);
//return the array
byte[] newbuffer = comBuffer;
return comBuffer;
}

private string ByteToHex(byte[] comByte)
{
//create a new StringBuilder object
StringBuilder builder = new StringBuilder(comByte.Length * 3);
//loop through each byte in the array
foreach (byte data in comByte)
//convert the byte to a string and add to the stringbuilder
builder.Append(Convert.ToString(data, 16).PadLeft(2, '0').PadRight(3, ' '));
//return the converted value
return builder.ToString().ToUpper();
}

#region DisplayData

private void DisplayData(MessageType type, string msg)
{
richTextBox1.Invoke(new EventHandler(delegate
{

string substring = string.Empty;
if (msg.Length > 57)
substring = msg.Substring(54, 2);

int x;

bool f = Int32.TryParse(substring, System.Globalization.NumberStyles.HexNumber, null, out x);
if (f)
{

string hexValue = x.ToString();
richTextBox1.AppendText(hexValue);

try
{

SqlConnection sqlconn = new SqlConnection("Data Source=ARSALAN-PC;Initial Catalog=latestsensordata;Integrated Security=True");
SqlDataAdapter sa = new SqlDataAdapter();

string query = "insert into Table_1(SensorValue,Time) values(@SV, @TM)";
sa.InsertCommand = new SqlCommand(query, sqlconn);

OriginalGriff 17-Jan-13 6:41am

So, you didn't notice the two lines below
sa.InsertCommand = new SqlCommand(query, sqlconn);
which set the variables? Perhaps if you add them as well, it might work a little better? :laugh:

ontheline89 17-Jan-13 6:44am

seriously i didnt get your point.

OriginalGriff 17-Jan-13 6:55am

If you look at my original post there are two lines below the creation of the SelectCommand object which define the parameters, and set the values from your variables into them:

sa.InsertCommand.Parameters.AddWithValue("@SV", hexValue);
sa.InsertCommand.Parameters.AddWithValue("@TM", DateTime.Now.ToString("HH:MM:ss"));

If you do not include these lines as well, then the parameters are not defined to SQL and it doesn't know what to do and complains. Add them, and all should be fine.

ontheline89 17-Jan-13 6:57am

i am so sorry i have taken so much time of yours.
Yes i have put these lines in my code in Visual Studio, actually these lines were not copied here.
Thank you so v much for your time and guidance.

OriginalGriff 17-Jan-13 7:00am

You're welcome!

ontheline89 17-Jan-13 6:48am

what i need to change in it now ?

Daniele Rota Nodari · Accepted Answer · 2013-01-16T23:46:00

Hi.

You missed two things inside the SQL statement:
- Double quotation marks around the "," between the two "+".
- Single quotes (or other separator) surrounding the DateTime string representation.

So the code should be:

string query = "insert into Table_1(SensorValue,Time) values(" +hexValue+ ",
                    '" +DateTime.Now.ToString("HH:MM:ss") + "')";

Regards,
Daniele.