469,963 Members | 1,632 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,963 developers. It's quick & easy.

Can anyone tell me why this does not work?

Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oled b.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
Jul 21 '08 #1
9 1092
"bill" <bi**@bottlegarden.comwrote in message
news:u6**************@TK2MSFTNGP05.phx.gbl...
Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oled b.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

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 "/".

Jul 21 '08 #2
One thing I see is the use of single "\" in the connection string, which I
think should either be "\\" or "/".
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

"PvdG42" <pv**@toadstool.eduwrote in message
news:u7**************@TK2MSFTNGP04.phx.gbl...
"bill" <bi**@bottlegarden.comwrote in message
news:u6**************@TK2MSFTNGP05.phx.gbl...
>Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oled b.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

Jul 21 '08 #3
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.
"bill" <bi**@bottlegarden.comwrote in message
news:u6**************@TK2MSFTNGP05.phx.gbl...
Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oled b.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

Jul 21 '08 #4
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.oled b.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

"PvdG42" <pv**@toadstool.eduwrote in message
news:u7**************@TK2MSFTNGP04.phx.gbl...
"bill" <bi**@bottlegarden.comwrote in message
news:u6**************@TK2MSFTNGP05.phx.gbl...
>Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oled b.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


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 "/".

Jul 22 '08 #5
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?

"Stephany Young" <noone@localhostwrote in message
news:OK**************@TK2MSFTNGP02.phx.gbl...
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.
"bill" <bi**@bottlegarden.comwrote in message
news:u6**************@TK2MSFTNGP05.phx.gbl...
>Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oled b.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


Jul 22 '08 #6
You declared dataAdapter twice.

Once on this line:
>Dim dataAdapter As OleDb.OleDbDataAdapter
and again a few lines later on this line:
>Dim dataAdapter As New OleDb.OleDbDataAdapter(assCmd, Con)
Just remove the first line.

On Mon, 21 Jul 2008 18:45:58 -0600, "bill" <bi**@bottlegarden.com>
wrote:
>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.oled b.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

"PvdG42" <pv**@toadstool.eduwrote in message
news:u7**************@TK2MSFTNGP04.phx.gbl...
>"bill" <bi**@bottlegarden.comwrote in message
news:u6**************@TK2MSFTNGP05.phx.gbl...
>>Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oled b.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


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 "/".
Jul 22 '08 #7
It still errors out there. I'm stumped!

"Jack Jackson" <jj******@cinnovations.netwrote in message
news:42********************************@4ax.com...
You declared dataAdapter twice.

Once on this line:
>>Dim dataAdapter As OleDb.OleDbDataAdapter

and again a few lines later on this line:
>>Dim dataAdapter As New OleDb.OleDbDataAdapter(assCmd, Con)

Just remove the first line.

On Mon, 21 Jul 2008 18:45:58 -0600, "bill" <bi**@bottlegarden.com>
wrote:
>>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.oled b.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

"PvdG42" <pv**@toadstool.eduwrote in message
news:u7**************@TK2MSFTNGP04.phx.gbl...
>>"bill" <bi**@bottlegarden.comwrote in message
news:u6**************@TK2MSFTNGP05.phx.gbl...
Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oled b.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

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 "/".

Jul 22 '08 #8
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.

On Mon, 21 Jul 2008 22:54:37 -0600, "bill" <bi**@bottlegarden.com>
wrote:
>It still errors out there. I'm stumped!

"Jack Jackson" <jj******@cinnovations.netwrote in message
news:42********************************@4ax.com.. .
>You declared dataAdapter twice.

Once on this line:
>>>Dim dataAdapter As OleDb.OleDbDataAdapter

and again a few lines later on this line:
>>>Dim dataAdapter As New OleDb.OleDbDataAdapter(assCmd, Con)

Just remove the first line.

On Mon, 21 Jul 2008 18:45:58 -0600, "bill" <bi**@bottlegarden.com>
wrote:
>>>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.oled b.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

"PvdG42" <pv**@toadstool.eduwrote in message
news:u7**************@TK2MSFTNGP04.phx.gbl...
"bill" <bi**@bottlegarden.comwrote in message
news:u6**************@TK2MSFTNGP05.phx.gbl...
Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oled b.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
>
>

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 "/".

Jul 22 '08 #9
You've got it declared twice.
"bill" <bi**@bottlegarden.comwrote in message
news:Oq**************@TK2MSFTNGP06.phx.gbl...
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?

"Stephany Young" <noone@localhostwrote in message
news:OK**************@TK2MSFTNGP02.phx.gbl...
>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.
"bill" <bi**@bottlegarden.comwrote in message
news:u6**************@TK2MSFTNGP05.phx.gbl...
>>Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oled b.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


Jul 23 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Google Mike | last post: by
5 posts views Thread by Dhruv | last post: by
33 posts views Thread by O-('' Q) | last post: by
13 posts views Thread by nigel.t | last post: by
4 posts views Thread by Bruno Alexandre | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.