-2

How can you add data to a specific row in a database, using the where clause.

Here is what I tried:

"Insert into table1 (name, address) values ('" + textbox1.Text+ "', '"+textbox2.Text+"')
 where (name ='"+textbox1.Text+"')"
gdbdable
  • 4,445
  • 3
  • 30
  • 46
Medard
  • 41
  • 6

3 Answers3

4

Firstly, never ever concatenate strings for database input. It leaves you wide open to SQL Injection attacks. See this MSDN article for more information

Secondly, you don't do inserts with a WHERE clause. You could do an UPDATE with a WHERE

Examples:

INSERT INTO TABLE (col1, col2) VALUES (val1, val2)

UPDATE TABLE SET col1 = val1, col2 = val2 WHERE col3 = somevalue

for example in your case, you would want to write this for an insert (including parameters)

sql = "INSERT INTO table1 (name, address) VALUES (@textbox1,@textbox2)"; 

SqlCommand query = new SqlCommand("connection string", sql);

query.Parameters.AddWithValue(@textbox1, textbox1.Text);
query.Parameters.AddWithValue(@textbox2, textbox2.Text);

If you know the database field type, instead of using AddWithValue, instead use this syntax:

query.Parameters.Add(@parametername, SqlDBType.Type,size).Value = somevalue;

where SqlDBType.Type is the database field type (eg VarChar, Int, VarBinary etc) and size is the value of the field. If my DB field was VarChar(500), then my parameter setup would be

query.Parameters.Add(@parametername, SqlDBType.VarChar, 500).Value = somevalue;

you can replace the sql string with the following if you want to update rather than insert. Note, update records using an identifier - it would be a bad practice to use name as your WHERE clause.

sql = "UPDATE table1 SET name = @textbox1, address = @textbox2 WHERE xyz";

Parameters prevent users from putting unexpected values into boxes allowing for unauthorised code to be run. Using a concatenated string as you currently are could enable an attacker to compromise your entire database

Takarii
  • 1,612
  • 2
  • 18
  • 29
  • Upvoted but I would suggest using [Parameters.Add](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx) instead of AddWithValue so that you can specify the SqlDbType of your parameter. AddWithValue has been known to cause some issues on certain scenarios AFAIK based on old related questions. – jegtugado Aug 03 '16 at 08:42
  • only reason i did that was due to not knowing what the dbtype was. Otherwise, i would always add with sqldbtype.type, ill update my answer to reflect that. – Takarii Aug 03 '16 at 08:44
1

you can't insert with a 'where' clause inseert is for adding new records. If your updating then use:

Update table1 set 
name = '" + textbox1.Text + "',
address ='" + textbox2.Text+ "' 
where (name ='"+textbox1.Text+"')

or insert should be:

  "Insert into table1 (name, address) values ('" + textbox1.Text+    
 "','"+textbox2.Text+"')"

however make sure everything is validated against sql injection. or parameterize the above.

Glenn Packer
  • 212
  • 1
  • 8
  • 1
    its probably better to demonstrate parameterisation rather than provide a solution using the same flaws as the original user. – Takarii Aug 03 '16 at 08:38
0

You don't need a where clause for inserting a record into SQL Server database using INSERT statement. You should change your code as below to make it work:

"Insert into table1 (name, address) values ('" + textbox1.Text+ "', '"+textbox2.Text+"')

Two possible forms of a SQL insert statement are as below:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);


INSERT INTO table_name
VALUES (value1,value2,value3,...);

You can learn more about INSERT SQL statement here:

http://www.w3schools.com/sql/sql_insert.asp

RBT
  • 24,161
  • 21
  • 159
  • 240