471,349 Members | 1,608 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,349 software developers and data experts.

Updating a table with an auto-increment key

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
2 2088
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
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.

Similar topics

3 posts views Thread by Andreas | last post: by
1 post views Thread by Chris Jackson | last post: by
33 posts views Thread by bill | last post: by
2 posts views Thread by rpjd | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.