ODBC ExecuteNonQuery() "System Error"

[dublinus]

Hi,

I am writing a VB.NET application, and I'm having a little trouble writing to the database (via ODBC). I can read from it perfectly well, but when I send an "UPDATE" command via ExecuteNonQuery(), I get a "System Error". Can someone help Identify the problem, or maybe propose alternatives? Here is the code (The error is on line 68):

Imports System.Data
Imports Microsoft.Data.Odbc

Public Class Form1
    Inherits System.Windows.Forms.Form

Windows Form Designer generated code

    Dim cn As OdbcConnection
    Dim reader As OdbcDataReader
    Dim adapter As OdbcDataAdapter
    Dim login As New Login()
    Dim commandstring As String = "SELECT * FROM OAUSER.PROJECT"
    Dim command As New OdbcCommand(commandstring, cn)


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ListBox1.Items.Clear()
        If Read_Database("SELECT proj_name FROM OAUSER.PROJECT") Then
            While reader.Read()
                ListBox1.Items.Add(reader.GetString(0))
            End While
            Write_to_Database() '"UPDATE SET access_level = 1 FROM OAUSER.PROJECT")
        End If
    End Sub
    

Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        'On Error Resume Next
        cn.Close()
        reader.Close()
    End Sub
    

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Connect_To_Server() Then
            command = New OdbcCommand(commandstring, cn)
            reader = command.ExecuteReader()
            adapter = New OdbcDataAdapter()
            Button1_Click(Me, e)
        Else
            Environment.Exit(0)
        End If
    End Sub
    

Public Function Read_Database(ByVal command1 As String) As Boolean
        'On Error GoTo errmessage
        command.Dispose()
        reader.Close()
        command = New OdbcCommand(command1, cn)
        reader = command.ExecuteReader()
        Read_Database = True
        Exit Function

errmessage:
        Read_Database = False
        MsgBox("There was an error reading from the database.")
    End Function
    

Public Function Write_to_Database() 'ByVal command1 As String) As Boolean
        On Error GoTo errmessage

        command.Dispose()
        command = New OdbcCommand("UPDATE OAUSER.PROJECT SET access_level=1", cn)
        command.Prepare()
        command.ExecuteNonQuery()



        Write_to_Database = True
        Exit Function

errmessage:
        Write_to_Database = False
        MsgBox("There was an error writing to the database.")
    End Function
    

Public Function Connect_To_Server() As Boolean
        On Error GoTo errmessage
        login.ShowDialog()
        cn = New OdbcConnection("DRIVER={Primavera Software Development Kit};SERVER=MSSQLSERVER;UID=" + login.user1.Text + ";PWD=" + login.pass1.Text + ";DATABASE=PMDB;")
        cn.Open()
        Connect_To_Server = True
        Exit Function

errmessage:
        MsgBox("Could not connect to the SQL server. Check your username and password.")
        Connect_To_Server = False
    End Function

End Class

[kvprajapati]

Try this code, if it is working then think where you got problem.

Sub Test()
    Dim cn As OdbcConnection
    Dim cmd As OdbcCommand
    cn=New OdbcConnection("DRIVER={Primavera Software Development Kit};SERVER=MSSQLSERVER;UID=your_username;PWD=your_pass;DATABASE=PMDB;")
  cmd=new OdbcCommand("UPDATE PROJECT SET access_level=1",cn)

 cn.Open()
 cmd.ExecuteNonQuery()
 cn.Close()
End sub

PS: Use Oledb, SqlClient provider class library.

[Stevoni]

I had the similar problem with a Pervasive ODBC adapter, have you tried opening the connection before Executing the query?
I.e.:

command.prepare()
cn.open()
command.ExecuteNonQuery()
if not cn.State = ConnectionState.Closed then cn.Close()