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.
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 NewRecordAddedHi 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 ifPlease 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.
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.
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.
What framework version are you compiling against?
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.
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?
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.