Queries to an Access db from vs2005

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

Hello,
I just have a form in which there's a textbox for queries. I need to use the
SQL statements for an Access db, like this, which works fine in a SQL db:
SELECT * FROM table WHERE name LIKE @name + '%'
I just need to query the db to find names that start with letters I digit in
the textbox.
This query comes from dataset designer. If I use it in my Access db I get a
syntax error from framework. Which is the Access equivalent?
Thanks.
Max
 
If I do that:
SELECT * FROM table WHERE name LIKE name + '*'

I get an error when starting compiling application:
"Too many arguments for "Public Overridable Overloads Function
FillByName(dataTable As demoDataSet.myTableDataTable) As Integer".
It reminds in a VB line:
"Me.MyTableTableAdapter.FillByName(Me.demoDataSet.myTable,
NameToolStripTextBox.Text)",
in which the error is underlined on "NameToolStripTextBox.Text". This is the
textbox where I digit the items to search for in my db.

Help would be appreciated.
Thanks.
 
Anybody can help?

Thanks.
Max

max said:
If I do that:
SELECT * FROM table WHERE name LIKE name + '*'

I get an error when starting compiling application:
"Too many arguments for "Public Overridable Overloads Function
FillByName(dataTable As demoDataSet.myTableDataTable) As Integer".
It reminds in a VB line:
"Me.MyTableTableAdapter.FillByName(Me.demoDataSet.myTable,
NameToolStripTextBox.Text)",
in which the error is underlined on "NameToolStripTextBox.Text". This is
the textbox where I digit the items to search for in my db.

Help would be appreciated.
Thanks.
Click to expand...
 
Can you post your code, or a small subset of the code
showing the query stuff?

Robin S.
------------------
 
Hi Robin,
I have an access db; in my form I have a textbox; on it, click on data menu,
added a new query, gave it a name and put it in query text:
SELECT * FROM table WHERE name LIKE name + '*' (I'm not really sure it's the
right syntax for querying an access db).
Closing the form, it adds a search button and a FillByNameToolStrip object.
My target is to write a partial text in the textbox, clilck to the button
just created, and get all the names that start for my partial text, ie: I
type just "bro" to get all the names starting from "bro" (brown, browns,
brother etc.). It sounds very simple. Using the SELECT instruction above,
and start compiling app, I get this:

"Too many arguments for "Public Overridable Overloads Function
FillByName(dataTable As demoDataSet.myTableDataTable) As Integer"

Here is the autogenerated vb code about the fill statement:

Private Sub FillByNameToolStripButton_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
FillByNameToolStripButton.Click
Try
Me.MyTableAdapter.FillByName(Me.demoDataSet.MyTable,
NameToolStripTextBox.Text)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub

I just need to get the result as indicated. I welcome any other methos, if
it exists. I'm trying to use designer for most of the job in vb programming.
Probably it's not the best method...

Thanks.
 
¤
¤ Hi Robin,
¤ I have an access db; in my form I have a textbox; on it, click on data menu,
¤ added a new query, gave it a name and put it in query text:
¤ SELECT * FROM table WHERE name LIKE name + '*' (I'm not really sure it's the
¤ right syntax for querying an access db).
¤ Closing the form, it adds a search button and a FillByNameToolStrip object.
¤ My target is to write a partial text in the textbox, clilck to the button
¤ just created, and get all the names that start for my partial text, ie: I
¤ type just "bro" to get all the names starting from "bro" (brown, browns,
¤ brother etc.). It sounds very simple. Using the SELECT instruction above,
¤ and start compiling app, I get this:
¤
¤ "Too many arguments for "Public Overridable Overloads Function
¤ FillByName(dataTable As demoDataSet.myTableDataTable) As Integer"
¤
¤ Here is the autogenerated vb code about the fill statement:
¤
¤ Private Sub FillByNameToolStripButton_Click(ByVal sender As
¤ System.Object, ByVal e As System.EventArgs) Handles
¤ FillByNameToolStripButton.Click
¤ Try
¤ Me.MyTableAdapter.FillByName(Me.demoDataSet.MyTable,
¤ NameToolStripTextBox.Text)
¤ Catch ex As System.Exception
¤ System.Windows.Forms.MessageBox.Show(ex.Message)
¤ End Try
¤ End Sub
¤
¤ I just need to get the result as indicated. I welcome any other methos, if
¤ it exists. I'm trying to use designer for most of the job in vb programming.
¤ Probably it's not the best method...

One of the first problems you will encounter is a reserved word issue. The column "name" is a Jet
reserved word so you will either need to rename the column or enclose it in brackets. As was
mentioned in a previous post, the wild card character for ADO is a percent symbol and not an
asterisk.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
¤
¤ Name is just 'something'. In my vb code it's 'Nome'.
¤

Unless you post the exact statement, including the actual column names you are using, it will be
very difficult to troubleshoot the problem.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
I repost this, with the actual column names:
I have an Access2003 db; in my form I have a textbox; on it, click on data
menu,
added a new query, gave it a name and put it in query text:
SELECT * FROM tblPatients WHERE nome LIKE nome + '*' (I'm not really sure
it's the
right syntax for querying an access db).
Closing the form, it adds a search button and a FillByNomeToolStrip object.
My target is to write a partial text in the textbox, clilck to the button
just created, and get all the names that start for my partial text, ie: I
type just "bro" to get all the names starting from "bro" (brown, browns,
brother etc.). It sounds very simple. Using the SELECT instruction above,
and start compiling app, I get this:

"Too many arguments for "Public Overridable Overloads Function
FillByNome(dataTable As demoDataSet.tblPatientsDataTable) As Integer"

Here is the autogenerated vb code about the fill statement:

Private Sub FillByNomeToolStripButton_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
FillByNomeToolStripButton.Click
Try
Me.tblPatientsAdapter.FillByNome(Me.demoDataSet.tblPatients,
NomeToolStripTextBox.Text)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub

I just need to get the result as indicated. I welcome any other methos, if
it exists. I'm trying to use designer for most of the job in vb programming.
Probably it's not the best method...

Thanks.
 
Hi,

max said:
Hi Robin,
I have an access db; in my form I have a textbox; on it, click on data
menu, added a new query, gave it a name and put it in query text:
SELECT * FROM table WHERE name LIKE name + '*' (I'm not really sure it's
the right syntax for querying an access db).
Click to expand...

Try:

SELECT * FROM table WHERE [name] LIKE ? + '%'

JET uses unnamed parameters (?) and % as wildcards.

HTH,
Greetings
 
Although I've read this at least a dozen times, I'm still confused.

The form you're talking about is in VS2005, not in Access, right? Access is
just your back-end data source.

So you have a Windows Form (?) with a textbox on it where the user is going
to put in search criteria, like "bro" for "bro*" (e.g. Brown).

I'm not sure how you added the query you're talking about. This doesn't
sound right for Visual Studio. You clicked on the Data Menu -- what did you
add? A new Data Source that is a query? Is this like a stored procedure?
It's a query definition in Access?

Because it looks like you're trying to do some kind of stored procedure, in
which case it should accept a parameter, and you should pass the parameter.

Please give us more specifics.

Robin S.
---------------------------------------------------------------
 
Hello Max,

I think your problem is in the sintax: you've got a field called 'nome' and
in the WHERE clause you use it again. If you want to get all names starting
with 'Al', you have to state something like this:

SELECT * FROM tlbPatients WHERE nome LIKE 'Al*'

so if your second 'nome' is a parameter that gets its value from any other
place (VS, for example) I think you should rename it to something different
than the field and change your sintaxis like this:

SELECT * FROM tlbPatients WHERE nome LIKE param_nome + '*'

Thanks what I think without having more clues.

Regards,
Carlos M Perez
http://www.picacodigos.com
mailto:[email protected]

