By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Updating a table with an auto-increment key

P: n/a
I'm using the following code to add a row to a table (for holding
images) in my database (obtained from
http://www.codeproject.com/aspnet/im...lect=1038401):

public string AddImage(byte[] buffer, string contentType)
{
string strSql = "SELECT * FROM WebImages";
DataSet ds = new DataSet("Image");
OdbcDataAdapter tempAP = new OdbcDataAdapter(strSql, this._objConn);
OdbcCommandBuilder objCommand = new OdbcCommandBuilder(tempAP);
tempAP.Fill(ds, "WebImages");
try
{
this._objConn.Open();
DataRow objNewRow = ds.Tables["WebImages"].NewRow();
objNewRow["ContentType"] = contentType;
objNewRow["Data"] = buffer;
ds.Tables["WebImages"].Rows.Add(objNewRow);
tempAP.Update(ds, "Table");
}
catch(Exception e){return e.Message;}
finally{this._objConn.Close();}
return null;
}

Now, the problem is that this table also contains a numeric primary key
field set to auto-increment. Whenever I use a regular INSERT statement,
the field does its job and automatically enters the next value.
However, the above method does not. I can just as easily add a field in
the above code, but what value would I assign it? I tried just
assigning it "null" but that didn't work, nor did an empty string. I
could grab the current highest value from the table, add one, and use
that. But wouldn't that risk a race condition if two users are
performing the operation simultaneously?

What do you recommend to solve this? Any help would be much
appreciated. Thank you.
Regards,
David P. Donahue
dd******@ccs.neu.edu
Jul 21 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Sure: change the data type of the primary key from an INT to a GUID. In the
code below, create your own Guid value ( see Guid.NewGuid() ), and place the
value into the table.

That pretty much solves it. I'm sure that there may be ways to do this with
INT as well, but honestly, once you are spoiled with GUIDS, it's kinda hard
to go back :-).

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"David P. Donahue" <dd******@ccs.neu.edu> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...
I'm using the following code to add a row to a table (for holding images)
in my database (obtained from
http://www.codeproject.com/aspnet/im...lect=1038401):

public string AddImage(byte[] buffer, string contentType)
{
string strSql = "SELECT * FROM WebImages";
DataSet ds = new DataSet("Image");
OdbcDataAdapter tempAP = new OdbcDataAdapter(strSql, this._objConn);
OdbcCommandBuilder objCommand = new OdbcCommandBuilder(tempAP);
tempAP.Fill(ds, "WebImages");
try
{
this._objConn.Open();
DataRow objNewRow = ds.Tables["WebImages"].NewRow();
objNewRow["ContentType"] = contentType;
objNewRow["Data"] = buffer;
ds.Tables["WebImages"].Rows.Add(objNewRow);
tempAP.Update(ds, "Table");
}
catch(Exception e){return e.Message;}
finally{this._objConn.Close();}
return null;
}

Now, the problem is that this table also contains a numeric primary key
field set to auto-increment. Whenever I use a regular INSERT statement,
the field does its job and automatically enters the next value. However,
the above method does not. I can just as easily add a field in the above
code, but what value would I assign it? I tried just assigning it "null"
but that didn't work, nor did an empty string. I could grab the current
highest value from the table, add one, and use that. But wouldn't that
risk a race condition if two users are performing the operation
simultaneously?

What do you recommend to solve this? Any help would be much appreciated.
Thank you.
Regards,
David P. Donahue
dd******@ccs.neu.edu

Jul 21 '05 #2

P: n/a
Nick Malik [Microsoft] wrote:
Sure: change the data type of the primary key from an INT to a GUID. In the
code below, create your own Guid value ( see Guid.NewGuid() ), and place the
value into the table.


In the database itself? I'm seeing no GUID data type. FYI, I'm using
MySQL Control Center to manage a MySQL database. Is the interface just
not showing me this type, or is it something specific to another database?
Regards,
David P. Donahue
dd******@ccs.neu.edu
Jul 21 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.