CODEWITHSUNDEEP
c#sql-server
CrBruno
CrBruno

Reputation: 1003

Update/Insert into table

I have to update table on SQL Server but first i have to check for existing data in table so if data is there update it, if not make a new insert:

cmd_sql.CommandText = " SELECT BrDok as id_dok " +
                      " FROM ordersstavke " +
                      " WHERE SifParFil = '" + rw_mat["sifskl_kor"] + "'" +
                      " AND DokumentTip = '" + rw_mat["vrst_dok"] + "'";

MySqlDataAdapter sql_adapter = new MySqlDataAdapter(cmd_sql);
DataSet dt_dok = new DataSet("DOK_MAT_EXCHANGE");
sql_adapter.Fill(dt_dok);

if (dt_dok.Tables["DOK_MAT_EXCHANGE"].Rows.Count == 0)
{
   myQuery = " INSERT INTO ordersstavke (BrDok, DocumentTip, SifParFil) " +
             " VALUES ('" + rw_mat["brdok"] + "', '" +
                            rw_mat["vrst_dok"] + "', '" +
                            rw_mat["sifskl_kor"] + "')";
}
else
{
    UPDATE DATA
}

But I have an error in the code, the error is here if (dt_dok.Tables["DOK_MAT_EXCHANGE"].Rows.Count == 0)

Object reference not set to an instance of an object.

The problem is in this if statement...

Upvotes: 1

Views: 157

Answers (4)

CrBruno
CrBruno

Reputation: 1003

Ok, thanks guys, I wrote it like this

if (ds_dok.Tables[0].Rows.Count <= 0)
                    {
                        myQuery = " INSERT INTO ordersstavke (BrDok, " +
                                  " SifParFil) " +
                                  " VALUES ('" + rw_mat["brdok"] + "', '" +
                                                 rw_mat["sifskl_kor"] + "')";
                    }
                    else if (ds_dok.Tables[0].Rows.Count >= 1)
                    {

                        myQuery = "UPDATE ordersstavke " +
                                  "SET BrDok = '" + rw_mat["brdok"] + "', " +
                                  "SifParFil = '" + rw_mat["sifskl_kor"] + "', " +
                                  "WHERE BrDok = " + ds_dok.Tables["ordersstavke"].Rows[0]["BrDok"].ToString() + "'";

                    }

But there is a small problem in the section update: s_dok.Tables["ordersstavke"].Rows[0]["BrDok"].ToString(), here it give me that loving error : Object reference not set to an instance of an object.

Maybe the update on MySQL goes on different way, I'm referencing on example on sql server and there update goes differently

Upvotes: 0

Thinking Sites
Thinking Sites

Reputation: 3542

Accessing the first table via the dataset name is incorrect, that's for setting the XML.

Instead use 

dt_dok.Tables[0].Rows.Count;

That being said, you're better off writing this as a single SQL statement instead of a separate select && insert. This way you're not going to the DB multiple times.

var sql = @"if exists(select * from ordersstavke where SifParFil = ? and DokumentTip = ?)
then 
 -- do insert statement
else
 -- do update
end if";

This might also be better done with a stored proc, so you don't have as much SQL code in C#. It's easier to manage multiple operations that way.

And for crying out loud, use SqlParameters, not string concatenation! That's just asking for trouble!

Upvotes: 1

Steve
Steve

Reputation: 216353

DOK_MAT_EXCHANGE is the name of the DataSet, not of the first table.

You should test with

if (dt_dok.Tables[0].Rows.Count == 0)

Also, I think is better to use a syntax like this to discover how many records are presents

cmd_sql.CommandText = "SELECT COUNT(BrDok) as id_dok " +
                      " FROM ordersstavke " +                         
                      " WHERE SifParFil = ?p1 " +                         
                      " AND DokumentTip = ?p2";   
cmd_sql.Parameters.AddWithValue("?p1", rw_mat["sifskl_kor"] );
cmd_sql.Parameters.AddWithValue("?p2", rw_mat["vrst_dok"] );
int rowCount = (Int32)cmd_sql.ExecuteScalar();

Upvotes: 4

Diego
Diego

Reputation: 36176

change 

DataSet dt_dok = new DataSet("DOK_MAT_EXCHANGE");

to

DataSet dt_dok = new DataSet("ordersstavke ");

and

if (dt_dok.Tables["DOK_MAT_EXCHANGE"].Rows.Count == 0)

to

if (dt_dok.Tables["ordersstavke "].Rows.Count == 0)

Upvotes: 1

Related Questions