473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Parameterised query question

Hi

I have the following sql for my dataadapter;

SELECT ID, Company, Status
FROM Companies
WHERE Status = @Status

I fill the dataadapter as
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Companies , "Current") and it works
great. The problem is how do I view all Companies and not just the current
ones, when I need to? What changes do I need to make to the SQL and the fill
statement?

Thanks

Regards
Nov 21 '05 #1
7 1000
Not quite sure I understand the question. Are you asking "how do I select
everything, regardless of status"?
If so, then just omit the WHERE clause:

SELECT ID, Company, Status FROM Companies;
"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:Ob**************@TK2MSFTNGP12.phx.gbl...
Hi

I have the following sql for my dataadapter;

SELECT ID, Company, Status
FROM Companies
WHERE Status = @Status

I fill the dataadapter as
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Companies , "Current") and it
works great. The problem is how do I view all Companies and not just the
current ones, when I need to? What changes do I need to make to the SQL
and the fill statement?

Thanks

Regards

Nov 21 '05 #2
Change the WHERE clause to something like this:
WHERE @Status IS NULL or @Status = Status

To get all companies, set @Status to DBNull.Value. You could also set a
default value of NULL.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:Ob**************@TK2MSFTNGP12.phx.gbl...
Hi

I have the following sql for my dataadapter;

SELECT ID, Company, Status
FROM Companies
WHERE Status = @Status

I fill the dataadapter as
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Companies , "Current") and it
works great. The problem is how do I view all Companies and not just the
current ones, when I need to? What changes do I need to make to the SQL
and the fill statement?

Thanks

Regards

Nov 21 '05 #3
Yes, sometimes I want to see all records regardless of the status. How do I
omit the where clause? There is only one SELECT SQL associated with the
datadapter. How do I switch between the one with WHERE clause and one
without, when needed?

Thanks

Regards

"Wicksy" <wi*****@nospam-yahoo.com> wrote in message
news:dh**********@news.freedom2surf.net...
Not quite sure I understand the question. Are you asking "how do I select
everything, regardless of status"?
If so, then just omit the WHERE clause:

SELECT ID, Company, Status FROM Companies;
"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:Ob**************@TK2MSFTNGP12.phx.gbl...
Hi

I have the following sql for my dataadapter;

SELECT ID, Company, Status
FROM Companies
WHERE Status = @Status

I fill the dataadapter as
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Companies , "Current") and it
works great. The problem is how do I view all Companies and not just the
current ones, when I need to? What changes do I need to make to the SQL
and the fill statement?

Thanks

Regards


Nov 21 '05 #4
Thanks. How can I set the default value of NULL?

Regards

"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:eG**************@TK2MSFTNGP12.phx.gbl...
Change the WHERE clause to something like this:
WHERE @Status IS NULL or @Status = Status

To get all companies, set @Status to DBNull.Value. You could also set a
default value of NULL.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:Ob**************@TK2MSFTNGP12.phx.gbl...
Hi

I have the following sql for my dataadapter;

SELECT ID, Company, Status
FROM Companies
WHERE Status = @Status

I fill the dataadapter as
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Companies , "Current") and it
works great. The problem is how do I view all Companies and not just the
current ones, when I need to? What changes do I need to make to the SQL
and the fill statement?

Thanks

Regards


Nov 21 '05 #5
When I call fill like so; Me.CompanyTableAdapter.Fill(Me.MyDataSet.Clients,
"Client", DBNull.Value) to get all records, I get this error; Value of type
'System.DBNull' cannot be converted to 'String'.

Regards
"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:eG**************@TK2MSFTNGP12.phx.gbl...
Change the WHERE clause to something like this:
WHERE @Status IS NULL or @Status = Status

To get all companies, set @Status to DBNull.Value. You could also set a
default value of NULL.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:Ob**************@TK2MSFTNGP12.phx.gbl...
Hi

I have the following sql for my dataadapter;

SELECT ID, Company, Status
FROM Companies
WHERE Status = @Status

I fill the dataadapter as
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Companies , "Current") and it
works great. The problem is how do I view all Companies and not just the
current ones, when I need to? What changes do I need to make to the SQL
and the fill statement?

Thanks

Regards


Nov 21 '05 #6
Sorry, I meant Me.CompanyTableAdapter.Fill(Me.MyDataSet.Clients,
DBNull.Value)

Regards

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:OD**************@TK2MSFTNGP11.phx.gbl...
Thanks. How can I set the default value of NULL?

Regards

"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:eG**************@TK2MSFTNGP12.phx.gbl...
Change the WHERE clause to something like this:
WHERE @Status IS NULL or @Status = Status

To get all companies, set @Status to DBNull.Value. You could also set a
default value of NULL.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:Ob**************@TK2MSFTNGP12.phx.gbl...
Hi

I have the following sql for my dataadapter;

SELECT ID, Company, Status
FROM Companies
WHERE Status = @Status

I fill the dataadapter as
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Companies , "Current") and it
works great. The problem is how do I view all Companies and not just the
current ones, when I need to? What changes do I need to make to the SQL
and the fill statement?

Thanks

Regards



Nov 21 '05 #7
On you CompanyTableAdapter you can add one more query without any where
clause, and give a good Method Name.
Use the new Method Name to Fill the DataTable.

JH
"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:Ob**************@TK2MSFTNGP12.phx.gbl...
Hi

I have the following sql for my dataadapter;

SELECT ID, Company, Status
FROM Companies
WHERE Status = @Status

I fill the dataadapter as
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Companies , "Current") and it
works great. The problem is how do I view all Companies and not just the
current ones, when I need to? What changes do I need to make to the SQL
and the fill statement?

Thanks

Regards

Nov 21 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jakob | last post by:
Hey... Why do I sometimes experience low performance when I use a parameter instead of an exact value? For example the following performs very bad:
9
by: Phil Sowden | last post by:
I'd really appreciate your help creating a parameterised view in SQL Server, which I can then invoke from VB5. I'm using DAO and ODBC to connect to SQL Server 2000. I can open Tables and Views,...
2
by: John | last post by:
Hi New to sql server. using beta2 2005. What is the way to implement parameterised queries? I need the queries to be updatable as these would work as recordsets that vb.net forms will be bound...
5
by: u7djo | last post by:
I've built a query in Access and added a parameter to it called . I'm trying to load the recordset by using an ADO command and attaching a parameter to it but I keep getting an error message when...
5
by: Tim Marsden | last post by:
Hello, I am building a parameterised query in vb.net for execution against a SQL server database. I am using a OLEDB command and OLEDB parameters. If one of the parameters is a date I sometimes...
0
by: ken | last post by:
Hi, I connected Access 2003 to SQL express 2005. My question is in access if I have a query, I can put in the criteria a function to return some kind of criteria statement. I can also redefine the...
2
by: B.r.K.o.N.j.A | last post by:
I'm using mysql 4.1 and php5.0.4. Since (AFAIK) this version of mysql supports parameterised queries, is there a way to accomplish that using mysql_... functions (looks like mysql client library is...
5
by: John | last post by:
Hi I have a parameterised query for a report like this; PARAMETERS Event_ID Short; SELECT DISTINCTROW Events., ... FROM Events WHERE (((Events.)=)); Now I am trying to run the report...
1
by: DebGuria | last post by:
I have written a managed C++ dll. I have to use that dll from C#, VB .NET and VB. It has got a parameterised propetyfor the very specific requirement. For example get_Pie(float f). When I am...
0
by: John | last post by:
Hi When using sql server, I can use a sql like below in my data adapter to send the sql a parameter value; SELECT <field list> FROM <table> WHERE Fieldx = @Para1 My...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.