Newbie question on identically named fields in a dataset

This is my first stab at this. I have a simple report that returns a few
columns from a database no sp no expressions just a select statement. There
are two tables that have the same field name and the dataset canâ't seem to
distinguish between them it returns the first field into both columns. When
I run it in Query Analyzer it looks fine. What am I missing here?Query Analyzer seems to be a bit more forgiving about identical names. In
your query, just use Aliases, and you should be ok.
A query like
"Select table1.col1 as Table1Col1, table2.Col1 as Table2Col1 from Table1,
Table2 (...)"
should give you fields named Table1Col1 and Table2Col1. A bit more trouble
to write, but you eliminate possible errors.
Kaisa M. Lindahl
"RYF" <RYF@.discussions.microsoft.com> wrote in message
news:BD3D2857-937A-4602-80AD-C472FB295245@.microsoft.com...
> This is my first stab at this. I have a simple report that returns a few
> columns from a database no sp no expressions just a select statement.
> There
> are two tables that have the same field name and the dataset can't seem to
> distinguish between them it returns the first field into both columns.
> When
> I run it in Query Analyzer it looks fine. What am I missing here?|||THX that did the trick!
"Kaisa M. Lindahl" wrote:
> Query Analyzer seems to be a bit more forgiving about identical names. In
> your query, just use Aliases, and you should be ok.
> A query like
> "Select table1.col1 as Table1Col1, table2.Col1 as Table2Col1 from Table1,
> Table2 (...)"
> should give you fields named Table1Col1 and Table2Col1. A bit more trouble
> to write, but you eliminate possible errors.
> Kaisa M. Lindahl
> "RYF" <RYF@.discussions.microsoft.com> wrote in message
> news:BD3D2857-937A-4602-80AD-C472FB295245@.microsoft.com...
> > This is my first stab at this. I have a simple report that returns a few
> > columns from a database no sp no expressions just a select statement.
> > There
> > are two tables that have the same field name and the dataset can't seem to
> > distinguish between them it returns the first field into both columns.
> > When
> > I run it in Query Analyzer it looks fine. What am I missing here?
>
>

Newbie question about indexes

Hi ,

I have following statement :

SELECT * FROM Table WHERE Col1=@.Var1 AND Col2=@.Var2 ... AND ColN=@.VarN

How should I design indexes for best performance ?
(
Add one index on columns Col1 till ColN
or add N indexes, first for column Col1, second for Col2, ...
)

Thanks, for your suggestions

How many rows do you expect it to return?
Do you prefer retrieval speed over update speed?
Do you have other queries that might benefit from individual indexes?

|||Strictly from your query perspective, you will only need one index with the key (col1...colN). However, this index will be useless if say col1 is missing from your where clause. So you need to evaluate full set of queries that you plan to run on this table. Also, as Eric points out, you will need to evaluate the cost of updating indexes if you have lots of updates/inserts/deletes
Thanks|||

Hi,

I expect to return c. 100 rows. (Would indexing strategy differs if I have return whole table ?)
Retrieval speed is priority.
I have no other queries for this table.

Thank you

|||Here's what the optimizer guys have to say:

One index should be fine if he always has every column in the WHERE condition and he is doing equality matching.

This will give two main plan options:

1. index lookup + fetch
2. table scan

It will be a cost-based decision.

Newbie Question

How do I get the unique fields from a select query without iterating. Is
this possible? My current statement is below. Thanks in advance.
"SELECT DealNumber, [User] FROM DealingFinal WHERE ([User] = 'dbo')"Hi
SELECT DISTINCT DealNumber, [User] FROM DealingFinal
"xfd" <xfd@.xfd.com> wrote in message
news:u9rL%23udkFHA.3756@.TK2MSFTNGP15.phx.gbl...
> How do I get the unique fields from a select query without iterating. Is
> this possible? My current statement is below. Thanks in advance.
> "SELECT DealNumber, [User] FROM DealingFinal WHERE ([User] = 'dbo')"
>

Newbie needs help with SQL statement

Hello,

I'm having a difficult time finding the right sql syntax to perform an
update. Here is the situation:

I have two tables, each with an orderid field and a removal_date field.
There is a one-to-many relationship between table A and table B, with B
having multiple records to each one in table A, related by the orderid
field. Table A's primary key is the orderid field, and is the only table
that has data in the removal_date field. I would like to update the
removal_date field in table B with the values of the removal_date field in
table A.

Can this be done with a single sql statement? Right now I'm using a VB
program to build the update sql, but this is cumbersome. Any help would be
appreciated.

Thanks.You would need to check this, but I think it would work.

update TableB
set TableB.removal_date = TableA.removal_date
from Tableb join TableA on TableB.orderid = TableA.orderid

"George J" <gjewell@.houston.rr.com> wrote in message
news:Sxn6d.31407$W21.29433@.fe2.texas.rr.com...
> Hello,
> I'm having a difficult time finding the right sql syntax to perform an
> update. Here is the situation:
> I have two tables, each with an orderid field and a removal_date field.
> There is a one-to-many relationship between table A and table B, with B
> having multiple records to each one in table A, related by the orderid
> field. Table A's primary key is the orderid field, and is the only table
> that has data in the removal_date field. I would like to update the
> removal_date field in table B with the values of the removal_date field in
> table A.
> Can this be done with a single sql statement? Right now I'm using a VB
> program to build the update sql, but this is cumbersome. Any help would be
> appreciated.
> Thanks.|||On Wed, 29 Sep 2004 00:42:26 GMT, George J wrote:

>Hello,
>I'm having a difficult time finding the right sql syntax to perform an
>update. Here is the situation:
>I have two tables, each with an orderid field and a removal_date field.
>There is a one-to-many relationship between table A and table B, with B
>having multiple records to each one in table A, related by the orderid
>field. Table A's primary key is the orderid field, and is the only table
>that has data in the removal_date field. I would like to update the
>removal_date field in table B with the values of the removal_date field in
>table A.
>Can this be done with a single sql statement? Right now I'm using a VB
>program to build the update sql, but this is cumbersome. Any help would be
>appreciated.
>Thanks.

Hi George,

As an alternative to Oscar's suggestion:

UPDATE tableB
SET removal_date = (SELECT removal_date
FROM tableA
WHERE tableA.orderid = tableB.orderid)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

newbie need help

I need to know how to write a sql statement for VB to Microsoft Access for the following criteria.

TblEmployees only Field to be concerned with is EmployeeID -Its PK
TblEmpAttendane has these fields:
EntryID - Pk
EmployeeID - Fk
Date- A text form date validated through vb to avoid hassle
Value-A single digit value that can be alpha OR numeric

To get the Date range for the current month I use variables to store the First and last day of the month(in a CUSTOM calendar control).

I have been trying to use this Sql statement(that wont work):
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date BETWEEN '" & FirstDay & "' AND '" & LastDay & "'"

I need to:
1.)using the current EmployeeID (from TblEmployees)
2.) find the same EmployeeID in TblEmpAttendance
3.)Get the date range for the current month and year(valid days currnt mo.)
-these date are supplied by the variables "FirstDay" AND "LastDay" as seen in above SqlString

4.) Find the Values associated with the dates and EmployeeIDAre you using Jet or MS-SQL (aka MSDE) as your database engine? They have differences in how they handle dates, and the example you gave would not fly very well in Jet.

-PatP|||Im using jet to connect(through code) to my db.
Can u assist me in how to improve my table format..or whatever I would have to do in order to be able to do this correctly?

All that I did was set up a string format in vb to make sure that the correct amount of characters and the proper syntax was used:
eg... 00/00/0000
In my code I nvr explicity refer to them as a date. Only the user would think that it was a date!

Thanks-Greg S|||I'd try using:SqlString = "SELECT * FROM TblEmpAttendance WHERE #" _
& FirstDay & "# <= Date AND Date <= #" & LastDay & "#"This is only a swag, but I think that it should work.

-PatP|||Thanks I will try that and get back to you. Much appreciated!|||I have a lot more to do than I thought. I did do a date conversion function from within access so it is only displayed as dd/mm/yy format.
Then from vb I used:
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date Bewteen '" & FirstDay & "' AND '" & LastDay & "'"

