insert several items at once with autoincrement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert several items at once with autoincrement

Author

Topic

knudmt
Starting Member

12 Posts

Posted - 2010-09-13 : 11:59:29

hello,

I am creating an API that works with another piece of software that was created by someone else. I am uploading binary data into the database.

Images_tbl

Images_ID (Int PK) Identity
Images_CarID (Int FK) not null
Images_FileName (varbinary(Max)) null

You can have up to 5 images per car

The images_ID is the primary key and auto incremented
There is only 1 carID for any car
for example.

+-----------------------------------------------+
|Images_ID | Images_CarID | Images_fileName |
+-----------------------------------------------+
| 345 | 79 | <binary data> |
+----------+----------------+-------------------+
| 346 | 79 | <binary data> |
+----------+----------------+-------------------+
| 347 | 79 | <binary data> |
+----------+----------------+-------------------+

etc... up to five entries per vehicle

*THE PROBLEM*
When I use an update statement to add 5 images to db it repeats the last image I input. Or the first image if I only upload 1!

/* statement */
public void update_data()
{
string query = "UPDATE tbImages SET Images_FileName = @image WHERE Images_CarID = @ImgID";
}

Can you guys help me out here?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-13 : 12:08:14

Maybe your call to update_data() is wrong?
The statement itself looks ok.

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-13 : 12:34:34

quote:
Originally posted by knudmt

<snip>/* statement */
public void update_data()
{
string query = "UPDATE tbImages SET Images_FileName = @image WHERE ~~Images_CarID~~Images_ID = @ImgID";
}

Can you guys help me out here?

knudmt
Starting Member

12 Posts

Posted - 2010-09-13 : 13:45:53

ok here is my update_Date() method:

private void updateData()
{
try
{
if (imagename != "")
{
FileStream fs;
fs = new FileStream(@imagename, FileMode.Open, FileAccess.Read);

byte[] picbyte = new byte[fs.Length];
fs.Read(picbyte, 0, System.Convert.ToInt32(fs.Length));
fs.Close();
//populate the textbox

/* open database */
SqlConnection conn = new SqlConnection("Data Source=WEBSERVER\\MSSQLLITMARK;Initial Catalog=Litmark;Integrated Security=True");
conn.Open();

string query;
/*
* must increment the ImagesID after each upload a for loop will suffice
* for(int i = 0; i < 4; i++)
* {
* upload images and increment the ImagesID
* }
*
* */
//try an insert statement
query = "UPDATE tbImages SET Images_ImageFile = @image WHERE Images_CarID = @ImgID";

SqlParameter picparameter = new SqlParameter();
picparameter.SqlDbType = SqlDbType.Image;
picparameter.ParameterName = "image";
picparameter.Value = picbyte;

SqlParameter idparameter = new SqlParameter();
idparameter.SqlDbType = SqlDbType.Int;
idparameter.ParameterName = "ImgID";
idparameter.Value = ImgID;

//error when call to execute the command
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add(idparameter);
cmd.Parameters.Add(picparameter);

cmd.ExecuteNonQuery();
MessageBox.Show("Image Added!");
cmd.Dispose();
conn.Close();
conn.Dispose();

Connection();
}
}
catch (System.IO.IOException ex)
{
MessageBox.Show("I/O exception occured: " + ex.StackTrace.ToString());
}
}

I will also try changing the from Images_carID to ImagesID;
we shall see.

knudmt
Starting Member

12 Posts

Posted - 2010-09-13 : 14:01:28

quote:
Originally posted by Lamprey

quote:
Originally posted by knudmt

<snip>/* statement */
public void update_data()
{
string query = "UPDATE tbImages SET Images_FileName = @image WHERE ~~Images_CarID~~Images_ID = @ImgID";
}

Can you guys help me out here?

This is the solution in a round about way. I have 2 forms. I pass data about the vehicle to the second form. I need to alter my scalar variable @ImgID. basically retrieve all the ID's for the Car_ID of that particular vechicle and upload each to the proper part of the table. THANKS!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources