Re: Retrieving QTY of records on a SQLClient - Database Discussion Boards

Shaun Wilde28-Aug-02 8:28

28-Aug-02 8:28

thanks - that'll help debug the obvious

I was hoping I could wait until it was called and see what values were being passed in and the step through.

Could I use the profiler for this at all?

Stupidity dies.
The end of future offspring.
Evolution wins.
- A Darwin Awards Haiku

Re: SQL Debugging

notadood28-Aug-02 8:57

notadood

28-Aug-02 8:57

Profiler wouldn't really be much help for debugging. It is mainly good for finding performance bottlenecks & problems.

I have had good success on debugging in Query Analyzer using the method described above. You can run the procedure straight out of QA and put exit points at various points if necessary - using the RETURN statement. Once you get used to it you can debug relatively quickly.

ODBC.NET and Excel

Knappen27-Aug-02 1:30

Knappen

27-Aug-02 1:30

Hello
I want to open a Excel-file in ADO.NET as a DataSet.

I downloaded the ODBC.NET. and made a DSN-file pointing to the excel-file.

Then I made a connection to the DSN-file in the Server-explorer.

So far so good.

But when I try to use the generated connectionString opening a OleDbConnection. I get the following error:

The .Net Data Ole DB Provider(System.Data.OleDb) does not support the MSDASQL Provider, Microsoft OLE DB Provider for ODBC Drivers.

-----
My connectionString looks like this:

Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DBQ=G:\costumers\foretag.xls;DefaultDir=G:\costumers;Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;FILEDSN=G:\costumers\foretag.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=0;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

How do I do this?

Re: ODBC.NET and Excel

Paul Riley27-Aug-02 4:55

Paul Riley

27-Aug-02 4:55

The ODBC.NET installation includes a new namespace: Microsoft.Data.Odbc as opposed to System.Data.OleDb. You need to be opening an OdbcConnection instead of an OleDbConnection.

If you want to add this to your toolbox, right-click the Data toolbox, select "Customize Toolbox", go to the .NET Framework Components tab and check the boxes next to the Odbc... objects.

Paul

Retrieving QTY of records on a SQLClient

toki24-Aug-02 11:30

toki

24-Aug-02 11:30

Any way to get the quantity of records on a SqlDataReader object ?

<br />
SqlDataReader reader = sqlcommand.ExecuteReader();<br />
String[,] array = new String[  qty_of_rows_in_Select_Statement ,4];

Re: Retrieving QTY of records on a SQLClient

James T. Johnson24-Aug-02 13:50

James T. Johnson

24-Aug-02 13:50

Not until you've gone through all the rows returned by the reader.

This is because you are working with data as it is coming from SQL server and it doesn't know how many results are returned until it has sent them all to you.

James
"And we are all men; apart from the females." - Colin Davies

Re: Retrieving QTY of records on a SQLClient

Paul Ingles25-Aug-02 4:24

Paul Ingles

25-Aug-02 4:24

You can always execute a SELECT COUNT statement before the main statement, that will return the number of rows.

i.e. if your usual where clause is something like

SELECT * FROM OrderDetails WHERE CustomerID=59

You can get the number of rows by executing

SELECT COUNT(OrderID) FROM OrderDetails WHERE CustomerID=59

If you execute that using ExecuteScalar, you'll get the number of rows that will be returned by that query.

Incidentally, if there's a chance that this number could change in-between executing the two queries, or you just want to be extra careful, you'll want to execute both queries as part of the same transaction, ensuring that the number of records doesn't change as a result of someone else inserting/deleting records from the table.

Alternatively, you could consider taking the data to a DataSet first, since that's held in-memory you only execute the query once, and I'm pretty sure there must be a way to obtain the number of rows. This will have the usual advantages/disadvantages of using a DataSet as opposed to a DataReader.

--
Paul
"I need the secure packaging of Jockeys. My boys need a house!"
- Kramer, in "The Chinese Woman" episode of Seinfeld