It seemd to work fine as I can refer to a field but for some reason when I refer to the recordcount property,I always get -1...Seems weird how I can read the info but not get the recordcount!!|||Check the BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp). If you use ADOpenStatic you won't have -1 as a recordcount.

-PatP|||I dont know what you mean by "BOL" but as I am using ADO as a connection it does support bookmarks(not that I believe I need on with the move.bof statement) If this is not what you were getting at could you be more specific?

OK WAIT..I added adOpenStatic to my statement!
now it seems to be working
But I had to put in in the form of:
AdoRecordset.Open SqlString, AdoConnection, adOpenStatic

instead of AdoRecordset.Open (SqlString, AdoConnection), adOpenStatic
like the link you posted...Im still not sure why the perverbial correct way wouldnt work but my way worked anyhow..
THANKS MUCH!|||adOpenStatic, adOpenDynamic, adOpenKeyset, and adOpenForwardOnly are qualifications of the cursor that is being open either on the server or client side (depends on the CursorLocation property). The default (adOpenForwardOnly) will have -1 for RecordCount property.

Newbie help: sql string conversion

Hi,
I am trying to avoid a horrendous amount of coding and see if i can get away
with a complex sql statement.

I have data values (measurements) which I have stored in the database in the
form of STRING so that i can keep the original format.

They look like this:

000078 -> 7.8 degrees
-99999M -> Missing data
000345 -> 34.5 degrees
-00993 -> -99.3 degrees
000011 -> 1.1 degrees

you get the idea.
They represent numbers (positive or negative) the last place is the decimal
, they all have 6 characters except the missing data which is -99999M (7
places).

How would I construct an SQL querry to be able to allow the user to retrieve
temperature between e.g. > -2.5 and <12.4 ?

TIA
-steveWhy is the data stored in this format? If these are numeric measurements you
will be much better off storing them with a numeric datatype. Storing
numbers as strings will just make your queries difficult and slow and also
make it hard to maintain any data integrity. Fix the design and convert the
data to numeric form is my advice.

If you've no other choice you could try something like this:

SELECT col
FROM Measurements
WHERE CAST(LEFT(col,6) AS INTEGER) > -2.5
AND CAST(LEFT(col,6) AS INTEGER) < 12.4

--
David Portas
SQL Server MVP
--|||steve,

SELECT * FROM Table1
WHERE CAST(CASE RIGHT(Measurement, 1) WHEN 'M' THEN NULL ELSE Measurement
END AS decimal) * .1
BETWEEN -2.5 AND 12.4

-Andy

"steve" <noemail.@.try.com> wrote in message
news:S5wgd.49358$5t4.774343@.wagner.videotron.net.. .
> Hi,
> I am trying to avoid a horrendous amount of coding and see if i can get
> away with a complex sql statement.
> I have data values (measurements) which I have stored in the database in
> the form of STRING so that i can keep the original format.
> They look like this:
> 000078 -> 7.8 degrees
> -99999M -> Missing data
> 000345 -> 34.5 degrees
> -00993 -> -99.3 degrees
> 000011 -> 1.1 degrees
> you get the idea.
> They represent numbers (positive or negative) the last place is the
> decimal , they all have 6 characters except the missing data which
> is -99999M (7 places).
> How would I construct an SQL querry to be able to allow the user to
> retrieve temperature between e.g. > -2.5 and <12.4 ?
> TIA
> -steve
>|||hmm i see your point and thanx for your answer.
As I said I'd rather keep them in this format for now.
However!
I was thinking is there a fast way that through an sql querry that I can
duplicate a table with a different name of course that will have the same
data but on the "proper" format?
give me a couple of keywords and I'll look google them if you can

Thanx again!

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news: avqdndYSfcazCB_cRVn-pA@.giganews.com...
> Why is the data stored in this format? If these are numeric measurements
> you will be much better off storing them with a numeric datatype. Storing
> numbers as strings will just make your queries difficult and slow and also
> make it hard to maintain any data integrity. Fix the design and convert
> the data to numeric form is my advice.
> If you've no other choice you could try something like this:
> SELECT col
> FROM Measurements
> WHERE CAST(LEFT(col,6) AS INTEGER) > -2.5
> AND CAST(LEFT(col,6) AS INTEGER) < 12.4
> --
> David Portas
> SQL Server MVP
> --|||Create a new table, then use the query I gave you to INSERT into it:

INSERT INTO NewTable (...)
SELECT ...
FROM OldTable

But if the data is changing, maintaining two copies of it is hard work and
unnecessary. The usual practice is to validate and transform data once and
then maintain it in a consistent, strongly-typed relational format in the
database. If you validate the data properly once then you won't need the
original format again. If you don't then you pay the price every time you
query the table.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Why is the data stored in this format? If these are numeric measurements
> you will be much better off storing them with a numeric datatype.
> Storing numbers as strings will just make your queries difficult and
> slow and also make it hard to maintain any data integrity. Fix the
> design and convert the data to numeric form is my advice.

And store the missing values as NULL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I think i *will* loose my mind!
Thanks both of you for your help.

You see, the problem is that i can have a few flags at the end of the string
which mean somehing. e.g. M means missing, T means Trace, E means estimated,
etc.Information that i should have. If i split each column into two ,
well...possible but a lot of work and too much overhead since most
measurements are "clean".

That's the reason i kept the data in their original format.

Now if i dont convert to strings how the heck am i going to create the sql
string to send to the database with things like:
temperature between so and so, humidity bigger than 50, blah blah blah...
The string is created by an interface in VB where the user scrolls down
various controls and selects things. But how would you enter the bounds of
temperature in a textbox since its not stored as an integer AND it might
have a stupid letter at the end of its value!!!

I could do the coding after the results are returned.
I think My Biggest problem is string comparisons!!! If the user wants a
temperature between -7 and +15 how well and reliably can I create code to
compare "-00007" and "000015".

Just some thoughts from my brainstorming...

"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> a crit dans le
message de news: CIwgd.1071$wN4.303@.newssvr16.news.prodigy.com...
> steve,
> SELECT * FROM Table1
> WHERE CAST(CASE RIGHT(Measurement, 1) WHEN 'M' THEN NULL ELSE Measurement
> END AS decimal) * .1
> BETWEEN -2.5 AND 12.4
> -Andy
> "steve" <noemail.@.try.com> wrote in message
> news:S5wgd.49358$5t4.774343@.wagner.videotron.net.. .
>> Hi,
>> I am trying to avoid a horrendous amount of coding and see if i can get
>> away with a complex sql statement.
>>
>> I have data values (measurements) which I have stored in the database in
>> the form of STRING so that i can keep the original format.
>>
>> They look like this:
>>
>> 000078 -> 7.8 degrees
>> -99999M -> Missing data
>> 000345 -> 34.5 degrees
>> -00993 -> -99.3 degrees
>> 000011 -> 1.1 degrees
>>
>> you get the idea.
>> They represent numbers (positive or negative) the last place is the
>> decimal , they all have 6 characters except the missing data which
>> is -99999M (7 places).
>>
>> How would I construct an SQL querry to be able to allow the user to
>> retrieve temperature between e.g. > -2.5 and <12.4 ?
>>
>> TIA
>> -steve
>>
>>
>>
>>|||steve wrote:
> I think i *will* loose my mind!
> Thanks both of you for your help.
> You see, the problem is that i can have a few flags at the end of the string
> which mean somehing. e.g. M means missing, T means Trace, E means estimated,
> etc.Information that i should have. If i split each column into two ,
> well...possible but a lot of work and too much overhead since most
> measurements are "clean".

That's not a "problem"! Basic database design says you shouldn't keep
multiple pieces of information in the same column. Doing it right
wouldn't be that difficult code wise. You simply have a column with your
valid codes in a check constraint and have the default be the "clean"
code if that is the most common entry.

> That's the reason i kept the data in their original format.
> Now if i dont convert to strings how the heck am i going to create the sql
> string to send to the database with things like:
> temperature between so and so, humidity bigger than 50, blah blah blah...
> The string is created by an interface in VB where the user scrolls down
> various controls and selects things. But how would you enter the bounds of
> temperature in a textbox since its not stored as an integer AND it might
> have a stupid letter at the end of its value!!!

You can do all that manipulation on the front in via code.

> I could do the coding after the results are returned.
> I think My Biggest problem is string comparisons!!! If the user wants a
> temperature between -7 and +15 how well and reliably can I create code to
> compare "-00007" and "000015".
> Just some thoughts from my brainstorming...

Honestly, your brainstorming is confusing the heck out of me. Or maybe
you don't understand the numeric data type. A query of a numeric column
for all values between -7 and +15 would be very simple: WHERE Temp
BETWEEN -7 and 15. Numeric data is not stored with leading 0's.

Zach

>
> "Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> a crit dans le
> message de news: CIwgd.1071$wN4.303@.newssvr16.news.prodigy.com...
>>steve,
>>
>>SELECT * FROM Table1
>>WHERE CAST(CASE RIGHT(Measurement, 1) WHEN 'M' THEN NULL ELSE Measurement
>>END AS decimal) * .1
>> BETWEEN -2.5 AND 12.4
>>
>>-Andy
>>
>>"steve" <noemail.@.try.com> wrote in message
>>news:S5wgd.49358$5t4.774343@.wagner.videotron.net.. .
>>
>>>Hi,
>>>I am trying to avoid a horrendous amount of coding and see if i can get
>>>away with a complex sql statement.
>>>
>>>I have data values (measurements) which I have stored in the database in
>>>the form of STRING so that i can keep the original format.
>>>
>>>They look like this:
>>>
>>>000078 -> 7.8 degrees
>>>-99999M -> Missing data
>>>000345 -> 34.5 degrees
>>>-00993 -> -99.3 degrees
>>>000011 -> 1.1 degrees
>>>
>>>you get the idea.
>>>They represent numbers (positive or negative) the last place is the
>>>decimal , they all have 6 characters except the missing data which
>>>is -99999M (7 places).
>>>
>>>How would I construct an SQL querry to be able to allow the user to
>>>retrieve temperature between e.g. > -2.5 and <12.4 ?
>>>
>>>TIA
>>>-steve
>>>
>>>
>>>
>>>
>>
>>
>|||> You see, the problem is that i can have a few flags at the end of the
> string which mean somehing. e.g. M means missing, T means Trace, E means
> estimated

Then you have a non-atomic column, which is a violation of the most
fundamental relational design principles. This information belongs in a
separate column.

--
David Portas
SQL Server MVP
--|||> But how would you enter the bounds of temperature in a textbox since its
> not stored as an integer AND it might have a stupid letter at the end of
> its value!!!

> I think My Biggest problem is string comparisons!!! If the user wants a
> temperature between -7 and +15 how well and reliably can I create code to
> compare "-00007" and "000015".

Yep, it's lousy... So why waste time on it when you could just redesign the
table properly :-)

--
David Portas
SQL Server MVP
--

Newbie - SqlConnection statement

I'm trying to get a combobox to fill with values from a table. I've
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
RandyHi Randy
"Randy" wrote:

> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
http://msdn.microsoft.com/library/d...opi
c.asp
John

Newbie - SqlConnection statement

I'm trying to get a combobox to fill with values from a table. I've
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
Randy
Hi Randy
"Randy" wrote:

> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.as p[/url]
John

Newbie - SqlConnection statement

I'm trying to get a combobox to fill with values from a table. I've
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
RandyHi Randy
"Randy" wrote:
> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.asp
John

NEWBIE - Parameter @EmployeeName to select a particular employee OR all employees

