Use ExecuteScalar to return identity or Guid newID?

[eagle]

How can I return the new id that is created when an insert command is used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text, qry)

I tried this and received an error "System.NullReferenceException: Object
reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't create a
stored procedure for this for various reasons.

 

[Clint Hill]

include this in your sql statement


select scope_identity() as returnidentity

Clint Hill
H3O Software
http://www.h3osoftware.com

 

[Clint Hill]

Actually, you will probably need to run two commands to get it done.
Keep your connection open for both and use it in both.

psuedo code:

SqlConn.Open()
InsertCommand.Execute(SqlConn) -- using your insert statement
int id = IdCommand.Execute(SqlConn) -- using either scope_identity or
@@identity
SqlConn.Close()

Clint Hill
H3O Software
http://www.h3osoftware.com

 

[eagle]

That worked great, thanks for your help! fyi, I was able to put it into one
qry:
"qry = "insert into tblClients (lname, fname) values ('smith', 'joe');
select newid()"
(or select scope_identity for identity fields)
Dim xyz as string = command.executescalar(strconn, commandtype.text,
qry).tostring

 

[eagle]

Never mind, that didn't work. The scope_Identity() works for identity
fields, what about uniqueidentifier fields? Is there a way to return the
new id from a uniqueidentifier field?

Thanks for all your help.

 

[Clint Hill]

Well, you can use ROWGUIDCOL in the select clause, however you will need
to add some method of grabbing it against the most recent data inserted.

Same concept, just using different select statement.

Clint Hill
H3O Software
http://www.h3osoftware.com

 

[mike7510uk]

I have code that returns the last inserted Id but what im trying to do now is store that ID into a variable, ready to be called upon whenever its needed during the session (i.e 3 pages later)

here is the code i have so far (the msg box is just to see if it returns the correct ID)

 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim connectionstring As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\table1.mdf;Integrated Security=True;User Instance=True"
        Dim sqlconnection = New Data.SqlClient.SqlConnection
        ''Dim selectstatement As String = "SELECT Scope_Identity() FROM Table1"
        Dim selectstatement As String = "SELECT IDENT_CURRENT('Table1')"
        Dim sqlcommand1 = New Data.SqlClient.SqlCommand
        Dim ID As Integer

        sqlconnection.ConnectionString = connectionstring
        sqlconnection.Open()

        sqlcommand1.Connection = sqlconnection
        sqlcommand1.CommandText = "INSERT INTO table1 (name,phone) VALUES(@name,@phone)"
        sqlcommand1.Parameters.Add("@Name", name.Text.ToString)
        sqlcommand1.Parameters.Add("@phone", phone.Text.ToString)
        sqlcommand1.ExecuteNonQuery()

        sqlcommand1.Connection = sqlconnection
        sqlcommand1.CommandText = selectstatement
        ''sqlcommand1.open;

        'MsgBox(sqlcommand1.ExecuteScalar())
        ID = sqlcommand1.ExecuteScalar()
        MsgBox(ID, MsgBoxStyle.OkOnly)



        ' Response.Redirect("Default.aspx")

    End Sub