CODEWITHSUNDEEP
c#asp.netsql-server
cmp
cmp

Reputation: 452

How can I retrieve the ID of the inserted row using ExecuteScalar function and the SCOPE_IDENTITY() function

I need to be able to get the ID (so I can then pass it through an Add News query) of the inserted row for my image upload table, so I can INNER JOIN a news item to it.

According to my research I need to use ExecuteScalar function and the SCOPE_IDENTITY() function.

Any suggestions on how I can achieve this will be greatly appreciated. 

 protected void btnUpload_Click(object sender, EventArgs e)
 {
     if (FileUpload1.PostedFile != null)
     {
         SqlConnection con = new SqlConnection(strConnString);

         string strQuery = "insert into tblFiles (FileName, FilePath) 
             values(@FileName, @FilePath)"

         SqlCommand cmd = new SqlCommand(strQuery);
         cmd.Parameters.AddWithValue("@FileName", FileName);
         cmd.Parameters.AddWithValue("@FilePath", "/images/admin/news/" + FileName);  
         cmd.CommandType = CommandType.Text;
         cmd.Connection = con;

         try {
             con.Open();
             cmd.ExecuteNonQuery();
         }
         finally {
             con.Close();
             con.Dispose();
         }
     }
 }

Once I have the ID I can do another SQL query to insert the news item, with the correct pic ID.

Upvotes: 0

Views: 738

Answers (1)

Dalorzo
Dalorzo

Reputation: 20014

First things first you need to use Store Procedures instead of just writing your SQL statements inside your application.

Second what you want is to run SCOPE_IDENTITY as part of your insert statement like

  INSERT INTO tblFiles (FileName, FilePath) 
         VALUE(@FileName, @FilePath);

  SELECT  SCOPE_IDENTITY()

And then change the execution statement in .NET to ExecuteScalar

var result = cmd.ExecuteScalar();

Upvotes: 1

Related Questions