how to set record by update query sql vb.net

Question

I have table and i want to set value if cell=null then set record by textbox . here is my table

--------------------
col1| col2| Note
1       2    aaa
5       5    (*)  set record Only here if cell is Null
42     14
47     55   
------------------

here is my code and my problem is the query write for every cell that is Null and I want to write only for next cell that before it is not null

con.Open()
query = " update firealarm set Note=@Note where Note Is Null"
Dim command As SqlCommand = New SqlCommand(query, con)
command.Parameters.Add(New SqlParameter("@Note", SqlDbType.NVarChar))
command.Parameters("@Note").Value = Notebox.Text.ToString
command.ExecuteNonQuery()
con.Close()

`query = " update firealarm set Note=@Note where Note is Null" — Subrati, Jun 20 '15 at 09:51

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

First, let's fix the immediate problem: in SQL NULL is not equal to anything, including other NULLs, so Note<>Null is true for all rows. To address this problem, SQL has special operators IS NULL and IS NOT NULL. Using where Note is not NULL will fix the problem.

A bigger problem remains, though: your program is vulnerable to SQL Injection attack. To fix this problem in Visual Basic see this Q&A.

Edit: (in response to an edit of the question)

I want to write only for next cell that before it is not null

The "next cell" implies some order of cells in the table. Since table rows should be treated as an unordered collection, let's assume that you would like to order by col1, the way the data is ordered in your example.

The query condition becomes much bigger for this:

UPDATE f
SET f.Note=@Note
FROM firealarm f
WHERE Note Is Null
  AND NOT EXIST (
    SELECT * FROM firealarm ff WHERE ff.col1 < f.col1 AND ff.Note IS NULL
  )

The new WHERE condition says that the update needs to be done on the cell with the lowest col1 and NULL value in the Note column.

edited May 23 '17 at 12:29

Community

1
1

answered Jun 20 '15 at 09:53

Sergey Kalinichenko

714,442
84
1,110
1,523

can you be my mentor? :3 – Anonymous Duck Jun 20 '15 at 09:55
@dasblinkenlight ----query = " update firealarm set Note=@Note where Note Is Null"---- the problem is set records in all cells that is null , i want to set only next cell after not null and keep all null cell – Subrati Jun 20 '15 at 10:03
@Subrati you need `where Note is not NULL`. After that add `query.Parameters.Add("@Note", SqlDbType.VarChar, 50).Value = Notebox.Text` to set the value of the `@Note` parameter. – Sergey Kalinichenko Jun 20 '15 at 10:06
@dasblinkenlight I have already added but it does not give me what I want ,it gives value for all null cells – Subrati Jun 20 '15 at 11:22

Fabio · Answer 2 · 2015-06-20T12:09:34.840

For updating exact row you need more conditions in the WHERE statement

UPDATE firealarm SET Note=@Note 
WHERE Note IS NULL 
AND Col1 = @ValueOfCol1
AND Col2 = @ValueOfCol2

vb.net code

con.Open()
Dim query As String = <![CDATA[UPDATE firealarm SET Note=@Note 
WHERE Note IS NULL
AND Col1 = @ValueOfCol1
AND Col2 = @ValueOfCol2]]>.Value
Dim command As SqlCommand = New SqlCommand(query, con)
With command.Parameters
    .Add(New SqlParameter With {.ParameterName = "@Note",
                                .SqlDbType = SqlDbType.NVarChar,
                                .Value = Notebox.Text})
    .Add(New SqlParameter With {.ParameterName = "@ValueOfCol1",
                                .SqlDbType = SqlDbType.Int,
                                .Value = 5})
    .Add(New SqlParameter With {.ParameterName = "@ValueOfCol2",
                                .SqlDbType = SqlDbType.Int,
                                .Value = 5})
End With
command.ExecuteNonQuery()
con.Close()

Or use value of identity column if your table have this

score 0 · Answer 3 · answered Jun 20 '15 at 12:13

0

Try this query,it will update just first row which is null

WITH UpdateList_view AS 
( SELECT TOP 1 * from  Fire alarm     
  Where NOTE is Null ) 


update UpdateList_view 
set Note=@Note

answered Jun 20 '15 at 12:13

Shekhar Pankaj

9,065
3
28
46

How sure you can be that updated row will be needed one? – Fabio Jun 20 '15 at 12:48
No cant assure,without few more details,hope there must be some id columns – Shekhar Pankaj Jun 20 '15 at 12:52

how to set record by update query sql vb.net

3 Answers3