Why isn't my SQL query to insert a table row w

问题:

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')

回答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>
;

回答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.

回答3:

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?