Sql, Insert, Update : The Official Microsoft ASP.NET Forums

Reply

Sql, Insert, Update

May 03, 2012 08:46 AM|LINK

This is too complicated for me need help :)

I have this code:

SqlConnection konekcija1 = new SqlConnection(ConfigurationManager.ConnectionStrings["something"].ConnectionString);
        SqlCommand komanda1 = new SqlCommand(@"INSERT  Rating (Kontakt, Ocena,Ocenili_Puta) VALUES  (@Kontakt, @Ocena, @Ocenili_Puta) ", konekcija1);
        komanda1.Parameters.AddWithValue(("@Ocena"), ThaiRating2.CurrentRating);
        komanda1.Parameters.AddWithValue(("@Ocenili_Puta"), 1);
        komanda1.Parameters.AddWithValue(("@Kontakt"), LabelKontakt.Text);
        konekcija1.Open();
        komanda1.ExecuteNonQuery();
        konekcija1.Close();

I need code where first check if Kontakt records exists, if exists need to update +sum like

Ocenili_Puta=Ocenili_Puta+1

and

Ocena=Ocena+ThaiRating2.CurrentRating

If not exist need to insert new value @Ocena,@Ocenili_Puta,@Kontakt

Sorry for bad english.

Edited by marinac on May 03, 2012 08:47 AM

Reply

Re: Sql, Insert, Update

May 03, 2012 09:02 AM|LINK

Hi, i u can do insert and update in sql using store procedure.

for insert data into sql ,

create procedure sp_timesheet
(
@p_employee_id tinyint ,
@p_employee_name varchar (15) ,
@p_cur_date nvarchar(10),

@p_dat nvarchar(10),
@p_proj_name nvarchar(15),
@p_task nvarchar(15),
@p_descr nvarchar(25),
@p_fro nvarchar(10),
@p_h_to nvarchar(10)
)
as
begin
insert into timesheet (employee_id,employee_name,cur_date,dat,proj_name,task,descr,fro,h_to) select @p_employee_id,@p_employee_name,@p_cur_date,@p_dat,@p_proj_name,@p_task,@p_descr,@p_fro,@p_h_to
end

for update ...

Reply

bibinbabu04

Member

56 Points

23 Posts

Re: Sql, Insert, Update

May 03, 2012 09:03 AM|LINK

Hey you can create a stored procedure and check the duplication there itself...

Reply

mishra.bhupe...

Participant

1598 Points

378 Posts

Re: Sql, Insert, Update

May 03, 2012 09:04 AM|LINK

You can do that via stored procedure in better way. As in current way you can do it as: First check if record exists.: SqlConnection konekcija1 = new SqlConnection(ConfigurationManager.ConnectionStrings["something"].ConnectionString); SqlCommand komanda1 = new SqlCommand(@"select * from Rating where Kontakt=@Kontakt and Ocena=@Ocena and Ocenili_Puta=@Ocenili_Puta", konekcija1); komanda1.Parameters.AddWithValue(("@Ocena"), ThaiRating2.CurrentRating); komanda1.Parameters.AddWithValue(("@Ocenili_Puta"), 1); komanda1.Parameters.AddWithValue(("@Kontakt"), LabelKontakt.Text); konekcija1.Open(); SqlDataReader reader= komanda1.ExecuteReader(); konekcija1.Close(); if(reader.next())//if record exists { //update query SqlConnection konekcija1 = new SqlConnection(ConfigurationManager.ConnectionStrings["something"].ConnectionString); SqlCommand komanda1 = new SqlCommand(@"update Rating set Kontakt=@Kontakt and Ocena=@Ocena and Ocenili_Puta=@Ocenili_Puta", konekcija1); komanda1.Parameters.AddWithValue(("@Ocena"), (int)reader["Ocena"]+ThaiRating2.CurrentRating); //convert data type of reader[Ocena] . I did it to int komanda1.Parameters.AddWithValue(("@Ocenili_Puta"), 1+1); komanda1.Parameters.AddWithValue(("@Kontakt"), LabelKontakt.Text); konekcija1.Open(); komanda1.ExecuteNonQuery(); konekcija1.Close(); } else { //inser query SqlConnection konekcija1 = new SqlConnection(ConfigurationManager.ConnectionStrings["something"].ConnectionString); SqlCommand komanda1 = new SqlCommand(@"INSERT Rating (Kontakt, Ocena,Ocenili_Puta) VALUES (@Kontakt, @Ocena, @Ocenili_Puta) ", konekcija1); komanda1.Parameters.AddWithValue(("@Ocena"), ThaiRating2.CurrentRating); komanda1.Parameters.AddWithValue(("@Ocenili_Puta"), 1); komanda1.Parameters.AddWithValue(("@Kontakt"), LabelKontakt.Text); konekcija1.Open(); komanda1.ExecuteNonQuery(); konekcija1.Close(); }

