Get ID of new record. 1

[Appollo14]

Hi,

I'm writing a vb.net (2003) app that links to an SQL db. On a form I have added a button that allows the user to insert data into a table. Once the item has been added i need to retrieve the new db ID and do an insert onto another table. How do i return the value of the new id so that i can use it on the insert statement?

regards,
Noel.

 

[Catadmin]

Assuming the ID you are referring to is an Identity column? Use Scope_Identity() in a T-SQL statement sent to the database. Do NOT use @@Identity as it will not always get you the correct number.

Of course, I'm assuming you're using Microsoft SQL Server. If you are not, you'll need to check with your DBAs for the proper call.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Appollo14]

Crikey Catamin,

you're quick of the mark!
Excuse my ignorance as i'm fairly new to programming but would i be right in thinking that i could declare the sql in the app and then return the value to a dataset as you would any other sql code? Also, would i need to specify the table in the statement?

Thanks,
Noel.

 

[Catadmin]

I don't know a lot about VB .Net (still learning), but yes, I believe you declare a SQLCommand object that contains the T-SQL statement in question and then pass that through your connector object. It should be able to pass through just like any other query returning a value.

I haven't used Scope_Identity() in a while and the example in Books Online (for SQL) is too large for me to post here. Look up the keywords "Scope_Identity Function" in BOL.

The following link should allow you to download the most recent Books Online from MS (if you don't have access to it):

http://www.microsoft.com/downloads/...B1-A420-445F-8A4B-BD77A7DA194B&displaylang=en

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Appollo14]

Ok, I'm trying to go down the route of adding the data via a stored procedure that i have created but I'getting no reaction to the commands. here is a sample of the code i'm using;

Dim Con As New Odbc.OdbcConnection
Dim cmd As New Odbc.OdbcCommand
Dim Param As Odbc.OdbcParameter

Con.ConnectionString = StkCon
Con.Open()
cmd.CommandType = CommandType.StoredProcedure
Try
With cmd
.Connection = Con
.CommandText = "sp_PInsertStk"
.CommandType = CommandType.StoredProcedure

Param = .Parameters.Add("@StkCode", System.Data.Odbc.OdbcType.VarChar)
Param.Value = Me.txtStkCode.Text
Param = .Parameters.Add("@Desc", System.Data.Odbc.OdbcType.VarChar)
Param.Value = Me.txtDesc.Text
Param = .Parameters.Add("@SalePrice", System.Data.Odbc.OdbcType.Double)
Param.Value = Me.txtSalePrice
Param = .Parameters.Add("@CostPrice", System.Data.Odbc.OdbcType.Double)
Param.Value = Me.txtCostPrice.Text
Param = .Parameters.Add("@Units", System.Data.Odbc.OdbcType.VarChar)
Param.Value = Me.txtUnits.Text
End With
cmd.ExecuteScalar()
catch ex as Exception
MessageBox.Show(ex.ToString)
End Try


Nothing Appears to fail but running sql profiler i just dont seem to be hitting the stored procedure and no records are being added.

Any ideas where i'm going wrong?

Regards,
Noel.

 

[Catadmin]

There's probably a simplier way to do this, but I don't know it off the top of my head. And I don't use ODBC for Microsoft SQL Server calls.

 Public ProdServer As New Server()
 Public ProdConn As ServerConnection
....
 ProdConn = ProdServer.ConnectionContext
 ProdConn.ServerInstance = "<MyServer>, <MyPort>"
....
 Dim SQLConnString As String
 SQLConnString = "data source=<MyServer>, <MyPort>;initial catalog=master;Trusted_Connection = Yes"
....
MyCommand = "Select Top 1 * from MyTable"
....
 Dim MySQLConn As New SqlConnection(SQLConnString)
 Dim MyCommand As New SqlCommand(MyTSQLStmt, MySQLConn)
 MySQLConn.Open()
    'Above opens the SQL Connection
 Dim MyReader As SqlDataReader = _
                       MyCommand.ExecuteReader()
   While MyReader.Read
     MyResults = MyReader.GetString(0)
   End While

  MyReader.Close()
     'Above executes the locate filename code

Does this help? And if anyone has a better answer for this question, please post.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Appollo14]

Hi Catadmin,

I've amended my code after reading your las post to use executenonquery rather that execurescalar and now i'm hitting the stored procedure so thanks for that,

However (why is there always a however!) i now get an error back saying that the store procedure is expecting a parameter that was not supplied (@StkCode) which as you will see is the first parameter i have added to the cmd. This would suggest that i'm not adding the parameters correctly. How should i be doing it?

Regards,
Noel.

 

[Catadmin]

You need to verify with your DBA whether or not the parameter in question can be NULL. If not (and it sounds like not), then the T-SQL part of the statement would be:

Exec MyStoredProcedure MyVar

or 

Exec MyStoredProcedure @StkCode = MyVar

Where MyStoredProcedure is the stored procedure name and MyVar is the value you are passing through to it.

Note that if a SP (stored procedure) has more than one possible input, you MUST put the values in the correct order OR you must use the @param = MyVar, @param2 = MyVar2 type of statement. Use the later version if you want to make your code more readable for future generations to understand. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Appollo14]

Hi Catadmin,

the db is not set to accept null values for the @stkCode Param so i've ensured that i've entered a value on the form until i tighten the code to make sure that a value is entered. Also the params i'm passing from the form are in the exact order of request from the SP. Any other ideas?

Regards,
Noel.

 

[Appollo14]

Hi,

been digging around and I've come across an article that suggests i use this;

Dim cmd As New Odbc.OdbcCommand("{call sp_PinsertStk(?,?)}", Con)

instead of this;
Dim cmd As New Odbc.OdbcCommand

I've just tried it as a quick test and it has now move onto param 3 before it errors so maybe it;s on the right track. just need to add "?" for each param required now.

I'll let you know how i get on.

Regards,
Noel.

 

[tpbjr]

I will jump in but by far--I am know expert.

Can you do a simple SQL command getting the max ID number.
Since it is most likely indexed it should be a fast retrieval.

Select max(ID)
into myLastID
from myTable

Have your procedure some how return myLastID.


Thank you for all your help

http://www.besware.com

Tom

 

[Catadmin]

Tom,

The problem with using that solution is if there is someone else (or something else like a trigger) inserting records at about the same time, you'll get the ID of the last literal insertion instead of just the ID of the last record you inserted.

On the other hand, Noel, if this is the only thing inserting records on the database (and no one or nothing else is, including the DBA), then go ahead and use Tom's solution.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Appollo14]

Hi Tom,

Thanks for the suggestion but as Catadmin points out it is a little flawed as I'm hoping that my app will go into a multi user enviroment (believe me if it was a single user app i think i'd have taken your simpler method as an option cos today has been hell of a learning curve for me!).

Catadmin, doing this;

Dim cmd As New Odbc.OdbcCommand("{call sp_PinsertStk(?,?)}", Con)
and including an '?' for each parameter solved the problem i was having getting data to the stored procedure. Now that it's all working tho I'm trying to get the ID back to the app.
In your initial post you suggested i use the select scope_identity() at the end of the sp. this i have done and running the sp thro Query analyser gives me the new id as a result.

I've changed the execute method back to executescalar as that now works and can return a value.
To capture the value I declared NewId as Integer and then after assigning all the param values i simply have;

NewId = cmd.ExecuteScalar()

and it works a treat. Thanks for the help, I think i'd have given up hours ago if i'd attempted this on my own.

Many thanks,
Noel.

 

[Catadmin]

NP. Glad I could offer some useful advice. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"