M> I repost this, with the actual column names:
M> I have an Access2003 db; in my form I have a textbox; on it, click on
M> data
M> menu,
M> added a new query, gave it a name and put it in query text:
M> SELECT * FROM tblPatients WHERE nome LIKE nome + '*' (I'm not really
M> sure
M> it's the
M> right syntax for querying an access db).
M> Closing the form, it adds a search button and a FillByNomeToolStrip
M> object.
M> My target is to write a partial text in the textbox, clilck to the
M> button
M> just created, and get all the names that start for my partial text,
M> ie: I
M> type just "bro" to get all the names starting from "bro" (brown,
M> browns,
M> brother etc.). It sounds very simple. Using the SELECT instruction
M> above,
M> and start compiling app, I get this:
M> "Too many arguments for "Public Overridable Overloads Function
M> FillByNome(dataTable As demoDataSet.tblPatientsDataTable) As Integer"
M>
M> Here is the autogenerated vb code about the fill statement:
M>
M> Private Sub FillByNomeToolStripButton_Click(ByVal sender As
M> System.Object, ByVal e As System.EventArgs) Handles
M> FillByNomeToolStripButton.Click
M> Try
M>
M> Me.tblPatientsAdapter.FillByNome(Me.demoDataSet.tblPatients,
M> NomeToolStripTextBox.Text)
M> Catch ex As System.Exception
M> System.Windows.Forms.MessageBox.Show(ex.Message)
M> End Try
M> End Sub
M> I just need to get the result as indicated. I welcome any other
M> methos, if it exists. I'm trying to use designer for most of the job
M> in vb programming. Probably it's not the best method...
M>
M> Thanks.
 
Hi Robin,

RobinS said:
Although I've read this at least a dozen times, I'm still confused.

The form you're talking about is in VS2005, not in Access, right? Access
is just your back-end data source.
Click to expand...

That's right.
So you have a Windows Form (?) with a textbox on it where the user is
going to put in search criteria, like "bro" for "bro*" (e.g. Brown).
Click to expand...

Yes, it's a Windows Form, I dragged a textbox on it.
I'm not sure how you added the query you're talking about. This doesn't
sound right for Visual Studio. You clicked on the Data Menu -- what did
you add? A new Data Source that is a query? Is this like a stored
procedure? It's a query definition in Access?
Because it looks like you're trying to do some kind of stored procedure,
in which case it should accept a parameter, and you should pass the
parameter.
Click to expand...

I'm working with VS2005. I had put a textbox bounded from datasource and,
clicking on it, the Data menu was showing a "Add query" item. But it doesn't
work and it gave me the
"Too many arguments for Public Overridable Overloads Function
FillByNome(dataTable As demoDataSet.tblPatientsDataTable) As Integer". So
forget it.
Then I tried a second option: right click to TableAdapter icon, Add query,
and using the query wizard, I wrote this:
SELECT * FROM tblPatients WHERE Nome LIKE Nome + '*'
Next, I gave a name to the query, "FillByNome", and a name to the DataTable,
"GetDataBy".
It added a ToolStripButton on my form called "FillByNomeToolStripButton".
Then, on the same toolstrip, using the "Add items" button close to the
button just created, I added a textbox, called "ToolStripTextBox"; then I
run the application, I see all my records in a DataGridView contol in my
Form, and when I fill the textbox with "bro", and clicked on the button, the
entire collection of records showing names disappears. So the query doesn't
work.
Then, I changed the query by right-clicking "Configure" on the query name in
the tblPatients in DataSet designer, and changed as it:
SELECT * FROM tblPatients WHERE (Nome LIKE [@WriteNome] + '%')
The result is that I get an error message like this (I'm sorry but I
translate the message to english, so it probably it doesn't appear exactly
as is):
"No needed value specified for some parameters". I also tried this:
SELECT * FROM tblPatients WHERE (Nome LIKE [@WriteNome] + '*'), getting the
same result. If I remove the square brackets I get an SQL syntax error. I
understand that I must pass a parameter to my query, but I can't understand
how; I think it's an Access issue, but at this point I'm not really sure
about nothing.

Thanks for your patience.
Max
 
Hi,

Bart Mermuys said:
Hi,

max said:
Hi Robin,
I have an access db; in my form I have a textbox; on it, click on data
menu, added a new query, gave it a name and put it in query text:
SELECT * FROM table WHERE name LIKE name + '*' (I'm not really sure it's
the right syntax for querying an access db).
Click to expand...

Try:

SELECT * FROM table WHERE [name] LIKE ? + '%'

JET uses unnamed parameters (?) and % as wildcards.

HTH,
Greetings
Click to expand...

It works fine!
I just add a query from the TableAdapter Icon using the query wizard, paste
the code you wrote, then designer adds a button and a textbox, and I wrote
on it just some digit to get the results I was expecting!
It was an Access issue. I didn't expect it was so hard to work with Access
db in VS2005, I find only sql samples in internet...
Do you know a place where I can learn more about SQL statements for Access
db in a VS2005 environment?
Thanks a lot Bart!!!

Max.
 
Robin,
the question has been solved.
This is the right code:
SELECT * FROM table WHERE [name] LIKE ? + '%'
I got it from Bart Mermuys, as you can read some post below.

Thanks a lot for your help.
Max.

max said:
Hi Robin,

RobinS said:
Although I've read this at least a dozen times, I'm still confused.

The form you're talking about is in VS2005, not in Access, right? Access
is just your back-end data source.
Click to expand...

That's right.
So you have a Windows Form (?) with a textbox on it where the user is
going to put in search criteria, like "bro" for "bro*" (e.g. Brown).
Click to expand...

Yes, it's a Windows Form, I dragged a textbox on it.
I'm not sure how you added the query you're talking about. This doesn't
sound right for Visual Studio. You clicked on the Data Menu -- what did
you add? A new Data Source that is a query? Is this like a stored
procedure? It's a query definition in Access?
Because it looks like you're trying to do some kind of stored procedure,
in which case it should accept a parameter, and you should pass the
parameter.
Click to expand...

I'm working with VS2005. I had put a textbox bounded from datasource and,
clicking on it, the Data menu was showing a "Add query" item. But it
doesn't work and it gave me the
"Too many arguments for Public Overridable Overloads Function
FillByNome(dataTable As demoDataSet.tblPatientsDataTable) As Integer". So
forget it.
Then I tried a second option: right click to TableAdapter icon, Add query,
and using the query wizard, I wrote this:
SELECT * FROM tblPatients WHERE Nome LIKE Nome + '*'
Next, I gave a name to the query, "FillByNome", and a name to the
DataTable, "GetDataBy".
It added a ToolStripButton on my form called "FillByNomeToolStripButton".
Then, on the same toolstrip, using the "Add items" button close to the
button just created, I added a textbox, called "ToolStripTextBox"; then I
run the application, I see all my records in a DataGridView contol in my
Form, and when I fill the textbox with "bro", and clicked on the button,
the entire collection of records showing names disappears. So the query
doesn't work.
Then, I changed the query by right-clicking "Configure" on the query name
in the tblPatients in DataSet designer, and changed as it:
SELECT * FROM tblPatients WHERE (Nome LIKE [@WriteNome] + '%')
The result is that I get an error message like this (I'm sorry but I
translate the message to english, so it probably it doesn't appear exactly
as is):
"No needed value specified for some parameters". I also tried this:
SELECT * FROM tblPatients WHERE (Nome LIKE [@WriteNome] + '*'), getting
the same result. If I remove the square brackets I get an SQL syntax
error. I understand that I must pass a parameter to my query, but I can't
understand how; I think it's an Access issue, but at this point I'm not
really sure about nothing.

Thanks for your patience.
Max
Please give us more specifics.

Robin S.
Click to expand...
Click to expand...
 
¤ > Hi Robin,
¤ > I have an access db; in my form I have a textbox; on it, click on data
¤ > menu, added a new query, gave it a name and put it in query text:
¤ > SELECT * FROM table WHERE name LIKE name + '*' (I'm not really sure it's
¤ > the right syntax for querying an access db).
¤
¤ Try:
¤
¤ SELECT * FROM table WHERE [name] LIKE ? + '%'
¤
¤ JET uses unnamed parameters (?) and % as wildcards.
¤

Looks like the third time is the charm. ;-)


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Brilliant! I'm so glad.

Robin S.
------------------------------
max said:
Robin,
the question has been solved.
This is the right code:
SELECT * FROM table WHERE [name] LIKE ? + '%'
I got it from Bart Mermuys, as you can read some post below.

