Updating a table with an auto-increment key

[David P. Donahue]

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/image_asp.asp?df=100&forumid=38225&select=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
(e-mail address removed)

 

[Nick Malik [Microsoft]]

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]

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
(e-mail address removed)