Edited by mishra.bhupesh on May 03, 2012 09:05 AM
Marked as answer by marinac on May 03, 2012 02:06 PM

Reply

mishra.bhupe...

Participant

1598 Points

378 Posts

Re: Sql, Insert, Update

May 03, 2012 09:09 AM|LINK

I am sorry my previous answer seems unreadable so i am posting it in parts: first: First check if record exists.: SqlConnection konekcija1 = new SqlConnection(ConfigurationManager.ConnectionStrings[&amp;quot;something&amp;quot;].ConnectionString); SqlCommand komanda1 = new SqlCommand(@&amp;quot;select * from Rating where Kontakt=@Kontakt and Ocena=@Ocena and Ocenili_Puta=@Ocenili_Puta&amp;quot;, konekcija1); komanda1.Parameters.AddWithValue((&amp;quot;@Ocena&amp;quot;), ThaiRating2.CurrentRating); komanda1.Parameters.AddWithValue((&amp;quot;@Ocenili_Puta&amp;quot;), 1); komanda1.Parameters.AddWithValue((&amp;quot;@Kontakt&amp;quot;), LabelKontakt.Text); konekcija1.Open(); SqlDataReader reader= komanda1.ExecuteReader(); konekcija1.Close(); contd.........

Edited by mishra.bhupesh on May 03, 2012 09:10 AM

Reply

ruipedromach...

Member

258 Points

84 Posts

Re: Sql, Insert, Update

May 03, 2012 09:09 AM|LINK

SqlConnection konekcija1 = new SqlConnection(ConfigurationManager.ConnectionStrings["something"].ConnectionString);
konekcija1.Open();

SqlCommand komanda1 = new SqlCommand("SELECT COUNT(*) as total FROM RATING WHERE KONTACT = 1");

SQLDATAREADER A = komanda1.executereader();

a.read();

int b = convert.toin32(a["total"]);

a.close();

if(a == 0)
{
        komanda1 = new SqlCommand(@"INSERT into Rating (Kontakt, Ocena,Ocenili_Puta) VALUES  (@Kontakt, @Ocena, @Ocenili_Puta) ", konekcija1);
        komanda1.Parameters.AddWithValue(("@Ocena"), ThaiRating2.CurrentRating);
        komanda1.Parameters.AddWithValue(("@Ocenili_Puta"), 1);
        komanda1.Parameters.AddWithValue(("@Kontakt"), LabelKontakt.Text);

        komanda1.ExecuteNonQuery();
}


konekcija1.Close();

RuiMachado

Reply

sriramabi

Contributor

4351 Points

1277 Posts

Re: Sql, Insert, Update

May 03, 2012 09:11 AM|LINK

Hai

cheng u r query

SqlCommand komanda1 = new SqlCommand(@"INSERT Into Rating (Kontakt, Ocena,Ocenili_Puta) VALUES  (@Kontakt, @Ocena, @Ocenili_Puta) ", konekcija1);

Reply

MahadTECH

Star

8976 Points

1659 Posts

Re: Sql, Insert, Update

May 03, 2012 10:33 AM|LINK

First Your Query has some syntax prob :s

// corrected!

SqlCommand komanda1 = new SqlCommand(@"INSERT Into Rating (Kontakt, Ocena,Ocenili_Puta) VALUES  (@Kontakt, @Ocena, @Ocenili_Puta) ", konekcija1);

you will have to read the previous data if it is "Kontakt" is '1' So dont Rate.

SqlCommand komanda1 = new SqlCommand("SELECT COUNT(*) as total FROM RATING WHERE KONTACT = 1");

SQLDATAREADER A = komanda1.executereader();

a.read();

int b = convert.toin32(a["total"]);

a.close();
if(a == 0)
{
        komanda1 = new SqlCommand(@"INSERT into Rating (Kontakt, Ocena,Ocenili_Puta) VALUES  (@Kontakt, @Ocena, @Ocenili_Puta) ", konekcija1);
        komanda1.Parameters.AddWithValue(("@Ocena"), ThaiRating2.CurrentRating);
        komanda1.Parameters.AddWithValue(("@Ocenili_Puta"), 1);
        komanda1.Parameters.AddWithValue(("@Kontakt"), LabelKontakt.Text);

        komanda1.ExecuteNonQuery();
}


konekcija1.Close();

Sincerely,
Mahad Bin Mukhtar
Remember to Mark the replies as Answers
The easiest day was 'yesterday'.
MCP, MCSD
For .NET TECH Blog

Reply

marinac

Member

154 Points

146 Posts

Re: Sql, Insert, Update