I have an operational parameter in my SQL select statement, @.EmployeeName,
that will filter timecard data for a particular employee. When I am running
the query and it prompts me for @.EmployeeName, I would like the option of
putting in * or [ALL] or something of that nature to return all the timecard
data.
Is there a wildcard that I can put in my parameter prompt to return all the
records?
I will greatly appreciate any help you can offer on the subject. Thank you,
-Dave> that will filter timecard data for a particular employee. When I am
running
> the query and it prompts me for @.EmployeeName, I would like the option of
> putting in * or [ALL] or something of that nature to return all the
timecard
> data.
What prompts you for this? Can you not leave the parameter empty? How is
the stored procedure coded?
Typically, you can implement optional parameters, and when you call the
procedure, you can either include that parameter or not.
http://www.aspfaq.com/2348
I think you are being slowed down by the GUI tool you are using, not the
nature of parameters. Also, keep in mind that * is only a wildcard in DOS,
Microsoft Access and a few other places. SQL Server uses % and _ ...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||I am using the Query Builder in VS.NET 2003, not a stored procedure. Do I
need to use a stored procedure to achieve this result?
When I leave the parameter empty, I get no results for my query.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23RLQapIMFHA.1308@.TK2MSFTNGP15.phx.gbl...
> running
> timecard
> What prompts you for this? Can you not leave the parameter empty? How is
> the stored procedure coded?
> Typically, you can implement optional parameters, and when you call the
> procedure, you can either include that parameter or not.
> http://www.aspfaq.com/2348
> I think you are being slowed down by the GUI tool you are using, not the
> nature of parameters. Also, keep in mind that * is only a wildcard in
> DOS,
> Microsoft Access and a few other places. SQL Server uses % and _ ...
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>

newbie - Most Recent Records from multiple tables

Hi,

I'm trying to create a view or TSQL statement to return in one recordset...

a) the most recent record of a PK in table1 [foodRecipes]

b) the most recent record (if exists) of FK from table 1 with the PK from table2

Goal: Each recipe can have many versions, and each version can have many historical attempts at making cookies...

example:

table 1: foodRecipes (PK = foodGroup + recipeName + recipeDateModified)

foodGroup [nvarchar (50)]

recipeName [nvarchar (50]

recipeDateModified [datetime]

cupsOfSugar [float]

sampleData:

cookies, peanutButter, 3/3/2007, 1.5

cookies, peanutButter, 3/4/2007, 2.0

cookies, sugar, 3/3/2007, 5.0

table 2: foodRecipeHistory (PK = foodGroup + recipeName + recipeDateModified + historyDateModified)

foodGroup [nvarchar (50)] ...FK from table1

recipeName [nvarchar (50] ...FK from table1

recipeDateModified [datetime] ...FK from table1

historyDateModified [datetime]

cupsOfSugarHistory [float]

sampleData:

cookies, peanutButter, 3/3/2007, 3/3/2007 10:15:00 AM, 1.5

cookies, peanutButter, 3/4/2007, 3/4/2007 10:20:00 AM, 2.0

cookies, peanutButter, 3/4/2007, 3/4/2007 10:21:00 AM, 2.2

What I want: the view or TSQL should provide the most recent unique recipes data + the most recent history (if exists, otherwise NULL)

SELECT * FROM myRecipies

sample Resultset:

foodGroup, recipeName, recipeDateModified, cupsOfSugar, historyDateModified, cupsOfSugarHistory

cookies, peanutButter, 3/4/2007, 2.0, 2.2

cookies, sugar, 3/3/2007, 5.0, <NULL>

What I've got now:

1. TSQL that gives me back the most recent recipes (No History yet)

SELECT foodGroup, recipeName, recipeDateModified, cupsOfSugar, CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate
FROM dbo.foodRecipes oher
WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN
(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))
FROM dbo.foodRecipes
WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

...and this works great, I get back each unique recipe from table #1, the most recent...

anyone good at this?

thanks in advance,

bsierad

You must have a sweet tooth if your only ingredient is CupsOfSugar.... ;)

Anyway, try the query below to see if this is what you're after.

Chris

SELECT foodGroup,

recipeName,

recipeDateModified,

cupsOfSugar,

CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate,

(SELECT TOP 1 frh.cupsOfSugarHistory

FROM dbo.foodRecipeHistory frh

WHERE frh.foodGroup = oher.foodGroup

AND frh.recipeName = oher.recipeName

AND frh.recipeDateModified = oher.recipeDateModified

ORDER BY frh.historyDateModified DESC) AS cupsOfSugarHistory

FROM dbo.foodRecipes oher

WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN

(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))

FROM dbo.foodRecipes

WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

|||

Thanks!

Works great...and I can soak this in and apply it in other areas...

My real fields don't taste this good...machineGasFlow sounds pretty boring...

Can't thank you enough,

bsierad