|
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, MonthNum I'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.
|
|
|
|
|
The code looks suspiciously like VB6
I would guess it is caused by the Recordset.Close line.
The first time you run the code, it will throw an error as the Recordset object has not been created and you try to call the Close function.
That's what the error "Object variable or With block variable not set" means.
The second time you run it, the code will work as the Recordset object has been created.
Try to check whether the Recordset object exists before calling the Close function. Better yet, always call Close RIGHT AFTER you open a recordset and use the data.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Any idea how you can retrieve a list of tables within a database using ADO.net?
|
|
|
|
|
If your database is SQLServer look for INFOMATION_SCHEMA in SQLServer book , and if you use Access seach this forum, this was answered so many times these days.
Mazy
You're face to face,
With the man who sold the world - David Bowie
|
|
|
|
|
I posted this about 5 posts ago.
http://www.codeproject.com/script/comments/forums.asp?msg=739476&forumid=1725#xx739476xx
Type = 'U' are tables and Type = 'V' are Views
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
can anyone please tell me how I can have a form in MS access 2003 with Slave master relationship. I mean just a form where I can input and edit data in two different related tables at the same time.
does it make sense?
I hope it makes sense to you.
Thank brother or sister!
|
|
|
|
|
It's been a while, like 6 years if you are refering to a subform. If so, check out the help or have the wizard create one for you.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Is there a way instead of using SELECT TOP 10
to say something like:
SELECT MID 11 10 or something, so it starts at the 11th record and takes 10 records after it.
This way I can easily put my data into pages, straight from the database, with 10 records on each page. I would also need to be able to first order them and then take 10 records starting from some nth record.
Any suggestions, returning 2000 records when i only display 10 seems very wastefull.
Thanks.
http://www.codeproject.com/aspnet/custompaging.asp
I found this link here, but this seems very inefficient as well. It creates a temp table and still selects all records and inserts them into the temp table. Regarding this way... does the # in front of the table name mean it gets dropped when the stored procedure is done running? The Author never drops it, and so if multiple people called this function at the same time, what happens to the temp table? Never seen the pound sign # in front of a table before.
Thanks some more.
|
|
|
|
|
How about:
SELECT TOP 10 field1, field2 ORDER BY field1 WHERE field2 IS BETWEEN 'VALUE1' AND 'VALUE2'
...hey that could even be a stored procedure, where you pass VALUE1 and VALUE2 as params...
|
|
|
|
|
but i have no idea what value1 and value2 would be. Maybe I have an auto-increment PK but that won't work because if some rows get deleted, then you dont have a range of 10 records knowing that a multiple of 10 records was skipped.
This wont work.
I modified my question, see the bottom of the original question, i added an example i found.
Thanks anyways.
|
|
|
|
|
Doing a SELECT TOP N ... without a WHERE and ORDER BY clause is kind of trivial, and you shouldn't rely on an id or auto-increment field in an ORDER BY, as it is no guarantee of the chonological order in which records are added to a table. You should work with other fields, such as datetime fields, foreign key fields, etc.
Another question: Are you referring to paging through a DataSet using ASP.NET? If so, there are plenty of examples and articles out there on data paging and controls like the DataGrid control.
|
|
|
|
|
when i initialize a connection string in my ASP page to connect to SQL Server 2000 Database as follows:
ConnStr = "Provider=SQLOLEDB; Data Source=DB1; Initial Catalog=MYDB; User ID=sa; Password=0000;"
===========================================================
Then I use the open method to open the connection for the Database as follows:
connStr.open 'This is Line 20
===========================================================
An error is shown as follows:
Error Type:
(0x80040E4D) Line 20
===========================================================
What is the error in this case?
Coz no error is specified. I'm waiting for your help.
Sam2004
|
|
|
|