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.
| Author |
Topic |
|
KarinsDad
Starting Member
17 Posts |
 Posted - 2002-04-09 : 18:14:09
|
| I am using ADO to connect to an SQL Server database using Visual C++. For the most part, this all works fine. However, several strange things are happening with my table with a datetime field in it.1) Although the SQL in the three examples below looks correct, it throws a com exception of 80020005, Type mismatch. The weird thing is that the record actually ends up in the table, even though the com exception is thrown.strTemp = "INSERT Table1 (field1, field2, field3) VALUES ('ABC', 'DEF', getdate())";strTemp = "INSERT Table1 (field1, field2, field3) VALUES ('ABC', 'DEF', CAST('01-01-2000 03:12:23' AS datetime))";strTemp = "INSERT Table1 (field1, field2, field3) VALUES ('ABC', 'DEF', CONVERT(datetime, '01-01-2000 03:12:23'))";Is there a way to convert string date times without the com exception?Better yet, is there a way to default a datetime field to current without passing it into the query string?2) When I accidentally ran the same SQL twice, I got two identical records in the table. Unfortunately, I cannot use Enterprise Manager to get rid of them now. I get the error "Key column information is insufficient or incorrect. Too many rows were affected by the update.".I put a DELETE query into my test program to get rid of them and that worked, but it seems strange that Enterprise Manager would be unable to delete them.Any ideas on this one?Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-09 : 18:19:56
|
| If there's no primary key, EM won't be able to determine which of the two identical rows to delete. You either have to delete both, or none. Or just add a primary key to your table, which you should do on all your tables anyway.If you CREATE or ALTER your tables to include a DEFAULT for your date column, and set that default to GETDATE(), then you can use the following syntax:INSERT Table1 (field1, field2, field3) VALUES ('ABC', 'DEF', DEFAULT)Or, you can just eliminate the column from the INSERT list:INSERT Table1 (field1, field2) VALUES ('ABC', 'DEF') |
 |
|
|
KarinsDad
Starting Member
17 Posts |
Posted - 2002-04-09 : 18:34:32
|
| Thanks robvolk.I tried to delete both, but that gave the same error in EM. No biggy.Ok, I tried your suggestion. Put a default of getdate() into the datetime field.Works fine except the com exception is still thrown with the string:INSERT Table1 (field1, field2) VALUES ('ABC', 'DEF') Btw, I am using the SQLOLEDB provider, not ODBC. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-09 : 18:45:27
|
| You know what? If you're using C++, it may be interpreting 'ABC' as character data, instead of a (SQL) string. Strings in C/C++ are delimited with " if I'm not mistaken (I'm not a C programmer) This may be throwing the COM interface off, although I can't imagine why.Are you sure it's the date that's throwing it off? Do the other columns go through OK? And does the first syntax (using the DEFAULT keyword) throw an error as well?What I'd recommend doing is creating a stored procedure to handle this operation, and then you can pass the values as parameters instead. This would eliminate the need to pass the command as a string with embedded character data (if that is indeed the cause of the problem) |
|
|
|
KarinsDad
Starting Member
17 Posts |
Posted - 2002-04-11 : 11:05:09
|
| I did have a stored procedure implementation in my test code, however, I did a series of multi-thread tests and the query string (for what we are doing) was faster than the simple stored procedure. I know, I know, every reference on the subject states that stored procedures should typically be faster. I assume this is the atypical case. :)I think the reason for this is due to the amount of ADO com objects that have to get set up in the stored procedure case. Each parameter (10 in this case) passed to the stored procedure has a series of 6 com methods and parameter assignments that must be called, just to set it up whereas the query string just has each parameter appended into a CString. Since the stored procedure does very little work (a single query), the benefits of using it are lessened when compared to the setup time in the code (course, I had to read/write millions of records to find this out).I'm also fairly confident that it is the datetime field that is the culprit. I have created a singleton database object along with a layer object that talks to it. All of the code then interfaces via the layer object (hence, having one database connection open to MSDE in the database object).Every single query in the system (insert, select, delete, etc.) for every table in the database (currently about 20) uses this layer object. All of them work fine with the exception of the one table that has a datetime field in it.And, in fact, my current tests to this table only include required fields (of which there are 3), an nvarchar, a uniqueidentifier, and a datetime. But, I use nvarchars and uniqueidentifiers in a lot of other tables with no problem.So, it's fairly peculiar. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-11 : 11:34:29
|
| Why dont you try these separately , this will make it doubly sure where exactly the error lies.strTemp = "INSERT Table1 ( field3) VALUES ( CONVERT(datetime, '01-01-2000 03:12:23',102)";strTemp = "INSERT Table1 (field1) VALUES ('ABC')"; strTemp = "INSERT Table1 ( field2) VALUES ('DEF')"; -------------------------------------------------------------- |
|
|
|
KarinsDad
Starting Member
17 Posts |
Posted - 2002-04-11 : 14:04:26
|
| Re: Why don't you ...strTemp = "INSERT Table1 ( field3) VALUES ( CONVERT(datetime, '01-01-2000 03:12:23',102)"; strTemp = "INSERT Table1 (field1) VALUES ('ABC')"; strTemp = "INSERT Table1 ( field2) VALUES ('DEF')"; I did. I changed the "not null" to allow nulls for the other two fields and I still get the problem.I allow nulls on the datetime field and insert field1 by itself and there is no problem. As long as I do not enter the datetime field, I'm ok.I allow nulls on the datetime field and insert field1 and default for the datetime field and the same problem occurs. Anytime it tries to assign data into the datetime field, I get the com exception. I also put in the style as per your example on the convert, but nada.I even just sent in the datatime field only, allowing all other fields to be null and not passing data for them and I get the com exception.Unfortunately, I can only break into msado15.tli and no further down for the recordset open, so I cannot really see why it's having a problem. For now, I can catch the com exception and ignore it, but I'd like a cleaner solution.For now, I'm going to try changing my cursor and lock types and see if it makes a difference.If that doesn't work, I think I will create a simple test program and then post it here so that people can see exactly what I am doing. If nobody here can help me after that, then I guess I'll send it to Microsoft.PS.I am using MDAC 2.6, SQL Server 7.0, and Visual C++ 6.0 SP5. |
|
|
|
KarinsDad
Starting Member
17 Posts |
Posted - 2002-04-11 : 15:05:11
|
| Ok, I don't get it.I didn't change the underlying code yet, merely the null/not null and the insert code, but now it is suddenly inexplicably working.Changed everything back to what it originally was and it works.Very strange.Thanks for your suggestions robvolk and Nazim. I'll chalk this one up as a gremlin. |
|
|
|
|
|
|
|
|
DateTime Weirdness