INSERT INTO question

  • Thread starter Thread starter Nak
  • Start date Start date
N

Nak

Hi there,

I have a little question regarding the "INSERT INTO" command. Lets say
I have a table with 3 columns, the first of which is an autonumber and the
second 2 are the first and last names of a person. How do I insert into the
table but have the autonumber generated automatically?

For example

cCmdCommand.CommandText = String.Format("INSERT INTO contacts(first,last) "
& _
"values('{0}','{1}')", _
iContact.ToParamArray)

This is causing me to recieve a syntax error. The iContact objects
method "ToParamArray" simply creates a 1 dimensional object array a fills it
with strings, this seems to contain the correct data but still I am
recieving a syntax error

"Error - Syntax error in INSERT INTO statement."

Should I be including a parameter in there to have an autonumber
generated for me? or do I have to specify the autonumber myself? Thanks in
advance.

cCmdCommand.CommandText = String.Format("INSERT INTO contacts(id,first,last)
" & _
"values('{0}','{1}','{2}')", _
"generate an autonumber for me!!",iContact.First,iContact.Last) ????

Nick.

--
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
"No matter. Whatever the outcome, you are changed."

Fergus - September 5th 2003
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
 
search the sdk for 'output params'..there is a great example there that
should help you
 
Hi Greg,

I am looking at the example now, it seems to be a different way of
creating the command, am I looking at the right one? If I am then the one I
am looking at is explicitly specifying the autonumber, first it adds one at
20 and then it adds one at 21. I wanted to generate this number
automatically, as I would end up getting collisions if I were to guess one
that hasn't been used wouldn't I? I'm slightly confused now, not that it
makes a change :-p

Nick.

--
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
"No matter. Whatever the outcome, you are changed."

Fergus - September 5th 2003
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
 
Nak,
Not sure what data provider you are using but if you are using the SQLData
Provider (for SQL2000) try this

cmd.CommandText = "INSERT INTO Contacts (First, Last) VALUES (@FirstName,
@LastName);SELECT SCOPE_IDENTITY();"

cmd.Paramerters.Add(..... ' Add your two parameters here
YourAutoNumberField = CType(mobjCMD.ExecuteScalar, Int32)

Dan
 
Christ, I have it working now, I was using reserved keywords for my
columns!! DOH!

Nick.

--
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
"No matter. Whatever the outcome, you are changed."

Fergus - September 5th 2003
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
 
Hi there,

Tis okay, I found it now, my problem was in the name of my columns,
cmd.CommandText = "INSERT INTO Contacts (First, Last) VALUES (@FirstName,
@LastName);SELECT SCOPE_IDENTITY();"
Click to expand...

Thanks for your help though, much appreciated!

Nick.

--
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
"No matter. Whatever the outcome, you are changed."

Fergus - September 5th 2003
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
 
Back
Top