new to vb - is this code ok ? - retrieving ID from SQL stored procedure

[luna_s]

i've been playing with this code to return an ID on an insert - it seems a
little dirty/hacky to me

stored proc goes something like this (ive stripped out uneccesary sql code -
the proc works fine) :-

CREATE PROCEDURE uipersonal

declare all the variables here

AS

IF EXISTS (SELECT * database WHERE id=@mainid)

UPDATE

ELSE

INSERT

SELECT SCOPE_IDENTITY()

GO

thats basically the stored proc
my vb code is something like :-

Private Sub putdatain()

mainid = TextBox1.Text.ToString
If mainid = "" Then mainid = "0"

Dim conn As New System.Data.SqlClient.SqlConnection(SQLstrConn)
Dim sql As String = "uipersonal '" all the variables i send
Dim Cmd As New System.Data.SqlClient.SqlCommand(sql, conn)
Dim objDR As System.Data.SqlClient.SqlDataReader

conn.Open()

If mainid = "0" Then
TextBox1.Text = Cmd.ExecuteScalar
ElseIf mainid > "0" Then
objDR =
Cmd.ExecuteReader(System.Data.CommandBehavior.Clos eConnection)
End If

conn.Close()

End Sub

this works ok but seems a little messy - and i was wondering if this method
would cause issues in the future
(atm its just test code to play around)

thanks

mark

[Cor Ligthert]

Luna,

Somewhere I miss the setting in your code the setting of the parameter
http://msdn.microsoft.com/library/de...erstopic.aspAs well do I miss where you tell that the command is a stored procedurehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclasscommandtype topic.aspI hope this helps?Cor

[Cor Ligthert]

Luna,

Somewhere I miss in your code the setting of the parameter
<>

http://msdn.microsoft.com/library/de...eterstopic.asp

<>
As well do I miss where you tell that the command is a stored procedure

<>

http://msdn.microsoft.com/library/de...dtypetopic.asp

<>

I hope this helps?

Cor

[luna_s]

"Cor Ligthert" <no************@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...

Luna,

Somewhere I miss in your code the setting of the parameter
<>

http://msdn.microsoft.com/library/de...eterstopic.asp Dim conn As New System.Data.SqlClient.SqlConnection(SQLstrConn)

sqlstrconn is a variable that just contains a standard connection string

As well do I miss where you tell that the command is a stored procedure
Dim sql As String = "uipersonal '"
is the stored procedure (i missed out all the variables as theres a few)

http://msdn.microsoft.com/library/de...dtypetopic.asp
<>

I hope this helps?

Cor

the code itself works fine - i just wasnt sure if it was good code or not -
seems a bit hacky to get the new @@identity out of the stored procedure -
just wondered if i was doing it right!

mark

[Cor Ligthert]

Luna,

As usual do I not give answers on security. When you don't use integrated
security, than it i thought that it was the next step as you do it. (I wrote
thought).

However for this I would not only rely for answers for this in this
newsgroup, however on more answers in the newsgroups

microsoft.public.dotnet.framework.adonet
microsoft.public.dotnet.security

I hope that it helps anyway something

Cor

[luna_s]

thanks , ill try a post in adonet

cheers

mark

[Nick Malik [Microsoft]]

Yes, this works, although I don't know if you need to create the reader for
the update condition. You could, as easily, use ExecuteNonQuery and check
to make sure that you updated one record.

These days, whenever I can, I avoid using identity columns because of
problems like this. I use GUIDs instead that are generated in the
application. (e.g. if the app is creating a new record, the app calls
Guid.NewGuid to create a new Guid and passes that in. The proc still needs
to look for update vs insert, but it never needs to return the new key
value, because the app already has it.

Anyway, your code looks fine.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"luna_s" <lu**@moonbasealpha.com> wrote in message
news:BM**************@newsfe3-win.ntli.net...

i've been playing with this code to return an ID on an insert - it seems a
little dirty/hacky to me

stored proc goes something like this (ive stripped out uneccesary sql code - the proc works fine) :-

CREATE PROCEDURE uipersonal

declare all the variables here

AS

IF EXISTS (SELECT * database WHERE id=@mainid)

UPDATE

ELSE

INSERT

SELECT SCOPE_IDENTITY()

GO

thats basically the stored proc
my vb code is something like :-

Private Sub putdatain()

mainid = TextBox1.Text.ToString
If mainid = "" Then mainid = "0"

Dim conn As New System.Data.SqlClient.SqlConnection(SQLstrConn)
Dim sql As String = "uipersonal '" all the variables i send
Dim Cmd As New System.Data.SqlClient.SqlCommand(sql, conn)
Dim objDR As System.Data.SqlClient.SqlDataReader

conn.Open()

If mainid = "0" Then
TextBox1.Text = Cmd.ExecuteScalar
ElseIf mainid > "0" Then
objDR =
Cmd.ExecuteReader(System.Data.CommandBehavior.Clos eConnection)
End If

conn.Close()

End Sub

this works ok but seems a little messy - and i was wondering if this method would cause issues in the future
(atm its just test code to play around)

thanks

mark

[luna_s]

"Nick Malik [Microsoft]" <ni*******@hotmail.nospam.com> wrote in message
news:UZ********************@comcast.com...

Yes, this works, although I don't know if you need to create the reader for the update condition. You could, as easily, use ExecuteNonQuery and check
to make sure that you updated one record.

These days, whenever I can, I avoid using identity columns because of
problems like this. I use GUIDs instead that are generated in the
application. (e.g. if the app is creating a new record, the app calls
Guid.NewGuid to create a new Guid and passes that in. The proc still needs to look for update vs insert, but it never needs to return the new key
value, because the app already has it.

Anyway, your code looks fine.

thanks for easing my mind !, ill look into the guid thing,
mark