[VB 2008] ERRORE: Fill proprietà SelectCommand.Connection non è stata inizializzata

[ans66]

Non riesco a comprendere il motivo dell'errore. Ho controllato il passaggio dei parametri, la query va bene.Sto tentando di creare funzioni di libreria per UPDATE/INSERT/DELETE con Oledb.OledbParameter:

OleDbCon = New OleDb.OleDbConnection
Oledbcom = New OleDb.OleDbCommand

OleDbCon.ConnectionString = ConnDB
Oledbcom.CommandText = Quer
Oledbcom.Connection = OleDbCon

OleDbCon.Open()
LanciaParametri(prm, StrutDaPassare)
OleDbAdpt.SelectCommand = Oledbcom
OleDbAdpt.Fill(ds) 'qui l'errore
dt = ds.Tables(0
Beep()

Query:
"SELECT tbl_EntrateUscite.ID_EntrateUscite, " & _
"tbl_EntrateUscite.Data_Operazione, tbl_Soggetti.Nome, tbl_EntrateUscite.Descrizione, " & _
"tbl_EntrateUscite.Amm_ent_usc, tbl_EntrateUscite.Note, " & _
"tbl_EntrateUscite.tbl_Cat, tbl_EntrateUscite.tbl_Sog, tbl_Soggetti.Cognome " & _
"FROM tbl_Soggetti INNER JOIN (tbl_Categorie INNER JOIN tbl_EntrateUscite ON " & _
"tbl_Categorie.id_Categorie = tbl_EntrateUscite.tbl_Cat) ON tbl_Soggetti.ID_Soggetto = " & _
"tbl_EntrateUscite.tbl_Sog WHERE (((tbl_EntrateUscite.Descrizione)=? AND " & _
"tbl_EntrateUscite.Data_Operazione=? " & _
"AND tbl_EntrateUscite.Amm_ent_usc=?))ORDER BY tbl_EntrateUscite.Descrizione;"

Passo i parametri con due Array contenuti in una matrice:

DaTrovVal.Add("COOP ADRIATICA") 'String Descrizione
TpPrm.Add(DbType.String)
DaTrovVal.Add("05/01/2008") 'DataTime Data_Operazione
TpPrm.Add(DbType.DateTime)
DaTrovVal.Add("-€ 13,14") 'Currency Amm_ent_usc
TpPrm.Add(DbType.Currency)

Rcd = New WindowsApplication1.MieClassePersonale.Record(TpPr m, DaTrovVal)

[DragonOfLight]

che tipo di errore ti restituisce?

comunque:

...
OleDbAdpt.
...

OleDbAdpt come è dichiarato?

...
OleDbAdpt.Fill(ds) 'qui l'errore
...

ds come è dichiarato?

dt = ds.Tables(0
Beep()

non manca una ')' ?

[ans66]

l'errore e` nell'oggetto: Fill proprieta` SelectCommand.Connection non e` stata inizializzata

scusatemi per la parentesi, ovviamente c'e`, e` stato un errore di battitura.

Ho passato i parametri con: OleDbAdpt.SelectCommand = OledbCom. Ho provato anche a passare Oledbcon (connection) con Oledb.SetCommand.connection ma nulla.

[oregon]

OleDbAdpt.SelectCommand = Oledbcom

e' un altro errore di battitura?

[ans66]

per completezza di informazione allego di seguito tutto il codice le funzioni e l'evento Button7_Click che fa partire l'evento (ho usato copia & incolla, non dovrebbero esserci quindi problemi di battitura]:

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
Dim dt As New System.Data.DataTable
Dim Fnct As New WindowsApplication1.MieClassePersonale
Dim FileMDB As String = Microsoft.VisualBasic.FileIO.SpecialDirectories.My Documents & "\EntrateUscite_2003.mdb"
Dim psw As String = "ciao"
Dim connDB As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileMDB & "; Jet OLEDBatabase Password=" & psw & ";"
Dim OledbCn As New OleDb.OleDbConnection
Dim OledbAdpt As New OleDb.OleDbDataAdapter
Dim Rcd As New WindowsApplication1.MieClassePersonale.Record
Dim Quer As String = "SELECT tbl_EntrateUscite.ID_EntrateUscite, " & _
"tbl_EntrateUscite.Data_Operazione, tbl_Soggetti.Nome, tbl_EntrateUscite.Descrizione, " & _
"tbl_EntrateUscite.Amm_ent_usc, tbl_EntrateUscite.Note, " & _
"tbl_EntrateUscite.tbl_Cat, tbl_EntrateUscite.tbl_Sog, tbl_Soggetti.Cognome " & _
"FROM tbl_Soggetti INNER JOIN (tbl_Categorie INNER JOIN tbl_EntrateUscite ON " & _
"tbl_Categorie.id_Categorie = tbl_EntrateUscite.tbl_Cat) ON tbl_Soggetti.ID_Soggetto = " & _
"tbl_EntrateUscite.tbl_Sog WHERE (((tbl_EntrateUscite.Descrizione)=? AND " & _
"tbl_EntrateUscite.Data_Operazione=? " & _
"AND tbl_EntrateUscite.Amm_ent_usc=?))ORDER BY tbl_EntrateUscite.Descrizione;"
Dim DaTrovVal As New ArrayList, TpPrm As New ArrayList
Dim SetcnPrm As Integer = WindowsApplication1.MieClassePersonale.EnumQuery.S electWithParameter

DaTrovVal.Add("COOP ADRIATICA") 'String Descrizione
TpPrm.Add(DbType.String)
DaTrovVal.Add("05/01/2008") 'DataTime Data_Operazione
TpPrm.Add(DbType.DateTime)
DaTrovVal.Add("-€ 13,14") 'Currency Amm_ent_usc
TpPrm.Add(DbType.Currency)

Rcd = New WindowsApplication1.MieClassePersonale.Record(TpPr m, DaTrovVal)

OledbCn.ConnectionString = connDB
OledbAdpt = New OleDb.OleDbDataAdapter

Fnct.Oledbcom = New OleDb.OleDbCommand(Quer, OledbCn)
dt = Fnct.LanciaQuery(Rcd, SetcnPrm, OledbCn, OledbAdpt, connDB, Quer, "Record Trovato")

'OledbAdpt = New Data.OleDb.OleDbDataAdapter(Fnct.Oledbcom)
'OledbAdpt.Fill(Fnct.ds)
'Fnct.dt = Fnct.ds.Tables(0)

Me.Hide()
Me.Close()
Dim fm As New ProvaClasse
fm.TextBox1.Text = dt.Rows(0).Item(1) 'Data_Operazione
fm.TextBox5.Text = dt.Rows(0).Item(2) 'Nome Soggetto
fm.TextBox2.Text = dt.Rows(0).Item(3) 'Descrizione
fm.TextBox4.Text = dt.Rows(0).Item(5) 'Note
fm.TextBox6.Text = FormatCurrency(dt.Rows(0).Item(4)) 'Amm_ent_usc
fm.ShowDialog()

MessageBox.Show("Id " & dt.Rows(0).Item(0))

End Sub

Public Class MieClassePersonale
'Struttura TipoQuery
Public TipoParam As Integer = Nothing
Public EQuer As New EnumQuery
Public Enum EnumQuery As Integer
Insert = 0
Update = 1
Delete = 2
SelectWithParameter = 3
SelectWithoutParameter = 4
End Enum
'Struttura Record
Public Structure Record

Public InsPrm As ArrayList
Public Valore As ArrayList

' constructor

Public Sub New( _
ByVal Tp_prm As ArrayList, ByVal Valor As ArrayList)
InsPrm = Tp_prm
Valore = Valor
End Sub 'New

Public Property InsPm() As ArrayList
Get
Return InsPrm
End Get
Set(ByVal Value As ArrayList)
InsPrm = Value
End Set
End Property

Public Property InsValue() As ArrayList
Get
Return Valore
End Get
Set(ByVal Value As ArrayList)
Valore = Value
End Set
End Property
End Structure
Public Oledbcom As New OleDb.OleDbCommand
Public Function LanciaQuery(ByVal StrutDaPassare As Record, ByVal TipoPrm As Integer, ByVal OleDbCon As OleDb.OleDbConnection, _
ByVal OleDbAdpt As OleDb.OleDbDataAdapter, _
ByVal ConnDB As String, ByVal Quer As String, _
Optional ByVal MsAfineAz As String = _
vbNullString) As Data.DataTable

Dim prm As New OleDb.OleDbParameter
Dim dt As New System.Data.DataTable
Dim ds As New System.Data.DataSet
Oledbcom = New OleDb.OleDbCommand

Select Case TipoPrm
Case 0 To 2
'Insert - 0 & Update - 1 & Delete - 2
LanciaParametri(prm, StrutDaPassare)

Oledbcom.CommandText = Quer
OleDbCon.ConnectionString = ConnDB
Oledbcom.Connection = OleDbCon
OleDbCon.Open()
Oledbcom.ExecuteNonQuery()
OleDbCon.Close()
Beep()
MessageBox.Show(MsAfineAz, MsAfineAz, MessageBoxButtons.OK, MessageBoxIcon.Information)

Return Nothing
Case Is = EnumQuery.SelectWithParameter ' Select con Parametri
OleDbCon = New OleDb.OleDbConnection
Oledbcom = New OleDb.OleDbCommand

OleDbCon.ConnectionString = ConnDB
Oledbcom.CommandText = Quer
Oledbcom.Connection = OleDbCon

OleDbCon.Open()
LanciaParametri(prm, StrutDaPassare)
OleDbAdpt.SelectCommand = Oledbcom
OleDbAdpt.Fill(ds) 'errore
dt = ds.Tables(0)
Beep()
MessageBox.Show(MsAfineAz, MsAfineAz, MessageBoxButtons.OK, MessageBoxIcon.Information)
MessageBox.Show(dt.Rows.Count)
Return dt

Case Is = EnumQuery.SelectWithoutParameter ' Select senza Parametri
OleDbCon.ConnectionString = ConnDB
Oledbcom.CommandText = Quer

Oledbcom.Connection = OleDbCon
OleDbAdpt.SelectCommand = Oledbcom

OleDbAdpt.Fill(ds)

dt = ds.Tables(0)
Return dt

Case Else
Return Nothing
End Select

End Function

Private Sub LanciaParametri(ByVal Pr As OleDb.OleDbParameter, _
ByVal StrutPassata As Record)

Pr.Direction = ParameterDirection.Input
Oledbcom = New OleDb.OleDbCommand


For i As Integer = 0 To StrutPassata.Valore.Count - 1
Select Case StrutPassata.InsPm.Item(i)
Case Is = DbType.String
Pr = New OleDb.OleDbParameter
Pr.DbType = DbType.String
Pr.Value = StrutPassata.Valore.Item(i)
Oledbcom.Parameters.Add(Pr)
Case Is = DbType.DateTime
Pr = New OleDb.OleDbParameter
Pr.DbType = DbType.DateTime
Pr.Value = StrutPassata.Valore.Item(i)
Oledbcom.Parameters.Add(Pr)
Case Is = DbType.Currency
Pr = New OleDb.OleDbParameter
Pr.DbType = DbType.Currency
Pr.Value = StrutPassata.Valore.Item(i)
Oledbcom.Parameters.Add(Pr)
Case Is = DbType.Boolean
Pr = New OleDb.OleDbParameter
Pr.DbType = DbType.Boolean
Pr.Value = StrutPassata.Valore.Item(i)
Oledbcom.Parameters.Add(Pr)
Case Is = DbType.Int32
Pr = New OleDb.OleDbParameter
Pr.DbType = DbType.Int32
Pr.Value = StrutPassata.Valore.Item(i)
Oledbcom.Parameters.Add(Pr)
Case Is = DbType.Object
Pr = New OleDb.OleDbParameter
Pr.DbType = DbType.Object
Pr.Value = StrutPassata.Valore.Item(i)
Oledbcom.Parameters.Add(Pr)
Case Else

End Select

Next

End Sub


End Class

[ans66]

Ho risolto con un approccio diverso:

LanciaParametri(prm, StrutDaPassare)
OleDbAdpt = New OleDb.OleDbDataAdapter(Oledbcom) 'parametri passati
OleDbAdpt.SelectCommand.CommandText = Quer
OleDbAdpt.SelectCommand.Connection = OleDbCon
OleDbCon.Open()
OleDbAdpt.SelectCommand.ExecuteNonQuery()
OleDbAdpt.Fill(ds)
dt = ds.Tables(0)
Beep()

cosi` funziona

Grazie