May 03, 2012 11:15 AM|LINK

mishra.bhupesh
You can do that via stored procedure in better way. As in current way you can do it as: First check if record exists.: SqlConnection konekcija1 = new SqlConnection(ConfigurationManager.ConnectionStrings["something"].ConnectionString); SqlCommand komanda1 = new SqlCommand(@"select * from Rating where Kontakt=@Kontakt and Ocena=@Ocena and Ocenili_Puta=@Ocenili_Puta", konekcija1); komanda1.Parameters.AddWithValue(("@Ocena"), ThaiRating2.CurrentRating); komanda1.Parameters.AddWithValue(("@Ocenili_Puta"), 1); komanda1.Parameters.AddWithValue(("@Kontakt"), LabelKontakt.Text); konekcija1.Open(); SqlDataReader reader= komanda1.ExecuteReader(); konekcija1.Close(); if(reader.next())//if record exists { //update query SqlConnection konekcija1 = new SqlConnection(ConfigurationManager.ConnectionStrings["something"].ConnectionString); SqlCommand komanda1 = new SqlCommand(@"update Rating set Kontakt=@Kontakt and Ocena=@Ocena and Ocenili_Puta=@Ocenili_Puta", konekcija1); komanda1.Parameters.AddWithValue(("@Ocena"), (int)reader["Ocena"]+ThaiRating2.CurrentRating); //convert data type of reader[Ocena] . I did it to int komanda1.Parameters.AddWithValue(("@Ocenili_Puta"), 1+1); komanda1.Parameters.AddWithValue(("@Kontakt"), LabelKontakt.Text); konekcija1.Open(); komanda1.ExecuteNonQuery(); konekcija1.Close(); } else { //inser query SqlConnection konekcija1 = new SqlConnection(ConfigurationManager.ConnectionStrings["something"].ConnectionString); SqlCommand komanda1 = new SqlCommand(@"INSERT Rating (Kontakt, Ocena,Ocenili_Puta) VALUES (@Kontakt, @Ocena, @Ocenili_Puta) ", konekcija1); komanda1.Parameters.AddWithValue(("@Ocena"), ThaiRating2.CurrentRating); komanda1.Parameters.AddWithValue(("@Ocenili_Puta"), 1); komanda1.Parameters.AddWithValue(("@Kontakt"), LabelKontakt.Text); konekcija1.Open(); komanda1.ExecuteNonQuery(); konekcija1.Close(); }

After I read everyting I think that is simplest and the most complete code

But when I paste code I try for half hour to to correct and wipe out a variety of styles.

if you is not difficult to write like this in right of forum rich textbox you can find "Insert Code" next to "HTML"

Reply

marinac

Member

154 Points

146 Posts

Re: Sql, Insert, Update

May 03, 2012 11:22 AM|LINK

ruipedromachado

SqlConnection konekcija1 = new SqlConnection(ConfigurationManager.ConnectionStrings["something"].ConnectionString);
konekcija1.Open();

SqlCommand komanda1 = new SqlCommand("SELECT COUNT(*) as total FROM RATING WHERE KONTACT = 1");

SQLDATAREADER A = komanda1.executereader();

a.read();

int b = convert.toin32(a["total"]);

a.close();

if(a == 0)
{
        komanda1 = new SqlCommand(@"INSERT into Rating (Kontakt, Ocena,Ocenili_Puta) VALUES  (@Kontakt, @Ocena, @Ocenili_Puta) ", konekcija1);
        komanda1.Parameters.AddWithValue(("@Ocena"), ThaiRating2.CurrentRating);
        komanda1.Parameters.AddWithValue(("@Ocenili_Puta"), 1);
        komanda1.Parameters.AddWithValue(("@Kontakt"), LabelKontakt.Text);

        komanda1.ExecuteNonQuery();
}


konekcija1.Close();

Ok this code checks if KONTACT = 1 and if not do INSERT?

If I understand it correctly?

If that so... how to update colone when KONTACT = 1

Ocenili_Puta=Ocenili_Puta+1

and

Ocena=Ocena+ThaiRating2.CurrentRating

And if not do that your code

Sql, Insert, Update RSS

14 replies

marinac

Sql, Insert, Update

parthiban566

Re: Sql, Insert, Update

bibinbabu04

Re: Sql, Insert, Update

mishra.bhupe...

Re: Sql, Insert, Update

mishra.bhupe...

Re: Sql, Insert, Update

ruipedromach...

Re: Sql, Insert, Update

sriramabi

Re: Sql, Insert, Update

MahadTECH

Re: Sql, Insert, Update

marinac

Re: Sql, Insert, Update

mishra.bhupesh

marinac

Re: Sql, Insert, Update

ruipedromachado

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft