Object reference not set to an instance of an object Error thrown?

Question

I am a beginner in c#. I try to get the last inserted id from an update query. But i get an error This is my code

String Query1 = "insert into test(userid,score) values ('"+userid+"',0);";
SqlConnection conn1 = new SqlConnection(constring);
SqlCommand cmdDatabase1 = new SqlCommand(Query1, conn1);
SqlDataReader dbreader1;
conn1.Open();
testid = (int)cmdDatabase1.ExecuteScalar();
dbreader1 = cmdDatabase1.ExecuteReader();
while (dbreader1.Read())
{    
}

I get this error.

Object reference not set to an instance of an object. on this line testid = (int)cmdDatabase1.ExecuteScalar();

I have also tried this

String Query1 = "insert into test(userid,score) values ('"+userid+"',0);";
SqlConnection conn1 = new SqlConnection(constring);
SqlCommand cmdDatabase1 = new SqlCommand(Query1, conn1);
SqlDataReader dbreader1;
conn1.Open();
object count = cmdDatabase1.ExecuteScalar();
System.Diagnostics.Trace.WriteLine(count.GetType());
int testid = (int)count;
dbreader1 = cmdDatabase1.ExecuteReader();
while (dbreader1.Read())
{    
}

Same error Object reference not set to an instance of an object.o nthis line System.Diagnostics.Trace.WriteLine(count.GetType());

String constring = @"Data Source=.\sqlexpress;Initial Catalog=geografia;Integrated Security=True"; — user224447, Jun 05 '13 at 07:23
try to remove the `Semicolon ;` from the Query1. its useless. — 7alhashmi, Jun 05 '13 at 07:23
possible duplicate of [SQL Server - Return value after INSERT](http://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert) — Oliver, Jun 05 '13 at 07:26
Try in `debugging mode` and tell which line is actually trowing the `exception` — Veer, Jun 05 '13 at 07:26
if you're trying to get the last inserted ID, use Scope_Identity — NDJ, Jun 05 '13 at 07:28
@veer i edited the question, with the lines with the errors. the update is succesful, but I don;t get last insert in a variable. — user224447, Jun 05 '13 at 07:33

Freelancer · Accepted Answer · 2013-06-05T07:35:44.527

3

For Getting last inserted value from DB, you should write select query.

Not a part of this question, but while using insert or select should make use of parametrized queries to avoid SQLInjections.

This is a link to learn more bout SQLInjections.

your code should be:

try
{
  con.open();
  cmd=new SqlCommand("select max(userid) from test",con);
  da=new SqlDataAdapter(cmd);
  DataSet ds=new DataSet();
  da.fill(ds);
  int lastInsertedId= int.parse(ds.Tables[0].Rows[0][0].toString());
  con.close();
}
catch(exception ex)
{
  messagebox.show(ex.Message);
}

You can also use different techniques in SQL according to SqlServer you are using such as

@@identity,rownumber over. Ofcourse, this can be according to your need and version of SQLServer you are using.

For RowNumber

For @@identity

edited Jun 05 '13 at 07:35

answered Jun 05 '13 at 07:26

Freelancer

9,008
7
42
81

1

not ideal - if somebody adds another record in the meantime, then you're getting the wrong ID. that's what scope_identity (and to a lesser extent @@identity) are for – NDJ Jun 05 '13 at 07:29
Yup you are right. wait, will Update my answer with this also – Freelancer Jun 05 '13 at 07:31
I didn't - in fact I'll +1 for the answer and the mention of sql injection. – NDJ Jun 05 '13 at 08:34
I tried it with update not select, but it can't find table[0] – user224447 Jun 05 '13 at 09:14
You can accept this answer by checking tick after the answer , if its helpful :) – Freelancer Jun 05 '13 at 09:48

Veer · Answer 2 · 2013-06-05T09:30:45.673

Use this code to insert as well as select last inserted id. Your main problem was 'Object reference not set to an instance of object'. Try the below code.

try
{
    String Query1 = "insert into test(userid,score) OUTPUT INSERTED.userid values ('"+userid+"',0);";
    SqlConnection conn1 = new SqlConnection(constring);
    SqlCommand cmdDatabase1 = new SqlCommand(Query1, conn1);
    conn1.open();
    da=new SqlDataAdapter(cmdDatabase1);
    DataSet ds=new DataSet();
    da.fill(ds);
    int Id= int.parse(ds.Tables[0].Rows[0][0].toString());
    con.close();
}
catch(exception ex)
{
  messagebox.show(ex.Message);
}

Answer edited try it now.Instead of `OUTPUT.ID` write `output.userid` — Veer, Jun 05 '13 at 09:32

score 0 · Answer 3 · answered Jun 05 '13 at 07:23

0

What line does the error happen on? I'm guessing here:

while (dbreader1.Read())

why? Because an insert query doesn't result in any rows... but I guess we'd have to check that.

answered Jun 05 '13 at 07:23

Daren Thomas

67,947
40
154
200

score 0 · Answer 4 · answered Jun 05 '13 at 07:36

0

For UPDATE, INSERT, and DELETE statements, try using ExecuteNonQuery()

answered Jun 05 '13 at 07:36

Praveen

55,303
33
133
164

Object reference not set to an instance of an object Error thrown?

4 Answers4