Thanks a lot for your help.
Max.

max said:
Hi Robin,

RobinS said:
Although I've read this at least a dozen times, I'm still confused.

The form you're talking about is in VS2005, not in Access, right?
Access is just your back-end data source.
Click to expand...

That's right.
So you have a Windows Form (?) with a textbox on it where the user is
going to put in search criteria, like "bro" for "bro*" (e.g. Brown).
Click to expand...

Yes, it's a Windows Form, I dragged a textbox on it.
I'm not sure how you added the query you're talking about. This doesn't
sound right for Visual Studio. You clicked on the Data Menu -- what did
you add? A new Data Source that is a query? Is this like a stored
procedure? It's a query definition in Access?
Because it looks like you're trying to do some kind of stored
procedure, in which case it should accept a parameter, and you should
pass the parameter.
Click to expand...

I'm working with VS2005. I had put a textbox bounded from datasource
and, clicking on it, the Data menu was showing a "Add query" item. But
it doesn't work and it gave me the
"Too many arguments for Public Overridable Overloads Function
FillByNome(dataTable As demoDataSet.tblPatientsDataTable) As Integer".
So forget it.
Then I tried a second option: right click to TableAdapter icon, Add
query, and using the query wizard, I wrote this:
SELECT * FROM tblPatients WHERE Nome LIKE Nome + '*'
Next, I gave a name to the query, "FillByNome", and a name to the
DataTable, "GetDataBy".
It added a ToolStripButton on my form called
"FillByNomeToolStripButton". Then, on the same toolstrip, using the "Add
items" button close to the button just created, I added a textbox,
called "ToolStripTextBox"; then I run the application, I see all my
records in a DataGridView contol in my Form, and when I fill the textbox
with "bro", and clicked on the button, the entire collection of records
showing names disappears. So the query doesn't work.
Then, I changed the query by right-clicking "Configure" on the query
name in the tblPatients in DataSet designer, and changed as it:
SELECT * FROM tblPatients WHERE (Nome LIKE [@WriteNome] + '%')
The result is that I get an error message like this (I'm sorry but I
translate the message to english, so it probably it doesn't appear
exactly as is):
"No needed value specified for some parameters". I also tried this:
SELECT * FROM tblPatients WHERE (Nome LIKE [@WriteNome] + '*'), getting
the same result. If I remove the square brackets I get an SQL syntax
error. I understand that I must pass a parameter to my query, but I
can't understand how; I think it's an Access issue, but at this point
I'm not really sure about nothing.

Thanks for your patience.
Max
Please give us more specifics.

Robin S.
---------------------------------------------------------------


I repost this, with the actual column names:
I have an Access2003 db; in my form I have a textbox; on it, click on
data menu,
added a new query, gave it a name and put it in query text:
SELECT * FROM tblPatients WHERE nome LIKE nome + '*' (I'm not really
sure it's the
right syntax for querying an access db).
Closing the form, it adds a search button and a FillByNomeToolStrip
object.
My target is to write a partial text in the textbox, clilck to the
button
just created, and get all the names that start for my partial text,
ie: I
type just "bro" to get all the names starting from "bro" (brown,
browns,
brother etc.). It sounds very simple. Using the SELECT instruction
above,
and start compiling app, I get this:

"Too many arguments for "Public Overridable Overloads Function
FillByNome(dataTable As demoDataSet.tblPatientsDataTable) As Integer"

Here is the autogenerated vb code about the fill statement:

Private Sub FillByNomeToolStripButton_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
FillByNomeToolStripButton.Click
Try

Me.tblPatientsAdapter.FillByNome(Me.demoDataSet.tblPatients,
NomeToolStripTextBox.Text)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub

I just need to get the result as indicated. I welcome any other
methos, if
it exists. I'm trying to use designer for most of the job in vb
programming.
Probably it's not the best method...

Thanks.


"Paul Clement" <[email protected]> ha scritto
nel messaggio
¤
¤ Name is just 'something'. In my vb code it's 'Nome'.
¤

Unless you post the exact statement, including the actual column
names you are using, it will be
very difficult to troubleshoot the problem.


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top