Query in VB will not work..What am I doing wrong?

[chris23892 via AccessMonster.com]

So..I have the following query in access:

SELECT Check_In.ID, Check_In.[Product Name], Check_In.[S/N], Check_In.[Part #]
, Check_In.[Date Receive], Check_In.[UPS Tracking#], Check_In.[DTAC Case],
Check_In.[Dealer Acct #], Check_In.Dealer, Check_In.[Claim# 1], Check_In.
[Claim# 2], Check_In.[Entered to FPRT (Y/N)], Check_In.Comments, Check_In.
[Date Entered to FPRT], Check_In.[Geographical Location (Ship From)]
FROM Check_In
WHERE ((([Check_in].[S/N])=[Enter Serial Number]));

I'm trying to get this to work in my VB project. I have all my database
linked, data grid view in my form and tested the connection..all work fine.
WITHOUT building a query in VB, I can debug and all the information from the
is there.

When I try to create a query based off of the data in the data grid, this is
where I'm running into problems.

Why would the SQL that works in my access badatbase not work in my vb project?

 

[Dirk Goldgar]

So..I have the following query in access:

SELECT Check_In.ID, Check_In.[Product Name], Check_In.[S/N],
Check_In.[Part #]
, Check_In.[Date Receive], Check_In.[UPS Tracking#], Check_In.[DTAC Case],
Check_In.[Dealer Acct #], Check_In.Dealer, Check_In.[Claim# 1], Check_In.
[Claim# 2], Check_In.[Entered to FPRT (Y/N)], Check_In.Comments, Check_In.
[Date Entered to FPRT], Check_In.[Geographical Location (Ship From)]
FROM Check_In
WHERE ((([Check_in].[S/N])=[Enter Serial Number]));

I'm trying to get this to work in my VB project. I have all my database
linked, data grid view in my form and tested the connection..all work
fine.
WITHOUT building a query in VB, I can debug and all the information from
the
is there.

When I try to create a query based off of the data in the data grid, this
is
where I'm running into problems.

Why would the SQL that works in my access badatbase not work in my vb
project?

How are you resolving the [Enter Serial Number] parameter? In Access, if
you open a query with such a parameter, you'll be prompted for the parameter
value. In VB, you'd need to provide your own means of filling in the
parameter value. If you've done so, what exactly have you done?

 

[chris23892 via AccessMonster.com]

I guess I need to resolve it in VB. I tried that first with a text box in my
vb project..could not get that to work.

I wanted to scan my S/N then have the query run and display the contents (if
it existed)

I then remebered how I was doing this in my access query.

Any pointers on how to point to this in my form in VB?
Awsome, thanks for the help. Great stuff. Makes my mind hurt with all the
possiabilities if I can get this to work in my project

So..I have the following query in access:

[quoted text clipped - 20 lines]

Why would the SQL that works in my access badatbase not work in my vb
project?

How are you resolving the [Enter Serial Number] parameter? In Access, if
you open a query with such a parameter, you'll be prompted for the parameter
value. In VB, you'd need to provide your own means of filling in the
parameter value. If you've done so, what exactly have you done?

 

[Dirk Goldgar]

I guess I need to resolve it in VB. I tried that first with a text box in
my
vb project..could not get that to work.

I wanted to scan my S/N then have the query run and display the contents
(if
it existed)

I then remebered how I was doing this in my access query.

Any pointers on how to point to this in my form in VB?

Is this VB "Classic", or (as I think more likely) VB.Net? Please describe
your setup in more detail. What sort of data grid control are you using,
and what are its relevant properties? Are you building the SQL string in
code, dynamically, or are you assigning it to a datasource property of some
sort at design time? If you have relevant code, it might help to post it.

One way or another, you're going to have to either build the SQL string on
the fly, incorporating the serial number into the string you build, or else
assign the serial number to some sort of parameter and requery your datagrid
control.

 

[chris23892 via AccessMonster.com]

I'm using visual studio 2008

New at this, so thank you for your patiance. I just this installed at work
two days ago. I was proud I got yhis far in this short of time

I just created the basic windows application (blank form then comes up),
created my data source (to my database, tested connection, can view all the
data ok though the explorer in vb), then pulled a data grid from the tool box
(assigned the data source from the intial connection I made at the beginning
of the vb project.


Just doing that, I can run debug and see the entire contents of my data: it's
indeed all there nice and neat.

I can go into the data grid proporties, create query and filter for a
specific S/N. That works just fine.

I guess (just from good old fashion logic) I have to somehow create a text
box on my form (where I want to scan my S/N into) then somehow in my query
filter criteia, tell it to filter by what is in that text box.

Is that logic correct?

I guess I need to resolve it in VB. I tried that first with a text box in
my

[quoted text clipped - 7 lines]

Any pointers on how to point to this in my form in VB?

Is this VB "Classic", or (as I think more likely) VB.Net? Please describe
your setup in more detail. What sort of data grid control are you using,
and what are its relevant properties? Are you building the SQL string in
code, dynamically, or are you assigning it to a datasource property of some
sort at design time? If you have relevant code, it might help to post it.

One way or another, you're going to have to either build the SQL string on
the fly, incorporating the serial number into the string you build, or else
assign the serial number to some sort of parameter and requery your datagrid
control.

 

[chris23892 via AccessMonster.com]

Man, would be sweet if i could show a screen shot.

Thinking about this, really, this should not be that hard. I just don't know
how to tell vb to take what is in my text box and filter my datagrid query by
what was entered in that box.

I'm using visual studio 2008

New at this, so thank you for your patiance. I just this installed at work
two days ago. I was proud I got yhis far in this short of time

I just created the basic windows application (blank form then comes up),
created my data source (to my database, tested connection, can view all the
data ok though the explorer in vb), then pulled a data grid from the tool box
(assigned the data source from the intial connection I made at the beginning
of the vb project.

Just doing that, I can run debug and see the entire contents of my data: it's
indeed all there nice and neat.

I can go into the data grid proporties, create query and filter for a
specific S/N. That works just fine.

I guess (just from good old fashion logic) I have to somehow create a text
box on my form (where I want to scan my S/N into) then somehow in my query
filter criteia, tell it to filter by what is in that text box.

Is that logic correct?
[quoted text clipped - 12 lines]

assign the serial number to some sort of parameter and requery your datagrid
control.

 

[AccessVandal via AccessMonster.com]

I would suggest you avoid using Access reserved charactors like "#" "/".
Sanitized these column names.

http://support.microsoft.com/kb/826763

http://allenbrowne.com/AppIssueBadWord.html

So..I have the following query in access:

SELECT Check_In.ID, Check_In.[Product Name], Check_In.[S/N], Check_In.[Part #]
, Check_In.[Date Receive], Check_In.[UPS Tracking#], Check_In.[DTAC Case],
Check_In.[Dealer Acct #], Check_In.Dealer, Check_In.[Claim# 1], Check_In.
[Claim# 2], Check_In.[Entered to FPRT (Y/N)], Check_In.Comments, Check_In.
[Date Entered to FPRT], Check_In.[Geographical Location (Ship From)]
FROM Check_In
WHERE ((([Check_in].[S/N])=[Enter Serial Number]));

I'm trying to get this to work in my VB project. I have all my database
linked, data grid view in my form and tested the connection..all work fine.
WITHOUT building a query in VB, I can debug and all the information from the
is there.

When I try to create a query based off of the data in the data grid, this is
where I'm running into problems.

Why would the SQL that works in my access badatbase not work in my vb project?

 

[Stuart McCall]

I would suggest you avoid using Access reserved charactors like "#" "/".
Sanitized these column names.

http://support.microsoft.com/kb/826763

http://allenbrowne.com/AppIssueBadWord.html

So..I have the following query in access:

SELECT Check_In.ID, Check_In.[Product Name], Check_In.[S/N],
Check_In.[Part #]
, Check_In.[Date Receive], Check_In.[UPS Tracking#], Check_In.[DTAC Case],
Check_In.[Dealer Acct #], Check_In.Dealer, Check_In.[Claim# 1], Check_In.
[Claim# 2], Check_In.[Entered to FPRT (Y/N)], Check_In.Comments, Check_In.
[Date Entered to FPRT], Check_In.[Geographical Location (Ship From)]
FROM Check_In
WHERE ((([Check_in].[S/N])=[Enter Serial Number]));

I'm trying to get this to work in my VB project. I have all my database
linked, data grid view in my form and tested the connection..all work
fine.
WITHOUT building a query in VB, I can debug and all the information from
the
is there.

When I try to create a query based off of the data in the data grid, this
is
where I'm running into problems.

Why would the SQL that works in my access badatbase not work in my vb
project?

While I echo AccessVandal's advice re field names, I suspect the problem is
this part:

[Enter Serial Number]

In the Access environment, because it doesn't refer to a valid field name in
the Check_In table, Access will automatically treat it as a parameter and
prompt the user for the value. This won't happen in VB. You'll need to feed
the value to the sql string another way. Post back if you need help with
that.

 

[chris23892 via AccessMonster.com]

Yeah, stuart...that is where i am at right now:
I need to feed the filter criteria in the query with my value that I have in
mt text box in my form.

Simply put, i want to scan my s/n from my bar code into a text box, click a
button and have that row come up in my datagrid via my query that i can't get
to work right now.

unless there is another idea on how i can get this query to filter from a
text box? not sure. open to all and any ideas.


thanks all for the help...

I would fix those reserved characters in my named fields, but this data is
coming from another source...i can't change the named fields from where this
data is coming from. it is what it is for right now.

I would suggest you avoid using Access reserved charactors like "#" "/".
Sanitized these column names.

[quoted text clipped - 27 lines]
While I echo AccessVandal's advice re field names, I suspect the problem is
this part:

[Enter Serial Number]

In the Access environment, because it doesn't refer to a valid field name in
the Check_In table, Access will automatically treat it as a parameter and
prompt the user for the value. This won't happen in VB. You'll need to feed
the value to the sql string another way. Post back if you need help with
that.

 

[Dirk Goldgar]

I'm using visual studio 2008

New at this, so thank you for your patiance. I just this installed at work
two days ago. I was proud I got yhis far in this short of time

I just created the basic windows application (blank form then comes up),
created my data source (to my database, tested connection, can view all
the
data ok though the explorer in vb), then pulled a data grid from the tool
box
(assigned the data source from the intial connection I made at the
beginning
of the vb project.


Just doing that, I can run debug and see the entire contents of my data:
it's
indeed all there nice and neat.

I can go into the data grid proporties, create query and filter for a
specific S/N. That works just fine.

I guess (just from good old fashion logic) I have to somehow create a text
box on my form (where I want to scan my S/N into) then somehow in my query
filter criteia, tell it to filter by what is in that text box.

Is that logic correct?

Sounds reasonable. As I said, I haven't worked much with VB.Net except for
ASP.Net apps. Tomorrow I'll try to set this up on my computer and see what
I can dope out for you. Assuming you haven't solved it by then.

 

[chris23892 via AccessMonster.com]

Any insight too this yet?

Yeah, stuart...that is where i am at right now:
I need to feed the filter criteria in the query with my value that I have in
mt text box in my form.

Simply put, i want to scan my s/n from my bar code into a text box, click a
button and have that row come up in my datagrid via my query that i can't get
to work right now.

unless there is another idea on how i can get this query to filter from a
text box? not sure. open to all and any ideas.

thanks all for the help...

I would fix those reserved characters in my named fields, but this data is
coming from another source...i can't change the named fields from where this
data is coming from. it is what it is for right now.
[quoted text clipped - 12 lines]

the value to the sql string another way. Post back if you need help with
that.

 

[AccessVandal via AccessMonster.com]

In the mean time while waiting for Dirk Goldgar reply.

In Access, this is the way to read a textbox value from a SQL String.

WHERE ((([Check_in].[S/N])= Ԡ& Me.YourTextBoxName & “’))â€

Watch for the single quotes, if the datatype is text. Remove quotes if number.


For VB.Net, sorry I can’t recall.

Any insight too this yet?

Yeah, stuart...that is where i am at right now:
I need to feed the filter criteria in the query with my value that I have in

[quoted text clipped - 18 lines]

 

[John W. Vinson]

In the mean time while waiting for Dirk Goldgar reply.

In Access, this is the way to read a textbox value from a SQL String.

WHERE ((([Check_in].[S/N])= ‘” & Me.YourTextBoxName & “’))”

Watch for the single quotes, if the datatype is text. Remove quotes if number.

Actually, DON'T use the "smart quotes" - use the plain vanilla apostrophe and
doublequote characters:

WHERE ((([Check_in].[S/N])= '" & Me.YourTextBoxName & "'))"

 

[AccessVandal via AccessMonster.com]

Darn it! Got to remember to edit those after using MS Word.

In the mean time while waiting for Dirk Goldgar reply.

[quoted text clipped - 3 lines]

Watch for the single quotes, if the datatype is text. Remove quotes if number.

Actually, DON'T use the "smart quotes" - use the plain vanilla apostrophe and
doublequote characters:

WHERE ((([Check_in].[S/N])= '" & Me.YourTextBoxName & "'))"

 

[chris23892 via AccessMonster.com]

I'll give it a shot here at work this morning and post back. Just want to say
thanks for everyone and your help. These boards are a life saver

Darn it! Got to remember to edit those after using MS Word.
[quoted text clipped - 6 lines]

WHERE ((([Check_in].[S/N])= '" & Me.YourTextBoxName & "'))"

 

[chris23892 via AccessMonster.com]

Nope, Does not work. There's a syntax error somewhere in this making it angry

I'll give it a shot here at work this morning and post back. Just want to say
thanks for everyone and your help. These boards are a life saver

Darn it! Got to remember to edit those after using MS Word.

[quoted text clipped - 3 lines]

WHERE ((([Check_in].[S/N])= '" & Me.YourTextBoxName & "'))"

 

[Douglas J. Steele]

You said that you're working in VB.Net. Me.YourTextBoxName is the syntax if
you were using VBA in Access. You'll need to replace that with however you
retrieve a value from a text box in VB.Net.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nope, Does not work. There's a syntax error somewhere in this making it
angry

I'll give it a shot here at work this morning and post back. Just want to
say
thanks for everyone and your help. These boards are a life saver

Darn it! Got to remember to edit those after using MS Word.

[quoted text clipped - 3 lines]

WHERE ((([Check_in].[S/N])= '" & Me.YourTextBoxName & "'))"

 

[chris23892 via AccessMonster.com]

I understand this...Here is the SQL code for the quesry in the datagrid::

SELECT [Claim# 1], [Claim# 2], Comments, [DTAC Case], [Date Entered to FPRT],
[Date Receive], Dealer, [Dealer Acct #], [Entered to FPRT (Y/N)],
[Geographical Location (Ship From)], ID, [Part #], [Product Name], [S/N],
[UPS Tracking#] FROM Check_In WHERE ([S/N] = '& Me.TextBox1 &')



Now, I'm not getting any errors, but when I scan my S/N and click the ~fill~
button that was generated when I created the Query, nothing comes up.

My Text Box I created is called TextBox1

You said that you're working in VB.Net. Me.YourTextBoxName is the syntax if
you were using VBA in Access. You'll need to replace that with however you
retrieve a value from a text box in VB.Net.

Nope, Does not work. There's a syntax error somewhere in this making it
angry

[quoted text clipped - 8 lines]

WHERE ((([Check_in].[S/N])= '" & Me.YourTextBoxName & "'))"

 

[John W. Vinson]

Nope, Does not work. There's a syntax error somewhere in this making it angry

Please post the complete SQL of your query, and indicate the datatype of the
field you're searching. It's especially important to note Lookup Fields since
they don't contain what they appear to contain!

 

[chris23892 via AccessMonster.com]

That is the full SQL code that VB genorated.

I used the ~add Query~ from the datagrid control. when I go into my dataset.
xsd tab then right click on my querry (which is named now ~fill(2),getdataby
(2) then goto ~configure~, I just copied the SQL text right to here:

SELECT [Claim# 1], [Claim# 2], Comments, [DTAC Case], [Date Entered to
FPRT], [Date Receive], Dealer, [Dealer Acct #], [Entered to FPRT (Y/N)],
[Geographical Location (Ship From)], ID, [Part #],
[Product Name], [S/N], [UPS Tracking#]
FROM Check_In
WHERE ([S/N] =' Form1.me.TextBox1_TextChanged_1')

When i run my app in debug, first thing i notice is all my data does indeed
show in in my datagrid. when I scan the number into my text box and click, no
record comes up. The record is there. I can modify my filter criteria to pull
it up.