INSERT INTO not inserting - losing will to live, please help!

[xavstone]

Hi All,

Have come back to programming after a lengthy break for a personal project using visual c# and SQL Server Express in a win forms app.

The problem I have is that my INSERT INTO statements are not working, while my SELECT statements are working perfectly.

While debugging the command seems to fire just fine, ExecuteNonQuery() returns 1 and no exception caught. But when looking at the table there is NO DATA!! Have tried obvious things like refreshing the table and restarting Visual c#. Still no data...

Any ideas greatly appreciated. Thanks in advance.

Xavi

Method here if it helps:

public static void addSymbol(string symbolCode, string securityName)
{

SqlConnection con = RM1.Classes.StaticData.GetConnectionString;

SqlCommand com = new SqlCommand("INSERT INTO symbol (symbolCode, securityName) VALUES (@symbolCode, @securityName)", con);
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@symbolCode", symbolCode);
com.Parameters.AddWithValue("@securityName", securityName);


int records = 0;
try
{
con.Open();

records = com.ExecuteNonQuery();

Console.WriteLine(records + " symbols added");

}
catch (Exception ex)
{
Console.WriteLine("Add symbol failed" + ex.ToString());

}
finally
{
con.Close();
}

}

 

[r937]

change this --

VALUES (@symbolCode, @securityName)

to this --

VALUES ( '@symbolCode' , '@securityName' )




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Your code seems to be correct. Did you try opening connection first (before instantiating a command)?

Also, if you're using any SQL Server version besides Express, I suggest to fire SQL Server profiler and examine what is exactly being sent (if anything) to SQL Server.

PluralSight Learning Library

 

[SgtJarrow]

You are building your SQL statement in your code...so you need to place the parameter values you passed into the SQL statement. Dynamic SQL as it were.

Change

SqlCommand com = new SqlCommand("INSERT INTO symbol (symbolCode, securityName) VALUES (@symbolCode, @securityName)", con);

To

SqlCommand com = new SqlCommand("INSERT INTO symbol (symbolCode, securityName) VALUES ('" + symbolCode + "', '" + securityName + "')", con);

NOTE: If either of the values is an integer, you can remove the single quotes around that value in the SQL creation line above.

=======================================
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

 

[bborissov]

Run SQL Profiler, like markros said and see what is sent to SQL Server.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

Robert L. Johnson,

This is a very bad advice. Don't use the way above unless you're forced to, because it opens a way to SQL injection. The original code was already OK and secure, although AddWithValue is not as good as Add method and it's going to deprecation. Better to be explicit and use Parameters.Add method instead of AddWithValue.

PluralSight Learning Library

 

[xavstone]

Finally figured it out. It was nothing to do with my code but the way Visual C# compiles. Basically it copies across the previous version of the .mdf file (the one prior to the build) back to the working directory after the solution is closed again. Or something similar.

I got round this by setting the properties of the mdf and the log file in the solution to 'copy if newer' and replaced the |Data Directory| from the connection string with the physical location of the mdf. Hey presto, I have have data.

Thank you very much for all your assistance.

xavi

 

[SgtJarrow]

markros,

I would never personally use the code as I posted above. I would create a stored procedure for this and pass in the parameters as you suggested. I (almost) never put any sort of SQL statement in my application code.

I was merely trying to state why the code in the original post did not work as I saw it.

=======================================
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