verify given values existing in database or not?

Posted by Mandlaa under ASP.NET on 10/12/2013 | Points: 10 | Views : 1616 | Status : [Member] | Replies : 2
TagId(PK) BatchId ClientId

100 1 111

200 2 222

300 3 333

this is my table

string tagid;
string batchid;
string clientid;
public void Data()
{
string Con = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection con1 = new SqlConnection(Con);
SqlDataAdapter da = new SqlDataAdapter("SELECT [TagId],[BatchId],[ClientId] FROM TagTable", con1);

DataSet ds = new DataSet();

da.Fill(ds, "TagTable");
foreach (DataRow myRow in ds.Tables[0].Rows)
{
tagid = myRow["TagId"].ToString();
clientid = myRow["ClientId"].ToString();
batchid = myRow["BatchId"].ToString();
}

public void Insertdata()
{
SqlConnection con2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con2.Open();
SqlCommand da1 = new SqlCommand("Insert into TagTable([TagId],[BatchId],[ClientId]) values('" + Tno + "'," + Bid + "," + Cid + ")", con2);
da1.ExecuteNonQuery();
con2.Close();
}

Task:Suppose i want to giving this 3 values

Tno=100,Bid=1,Cid= 111,

I want to check this condition this condition is true get that mathing values, condition is false Insert that values in that table,

I am giving Tno=1001,Bid=1,Cid= 111, this conditiuon is false this values insert into table

Problem:

Getting this batchid ,clientid, tagid values from database for checking condition

I am writting this code

public void Data()
{
string Con = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection con1 = new SqlConnection(Con);
SqlDataAdapter da = new SqlDataAdapter("SELECT [TagId],[BatchId],[ClientId] FROM TagTable", con1);

DataSet ds = new DataSet();

da.Fill(ds, "TagTable");
foreach (DataRow myRow in ds.Tables[0].Rows)
{
tagid = myRow["TagId"].ToString();
clientid = myRow["ClientId"].ToString();
batchid = myRow["BatchId"].ToString();
}

I am giving Tno=100,Bid=1,Cid= 111, database existing values also control directly going else part,please correct my code

protected void Page_Load(object sender, EventArgs e)
{

Data();

try
{
if (Bid == batchid && Cid == clientid&& Tno == tagid)
{
display matching values
}
else
{
Insertdata();

}

}

catch (Exception ex)
{

Response.Write("Sytem Error happend. Please contact support. " + ex.InnerException.ToString());
}

finally { con.Close(); }

}
}




Responses

Posted by: Bandi on: 10/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Better to use stored procedure for this kind of situation
CREATE PROCEDURE USP_InsertTag
(
@P_TagId INT,
@P_BatchId INT,
@P_ClientId INT
)
AS
BEGIN
IF NOT EXIST (SELECT [TagId],[BatchId],[ClientId] FROM TagTable WHERE TagId= @P_TagId AND BatchId = @P_BatchId AND ClientId= @P_ClientId )
BEGIN
Insert into TagTable([TagId],[BatchId],[ClientId]) values(@P_TagId, @P_BatchId, @P_ClientId )
END
ELSE
SELECT [TagId],[BatchId],[ClientId] FROM TagTable WHERE TagId= @P_TagId AND BatchId = @P_BatchId AND ClientId= @P_ClientId
END



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Sample code for calling stored procedure in ASP
      SqlConnection conn = new SqlConnection(path);

/* set up call for stored procedure */
SqlCommand doit = new SqlCommand("usp_insertTag", conn);
doit.CommandType = CommandType.StoredProcedure;
// parameter settings
doit.Parameters.Add(new SqlParameter ("@mName", name));
doit.Parameters.Add(new SqlParameter("@mType", type));
doit.Parameters.Add(new SqlParameter("@mFriendly", friend));
/* end call params */

conn.Open();
doit.Connection = conn;
doit.ExecuteNonQuery();
conn.Close();


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response