Why isn't my SQL query to insert a table row working properly?

Question

I am fairly new to C# and SQL, so this may be a very easy question to answer.

I am trying to add a row to a table (EventList) through C# code. I have opened my SqlConnection without any issues, and I know I am connected to the correct database as some earlier code is querying for rows in one of the tables and it's returning the correct keys.

The SQL query to insert the row into the table is like this:

sqlCmd.CommandText =
    "insert into EventList values ('" +
    eventListIdentifier + "','" +
    eventId.ToString() + "')";
sqlCmd.ExecuteNonQuery();

I am using SQL Server Management Studio Express to view the tables in my database. After running the above query, I right-click on the EventList table and click Open Table.

I am not seeing the new row added based on the above call. Any ideas what I may be doing wrong?

Update 1

The data types I'm inserting are:

eventListIdentifier (varchar(100), null)
eventId (varchar(8000), null)

I manually created the same query in SSMS like this:

insert into EventList(eventListIdentifier, eventId ) values('test', 'blah')

and says the following:

(1 row(s) affected)

However no row has been added to the table when I right-click on it and open it.

Update 2

Output of System.Console.WriteLine(sqlCmd.CommandText); as requested by @billinkc:

insert into EventList(eventListIdentifier, eventId) values ('7/09/2011 10:43:55 AM','7')

You should use parameterised queries not string concatenation but probably not relevant to your issue. Is connection string definitely pointing at correct server/database? Do you have multiple `EventList` in different schemas? Is your code in any sort of transaction? Any catch blocks that suppress errors? In SSMS try pressing the red exclamation mark icon to refresh results if table might have been already open in an existing tab. — Martin Smith, Sep 07 '11 at 00:17
Are you sure the table is in the right schema? Is it `dbo.EventList`, or something else? Try to get in the habit of specifying the schema prefix always. Next, is the above code inside a `TRY/CATCH` block? Are there triggers on the table? What are the datatypes? If the row isn't getting inserted, there is going to be a reason. — Aaron Bertrand, Sep 07 '11 at 00:18
And don't use open table. Run a query (e.g. `SELECT * FROM dbo.EventList;`) - this functionality has some weird locking behavior, some nasty bugs if you decide to (or inadvertently) start editing data, and will potentially cache the old results if you leave the window open. See this bug I filed: http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=464596 while it applies to SQL Server 2008's "Edit Top N Rows" feature, this is essentially Open Table with a new label. — Aaron Bertrand, Sep 07 '11 at 00:21
System.Console.WriteLine(sqlCmd.CommandText); Copy the evaluated query and pop that into the ticket — billinkc, Sep 07 '11 at 00:39
Perfect, if you then run that generated query in SSMS, what is the result? Is it expected that the EventListIdentifier is a date/time object? — billinkc, Sep 07 '11 at 00:50
@billinkc - see my latest comment under [this answer](http://stackoverflow.com/questions/7327563/why-isnt-my-sql-query-to-insert-a-table-row-working-properly/7327624#7327624). It appears that the row is being added, just that Open Table in SSMS isn't showing the row being added. — LeopardSkinPillBoxHat, Sep 07 '11 at 00:53
@billinkc - yes it's a date/time represented as a string (I didn't write the original database, so not sure about the original intention for this). — LeopardSkinPillBoxHat, Sep 07 '11 at 00:54
@Aaron - thanks for the tip, I think your answer may be the key here. If I close SSMS and re-open it, and then select Open Table, should that show the correct table rows? — LeopardSkinPillBoxHat, Sep 07 '11 at 01:02
@Aaron - after further experimentation, you have nailed it. If I close all open panes in SSMS and then select "Open table", the table displays correctly. So this appears to be directly related to the bug you logged with MS. If you put your comment as an answer I will accept it. — LeopardSkinPillBoxHat, Sep 07 '11 at 01:06

