cmp cmp - 11 days ago 5
ASP.NET (C#) Question

Why can't I get the current ID and place in another table?

I am attempting to create a simple news and image system, I first need to use SCOPE_IDENTITY() and execute scalar, but I'm not having much luck. I get a:


The name 'newID' does not exist in the current context


protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.PostedFile != null)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

//Save files to disk
FileUpload1.SaveAs(Server.MapPath("/images/admin/news/" + FileName));

//Add Entry to DataBase
String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
int newID = 0;

string strQuery = @"insert into tblFiles (FileName, FilePath) values(@FileName, @FilePath); select cast(scope_identity() As int);";

using (SqlConnection connection = new SqlConnection(strConnString))
using (SqlCommand command = new SqlCommand(strQuery, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add("@FileName", SqlDbType.VarChar).Value = FileName;
command.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = "/images/admin/news/" + FileName;

try
{
connection.Open();
newID = (int)command.ExecuteScalar();
}
catch
{
}
}
}

if (newID > 0)

{
string strAddNewsQuery = @"insert into tblNews (newsTitle, newsDate, newsSummary, newsContent, newsPicID)
values(@newsTitle, @newsDate, @newsSummary, @newsContent, @newsPicID)";
using (SqlConnection connection = new SqlConnection(strConnString))
using (SqlCommand command = new SqlCommand(strAddNewsQuery, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add("@newsTitle", SqlDbType.VarChar).Value = FileName;
command.Parameters.AddWithValue("@newsDate", txtnewsdate.Text);
command.Parameters.AddWithValue("@newsSummary", txtnewssummary.Text);
command.Parameters.AddWithValue("@newsContent", txtnewsmaincontent.Text);
command.Parameters.Add("@newsPicID", SqlDbType.Int).Value = newID;

try
{
connection.Open();
command.ExecuteNonQuery();
}
catch
{
}
finally {
connection.Close();
connection.Dispose();
}
}
}
}
}

Answer

An int does not have properties you can access. Change

command.Parameters.AddWithValue("@newsPicID", newID.Value); 

into

command.Parameters.AddWithValue("@newsPicID", newID); 

Even better is to use parameters with the database value type specified.

command.Parameters.Add("@newsPicID", SqlDbType.Int).Value = newID;

But you are trying to get the SCOPE_IDENTITY() of table tblNews, not from tblFiles to be used in tblNews as newsPicID. You need to get SCOPE_IDENTITY() from the first database command.

UPDATE

And you need to assign the connection to the command.

SqlCommand cmd = new SqlCommand(strQuery, con)

UPDATE 2

Here is a complete snippet to get you started. Notice the wrapping with using. This ensures proper disposal of connections.

int newID = 0;

using (SqlConnection connection = new SqlConnection(strConnString))
using (SqlCommand command = new SqlCommand(strQuery, connection))
{
    command.CommandType = CommandType.Text;
    command.Parameters.Add("@FileName", SqlDbType.VarChar).Value = FileName;
    command.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = "/images/admin/news/" + FileName;

    try
    {
        connection.Open();
        newID = (int)command.ExecuteScalar();
    }
    catch 
    {
    }
}

if (newID > 0)
{
    using (SqlConnection connection = new SqlConnection(strConnString))
    using (SqlCommand command = new SqlCommand(strAddNewsQuery, connection))
    {
        command.CommandType = CommandType.Text;
        command.Parameters.Add("@newsTitle", SqlDbType.VarChar).Value = FileName;
        //etc
        command.Parameters.Add("@newsPicID", SqlDbType.Int).Value = newID;

        try
        {
            connection.Open();
            command.ExecuteNonQuery();
        }
        catch
        {
        }
    }
}