in vb.net error Command text was not set for the command object

Question

I have a program in vb.net where I need data inserted into the database. When I run this code I get an error:

Command text was not set for the command object

Here is the code I have:

Private Sub InsertRelease(strRelease As String, rowInserted As Boolean)
    On Error GoTo errH

    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strPath As String
    Dim intImportRow As Integer
    Dim objType As String
    Dim strUsername, strPassword, strTable, strDatabase, strDsn, strSystem, strNewSql, sqlStr As String
    Dim intRecsAffected As Integer
    Dim boolRowInserted As Boolean

    strDsn = ComboBox1.Text
    strSystem = txtSystem.Text
    strUsername = txtUser.Text
    strPassword = txtPassword.Text

    If con.State <> 1 And strUsername <> "" And strPassword <> "" Then
         con.Open("{iSeries As ODBC Driver};System=" + strSystem + ";Dsn=" + strDsn + "; Uid=" + strUsername + "; Pwd=" + strPassword + ";")
    Else
        MessageBox.Show("Please enter the correct UserName And Password")
        txtUser.Focus()
        con = Nothing
    End If
    sqlStr = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values (strRelease,'81 AB',0);"
    strNewSql = ""

    con.Execute(strNewSql, intRecsAffected)
    con.Close()
    con = Nothing
    boolRowInserted = (intRecsAffected > 0)
    If (boolRowInserted) Then
        MessageBox.Show("Release " + strRelease + " added")
    Else
        MessageBox.Show("Release " + strRelease + "not added")
    End If
    Exit Sub
errH:
    MsgBox(Err.Description)
    con = Nothing

End Sub

Answer 1

The following demonstrates what your code might look like using ADO.net.

Pass the connection string directly to the constructor of the connection and pass the command text and connection to the constructor of the command. Open the connection and execute the command. ExecuteNonQuery returns rows affected.

Always use parameters to avoid sql injection.

Private Sub InsertRelease(strRelease As String)
    Dim intRecsAffected As Integer
    Dim strDsn = ComboBox1.Text
    Dim strSystem = txtSystem.Text
    Dim strUsername = txtUser.Text
    Dim strPassword = txtPassword.Text
    'Validate Input
    If strUsername = "" OrElse strPassword = "" Then
        MessageBox.Show("Please enter the correct UserName And Password")
        txtUser.Focus()
        Exit Sub
    End If

    Using con As New OdbcConnection($"{{iSeries As ODBC Driver}};System={strSystem};Dsn={strDsn}; Uid={strUsername}; Pwd={strPassword};"),
            cmd As New OdbcCommand("insert into jobscopedb.ppusrfs (search_key_uf, DATA_ITEM_UF, NUMERIC_VALUE_UF) values (@Release,'81 AB',0);", con)
        cmd.Parameters.Add("@Release", OdbcType.VarChar).Value = strRelease
        con.Open()
        intRecsAffected = cmd.ExecuteNonQuery
    End Using 'Closes and disposes the connection and command even it there is an error

    If intRecsAffected = 1 Then
        MessageBox.Show("Release " + strRelease + " added")
    Else
        MessageBox.Show("Release " + strRelease + "not added")
    End If
End Sub