?
Solved

Sql Insert , How to add the autoincrement field?

Posted on 2009-04-30
16
Medium Priority
?
255 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:MSFanboy
  • 8
  • 7
16 Comments
 
LVL 6

Expert Comment

by:openshac
ID: 24268362
You can explicitly specify the the PK value by using

SET IDENTITY_INSERT yourTable ON

Open in new window

0
 
LVL 6

Expert Comment

by:openshac
ID: 24268367
You may want to run the whole thing as a stored procedure rather than just sql text
0
 

Author Comment

by:MSFanboy
ID: 24268592
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 6

Expert Comment

by:openshac
ID: 24268699
Sorry I misunderstood your original requirements, could you clarify them?
0
 

Author Comment

by:MSFanboy
ID: 24268873
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
 
LVL 6

Expert Comment

by:openshac
ID: 24268974
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
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24269608
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
 

Author Comment

by:MSFanboy
ID: 24269625

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
 

Author Comment

by:MSFanboy
ID: 24269809
@ragi017 as written at the beginnign:
my error message is: Auto increment fields are readONLY.
0
 
LVL 6

Expert Comment

by:openshac
ID: 24269821
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
 

Author Comment

by:MSFanboy
ID: 24269891
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
 

Accepted Solution

by:
MSFanboy earned 0 total points
ID: 24269944
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
 
LVL 6

Expert Comment

by:openshac
ID: 24270814
So why do you put ":" before your fields? that doesnt make sense.
0
 

Author Comment

by:MSFanboy
ID: 24273267
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
 
LVL 6

Expert Comment

by:openshac
ID: 24277258
I don't understand why you said:

"So why do you put my ":" before my fields? that doesnt make sense.    "
0
 

Author Comment

by:MSFanboy
ID: 24277554
@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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question