newbie question on accessing count(*) results

hello,
this is my sql query with access:
Dim SQLstr2 As String = "Select count(*) as total, oDate from Order_Details where oNo = " + Request("oNo") + " group by oDate"

However, problem is, I am clueless about how to access the data that is derived from my count(*).

I tried this: Response.Write(reader2("total"))

All I get is that No data exists for the row/column.

I tried not to select oDate and even took away the WHERE, but the same problem persists. Any ideas please?here's a template code that might help you.

Dim ConnectionString As String = "Your_Connection_String"
Dim CommandText As String = "select Col1, Col2, Col3 from TableName"
Dim myConnection As New System.Data.SqlClient.SqlConnection(ConnectionString)
Dim myCommand As New System.Data.SqlClient.SqlCommand(CommandText, myConnection)
myConnection.Open()
Dim DataReader As System.Data.SqlClient.SqlDataReader = myCommand.ExecuteReader()
If DataReader.HasRows Then
 Do While DataReader.Read()
 Response.Write(DataReader.Item("Col1"))
 Response.Write(DataReader.Item("Col2"))
 Response.Write("<BR>")
 Loop
End If
DataReader.Close()
myconnection.close()

hth

Newbie Parameter Problem

Hi, I have 3 parameters on my form. StartDate (datetime), EndDate (datetime) and CompanyName(string). The default values are: StartDate (Non-queried) 1-1-2005, EndDate (Non-queried) 1-1-2008, CompanyName (From query) DataSetBelow, Value field (AccountFamily):

SELECT DISTINCT AccountFamily
FROM CallDataRecords

The table on the form contains the following DataSet:

SELECT Salutation, InboundTimeMS, OutboundTimeMS, ModifiedOn, IsRightParty, AccountFamily

FROM CallDataRecords

WHERE AccountFamily = @.CompanyName
AND ModifiedOn
BETWEEN @.StartDate AND @.EndDate

The error I get is: "Query execution failed for data set (one directly above)".

"Must declare the scalar variable "@.CompanyName".

Can anybody shed light please?

Thanks, Dan

I believe you have to declare the variable first and then use it the query..

DECLARE @.CompanyName nvarchar(25)

--Initilize the declared variable

SELECT DISTINCT @.CompanyName = AccountFamily
FROM CallDataRecords

-- use it

SELECT Salutation, InboundTimeMS, OutboundTimeMS, ModifiedOn, IsRightParty, AccountFamily

FROM CallDataRecords

WHERE AccountFamily = @.CompanyName
AND ModifiedOn
BETWEEN @.StartDate AND @.EndDate

Hope this helps.....

|||

I tried that, but I got the following error:

"The report parameter 'CompanyName' uses the field 'AccountFamily' in a data set reference, but the data set 'DistinctComanyName' does not contain that field".

I also tried editing the Dataset and adding in the parameters tab of the Dataset. However that doesn;t help either (?).

|||

Sorry, please ignore my last post. I fixed it by adding the parameters to the second dataset. (They were not defined).

Thanks!

|||

cool ... all the best

newbie openquery question

