Autonumber Column in Insert

[Guest]

I have an Access table I am doing an insert into. The Insert includes a
select from another table, but does not include the column name of the
autonumber column. If I run the query in Access, it inserts correctly,
generating a new number in the autonum column. If I run it from .net using
executenonquery, I get an error saying the number of columns in the insert
doesn't match the table.

Obviously it's because I am not including the column name. How can I get
around this?

 

[William \(Bill\) Vaughn]

Let's see the code--how are you setting up the InsertCommand (or how is the
wizard setting it up)?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

Below is the code. The select statement is missing the field called giftseq
which is the autonumber column. The column exists in both the insert table
and the select table. But I don't want the giftseq from the select table to
move over. I want it to generate a new number. The query works when run
directly in access, but gets the error I mentioned before when run from .NET.

Public Shared Function Create_Empdtl(ByVal P_startdte As Date, ByVal
P_enddte As Date) As Integer
Dim MGSDATA As Odbc.OdbcConnection = GetMGSdataConnection()
Dim empdtlcmd As String _
= "INSERT INTO empdtl " _
& " SELECT gifts.APVENDOR AS APVENDOR, gifts.GIFTemplid AS
GIFTemplid, gifts.GIFTAMOUNT AS giftamt, " _
& " gifts.giftdate AS giftdate, gifts.restrictto AS restrictto,
gifts.spousgift AS spousgift, " _
& " inst.instname1 AS instname1, inst.instname2 AS instname2,
empl.emplname AS emplname, empl.empfname AS empfname, " _
& " empl.empmidinit AS empmidinit, empl.spouslname AS
spouslname, empl.spousfname AS spousfname, empl.spousinit AS spousinit " _
& " FROM Employee empl, gifts, instfile inst " _
& " WHERE gifts.giftemplid=empl.emplid And
gifts.APVENDOR=inst.apvendor And giftstatus='N' And gifts.deleteflag <> 'D' "
_
& " and gifts.giftdate >= ? and gifts.giftdate <= ? " _
& " ORDER BY gifts.giftamount,gifts.apvendor ;"


MGSDATA.Open()
Dim cmdemplfile As New Odbc.OdbcCommand(empdtlcmd, MGSDATA)
With cmdemplfile.Parameters
.Add("startdte", P_startdte)
.Add("enddte", P_enddte)
End With

Dim recs_added As Integer
Try
recs_added = cmdemplfile.ExecuteNonQuery()
Catch e As OdbcException
MsgBox("Error on Add to Empdtl Table: " & e.Errors(0).Message)
End Try
MGSDATA.Close()
Return recs_added
End Function

 

[Guest]

mh1mark,

If you are not going to supply all the columns in the Insert statement then
I think you need to specify the columns you are supplying. For example:

INSERT INTO empdtl (Column1, Column2, etc) Values (etc)

Kerry Moorman

 

[Guest]

That won't work with a select statement. And If I supply the value of the
matching column from the select table, it uses the seq no from that table
instead of creating a new seq no in the insert table. What is interesting is
that the query works if run in Access, but doesn't work in .NET