Naturally, in many, if not most cases, the best scenario, before inserting new data, is to check to see if it’s already there, in order to keep from double-entering information.
In this sample we use a SQL Server table with 4 fields (id, title, link, and Description). The id field is an identity field, for auto-numbering purposes. That field will not need to be checked, of course. In this example, we will just be checking the title and link fields. Naturally, you can check more, depending on your circumstances.
The subroutine ‘SeeRecords’ is not necessary, but is here merely to show you the last record inserted (with your data).
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" Runat="server">
Dim blHasRows as Boolean
Sub doInsert(Source as Object, E as EventArgs)
Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPWD;database=YourDB"
Dim MySQL as string = "Select * from samples where title=@title and link=@link"
Dim MyConn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
With Cmd.Parameters
.Add(New SQLParameter("@title", frmtitle.text))
.Add(New SQLParameter("@link", frmlink.text))
End With
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
if objDR.HasRows then
blHasRows="True"
else
blHasRows="False"
End If
MyConn.Close()
MySQL = "Insert into samples (title, link, Description) Values (@title, @link, @Description)"
Dim Cmd2 as New SQLCommand(MySQL, MyConn)
With Cmd2.Parameters
.Add(New SQLParameter("@title", frmtitle.text))
.Add(New SQLParameter("@link", frmlink.text))
.Add(New SQLParameter("@Description", frmDescription.text))
End With
if blHasRows="False" then
MyConn.Open()
cmd2.ExecuteNonQuery()
lblResults.text="Your information has been received!"
MyConn.Close
seeRecords()
else
lblResults.text="Sorry, that information is already in the database"
End If
End Sub
Sub Page_Load(Source as Object, E as EventArgs)
End Sub
Sub seeRecords()
Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPWD;database=YourDB"
Dim MySQL as string = "Select Top 1 * from samples order by ID desc"
Dim MyConn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
With Cmd.Parameters
.Add(New SQLParameter("@title", frmtitle.text))
.Add(New SQLParameter("@link", frmlink.text))
End With
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
MyDataGrid.DataSource=objDR
MyDataGrid.DataBind
MyConn.Close()
End Sub
</script>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 3.2">
<title>Checking Data before Inserting New Data</title>
</head>
<body>
<form id="form1" Runat="server">
<div align="center"><table>
<tr>
<td align="right">Title</td>
<td> <asp:textbox id="frmtitle" Text="Tender Moments" Runat="server" /></td>
</tr>
<tr>
<td align="right">Link</td>
<td> <asp:textbox id="frmlink" Text="http://tendermoments.com" Runat="server" /></td>
</tr>
<tr>
<td align="right">Description</td>
<td> <asp:textbox id="frmDescription" Runat="server" /></td>
</tr><tr>
<td align="right"><asp:button id="button1" onclick="doInsert" Text="Submit" Runat="server" /></td>
<td></td>
</tr>
</table>
<asp:Label ID="lblResults" Font-Bold="True" Runat="server" />
<asp:Datagrid Runat="server"
Id="MyDataGrid"
GridLines="Both"
cellpadding="0"
cellspacing="0"
Headerstyle-BackColor="#BDCFE7"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="8"
BackColor="#E7EFFF"
Font-Name="Arial"
Font-Size="8"
BorderColor="Black">
</asp:DataGrid>
</div>
</form>
</body>
</html>
