vb.net 2003 - Window application Confirm record exist

[LennieKuah]

Hi There,
In VB6 I used ADP to confirm record existence by using this script
IF ADO.EOF OR ADO.BOF then
ado.addnew
End if

but in VB.NET 2003, How before using SQL String to Insert the record how do I confirm it's existence in the Table to prevent duplication.

Please help me.

[sknake]

This is more of an SQL question than VB.NET:

IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL DROP TABLE #Temp
Create Table #Temp
(
  UserName varchar(10),
  [Admin] bit
)

Declare @UserName varchar(10), @Admin bit
Set @UserName = 'sknake'
Set @Admin = Cast(1 as bit)
--Ok we have the test environment set up. Here is the logic:
INSERT INTO #Temp (UserName, [Admin]) 
Select @UserName, @Admin
Where NOT EXISTS
(
  Select *
  From #Temp x
  Where x.UserName = @Username
)

Select Cast((Case When @@ROWCOUNT = 1 Then 1 Else 0 End) as bit) As NewRecordAdded

[LennieKuah]

Hi Sknake
I cannot try out your suggestion as I don't understand it. The sample script you provided are you referring it to SQL SERVER STORED PROCEDURE ?

I have tried out this script but it's not working.

dim strSql as string ="Select * from Customers where CustomerId = '" & txtCustId.text & "' "

sqlConn = new sqlconnection(connStr)
sqlConn.open
sqlCmd = new sqlcommand(strSql, sqlconn
dim intRec as integer = sqlcmd.ExecuteNonQuery

If intRec <> 0 then
   messagebox.show("Duplicate Record")
End if

[sknake]

Please use code tags when posting code on daniweb:

The query I provided was not a stored procedure. Here is a complete example of it using temporary tables since I don't know your table definition:

Imports System.Data.SqlClient
Imports System.Text

Public Class frmInsertRecord

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Const connStr As String = "Data Source=apex2006sql;Initial Catalog=Scott;Integrated Security=True;"

    'First lets create the temporary customer table
    Using conn As New SqlConnection(connStr)
      conn.Open()

      'Lets create our test table
      Dim Sql As New List(Of String)
      Sql.Add("IF OBJECT_ID('tempdb..##Customer', 'U') IS NOT NULL DROP TABLE ##Customer")
      Sql.Add("Create Table ##Customer")
      Sql.Add("(")
      Sql.Add("  CustomerId int PRIMARY KEY,")
      Sql.Add("  CustomerName varchar(50)")
      Sql.Add(")")
      Dim query As String = GetText(Sql)

      Using cmd As New SqlClient.SqlCommand(query, conn)
        cmd.ExecuteNonQuery()
      End Using

      'I'm using a temp table so the connection has to be left open

      Sql.Clear()
      Sql.Add("Insert Into ##Customer (CustomerId, CustomerName)")
      Sql.Add("Select @CustomerId, @CustomerName")
      Sql.Add("Where")
      Sql.Add("NOT EXISTS")
      Sql.Add("(")
      Sql.Add("  Select *")
      Sql.Add("  From ##Customer")
      Sql.Add("  Where ##Customer.CustomerId = @CustomerId")
      Sql.Add(")")
      query = GetText(Sql)

      'Insert our first record. Its a new table so it should be inserted
      Using cmd As New SqlClient.SqlCommand(query, conn)
        cmd.Parameters.Add(New SqlParameter("@CustomerId", SqlDbType.Int)).Value = 1000
        cmd.Parameters.Add(New SqlParameter("@CustomerName", SqlDbType.VarChar)).Value = "sknake"
        Dim recCnt = cmd.ExecuteNonQuery()
        If (recCnt = 1) Then
          Console.WriteLine("The first record was inserted")
        Else
          Console.WriteLine("The first record was NOT inserted")
        End If
      End Using

      Using cmd As New SqlClient.SqlCommand(query, conn)
        'Changed the customer id to 1001, so this should be inserted
        cmd.Parameters.Add(New SqlParameter("@CustomerId", SqlDbType.Int)).Value = 1001
        cmd.Parameters.Add(New SqlParameter("@CustomerName", SqlDbType.VarChar)).Value = "sknake2"
        Dim recCnt = cmd.ExecuteNonQuery()
        If (recCnt = 1) Then
          Console.WriteLine("The second record was inserted")
        Else
          Console.WriteLine("The second record was NOT inserted")
        End If
      End Using

      Using cmd As New SqlClient.SqlCommand(query, conn)
        'Changed the customer id BACK to 1000, so this should NOT be inserted
        cmd.Parameters.Add(New SqlParameter("@CustomerId", SqlDbType.Int)).Value = 1000
        cmd.Parameters.Add(New SqlParameter("@CustomerName", SqlDbType.VarChar)).Value = "sknake2"
        Dim recCnt = cmd.ExecuteNonQuery()
        If (recCnt = 1) Then
          Console.WriteLine("The third record was inserted")
        Else
          Console.WriteLine("The third record was NOT inserted")
        End If
      End Using


      conn.Close()
    End Using
  End Sub

  Private Shared Function GetText(ByRef lst As List(Of String)) As String
    Dim sb As New StringBuilder()
    For Each s As String In lst
      sb.AppendLine(s)
    Next
    Return sb.ToString()
  End Function

End Class

Results in:

The first record was inserted
The second record was inserted
The third record was NOT inserted

As you can see I created a table then:
1) Insert record 1000. Its a brand new table so it should be inserted
2) Insert record 1001. Same thing, new record
3) I re-used record id 1000. This should NOT be inserted.

Also notice how I am using parameters on the SqlDbCommand instead of building the values directly in to the command text. This is a best practice for security and performance reasons and I would strongly advise you to start writing parameterized SQL statements.

[LennieKuah]

Hi SkNake,
Thanks for your sample script. I will cut and paste it onto my application to learn from it . Will get back to you. Thanks again.

[LennieKuah]

Hi SkNake,
The sample scripts that you have supplied, is is VB.NET 2003 version?
I need Vb.Net 2003 version. Please confirm. I am having problem with this SQLPARAMETERS statement.

[sknake]

What framework version are you compiling against?

[LennieKuah]

Hi Scotty,
I am using Microsoft .NET Framework 1.1 and Microsoft Development Environment 2003. and SQL SERVER 2000.

Thank you very much for your time and your help. So sorry to interrupt your Christmas Holiday session.

[sknake]

It seems I only have back to framework 2.0 installed and cannot actually test the code on 1.1. but looking at msdn it appears the syntax is the same for the section of code providing errors so I don't know what else to tell you. Have you considered upgrading to a more recent version of the .NET framework?