How do I use openquery to create a table in an oracle database. Any sql
string that I pass thru fails as it doesn't return any rows.Please post which query you are using and whats the error message that
comes back.
HTH, Jens Suessmeyer.|||I'm trying to use:
select 1
from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM EMP')
and getting error message:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE TEST01 AS SELECT * FROM SCOTT.EMP'.
The OLE DB provider 'MSDAORA' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='MSDAORA', Query=CREATE
TABLE TEST01 AS SELECT * FROM SCOTT.EMP'].
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1137227213.161298.325390@.f14g2000cwb.googlegroups.com...
> Please post which query you are using and whats the error message that
> comes back.
> HTH, Jens Suessmeyer.
>|||AS OpenQUERY is expecting a resultset being send back, you have to send
back even a dummy Select like "Select 1" (especially in Oracle "Select
1 from Dual"), so that should do the trick. (BTW: Do not use * in
productional systems for selecting, but I assume that you only did this
for testing, right ;-) )
select 1
from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM
EMP;Select 1 FROM dual;')
HTH, Jens Suessmeyer.|||unfortunately that still didn't work. Getting:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE TEST01 AS SELECT * FROM EMP;Select 1
FROM dual;'. The OLE DB provider 'MSDAORA' indicates that the object has no
columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='MSDAORA', Query=CREATE
TABLE TEST01 AS SELECT * FROM EMP;Select 1 FROM dual;'].
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1137229967.162954.49310@.g14g2000cwa.googlegroups.com...
> AS OpenQUERY is expecting a resultset being send back, you have to send
> back even a dummy Select like "Select 1" (especially in Oracle "Select
> 1 from Dual"), so that should do the trick. (BTW: Do not use * in
> productional systems for selecting, but I assume that you only did this
> for testing, right ;-) )
> select 1
> from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM
> EMP;Select 1 FROM dual;')
>
> HTH, Jens Suessmeyer.
>|||ok, I think as the provider I waiting for a column description, try to
name the column which is coming back. I assume that this is expecting a
column description meta data. Once we had something similar executing a
stored procedure on a informix server. Due to the fact that the
procedure didn=B4t passed back anything, we received a smiliar error.
Adding a Select "'Procedure ready' AS result" at the end of the
procedure did the trick.
So try
select 1
from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM
EMP;Select 'Table created' AS ResultMessage FROM dual;')
HTH, Jens Suessmeyer.|||I got a syntax error due to the single quotes I think. When I modified to
add extra quotes:
select 1
from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM EMP;Select
''Table created'' AS ResultMessage FROM dual;')
I still get the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE TEST01 AS SELECT * FROM EMP;Select
'Table created' AS ResultMessage FROM dual;'. The OLE DB provider 'MSDAORA'
indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='MSDAORA', Query=CREATE
TABLE TEST01 AS SELECT * FROM EMP;Select 'Table created' AS ResultMessage
FROM dual;'].
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1137233494.070652.28380@.f14g2000cwb.googlegroups.com...
ok, I think as the provider I waiting for a column description, try to
name the column which is coming back. I assume that this is expecting a
column description meta data. Once we had something similar executing a
stored procedure on a informix server. Due to the fact that the
procedure didn´t passed back anything, we received a smiliar error.
Adding a Select "'Procedure ready' AS result" at the end of the
procedure did the trick.
So try
select 1
from openquery(ORACLE8I,'CREATE TABLE TEST01 AS SELECT * FROM
EMP;Select 'Table created' AS ResultMessage FROM dual;')
HTH, Jens Suessmeyer.

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 help On Sql string form vb

HI, I just started db programming for this project I am working on. I am having a problem joining two tables where both of their "EmployeeID" fields is equal to my Vb EmpID string...

I was trying the code:

SqlQuery = "SELECT TblEmpAttendance.Date, TblEmpAttendance.Value," & _
"TblEmployees.TotalPoints, TblEmployees.DaysWorked FROM TblEmployees RIGHT JOIN TblEmpAttendance ON TblEmployees.EmployeeID WHERE TblEmpAttendance.EmployeeID" & _
"= TblEmpAttendance.EmployeeID AND TblAttendance.EmployeeID=" & EmpID

Any help would be appreciated.
Thanks
-Greg S.Oops I already posted it but ill dbl chk anyhow. I got it to work using:

SqlQuery = "SELECT TblEmpAttendance.EmployeeID, TblEmpAttendance.Date, TblEmpAttendance.Value," & _
"TblEmployees.TotalPoints, TblEmployees.DaysWorked FROM TblEmployees RIGHT JOIN TblEmpAttendance ON TblEmployees.EmployeeID = TblEmpAttendance.EmployeeID WHERE TblEmployees.EmployeeID = " & EmpID

Is their anyhting that could cause problems with this statement?
Thanks
-Greg S

Newbie Connection Problem

I'm having a problem writing aconnection string in .NET. I'm trying to use:

SqlConnection myConnection = new SqlConnection("Provider=SQLOLEDB;Data Source=mssql;Initial Catalog= cat;UserId=ddd;Password=ddd;");

which unfortunately is giving me:

SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution ofthe current web request. Please review the stack trace for moreinformation about the error and where it originated in the code.

Can someone tell me what I'm doing wrong? Thanks in advance!

do you only have Windows Authentication setup for your SQL server?

or - if not -does the user you put in the connection string actually have an account in SQL Server?

|||We're not using Windows Authentication and there is a valid account being referenced in the SQL Server (I just put letters in my post to show what I was using)|||

mtarby:

SqlConnection myConnection = new SqlConnection("Provider=SQLOLEDB;Data Source=mssql;Initial Catalog= cat;UserId=ddd;Password=ddd;");

May be a silly question: can Provider key word be used in SqlConnection object? How about remove the Provider attribute from the connection string?

|||Without wanting to come across as too condescending, i'm taking it that your server is called mssql (and it's the only instance on your machine) and that the username and password are correct...?|||Yes, those are just placeholders I put in my post. The server, user name and password are all correct in my actual code|||

