How to determine Record exist using SQL string

[LennieKuah]

Hi there,
Being new to Vb.Net 2003, I need help to determine whether to Create new Record or Update an exisitng record in SQL SERVER table.

In VB6 I used this logic to do that but in Vb.Net 2003, I am totally lost at it. Please Help me. Thanks.

VB6 Script:

Dim AdoRst as new ADODB.Recordset
    Dim AdoConn as new ADODB.Connection
    Dim connStr as string
    Dim strSql as string

StrSql = "Select * from TblOrder where OrderId = " &            txtOrderId.text
Connstr = etc.......
adoConn.connectionstring = connstr
AdoConn.open

if AdoRst.EOF and AdoRst.BOF then
     AdoRst.Fields("OrderId").value = txtOrderId.text
endif

    AdoRst.fields("OrderQuantity").value = txtOrderQuantity.text

[kvprajapati]

Post source code using bb code tags.
Read How to use bb code tags?

Start to read - ADO.NET class library from the books or MSDN online pages.

[everettnewell]

Here's an example of adding a record and updating records it's not commented I'm horrible at remembering to comment but, if u have any questions please feel free to ask on here or u can email me.

Public Sub AddRecord(ByVal tbl As String, ByVal fields As String, ByVal values As String)
        Dim con As New SqlConnection
        Dim sqlcmd As SqlCommand
        con.ConnectionString = My.Resources.ConnString
        Try
            con.Open()
            Try
                sqlcmd = New SqlCommand("INSERT INTO [" & tbl & "] (" & fields & ") VALUES(" & values & ")", con)
                Dim check As Integer = sqlcmd.ExecuteReader.RecordsAffected
                If check < 1 Then
                    MsgBox("Record was not added to " & tbl)
                End If
            Catch ex As Exception
                MsgBox(errormsg(ex))
            End Try
        Catch ex As Exception
            MsgBox(errormsg(ex))
        Finally
            con.Close()
        End Try

    End Sub

    Public Sub UpdateRecord(ByVal tbl As String, ByVal id As String, ByVal fields As String, ByVal values As String)
        Dim con As New SqlConnection
        Dim sqlcmd As SqlCommand

        con.ConnectionString = My.Resources.ConnString
        Dim multifields() As String = {fields}
        Dim multivalues() As String = {values}

        If InStr(",", fields) Then
            multifields = Split(fields, ",")
        End If
        If InStr(",", values) Then
            multivalues = Split(values, ",")
        End If

        Try
            con.Open()
            Try
                For n As Integer = 0 To multifields.Length - 1
                    If multifields(n) <> "" Then
                        sqlcmd = New SqlCommand("UPDATE [" & tbl & "] SET=[" & multifields(n) & "]=" & multivalues(n) _
                                                & " WHERE ([Id]=" & id & ")", con)
                        Dim check As Integer = sqlcmd.ExecuteReader.RecordsAffected
                        If check < 1 Then
                            MsgBox("Record was not modified")
                        End If
                    End If
                Next
            Catch ex As Exception
                MsgBox(errormsg(ex))
            End Try
        Catch ex As Exception
            MsgBox(errormsg(ex))
        Finally
            con.Close()
        End Try
    End Sub

Where we check the affected records you could change it to....

If check < 1 Then
  UpdateRecord(args)
Else
 MsgBox("Record Added")
End If

That way if no records were affected it would update it the given information instead.

[LennieKuah]

Thank you for your help. I will try out your sample script.