Click here to Skip to main content
15,885,767 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
with this code am trying to update my table . but am not getting idea how to execute it.

at int UserId; am getting error
C#
public void ServiceInfo(int id)
       {

           string conn = Connection.ConnectionString;
           //"Data Source=IN-WKS-410;Initial Catalog=MSNETDB;Integrated Security=True;Pooling=False";
           SqlConnection mycon = new SqlConnection(conn);
           mycon.Open();
           foreach (DataGridViewRow row in dataGridView1.Rows)
           {

               string DisplayName = row.Cells[3].Value.ToString();
               string ServiceName = row.Cells[4].Value.ToString();
               string Status = row.Cells[5].Value.ToString();
               string ServiceType = row.Cells[6].Value.ToString();
               int UserId;
               SqlCommand com = new SqlCommand(conn, mycon);
               //Insert System table ID
               if (UpdateServices(UserId))
               {
                   com.CommandText = "insert into ServiceInfo(DisplayName,ServiceName,Status,ServiceType,UserId)values('" + DisplayName + "','" + ServiceName + "','" + Status + "','" + ServiceType + "'," + id + ")";
               }
               else
               {
                   com.CommandText = "UPDATE ServiceInfo SET" + " DisplayName='" + DisplayName + "',ServiceName='" + ServiceName + "',Status='" + ServiceType + "',ServiceType='" + ServiceType + "',UserId='"+UserId +"' Where UserId='" + UserId  + "'";
               }
               com.ExecuteNonQuery();
               com.Parameters.Clear();
               com.Dispose();


           }

           mycon.Close();
           mycon.Dispose();

       }



Help me in solving this query
thanks
sam.198979
Posted
Comments
sam.198979 25-Jun-13 3:26am    
public Boolean UpdateServices(int UserId)
{
Boolean @retunval;
string conn = Connection.ConnectionString;
SqlConnection mycon = new SqlConnection(conn);
SqlCommand command = new SqlCommand("select * from ServiceInfo where ID = '" + UserId.ToString() + "'", mycon);
mycon.Open();

SqlDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
MessageBox.Show("ID already exists");
this.DialogResult = DialogResult.Cancel;
@retunval = false;
}

else
{
@retunval = true;

}


return @retunval;
}
[no name] 25-Jun-13 6:00am    
Your first problem is that you are using string concatenation to construct your SQL query which is an invitation to SQL injection attacks. You should be using parameterized queries instead.
Your second problem is that UserId is an integer and you are treating it as if it were a string in your query. You need to get rid of the quotes for UserId. Which, by the way, would not have been a problem to begin with if you had just used a parameterized query to begin with.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900