score 2 · Answer 1 · answered Sep 07 '11 at 00:16

2

Any errors? What happens if you output the SQL statement instead of executing it and copy/paste it into SSMS?

Try specifying the columns in the insert:

insert into EventList(col1, col2) values (...)

Also, use parameters instead of string concatenation. The reasons for doing so are well documented in about 200000 questions here already. Just search for SQL injection.

answered Sep 07 '11 at 00:16

Derek

21,828
7
53
61

Thanks for the suggestion, but specifying the columns in the insert didn't help. – LeopardSkinPillBoxHat Sep 07 '11 at 00:28
I will update the code to use parameters, and I have read quite a bit about SQL injection. Just out of interest, if this code is not going to be interfaced via a web service/application, is SQL injection as much of a concern? – LeopardSkinPillBoxHat Sep 07 '11 at 00:30

score 1 · Answer 2 · answered Sep 07 '11 at 00:23

1

You haven't really provided enough detail to help. At the least, it would be helpful to know:

Are there any errors?
Is the code snippet you posted in a try/catch block?
What datatypes are the variables you are inserting?
Are you using a Transaction that wasn't committed?

Finally, how is the table sorted? Are there any indexes, including a primary key? If you run a SELECT in Management Studio based on the value in eventId, do you see the record?

answered Sep 07 '11 at 00:23

Paul Walls

5,884
2
22
23

In response to your questions: no errors, using try/catch block, data-types are varchar (see update to post), no keys. How do I know if the transaction wasn't committed? – LeopardSkinPillBoxHat Sep 07 '11 at 00:49
very interesting - the query returned the row successfully. So now it appears it's only the Open Table which isn't working properly. Note that `EventList` is an empty table prior to my insert, so it's not a sorting issue, Open Table actually continues to show an empty table, even though the query I just tried seemed to work! – LeopardSkinPillBoxHat Sep 07 '11 at 00:51
1

See Aaron's comment about the Open Table behavior. Also, you might consider creating a primary key, even if it is just an identity column, as that will enforce a sort order in the table. – Paul Walls Sep 07 '11 at 00:58
Yep, Aaron's comment about Open Table behaviour (and specifically the MS bug he logged about it) is the correct answer. My query was ok, it was just a bug in the table viewer. – LeopardSkinPillBoxHat Sep 07 '11 at 01:10
@Paul just to clarify, that will *allow* you to enforce a sort order by specifying an ORDER BY clause. Without ORDER BY, with or without an IDENTITY column, the ORDER is not guaranteed and you should not rely on observed behavior to always work the same way. Unless you specify, SQL Server is literally free to choose any ordering it deems most efficient. – Aaron Bertrand Sep 07 '11 at 01:15
1

Please just don't rely on it. If you want an ORDER BY, say so. Period. https://sqlblog.org/blogs/alexander_kuznetsov/archive/2009/05/20/without-order-by-there-is-no-default-sort-order.aspx http://stackoverflow.com/questions/7116004/guarantee-order-of-table-valued-function-results http://stackoverflow.com/questions/882195/what-does-top-1-mean-in-an-sql-query/ – Aaron Bertrand Sep 07 '11 at 01:49
@Aaron I guess I was user #1000054 to fall victim to that myth, and I've been doing this a while. I learned something new today... Thanks! – Paul Walls Sep 07 '11 at 02:12
@Paul No worries, glad I could help with the light switch. :-) – Aaron Bertrand Sep 07 '11 at 02:24

score 1 · Accepted Answer · answered Sep 07 '11 at 01:14

1

Don't use Open Table due to the cache/refresh bug I pointed out in my comment. Just re-run the same query in a query window:

SELECT * FROM dbo.EventList
-- WHERE EventId = <EventId>
;

answered Sep 07 '11 at 01:14

Aaron Bertrand

272,866
37
466
490

Why isn't my SQL query to insert a table row working properly?

3 Answers3

Linked

Related