Try using a stored procedure but instead of
INSERT INTO tablename (WorkDate) VALUES (@.WorkDate)
have
IF NOT EXISTS(SELECT * FROM tablename WHERE WorkDate =@.WorkDate)
INSERT INTO tablename (WorkDate) VALUES (@.WorkDate)
That way you can call the stored procedure passing in the WorkDate value, but a record will only be inserted if the value does not already exist.
|||Thanks TAT for your help... question, what if a record in there with the same date how would I let the user know?
|||To let the user know, you need an additional parameter so your stored procedure would look like this
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: Add record if not already on file
-- =============================================
CREATE PROCEDURE dbo.usp_TableNameAdd
@.WorkDate DateTime,
@.Exists BIT OUTPUT
AS
SET NOCOUNT ON;
SET @.Exists = 0
IF NOT EXISTS(SELECT * FROM tablename WHERE WorkDate = @.WorkDate)
INSERT INTO tablename (WorkDate) VALUES (@.WorkDate)
ELSE
SET @.Exists = 1
GO
Another way could be to raise user friendly error message from within your SP. Read BOL for RAISERROR.
I have a few more questions. When calling the sp I am getting an error message also in the SP the staring lines
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
are not working for me, is there something I am not seeing.
namespace WindowsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//Pull from Text File in from Network Drive
string Source = @."\.\J-1119.txt";
string Date = "";
string O1 = "";
string A1;
string P1;
string R1;
string LineIn;
StreamReader InFile = new StreamReader(Source);
SqlCommand SC = new SqlCommand("SP_PromoNoDups", sConnect);
SC.Parameters.Add(new SqlParameter("@.WorkDate", WorkDate));
// SqlCommand SC = new SqlCommand("Select * from [tblname]", sConnect);
SC.CommandType = CommandType.StoredProcedure;
LineIn = InFile.ReadLine();
// pos is the location of the cursor
int pos = LineIn.IndexOf("WORK DATE:");
if (pos > 0)
{
Date = LineIn.Substring(pos + 10, 10).Trim();
}
while (LineIn != null)
{
if (LineIn.IndexOf("O1:") == 2)
{
if (LineIn.Length > 10)
{
O1 = LineIn.Substring(10).Trim();
}
else
{
O1 = "";
}
LineIn = InFile.ReadLine();
LineIn = InFile.ReadLine();
while (LineIn != null && LineIn.Trim() != "")
{
P1 = LineIn.Substring(2, 5).Trim();
A1 = LineIn.Substring(19, 20).Trim();
R1 = LineIn.Substring(41, 6).Trim();
string Adjustment = LineIn.Substring(57).Trim(); LineIn = InFile.ReadLine();
if (LineIn.IndexOf("mane") >= 0)
{
break;
} SC.CommandType = LineOut;(Erroring out here)
SC.ExecuteReader();
// if page header - look for next O1
}
LineIn = InFile.ReadLine();
LineIn = InFile.ReadLine();
}
InFile.Close();
}
}
}
}
