Can anyone tell me why this does not work?

  • Thread starter Thread starter bill
  • Start date Start date
B

bill

Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;" &
"data source=c:\_Archive\Documentation - Projects\Hardware Tracking -
2008\IT_Assets.mdb")

Dim dataAdapter As OleDb.OleDbDataAdapter

Dim assCmd As New OleDb.OleDbCommand("SELECT * from tblAssets where
asset_tag = @fn", Con)

assCmd.Parameters.Add(New OleDb.OleDbParameter("@fn",
OleDb.OleDbType.VarChar, 30)).Value = Me.cboAsset.Text

Dim dataAdapter As New OleDb.OleDbDataAdapter(assCmd, Con)

Dim DT As New DataTable

DataAdapter.Fill(DT)

DataAdapter.Dispose()

'I'm hoping that this recordset will populate the grid but nothing happens?

Me.DataGridView1.DataSource = DT
 
bill said:
Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;" &
"data source=c:\_Archive\Documentation - Projects\Hardware Tracking -
2008\IT_Assets.mdb")

Dim dataAdapter As OleDb.OleDbDataAdapter

Dim assCmd As New OleDb.OleDbCommand("SELECT * from tblAssets where
asset_tag = @fn", Con)

assCmd.Parameters.Add(New OleDb.OleDbParameter("@fn",
OleDb.OleDbType.VarChar, 30)).Value = Me.cboAsset.Text

Dim dataAdapter As New OleDb.OleDbDataAdapter(assCmd, Con)

Dim DT As New DataTable

DataAdapter.Fill(DT)

DataAdapter.Dispose()

'I'm hoping that this recordset will populate the grid but nothing
happens?

Me.DataGridView1.DataSource = DT
Click to expand...

No error message at all?
One thing I see is the use of single "\" in the connection string, which I
think should either be "\\" or "/".
 
One thing I see is the use of single "\" in the connection string, which I
think should either be "\\" or "/".
Click to expand...

In VB you do not have to escape slashes. The ConnectionString is fine as
long as the provider and data source are correctly defined.

Best Regards,
Stanimir Stoyanov | www.stoyanoff.info
 
The OleDbCommand, in general but especially for JET, does not support named
parameters in the CommandText.

Instead parameters are positional and designated by ?.

So your CommandText then becomes:

"SELECT * from tblAssets where asset_tag=?"

The positional parameter is satisfied by the OleDbParameter in the relative
position in the Parameters collection so it is CRITICAL that they are added
in the correct sequence.

When you defined an OleDbCommand, you must give it a name, even though it is
is ignored.

So your parameter collection, is populated thus:

assCmd.Parameters.Add("asset_tag", OleDbType.VarChar).Value =
cboAsset.Text

Note that I have demonstrated using 'asset-tag' so that it becomes
self-documenting to a degree.

Also note that the Add method has a number of overloads that make defining
OleDBParameters easier.

In addition, you do not HAVE to define the width of the column in question.
If you omit the width then it will be inferred at execution-time.

IIRC there is also a method AddWithValue so that you can add an
OleDbParameter thus:

assCmd.Parameters.AddWithValue("asset_tag", cboAsset.Text)

In this case both the type and the with of the column in question are
inferred at execution-time.
 
I do get an error message here:
Dim dataAdapter As New OleDb.OleDbDataAdapter(assCmd, Con)

the dataAdapeter underlines and says "dataAdapter already declared in local
block" but I don't see what the problem is there?
Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;" &
"data source=c:\_Archive\Documentation - Projects\Hardware Tracking -
2008\IT_Assets.mdb")

Dim dataAdapter As OleDb.OleDbDataAdapter

Dim assCmd As New OleDb.OleDbCommand("SELECT * from tblAssets where
asset_tag=?")

assCmd.Parameters.Add("asset_tag", OleDbType.VarChar).Value = cboAsset.Text

Dim dataAdapter As New OleDb.OleDbDataAdapter(assCmd, Con)

Dim DT As New DataTable

DataAdapter.Fill(DT)

DataAdapter.Dispose()

Me.DataGridView1.DataSource = DT
 
Thank you! You've pretty much sorted me out. I'm still getting the error
message that the dataAdapter is already declared but I don't know of any
other way to get what I'm after for the oledb data adapter?
 
It still errors out there. I'm stumped!

Jack Jackson said:
You declared dataAdapter twice.

Once on this line:

and again a few lines later on this line:

Just remove the first line.
Click to expand...
 
Are you saying that you still get the error "dataAdapter already
declared in local block"? If so, then you must still have more than
one definition of dataAdapter in the routine. If you can't figure it
out post the entire routine.
 
You've got it declared twice.


bill said:
Thank you! You've pretty much sorted me out. I'm still getting the error
message that the dataAdapter is already declared but I don't know of any
other way to get what I'm after for the oledb data adapter?
Click to expand...
 
Back
Top