Data Values Validation

Hello all...I am trying to validate that the new work date that is being imported from the text file does not exist in the table. So in other words I do not want duplicate data. This is what I haveSqlDataReader dr =newSqlDataReader(); SqlParameter sp =newSqlParameter("@.WorkDate", Data.SqlDbType.datetime, 8, Data.ParameterDirection.Input); if (dr.HasRows) sp ="@.WorkDate"; else *How does that look? Am I in the ball part…

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);

***TRYING TO CALL MY SP****
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();
}
}
}
}