|
I use the .type to extrract the type of the field in the database. It works fine....
Thanks to.. the UTE[^].. where I got this idea
I was born intelligent
Education ruined me!.
|
|
|
|
|
I have a query which returns a recordset for a statement which is displayed in ASP. It works very nicely - except for one thing. I suddenly noticed that although my
if rstDisplay.BOF or rstDisplay.EOF then - display error msg
else - display statement
works really well unless the firm has never had a single transaction (iow. if it has had transactions, just not within the current requested dates). The page then errors. After investigation I have found that it is because of fields sitting in the background which have been returned (eg. the firm name does exist in one of the tables, so that field is hiding in the background somewhere)
so the following returns:
Response.Write "state: " & rstDisplay.State -Displays "1"
Response.Write "count: " & rstDisplay.Fields.count -Displays "18"
so now I want to play with joins in query analyser. I am using @@rowcount to see how many rows are displayed, but don't know what to use to show the fields (like asp .fields.count). Can anyone enlighten me please?
Thanks 
I knew it would end badly when I first met Chris in a Canberra alleyway and he said 'try some-it won't hurt you'... -Christian Graus on Code Project outages
His thoughts tumbled in his head, making and breaking alliances like underpants in a tumble dryer.
It hurt the way your tongue hurts after you accidentally staple it to he wall-Shaun Wilde
|
|
|
|
|
Megan,
If you are using Query Analyzer, it should show you the number of rows returned in the lower right hand corner without having to use rowcount. Under the results tab, there should be a boxes that show the following:
General Message, Server Name, User Name, Database, Execution Time, Number of Rows, and Caret Position
Additionally, if you want to see the @@Rowcount Variable in Query Analyzer, you mut declare a variable, set the variable = @@Rowcount and then print the variable.
<br />
DECLARE @myRows Int<br />
Select * from myTable<br />
Set @myRows = @@RowCount<br />
PRINT @myRows<br />
Hope this helps.
Jeremy
Jeremy Oldham
|
|
|
|
|
Hi,
Thanks - that's exactly the problem. The rowcount returned is 0. But if I click on the messages tab there appears to be an empty row there (I can go up to an empty line with my arrow keys). There is actually no row returned that meets my criteria, but info does exist that does meet some of the criteria, but not all (perhaps I should use a join instead of "and"?) and so the recordset is returned empty.
It's all very odd, but I'm probably just doing something silly.
Thanks again
I knew it would end badly when I first met Chris in a Canberra alleyway and he said 'try some-it won't hurt you'... -Christian Graus on Code Project outages
His thoughts tumbled in his head, making and breaking alliances like underpants in a tumble dryer.
It hurt the way your tongue hurts after you accidentally staple it to he wall-Shaun Wilde
|
|
|
|
|
Megan,
If you can show me your SQL, sample data in the table, and the result you expect, maybe I can help.
Let me know.
Jeremy
Jeremy Oldham
|
|
|
|
|
Hi,
 Thanks for all the help - I've fixed the problem.
