update / insert button

[luna]

trying to create an update/insert button
basically if the record exists in the database i want to do an update
if it doesnt exist i want to create a new record using insert
all using the same button

my code (which is a mess from playing around!) (this is a test page im
playing with)
ID in the database is auto increment
just cant seem to get anything to work
(id rather have it checking the database for the exisitence of an ID rather
than relying on the contents of the
id.text box)
(this code wont work as im unsure how to query the database first to check
the exisitence of an ID then
create a sql string accordingly!)


Dim Sql As String = ""
Dim sqlinsert As String = "INSERT INTO news (news) VALUES ('" +
TextBox2.Text.ToString + "')"
Dim sqlupdate As String = "UPDATE news SET news = '" +
TextBox2.Text.ToString + "' WHERE Id = ('" + TextBox1.Text.ToString + "')"
Dim strConn As String = "server=;uid=sa;pwd=;database="
Dim conn As New System.Data.SqlClient.SqlConnection(strConn)
Dim Cmd As New System.Data.SqlClient.SqlCommand(Sql, conn)
Dim objDR As System.Data.SqlClient.SqlDataReader
conn.Open()
objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
While objDR.Read()
If objDR.IsDBNull(0) Then
Sql = sqlinsert
Else : Sql = sqlupdate
End If
End While
Dim Cmd2 As New System.Data.SqlClient.SqlCommand(Sql, conn)
objDR = Cmd2.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

 

[Guest]

Can you alternatively implement this business logic in a Stored Proc at DB level....That seems to be an effective solution!!!

Alternatively...you can write a conditional SQL query in your code ( and assign to var sqlInsertUpd) which handles the logic, and then execute the same using reader.

Regards
Avneesh