|
Thanks. I finally found the solution. It turns out that it was something simple, but I wouldn't have known it without digging through the endless dbConnect values in the debugger. In essence, I was doing the following:
dbCommand = new OdbcCommand(Query, dbConnect);
dbCommand.CommandTimeout = 300;
dbAdapter = new OdbcDataAdapter();
dbData = new DataSet();
dbAdapter.SelectCommand = dbCommand;
dbAdapter.Fill(dbData,"Play");
What I found, after going through the 'dbCommand' parameters in debug, is that the
'dbAdapter.SelectCommand = dbCommand'
RESETS THE TIMEOUT VALUE BACK TO THE DEFAULT!!! This is why it would ignore any timeout values I set. AAAARRRRGGGGHHHH!!!! So, you MUST set the timeout AFTER calling that line, preferrably just before the "Fill()" call. When I did this...
dbCommand = new OdbcCommand(Query, dbConnect);
dbAdapter = new OdbcDataAdapter();
dbData = new DataSet();
dbAdapter.SelectCommand = dbCommand;
dbCommand.CommandTimeout = 300;
dbAdapter.Fill(dbData,"Play");
it worked fine. No timeout whatsoever. GEEEEZZZZEEE!! It even worked great setting a timeout of 90, but 60 timed out. Microsoft should not reset values like this. Putting initial values should be left up to constructors and explicitly called initializers ONLY!!!
I have noticed other people on this board complaining of the same problem. They were probably doing the very same thing as I was.
|
|
|
|
|
Hi,
table containing two fields one is code and another is name. i need to fetch repeated names only.(more than one occurance of name and code is not same). any one help to me. thanks in advance.
Have A Nice Day!
Murali.M
|
|
|
|
|
SELECT name
FROM table
GROUP BY name
HAVING COUNT(name)>1
Cheers,
_____________
stefan bornuz
|
|
|
|
|
hi stefan,
thanks for ur help.
Have A Nice Day!
Murali.M
|
|
|
|
|
in C# /w asp.net using SQL... (which forum, lol)
i have a dataset that i bind to a html datagrid, is there anyway i can manipulate the data in between the grab from the database, and the posting into the table?
i just want to do some simple replaces... to replace some strings with images, etc... things i can't do with simple formatting... this is NOT for posting back to the db...
|
|
|
|
|
I feel your question is not clear enough to tell us your requirement. Can you explain it bit more detail?
If I understand your requirement correctly, I feel you can use template columns in which you can have the images.
|
|
|
|
|
the data in the database in the columns i want to "interject" is signed integers.... i want negetive integers to have a upwards arrow (html img tag) replace the negative sign, and positive integers to have a downwards facing arrow in front of the number
|
|
|
|
|
Hi,
Sure can! Your easiest solution is probably to add a column to your DataTable and set an Expression on that column. Here's code snippet:
<code><pre>
DataColumn DC=new DataColumn("strDisplay");
DS.Tables[0].Columns.Add (DC);
DS.Tables[0].Columns["strDisplay"].Expression = "IIF(iIndex<0,'your downarrow html' + -1*iIndex,'your up arrow html'+iIndex)";
</pre></code>
Check out the VS.NET help for DataColumn.Expression
I am assuming 1) that you only have one DataTable in your DataSet 2) that the column of intergers on which you wish to operate is named "iIndex".
Then you just display the content of the new column, "strDisplay", in your DataGrid.
Hope this helps,
Bill
|
|
|
|
|
|
how can i pass a null value to PutElement()?
|
|
|
|
|
I am creating a 3 level tree menu using dataset. I have an sql statement that gets the parent of the tree.. but how do i loop the dataset and re-query the database to associate each parent to it's child? I am coding in c#.
String strConn="...";
string strSQL = "SELECT TabName FROM rb_Tabs WHERE ParentTabID IS NOT NULL AND TabID='" + tabid + "' OR ParentTabID='"+ tabid + "'";
SqlConnection objConn = new SqlConnection(strConn);
objConn.Open();
DataSet objDS = new DataSet();
SqlDataAdapter daSuppliers = new SqlDataAdapter(strSQL,objConn);
daSuppliers.Fill(objDS, "rb_Tabs");
objConn.Close();
TreeNode nodeSupp, nodeProd;
foreach(DataRow rowSupp in objDS.Tables["rb_Tabs"].Rows)
{
nodeSupp = new TreeNode();
nodeSupp.Text = rowSupp["TabName"].ToString();
Treeview1.Nodes.Add(nodeSupp);
}
objDS.Dispose();
daSuppliers.Dispose();
objConn.Close();
objConn.Dispose();
Laine
|
|
|
|
|
When I try to execute an SqlCommand (even the shortest and very simple), the application always throws an exception: "VNSAPI32.dll is missing. Reinstalling the product may solve the problem." I don't know what Windows means by "product", 'cos I've re-installed the IDE, but nothing changed. The same after creating a new instance of SQL server. I don't want to suffer with reconfiguring XP; so is there a solution for this?
|
|
|
|
|
It's trying to find the Banyan VINES network library - it appears that some SQL Server and MDAC updates enable this when it shouldn't be.
Run cliconfg.exe and disable Banyan VINES by selecting it in the right-hand box ('Enabled Protocols') and clicking Disable.
|
|
|
|
|
Hi there I habve a problem with a query on two relations
I have two relations here:
RESERVED_LEGS
PNR|FLNO|FLDATE|RSRVDATE
And
Tickets_Purchased
TKTNO|CLASS|DATEPURCHASE|PRICE|LEGID|PNR|WLR
this is a simple flight reservation scenario.
-When a passenger buys a ticket an entry is made in the tickets
purchased relation
- and if he makes a booking on that relation the ticket number is
associated with an autogenerated PNR value (other wise null if no reservation is made on ticket) and updated in the
tickets_PURCHASED relation
- AND LIKEWISE an entry with the correposnding PNR is made in the
RESERVED_LEGS TABLE where details of the flight number and date are
added.
There are 2 classes of tickets here namely
-first
-Third
The problem is that I have to make a query which shows the number of
resercvations made (in this case entries in the Reserved_Legs table) on
the basis of class of tickets upon which reservations were made as well
as broke up into months i.e.
------------------------------------------------
|Class |Jan|Feb|March|April|May|June|July|Aug.....
------------------------------------------------
------------------------------------------------
|First |130|500|320 |112 |120|100 |145 |145|
------------------------------------------------
|Third |402|180|110 |510 |410|110 |128 |162|
------------------------------------------------
The thing is that I'm not sure how this can be accomplished by an SQL
query, I've been hacking at it for a while and still have no luck. I
tried some queries but the thing is that at anytime there is no
guarantee that there will be a ticket of all teh classes booked in all
the months.
What would be the best way to do this. Any SQL that can create the
above relation or is any PL required. I'm doing this for a project and
I'm using SQL server 2000  and would like any suggestions.
|
|
|
|
|
Ah, the old cross-tab problem. Quite tricky in SQL Server 2000.
One technique that I've had success with is to define a temporary table containing all the months of interest:
DECLARE @months TABLE
(
MonthNum int NOT NULL
)
INSERT INTO @months
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 You also need a table for categories:
DECLARE @classes TABLE
(
ClassName varchar(5) NOT NULL
)
INSERT INTO @classes
SELECT 'First' UNION ALL
SELECT 'Third' You can then join these tables onto your data to get an output that looks something like this:
Class | Month | Reservations
-----------------------------
First | Jan | 130
First | Feb | 500
First | Mar | 320
First | Apr | 112
...
First | Dec | count
Third | Jan | count
Third | Feb | count
...
Third | Dec | count You'll then need to juggle the results into the appropriate output format in the client application.
To give you a bit of a start, you might count the number of tickets issued in each month like so:
SELECT
ClassName AS Class,
DATENAME(month, DATEADD(m, MonthNum - 1, '20040101')) AS Month,
COUNT(TKTNO) AS [Count]
FROM
Tickets_Purchased TP
RIGHT JOIN
(
SELECT ClassName, MonthNum
FROM @classes CROSS JOIN @months
) Crit
ON TP.CLASS = Crit.ClassName AND MONTH(TP.DATEPURCHASE) = Crit.MonthNum
GROUP BY
ClassName, MonthNum
ORDER BY
ClassName, MonthNumI'm sure you can work out the rest from here.
|
|
|
|
|
This is quite complex, but I'm gonna give it a try
You can probably do this in two ways (without using a temporary table, that is).
1. If you're fine with the result being shown vertically:
SELECT CLASS, DATENAME([month], RSRVDATE) AS Month, COUNT(*) AS Total<br />
FROM RESERVED_LEGS <br />
INNER JOIN Tickets_Purchased ON RESERVED_LEGS.PNR = Tickets_Purchased.TKTNO<br />
GROUP BY CLASS, MONTH(RSRVDATE)<br />
ORDER BY CLASS, MONTH(RSRVDATE)
This should return something like:
CLASS Month Total
First January 100
First February 150
...
2. If you want the result to be shown horizontally (this is more difficult):
SELECT CLASS, <br />
(SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 1) AND TP.CLASS = TicketsPurchased.CLASS) AS Jan, <br />
(SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 2) AND TP.CLASS = TicketsPurchased.CLASS) AS Feb,<br />
(SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 3) AND TP.CLASS = TicketsPurchased.CLASS) AS Mar,<br />
...<br />
...<br />
...<br />
FROM Tickets_Purchased<br />
GROUP BY CLASS<br />
ORDER BY CLASS
You can always use the query from #1 and format it to present it the way you want it to be, as I think it will be the most efficient query from both.
I hope it helps 
PS: You might want to tweak the SQL queries a bit to make it work
PPS: Maybe there is a more efficient way to do #2. Anyone?
Edbert P.
Sydney, Australia.
|
|
|
|
|
i am very new to SQL, and my first project has been a pretty big undertaking... i am grabbing a set of textfiles from a webserver, parsing them, and doing a number of calculations, etc on them, and storing the result in SQL, this is the main load on SQL... other loads are nearly irrelevant and are always simple selects with few joins.
i am having the problem that the CPU is getting toasted, and i know there is plenty of room for optimization in my (lengthy) procedure, which is being supplied to sql pretty much with no overhead (the parsing program uses very little cpu power and during an update always has a queue ready)
so if you see anything at a glance that can speed it up please help!
(i have guessed that having no extra indexes for this database is probably for the best?)
Method Caller (C#) from web downloader
Method (C#) i have a class that manages all the sql operations for the program
SQL Stored Procedure (SQL) huge thing that is CPU bound...
any hints are more than appreciated... this is for a project i intend to be release as code for anyone to use... but first i need to get it running at a decent speed...
if i did all the processing in .net and dumped it into a txt, and BCP'd it in, would it be faster?
i would still need many of the database operations there.
-Luke
|
|
|
|
|
One thing you may want to try is using a dataset and dataadapter. Read your files and fill in the dataset, then call the DataAdapter.Update rather doing the ExecuteNonQuery for every line read.
|
|
|
|
|
any thoughts on the size of the data set i should use?
a typical update would probably be around 15-40k new/modified entries in [user_history]
perhaps i will try using a 1/4 of the system memory to start...
thanks, wether or not its will be an improvement will be interesting, as i still need a lot of if-exists... i guess it depends what factor is causing the cpu to remain at high load...
i also have the option of switching the server... from a single Athlon XP 2500+ to a dual 900Mhz Xeon... i think staying with this machine is for the best...
|
|
|
|
|
I have a table as given below
1 5 57 &Prosjekter
3 5 57 &Planning
3 5 122 &Proposal
3 5 232 &Preventive
3 5 233 &Paste Ctrl+V
I want this in row wise
select a,b,c,d,e,f where queryname
and column a=&Prosjekter
and column b=&Planning etc
I need a query how i can get this done.
Thank you
with regards
vimal
Help in need is the help indeed
|
|
|
|
|
SELECT a,b,c,d,e,f FROM tablenamw WHERE a='&Prosjekter' AND b='Planning'
Mazy
You're face to face,
With the man who sold the world - David Bowie
|
|
|
|
|
To maintain data integrity in an n-tier application I'm storing the layout of an SQL table within XML dataset schemas.
I have managed to get the two to compare with one another, to check that the specified database is valid for the application. However, I don't know my way around ADO.net enough to create a new table based on the XDS, which is what I'd like to do next.
Any help would be greatly appreciated.
|
|
|
|
|
Hi!
I m working on an IS system being developed in asp.net using C#.
Now my problem is that i have 2 tables namely
USER1 [having fields like UserID, FName, LName, .....] and EMPLOYEE[having fields like Employee_ID, Job_Title1, Industry_Sector1, Industry_Sector2.....]
Employee_ID is the Foriegn Key for UserID in User1 Table.
now i take the join as:
string query="Select FName, LaName, Job_Title1, Industry_Sector1 from User1 u, Employee e where u.UserID=e. Employee_ID and e.Industry_Sector1='"+Human Resourses+"'";
then after establishing the connection i have my code as:
SqlCommand mycommand =new SqlCommand(query, MyConnection);
datagridEmployee.DataSource=Command.ExecuteReader();
datagridEmployee.DataBind();
....
now i get the following error.
A Field or prperty with the name "Employee_ID" was not found on the selected source.
Does any on have the solution,. I mean can a datahrid support more than onetables data at a time? or whts the problem??
waiting 4 reply
|
|
|
|
|
You didn't add EMPLOYEE_ID in your query then you use it somewhere i your code, so tthis error is obvious, so add this column to your query too.
Mazy
You're face to face,
With the man who sold the world - David Bowie
|
|
|
|
|
Hi Folks !
i want to share my problem with u hope u will solve it....
i have a datacombo attached to ADO (Database Table).
1. I have coded the function for the combo on 'change' as follows to run the query on the ADO.
[code]
Private Sub cmbOdr_Change()
ADO_Ven.Recordset.Close
ADO_Ven.Recordset.Open "SELECT * FROM VENDOR WHERE VENID IN (SELECT VENID FROM PROD WHERE PRODID IN (SELECT PRODID FROM BYR_ODR_DTL WHERE ORDERNO ='" & cmbOdr.BoundText & _
"' AND [DATE] IS NULL))"
ADO_Ven.Recordset.Requery
CmbVen.Text = ""
End Sub
[/code]
i receive an error Msg...
Run-time error '91':
Object variable or With block variable not set
but when i code it in the click event of the combo "cmbOdr"
the code works fine......
2. Similar kind of Problem is with another piece of code ,
which i code on the button click event
[code]
Private Sub Command3_Click()
ADO_ByrOdrDtl.Recordset.Close
ADO_ByrOdrDtl.Recordset.Open "INSERT INTO BYR_ODR_DTL( [DATE] ) VALUES ( txtDate.Text )WHERE ORDERNO ='" & cmbOdr.BoundText & "'"
End Sub
[/code]
the abvoe mentioned Error Msg is Generated......
Plz tell me how to avoid this error...
Thanx in Advance.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