have you tried:

SqlConnection myConnection = new SqlConnection("Server= YourSrvr; Database=YourDB;UserId=ddd;Password=ddd;");|||I ended up adding an entry to my web.config file and its working fine now. Thanks for the help!

NEWBIE AND EASY QUESTION

Hi,
Sorry for my poor english. I'm a newbie in SQL Server, and I want to know if
it's possible (and How :)) i can find a string of text in all tables of a
SQL Database.
I explain my problem for easy understanding:
I have a form that submit data in to a SQL database and I want to know
in wich table does it goes... I inserted a simple text on a form's field
(ex. ABCDEFG) now I want to search every table until i find that string.
Thanks,This isn't that easy to do as it might sound. I suggest you run Profiler and catch the statement the
app submits and inspect that INSERT statement. In return, you will learn to use the very valuable
Profiler tool!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joao Bras - Brasmatica" <no@.spam.com> wrote in message
news:%237fAUjNoEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Sorry for my poor english. I'm a newbie in SQL Server, and I want to know if
> it's possible (and How :)) i can find a string of text in all tables of a
> SQL Database.
> I explain my problem for easy understanding:
> I have a form that submit data in to a SQL database and I want to know
> in wich table does it goes... I inserted a simple text on a form's field
> (ex. ABCDEFG) now I want to search every table until i find that string.
> Thanks,
>|||This should help you:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
--
David Portas
SQL Server MVP
--|||Thank you for all the answers. Special Thanks for David Portas (are you
portuguese?) It worked wonderfully. Tibor Karaszi, thank you, for your
promptly help.

NEWBIE AND EASY QUESTION

Hi,
Sorry for my poor english. I'm a newbie in SQL Server, and I want to know if
it's possible (and How ) i can find a string of text in all tables of a
SQL Database.
I explain my problem for easy understanding:
I have a form that submit data in to a SQL database and I want to know
in wich table does it goes... I inserted a simple text on a form's field
(ex. ABCDEFG) now I want to search every table until i find that string.
Thanks,
This isn't that easy to do as it might sound. I suggest you run Profiler and catch the statement the
app submits and inspect that INSERT statement. In return, you will learn to use the very valuable
Profiler tool!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joao Bras - Brasmatica" <no@.spam.com> wrote in message
news:%237fAUjNoEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Sorry for my poor english. I'm a newbie in SQL Server, and I want to know if
> it's possible (and How ) i can find a string of text in all tables of a
> SQL Database.
> I explain my problem for easy understanding:
> I have a form that submit data in to a SQL database and I want to know
> in wich table does it goes... I inserted a simple text on a form's field
> (ex. ABCDEFG) now I want to search every table until i find that string.
> Thanks,
>
|||This should help you:
http://vyaskn.tripod.com/search_all_...all_tables.htm
David Portas
SQL Server MVP
|||Thank you for all the answers. Special Thanks for David Portas (are you
portuguese?) It worked wonderfully. Tibor Karaszi, thank you, for your
promptly help.