Update not working using iDB2

[xuexue]

Hi! I have no problem when trying to execute and insert or a delete SQL Command. However, this update command does not seems to work well and I am having a hard time to figure it out. Kindly help me please.

I am using an i Series or AS/400 database.

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

Public conn As New iDB2Connection
Public str As String = "Datasource=10.0.1.11;UserID=edith;password=edith;DefaultCollection=impexplib"

Dim cmdUpdate As New iDB2Command
Dim sqlUpdate As String

conn = New iDB2Connection(str)
conn.Open() 

sqlUpdate = "UPDATE impexplib.expusers SET loginDate=@loginDate, loginTime=@loginTime WHERE username=@username"

cmdUpdate.Parameters.Add("username", iDB2DbType.iDB2VarChar)
cmdUpdate.Parameters.Add("loginDate", iDB2DbType.iDB2Date)
cmdUpdate.Parameters.Add("loginTime", iDB2DbType.iDB2Time)

cmdUpdate.Parameters("username").Value = txtUsername.Text
cmdUpdate.Parameters("loginDate").Value = Now.ToString("d")
cmdUpdate.Parameters("loginTime").Value = Now.ToString("T")

cmdUpdate.Connection = conn
cmdUpdate.CommandText = sqlUpdate
cmdUpdate.ExecuteNonQuery()
conn.Close()

Please help me what I am doing wrong? The update code does not really work. Even a simple update of password does not work to.

Thanks!

[cgeier]

Try the following:

Dim param1 As New iDB2Parameter
param1.ParameterName = "@username"
param1.iDB2DbType = iDB2DbType.iDB2VarChar
param1.Value = txtUsername.Text

cmdUpdate.Parameters.Add(param1)

Dim param2 As New iDB2Parameter
param2.ParameterName = "@loginDate"
param2.iDB2DbType = iDB2DbType.iDB2Date
param2.Value = DateTime.Now.ToString("d")

cmdUpdate.Parameters.Add(param2)

Dim param3 As New iDB2Parameter
param3.ParameterName = "@loginTime"
param3.iDB2DbType = iDB2DbType.iDB2Time
param3.Value = DateTime.Now.ToString("T")

cmdUpdate.Parameters.Add(param3)

Or in lines 15-17 you could try the following:

cmdUpdate.Parameters.Add("@username", iDB2DbType.iDB2VarChar)
cmdUpdate.Parameters.Add("@loginDate", iDB2DbType.iDB2Date)
cmdUpdate.Parameters.Add("@loginTime", iDB2DbType.iDB2Time)

add the "@" sign.

Also in lines 19-21:

cmdUpdate.Parameters("@username").Value = txtUsername.Text
cmdUpdate.Parameters("@loginDate").Value = Now.ToString("d")
cmdUpdate.Parameters("@loginTime").Value = Now.ToString("T")

The above code is untested.