VB.NET - odbc connection - Asked By sadhish babu muthusamy on 14-Jun-12 04:07 AM

Actually, i try to execute some error and i received error which shows as below when i execute.

system.invalidoperationexception:ExecuteNonQuery:Connection property has not been initialized

Dim odbccon3 As OdbcConnection   'Declare the ODBC connection
    Dim dr3 As OdbcDataReader
    Dim cmd3 As New OdbcCommand
    Dim ConnString3 As String = "Dsn=premierlc"

cmd3 = New OdbcCommand("SELECT * FROM XXXX ", odbccon3)
      With cmd3
        .CommandText = "insert into jobsheet values('" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "')"
        .CommandType = CommandType.Text
        .Connection = odbccon3

      End With
      Dim z As Integer
      Try
        odbccon3 = New OdbcConnection(ConnString3)
        odbccon3.Open()    \\received error
        z = cmd3.ExecuteNonQuery()

        If z = 1 Then
          'ToolStripStatusLabel1.Text = custname_cmb.Text & " data has been saved"
        End If
      Catch ex As Exception
        MsgBox(ex.ToString())
      End Try

 Thanks in advance.
Vikram Singh Saini replied to sadhish babu muthusamy on 14-Jun-12 05:34 AM
You have not initialized the OdbcConnection. Do as:

1.  Dim ConnString3 As String = "Dsn=premierlc"
2.  Dim odbccon3 As OdbcConnection  = New OleDbConnection(ConnString3)

Done!
Vikram Singh Saini replied to sadhish babu muthusamy on 14-Jun-12 05:37 AM
Sorry for the second code line. It should be modified as:

Dim odbccon3 As OdbcConnection = New OdbcConnection(ConnString3)
TSN ... replied to sadhish babu muthusamy on 14-Jun-12 05:43 AM

Change the below line of code

odbccon3 = New OdbcConnection(ConnString3)

to

dim odbccon3 as OdbcConnection  = New OdbcConnection(ConnString3)

 

sadhish babu muthusamy replied to Vikram Singh Saini on 14-Jun-12 06:47 AM
Hi Vikram Singh Saini,



Thanks alot  :)..........you really solve my problem.

But i have another new problem........i hope that u can help me for that too.

the new problem with same coding is when i execute the program. For first time i click the button it show that the connection state is close but when i click the button for second time the data save into database without any error.


please check the coding for me.

Dim ConnString3 As String = "Dsn=premierlc"
    Dim odbccon3 As OdbcConnection = New OdbcConnection(ConnString3)
    Dim dr3 As OdbcDataReader
    Dim cmd3 As New OdbcCommand


cmd3 = New OdbcCommand("SELECT * FROM jobsheet ", odbccon3)
      With cmd3
        .CommandText = "insert into XXXX values('" & "67885" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "','" & "dsfsdf" & "')"
        .CommandType = CommandType.Text
        .Connection = odbccon3

      End With
      Dim z As Integer
      Try
        odbccon3 = New OdbcConnection(ConnString3)
        odbccon3.Open()
        z = cmd3.ExecuteNonQuery()

        If z = 1 Then
          'ToolStripStatusLabel1.Text = custname_cmb.Text & " data has been saved"
        End If
      Catch ex As Exception
        MsgBox(ex.ToString())
      End Try

   
Vikram Singh Saini replied to sadhish babu muthusamy on 14-Jun-12 07:17 AM
It seems that the opening of connection takes time and so might be the cause of error. Try to avoid it by adding code as:

 If odbccon3.Open().State.Equals(ConnectionState.Closed) Then
       odbccon3.Open()
      End If


I think this would help you to solve your problem.
sadhish babu muthusamy replied to Vikram Singh Saini on 14-Jun-12 07:45 AM
Hi Vikram Singh Saini,

Thanks for ur help.

its really work........thanks alot buddy.....

ur coding got some mistake but i corrected it.

it should like this

If odbccon3.State.Equals(ConnectionState.Closed) Then
        odbccon3.Open()
End If

Any how you really help me alot............

thanks buddy.......
pls provide ur email address or facebook id.....
my id is babu.sadhish@gmail.com

Vikram Singh Saini replied to sadhish babu muthusamy on 14-Jun-12 07:50 AM
Glad to know it helped you. It's the appreciation token for our efforts.

Coding mistake - Because of responding as soon as possible.

Facebook Id - vs00saini