Member Avatar for xuexue

Hi! I already established an iDB2Connection, now what I want to know, is how could I execute SQL Queries for INSERT, DELETE and UPDATE. I had tested the code already and it works. The code for count works; I get the total number of records.

My question is that for the UPDATE one, this does not work :( any problem or missing line of code?

Thanks!

Imports IBM.Data.DB2
Imports IBM.Data.DB2.iSeries

Dim str, sql, sqlUpdate As String
Dim conn As New iDB2Connection
Dim cmd, cmdUpdate As New iDB2Command
Dim count As Integer

'Opening of iDB2Connection
str = "Datasource=10.0.1.11;UserID=edith;password=edith;"
conn = New iDB2Connection(str)
conn.Open()

'SQL Query
sql = "SELECT COUNT(*) AS count FROM ictms.import"
cmd = New iDB2Command(sql, conn)

'SQL Query Execution
count = Convert.ToInt32(cmd.ExecuteScalar)

sqlUpdate = "UPDATE ictms.import SET document_number=123456 WHERE recid=11186"
cmdUpdate = New iDB2Command(sqlUpdate, conn)

conn.Close()
  • 1 Contributor
  • 1 Reply
  • 957 Views
  • 56 Minutes Discussion Span
  • Latest Post Latest Post by xuexue

Dani AI

Generated

— the reason your COUNT worked but the UPDATE did not is that SELECT used ExecuteScalar() (it returns a value) while DML (INSERT/UPDATE/DELETE) must be executed with ExecuteNonQuery() before you close the connection. In your first post you construct cmdUpdate and then immediately close the connection, so the UPDATE never runs. The other good practice you already used in post #2 (parameterized DELETE) is exactly what should be applied to UPDATE as well: parameters, proper types, and disposing the connection/command with a Using block.

The minimal, reliable pattern is: open the connection, create a parameterized UPDATE command, add parameter values, call ExecuteNonQuery() and check the returned row count, then close/dispose. Also catch exceptions and check for zero rows affected (means WHERE matched nothing) or permission/transaction issues.

Dim cs As String = "DataSource=10.0.1.11;UserID=edith;Password=edith;"
Using cn As New iDB2Connection(cs)
  cn.Open()
  Using cmd As New iDB2Command("UPDATE ictms.import SET document_number = @docNum WHERE recid = @recid", cn)
    cmd.Parameters.Add("docNum", iDB2DbType.iDB2Char, 20).Value = "123456"
    cmd.Parameters.Add("recid", iDB2DbType.iDB2Integer).Value = Integer.Parse(Label1.Text)
    Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
    ' rowsAffected = number of rows updated
  End Using
End Using

If rowsAffected = 0, verify the WHERE value and types; if updates execute but do not persist, check transaction/commit settings and the DB user privileges.

Member Avatar for xuexue

Just want to share with you guys my line of code :)

'*****************************Opening of iDB2Connection
Dim str, sqlDelete As String
Dim conn As New iDB2Connection
Dim cmdDelete As New iDB2Command

str = "Datasource=10.0.1.11;UserID=edith;password=edith;"
conn = New iDB2Connection(str)
conn.Open()

sqlDelete = "DELETE FROM ictms.import WHERE recid=@recid"

cmdDelete.Parameters.Add("recid", iDB2DbType.iDB2Integer)
cmdDelete.Parameters("recid").Value = Label1.Text

cmdDelete.Connection = conn
cmdDelete.CommandText = sqlDelete
cmdDelete.ExecuteNonQuery()

conn.Close()

MsgBox("Delete Successful!")

Response.Redirect("Test.aspx")
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.