MS Messenger: paul@oobaloo.co.uk
Sonork: 100.22446

Re: Retrieving QTY of records on a SQLClient

toki28-Aug-02 16:06

toki

28-Aug-02 16:06

Thanks, i solve the issue doing the following:

System.Data.SqlClient.SqlDataAdapter myDA = new System.Data.SqlClient.SqlDataAdapter(sqlcommand);<br />
System.Data.DataSet myDS = new System.Data.DataSet();<br />
String[,] array = new String[myDA.Fill(myDS),4]<br />
reader = sqlcommand.ExecuteReader();

but, i think that "select count" is a better idea because doesn't create the adapter and dataset and consume less processor.

Whats wrong with this picture?

Robby22-Aug-02 3:07

Robby

22-Aug-02 3:07

I am attempting to query every table in my database and put the results in a record set, format the results to a cell string in html and print it out. The problem is that it is displaying only the results from the first table it matches to. Am I mistaken in thinking that it combines all results into one RS? If not, how do I accomplish what I want to do? The premise is this: I have several user request forms that output to their respective tables in my SQL Sevrver 2k DB. All the forms have the columns listed in the SELECT stametment below. I want to query all these tables for anything matching STATUS='OPEN' and display the results in a user queue. I have eliminated the HTML from the user queue page just to show what function I am trying to accomplish. Any help is GREATLY appreciated! (I aplogize for the sloppyness of the CELLSTRING & "<>" has been replaced with "[]")

============================CODE===========================

Dim objRS
Dim strSQL

Set objRS = Server.CreateObject("ADODB.Recordset")

strSQL = "sp_MSforeachtable 'select submitDate, requestorLastName, requestorFirstName, urlToForm, nof, status from ?'"
objRS.Open strSQL, MyConn

if objRS.eof then
response.redirect "noQueue.asp"

else
do until objRS.eof

cellString = "[tr style='padding: 3'][td][font face='Arial' size='2' color='#CCCCCC'][center]" & objRS["submitDate"] & "[/center][td][font face='Arial' size='2' color='#FFFFFF'][center]" & objRS("requestorFirstName") & " " & objRS("requestorLastName") & "[/center][td][font face='Arial' size='2' color='#FFFFFF'][center]" & "[a href='" & objRS("urlToForm") & "'][font color='#66CCFF']" & objRS("nof") & "[/a]" & "[/center][/td][td][font face='Arial' size='2' color='#FFFFFF'][center]" & objRS("status") & "[/tr]"

response.write(cellString)
objRS.movenext
loop

end if

Re: Whats wrong with this picture?

notadood22-Aug-02 3:12

notadood

22-Aug-02 3:12

The procedure probably does not return one recordset. Your other option is to create a SQL statement using UNION and put the select in for each table name.

Re: Whats wfdgwith this picture?

Anonymous22-Aug-02 9:18

Anonymous

22-Aug-02 9:18

dgfdg

Re: Whats wrong with this picture?

Bruce Duncan22-Aug-02 9:34

Bruce Duncan

22-Aug-02 9:34

As notadood mentioned, the storedproc is most probably not returning a single resultset, but rather a compound Recordset object.

Take a look at Recordset.NextRecordset it may be what you're looking for.

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
'ugly naked women are good, when i'm not around, in front of someone else' - Shog⁹

DleDbDataAdapter and DSNs

thiggs21-Aug-02 4:02

thiggs

21-Aug-02 4:02

I'm trying to connect to an access database via a system DSN because that's the database connection method utilized by my web provider. However, when I use the data adapter's wizard to configure the object, I see no option to use a DSN as a datasource. I either have to specify a server name or in the case of my Access database, specify a file path.

Do I manually have to configure the dataadapter to use a dsn? How do I do that?

Thanx.

SQL - SELECT DISTINCT myfield

Tim Rymer20-Aug-02 4:59

Tim Rymer

20-Aug-02 4:59

