verify given values existing in database or not?

Posted by Mandlaa under ASP.NET on 10/12/2013 | Points: 10 | Views : 2426 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

}
}

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

Latest Posts