Sql Insert , How to add the autoincrement field?

Hello,

my error message is: Auto increment fields are readONLY.

I have 5 fields in my database. The first field is auswertung_id which is PK + autoincrement.

the other 4 fields are these: (:titel,:startdatum,:enddatum,:rowfilter)"; as I have stated below in my code.

HOW can I fix that autoincrement field in my code before the titel field?

I use : instead of @ before field names because of using ADS database just to let you know...
AdsCommand cmd = _configdbConnection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO _AUSWERTUNG VALUES (:titel,:startdatum,:enddatum,:rowfilter)";
 
                cmd.Parameters.Add(":titel", DbType.String).Value = title;
                cmd.Parameters.Add(":startdatum", DbType.String).Value = startDate;
                cmd.Parameters.Add(":enddatum", DbType.String).Value = endDate;
                cmd.Parameters.Add(":rowfilter", DbType.String).Value = "test";
 
                cmd.ExecuteNonQuery();

Open in new window

MSFanboyAsked:
Who is Participating?
 
MSFanboyConnect With a Mentor Author Commented:
solved it for my solves, just wonder why you guys could not solve it...

cmd.CommandText = "INSERT INTO VA_AUSWERTUNG (titel, startdatum, enddatum) VALUES (:titel,:startdatum,:enddatum)";
0
 
openshacCommented:
You can explicitly specify the the PK value by using

SET IDENTITY_INSERT yourTable ON

Open in new window

0
 
openshacCommented:
You may want to run the whole thing as a stored procedure rather than just sql text
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
MSFanboyAuthor Commented:
not allowed to run stored proc here

@ openshac -> http://www.topfreestuff.com/identity_insert.shtml

I dont want to insert a number as autoinc id. Can you please use your suggestion and fix my code so I see what you mean?
0
 
openshacCommented:
Sorry I misunderstood your original requirements, could you clarify them?
0
 
MSFanboyAuthor Commented:
I have 5 fields. ONE integer autoinc primary key field + 4 string fields.

I wanna do a INSERT into passing only the 4 string fields AND NO value for the first field which has a autoincrement. The server is increasing the id not me!
0
 
openshacCommented:
OK, I understand now try this:

If there are any columns that you are NOT specifiy you'll have to change the INSERT statement to specifiy which columns you are specifying like this:

"INSERT INTO _AUSWERTUNG (:titel, :startdatum, :enddatum, :rowfilter) VALUES (?, ?, ?, ?)";




cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO _AUSWERTUNG VALUES (?, ?, ?, ?)"; 
cmd.Parameters.Add(":titel", DbType.String).Value = title;
cmd.Parameters.Add(":startdatum", DbType.String).Value = startDate;
cmd.Parameters.Add(":enddatum", DbType.String).Value = endDate;
cmd.Parameters.Add(":rowfilter", DbType.String).Value = "test";

Open in new window

0
 
Anurag ThakurTechnical ManagerCommented:
normally in sql insert (for a table which has an autoincrement column) you dont specify the column name in the insert statement
that column value is automatically added when a new row is created/added
0
 
MSFanboyAuthor Commented:

what is that?
"INSERT INTO _AUSWERTUNG (:titel, :startdatum, :enddatum, :rowfilter) VALUES (?, ?, ?, ?)";

select ... where titel = @titel; is the usual way. So why do you put my ":" before my fields? that doesnt make sense.
0
 
MSFanboyAuthor Commented:
@ragi017 as written at the beginnign:
my error message is: Auto increment fields are readONLY.
0
 
openshacCommented:
You have colons in your original post , I can't understand why you would want use them, or if it even is valid syntax
0
 
MSFanboyAuthor Commented:
that way it works:

cmd.CommandText = "INSERT INTO VA_AUSWERTUNG (titel, startdatum, enddatum, rowfilter) VALUES ('"+title+"','"+startDate+"','"+endDate+"','"+myrowfilter+"')";

but I want to use the above Params.Add(..) for building the select string! How to?



0
 
openshacCommented:
So why do you put ":" before your fields? that doesnt make sense.
0
 
MSFanboyAuthor Commented:
if you would read my question properly then you would have read this ;-)
--> I use : instead of @ before field names because of using ADS database just to let you know...
0
 
openshacCommented:
I don't understand why you said:

"So why do you put my ":" before my fields? that doesnt make sense.    "
0
 
MSFanboyAuthor Commented:
@openshac
that is from you: "INSERT INTO _AUSWERTUNG (:titel, :startdatum, :enddatum, :rowfilter) VALUES (?, ?, ?, ?)";

I asked why you put the ":" before the field names, because thats not right. it would be right if you would put the ":" before every variable which contains value to write into the fields like I did here:
cmd.CommandText = "INSERT INTO VA_AUSWERTUNG (titel, startdatum, enddatum) VALUES (:titel,:startdatum,:enddatum)";
see the difference?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.