Hey, is there any way when you select a distinct field, to get the rest of the fields in the table? Something like this is what i'm "wanting it to do":

aka: SELECT * DISTINCT myfield FROM MyTable WHERE isFunky = True

so is there any work around? i mean anything that you can do in vb6/access 2000? I dont know much about sql, just the basics, so if i can do anything obvious let me know, cuz truth is i probably dont know how to do it or wouldnt think of it.

~Timothy T. Rymer

http://tim.xpertz.com
http://www.digipen.edu
http://www.ttrx.com

Re: SQL - SELECT DISTINCT myfield

Mazdak20-Aug-02 9:02

Mazdak

20-Aug-02 9:02

I can't understand your question,its not clear but I know the statement you write is correct and work.

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

That doesnt work.

Tim Rymer20-Aug-02 9:48

Tim Rymer

20-Aug-02 9:48

Ok, everyone always sees tutorials on how to use "SELECT DISTINCT field" and then they name their field on which they want each record to have a different value in that field. So when you use just that statement, all you get is:

CUSTOMER_NAME
-----------------
Jimmy
Steve
Guido
Barry
Cloud
Kefka
Jonny
-----------------

I'm wondering how i can setup my Sql statement so that i can get the rest of that information with it, in vb6/access 2000.

"SELECT * DISTINCT url FROM Emails WHERE checked = False ORDER BY foundwith"

Passing this into a data object doesnt work, i want to be able get this in a recordset:

CUSTOMER_NAME | ID | CHECKED | FOUNDWITH | URL
------------------------------------------------
Jimmy | 45 | False | 4 | "http://www.web.com"
Cloud | 46 | False | 1 | "http://www.howdy.com"
Kefka | 47 | False | 1 | "http://www.studmuffin.com"
------------------------------------------------

So as long as everyone has been "CHECKED" from that url, then i dont want to look at that "URL".

Hope that explains my problem. Read my example above, does that look like it should work to you? i get

RunTimeError 3075: Missing operator in query expression (* DISTINCT url)

any reason why? thanks in advance.

~Timothy T. Rymer

http://tim.xpertz.com
http://www.digipen.edu
http://www.ttrx.com

Re: That doesnt work.

DanielO21-Aug-02 3:21

DanielO

21-Aug-02 3:21

hi,

you could try the following

SELECT DISTINCT CUSTOMER_NAME ID CHECKED FOUNDWITH
FROM Emails WHERE checked = False ORDER BY foundwith

for all fields except url

or

SELECT DISTINCT URL
FROM Emails WHERE checked = False ORDER BY foundwith

for url field only

you are right about "Select * Distinct .." won't work

Daniel O

Minor addition

Anonymous21-Aug-02 4:38

Anonymous

21-Aug-02 4:38

I vote for this answer. But i think you have to seperate field names with commas.

Re: That doesnt work.

Tim Rymer22-Aug-02 3:51

Tim Rymer

22-Aug-02 3:51

SELECT DISTINCT CustomerName, ID, Checked, FoundWith, URL

So you're saying that I will get every Record with a distinct URL with this statement? If so will the fact that it has checked(which may all be false at the beginning), or foundwith(which will more than likely have other urls to have the same FoundWith = # with them) so if i state all of those as having to be DISTINCT, does that mean it checks all before adding them? Because basically if there's a statement that says this:

SELECT DISTINCT URL, Checked FROM Emails WHERE Checked = False

Isnt that going to cause problems? because say they're all false, does that mean i'm to get only 1 urls back no matter what the difference in URLs?

And this statement,

DanielO wrote:
you could try the following

SELECT DISTINCT CUSTOMER_NAME ID CHECKED FOUNDWITH
FROM Emails WHERE checked = False ORDER BY foundwith

for all fields except url

I'm just trying to get one url that isnt checked, that's all. The only reason why i'm doing that is so that I dont have to open up another recordset and look for all Customers from that URL, then see if they've been checked or not, and if they've all been checked, then goto the next url.