I knew it would end badly when I first met Chris in a Canberra alleyway and he said 'try some-it won't hurt you'... -Christian Graus on Code Project outages
His thoughts tumbled in his head, making and breaking alliances like underpants in a tumble dryer.
It hurt the way your tongue hurts after you accidentally staple it to he wall-Shaun Wilde
|
|
|
|
|
Megan,
When you run a query in QA here's what happens.
SQL Server sends back as part of the data stream, a header, data rows and the footer. The header contains the column information as to the number of columns, column names, and data types. The next section in the data stream is the actual row data and the footer contains the rowcount information.
So if you issued a query "SELECT * FROM authors WHERE 1=0" you would get a header with the column descriptions, no row data, and a footer that says "0 rows affected".
So when you are running your query, sql server is building the datastream and returning it to you but due to your join there are not any qualifying records, so you don't see any data.
|
|
|
|
|
It sounds like you want to return the firm regardless of whether they have any orders or not. if this is the case then you need to do an outer join between the firm table and the order table so that you guarantee yourself that you always get the firm data, but then all the order data would be null if there aren't any matches.
SELECT f.*, o.*
FROM firm f
LEFT OUTER JOIN order o on (
f.firm_id = o.firm_id
)
But if you post a sample of the query you're trying to run and the results that you want, I'll be better able to give you a solution.
You don't have to post the full query, just enough to get the general principle.
|
|
|
|
|
I use access database. I use ASP page to communicate with my access database. Is there any way to find the list of 'tables' in my access database from ASP or using jscript??
Thanks
I was born intelligent
Education ruined me!.
|
|
|
|
|
Sure, using ADODB you can.
Have a look at the MSysObjects[^] table in Access.
It is very similar to the Sysobjects ,et al tables in SQL.
HTH
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
|
Let me check.... Thanks
I was born intelligent
Education ruined me!.
|
|
|
|
|
|
I was born intelligent
Education ruined me!.
|
|
|
|
|
I have created a simple C# ASP.NET app, that uses a database(SQL) which is programed only to check users againsit a the database entry. when I try to run the program, I get to the server page, and than when I enter the user password, than I press invoke. however I just get a "The page can not be displayed - There is a problem with the page you are trying to reach and it cannot be displayed." error message. To my knowledge I have programmed this correctly, but can't seem to get pass this error. The below code is all I'm doing:
[WebService(Name="TestWebServer Web Service", Description="Test SQL Database")]
public class Service1 : System.Web.Services.WebService
{
const string connStr="server=SILICON;uid=sa;pwd=;database=MytestDatabase";
[WebMethod(Description="Method to Authenticate Users")]
public bool Authenticate(string username, string password)
{
SqlConnection dbConn = new SqlConnection(connStr);
string sqlStr = "SELECT Password from PM_USERS where UserName = '" + username + "';";
dbConn.Open();
SqlCommand dbCommand = new SqlCommand(sqlStr,dbConn);
SqlDataReader dbReader = dbCommand.ExecuteReader();
bool returnBool;
if (dbReader.Read())
{
if (dbReader[0].ToString()==password)
{
returnBool = true;
}
else
{
returnBool = false;
}
}
else
{
returnBool=false;
}
dbReader.Close();
dbConn.Close();
return returnBool;
}
that's pretty much it! 
|
|
|
|
|
cross posting!!
I was born intelligent
Education ruined me!.
|
|
|
|
|
Here is my problem.
My code reveives a ADO recordset. I have no control over the cursor types etc on this recordset. I need to copy this recordset to another Recordset. The new recordset is not connected to any database or any source.
Then I am to do stuff to the data in the new recordset, delete some of the rows and then pass the recordset on to a module that expects a Recordset as input.
How do I create a recordset, and then copy data from an existing recordset to this one. Each time I try some code it blows up. Is it even possible?
I tried something like:
while not eof
localRecordset.AddNew //It blows up here if the recordset is not open.
If I do recordset.Open //Then I do not know what parameters to open it with
Thanks
|
|
|
|
|
Hi..
Use the method Clone of the recordset object to copy a recordset into another..
Set rs2 = rs1.Clone (LockType)
|
|
|
|
|
My users will be uploading a comma-delimited text file that I need to check for format (I do not have control over the file they create) and consistency with my existing data. If the file passes the checks I will import the file into the database otherwise, I'll tell them to go back, fix the file and try again.
I can upload the file, perform the checks (once it's in a table) and report the errors. Here's the question.
How do I import a tex file of an unknown format (what I need to check)
Since this is web-based how do I accomodate multiple-users (i.e. I can't import into one temp file 'cause users will step all over each other)
Thanks
David
|
|
|
|
|
You might want to use DTS (distributed transaction server) to do a bulk insert from your file. You can even set DTS to run at a later point in time too. Check the SQL Server documentation, I don't remember exactly how DTS works, but there is a VBScript object for it.
|
|
|
|
|
I agree, I could use either a BULK INSERT or DTS - if I knew that the uploaded file was going to be in the proper format (i.e. # of columns, datatypes etc.) I beleive for either method we need to stipulate the format. However, since this is what I need to check and I want to tell the user the errors in the file (i.e. You file does not contain the required field "x" or the required field "x" must be of datatype "y")I need a method that will import the data into a table regardless of the format, so that I can check it.
I'm thinking of writing an XML file from the comma-delimited upload. Any comments
|
|
|
|
|
If you can't verify that the data has the proper column delimiters and the proper row delimiters then you don't really have a data file, just a text file. You will need to parse it yourself to verify that validity of the data.
You could just import each row into a TEXT field in SQL Server and try to parse the data yourself, but you don't know if the user put the proper row delimiter in the file before uploading it, so you may have incorrect data in this TEXT field.
Your best bet would be to write a parser yourself and parse the file looking for all the propery column delimiters and row delimiters and report the errors as you find them.
If you want to write your own bulk copy program, look at the IRowsetFastLoad interface if you are using OLE-DB, or if your going to program in ODBC, look at the bcp_* functions in the SQL Books Online.
|
|
|
|
|
Hi,
I use VB.NET to access SqlServer 2000 arabic Database.
I wrote a query for searching customers having names contain some arabic characters as follows :
Select * from client where contains(cust_name,'أحمد')
it's not working. I even test it under SqlServer Query Analyser also it's not working.
i tried the same query with the (Like "%") comparison, no result.
i tried ltrim(rtrim(cut_name)) but also no result.
I attract your attention that :
- The type of Cut_Name Field is nvarchar(50).
- The table is full-Text Indexed on cust_name field
does someone have an idea?
Thank you very much.
* *
*
* *
|
|
|
|
|
Just a guess:
Change Collation of your database or that specific column to one of Arabic options.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Hi..
I entered arabic data in a column in an existing database and and searched it using
like operator.. it worked..
the column was nchar(50) but I did not try full search..
maybe the problem is the colations installed.. but collation should affect
only char or varchar columns..
So it is a strange problem..
but anyway try adding Arabic collation..This may fix the problem..
|
|
|
|
|
I have a table which requires records to be deleted programmatically at time intrvals.
I have an created field which stores the time it was created and I can't figure out how to delete records older than 3 hours.
ie:
DELETE FROM temp WHERE created < now()+(3600*3)
I'm using mysql BTW, but I don't think SQL or SQL would have much discrepancies here would they?
Thanks for you time
"Two wrongs don't make a right, but three lefts do!" - Alex Barylski
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
