Inserting a Record in table that has Auto_Increment 2

[dbanker]

I need help

I'm trying to insert a row into a table using data from variables with values from an excel sheet. the problem I'm having is that the table I'm inserting them into has an Auto_increment field and I can't get the code right for it to work. Here is a sample of what I have:

Code
' ProjectNum is the Auto_increment field
INSERT INTO ProjectData ( ProjectNum, CompanyName, ProjectName, Latitude, Longitude, StateName, TotalPrints, TotalDiapos, FltLinesH, FltLinesM, FltLinesL, PhotoScaleH, GoDate, CreationDate, FlownDate, InvoiceDate )
VALUES (null, '" & GName & "', '" & ProjectName & "', '" & Lat & "', '" & Lon & "', '" & StateName & "', '" & NumPrints & "', '" & NumPos & "', '" & FltlinesH & "', '" & FltlinesM & "', 0, '" & PhotoScaleH & "', '', '" & Date & "', '', '');

When I run this I get Error Msg "You tried to assign the null value to a variable that is not a Variant data type"
If I change the null to a number I get Error Msg "Microsof set 1 field(s) to null due to a type convertion failure..." and doesn't append the record.

any help and or example code would be greatly appreciated. Thanks in advance

 

[SQLSister]

take the name of the identity field out of the list of fields and remove the null from the values part of the statement.

"NOTHING is more important in a database than integrity." ESquared

 

[dbanker]

Tried that already. When I do it I get the second error msg as listed above ("Microsof set 1 field(s) to null").

 

[ESquared]

SQL Server doesn't have Auto_Increment. It has identity(). Are you sure you're in the right forum?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[LNBruno]

Maybe because you're not passing a date value to one of these: GoDate, FlownDate, InvoiceDate

< M!ke >
[small]"Oops" is never a good thing.[/small]

 

[ESquared]

Print out the final statement after it's all concatenaed together and post it here.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[SQLDenis]

PhotoScaleH, GoDate, CreationDate, FlownDate, InvoiceDate )

PhotoScaleH & "', '', '" & Date & "', '', '');

now where are these 3 values?
CreationDate, FlownDate, InvoiceDate



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[dbanker]

(GoDate, CreationDate, FlownDate, InvoiceDate ) all but CreationDate is to be entered at a later time so I gave them values of ''. I thought that would be what you did if the field doesn't have a value and its not a number.

Also, I created the tbl in Access design mode thats why I said auto increment not sure of the technical jargon but It auto steps up the number everytime a record is added.

 

[SQLDenis]

that should create 1900-01-01, is that what you want?

run this to see the behaviour
select convert(datetime,'')
select convert(datetime,0)
select convert(datetime,' ')
select convert(datetime,null)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

Brilliant, Denis! I gave you a star!



< M!ke >
[small]"Oops" is never a good thing.[/small]

 

[dbanker]

Thanks for the advice. I will try it Monday. For now its friday and I have to go get ready for the Buckeye's game tomorrow. Thanks all.

 

[dbanker]

Just an afterword for anyone reading this. SQLSister was right, I don't know why I couldn't get it to work at first but I tried taking out the field that has auto increments in it and the null value for it and it worked. I also had to give a null value to the date fields to get rid of the type mismatch error. Thanks for everyones suggestions and help.