This is all really just fickle, I just wanted to know if there was a way to get all the fields from a Record, where i had a DISTINCT Field that had to be different, then if i have that first distinct record, i could see if it had been checked, and if not then move onto the next one. *shrug*

Thanks guys.

~Timothy T. Rymer

http://tim.xpertz.com
http://www.digipen.edu
http://www.ttrx.com

Re: That doesnt work.

Mazdak21-Aug-02 4:25

Mazdak

21-Aug-02 4:25

Yes,* DISTINCT url is wrong.You can't use anything before DISTINCT expression.After DISTINCT you listed the column you want to be distincted and only they will be return.

So:
If you write:

SELECT DISTINCT * from Emails where checked=False

SELECT * from Emails where cheched=False

then only you will see same result .If you write:

SELECT DISTINCT url from Emails where checked=false

Then you will see only url column.

And this statement:

SELECT DISTINCT url,ID from Emails where CHECKED=false

Return url and ID column and their COMBINATION will be unique.

If you write:

SELECT * FROM Emails where Checked=false

you will get all columns that their Checked is false.You can't have DISTINCT expression here because if you have two rows with the same URL then which one should return?Maybe both of them have false record for CHECKED column.So as I understand the best statement for you is the last one.Why do you want DISTINCT here?

Hope that helps.If you need more help or information you are welcome. Smile | :)

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

Re: That doesnt work.

notadood26-Aug-02 7:44

notadood

26-Aug-02 7:44

If I am reading this thread correctly than you should be able to use the GROUP BY clause. It does the same thing as DISTINCT but uses a different format.

It would look something like:

SELECT CUSTOMER_NAME, ID, CHECKED, FOUNDWITH, URL
FROM Emails WHERE CHECKED= False
GROUP BY CUSTOMER_NAME, ID, CHECKED, FOUNDWITH, URL
ORDER BY FOUNDWITH

Check (create table) error

Hans Ruck20-Aug-02 0:40

Hans Ruck

20-Aug-02 0:40

Typing create table test.ooo ( a int check(a>0)); at the mysql prompt, i get an error message about using check constraint.
What's wrong?

rechi

sp_OAMethod type mismatch error

Le centriste19-Aug-02 10:36

Le centriste

19-Aug-02 10:36

I have a COM object method which has the following signature:

HRESULT MyMethod(LONG val1, BSTR val2);

When I call this method with the following syntax:

EXEC @hr = sp_OAMethod "MyMethod", NULL, 10, 'slkdfj'

I get a type mismatch error. Anyone have had this problem before?

Michel

It is a lovely language, but it takes a very long time to say anything in it, because we do not say anything in it, unless it is worth taking a very long time to say, and to listen to.

- TreeBeard

ODBC & SQL - Modify the "Table" name in Excel

Stefan_L_0119-Aug-02 10:32

Stefan_L_01

19-Aug-02 10:32

Hello

I´m new to this board, and found already some good advice here, but for this problem i didn´t find an answer so far.

I already have an Excel-Doc that consists of several sheets. I need to write now in the 3rd sheet data, from the first row to row 40 in column 2 for example.

The problem is that the row doesn´t has a header, what would identify the column as a "table". But that´s no problem, because i can fetch a Recordset to a column for example.

But this allows me only to modify row 2 and all further rows, but not the first row where ODBC expects the table name.

But i also have to modify the first row, i MUST Smile | :)

Does anyone know to access by ODBC & SQL a EXCEL sheet/data from c++ on and writing at desired column and row position data?

Thank you

Re: ODBC & SQL - Modify the "Table" name in Excel

DanielO21-Aug-02 3:44

DanielO

21-Aug-02 3:44

hi,

perhaps you could use automation execel

look at www.microsoft.com - knowledge base

HOWTO: Create Automation Project Using MFC and a Type Library (Q178749)
HOWTO: Use MFC to Automate Excel and Create/Format a New Workbook (Q179706)

hope it helps

Daniel O

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.