|
As Rob answered, the parameters are order-sensitive for OleDb* classes. So add the @news-id parameter again at the end, since you reference it at the end.
I just answered this more throughly on the ASP.NET forum, so please don't cross-post in different forums, it's considered bad manners here.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Hiya all.
I really hope you guys can help me with this question/problem.
First off - sorry for the lenght of the post. 
I have a productstructure which I'm trying to read out via SQL, using User Defined Functions.
Setting:
Basically - my structure in the database consists of product groups and products. A product can be sub-group to the parent, and have products connected.
So I have a Group A, which is a parent to Group A-1 which contains the product A-1p1, A-1p2 etc. (simplified, I think currently there is only 7 parent groups, which have a number of subgroups, which have yet another bunch of subgroups which have products connected them)
I use a function which returns a table of groups, given the parents id, so parent id = 0 will return all the root elements.
I also have a function which returns a table of products given the group id.
Then I have created this recursive function:
<small>CREATE FUNCTION fnGroupListByParentRecusive<br />
(<br />
@CurrentGroupID int,<br />
@NodePath nvarchar(2000),<br />
@LanguageCode nvarchar(5)<br />
)<br />
RETURNS @ProductStruct TABLE(NodePath nvarchar(2000), PROD_GroupID int, PROD_GroupName nvarchar(200), PROD_ProductID int, PROD_ProductName nvarchar(200))<br />
AS <br />
BEGIN <br />
<br />
-- DECLARE LOCAL VARIABLES --<br />
DECLARE @GroupID INT<br />
DECLARE @Group TABLE(PROD_GroupID int, PROD_GroupName nvarchar(200))<br />
DECLARE @Product TABLE(PROD_ProductID int, PROD_ProductName nvarchar(200), PROD_GroupID int)<br />
DECLARE @InternalNodePath nvarchar(2000)<br />
<br />
-- Get product group data<br />
INSERT @Group SELECT PROD_GroupID, PROD_GroupName FROM fnGroupListByParentID(@CurrentGroupID, @LanguageCode)<br />
<br />
-- DECLARE CURSOR --<br />
DECLARE myCursor CURSOR<br />
FOR <br />
SELECT PROD_GroupID FROM @Group<br />
OPEN myCursor<br />
<br />
FETCH NEXT FROM myCursor INTO @GroupID<br />
WHILE (@@FETCH_STATUS <> -1 AND @@FETCH_STATUS <> -2) BEGIN<br />
-- Get Product data<br />
INSERT @Product SELECT PROD_ProductID, PROD_ProductName, PROD_GroupID FROM fnProductListByGroupID(@GroupID,@LanguageCode)<br />
<br />
-- Set path <br />
SET @InternalNodePath = @NodePath + '/' + LTRIM((str(@GroupID)))<br />
<br />
-- Build ProductStructure Table<br />
INSERT @ProductStruct <br />
SELECT @InternalNodePath, T1.PROD_GroupID, T1.PROD_GroupName, T2.PROD_ProductID, T2.PROD_ProductName<br />
FROM @Group AS T1 LEFT JOIN @Product AS T2<br />
ON T1.PROD_GroupID = T2.PROD_GroupID <br />
<br />
-- Build ProductStructure table with Recursive data.<br />
INSERT @ProductStruct SELECT * FROM dbo.fnGroupListByParentRecusive(@GroupID, @InternalNodePath, @LanguageCode)<br />
<br />
-- Get Next cursor<br />
FETCH NEXT FROM myCursor INTO @GroupID<br />
END<br />
CLOSE myCursor<br />
DEALLOCATE myCursor<br />
RETURN<br />
END</small>
My problem is that this works somewhat.
I apparently get the results from the tables repeated a number of times. The way I figured this out was by view in the NodePath.
This path is the nodes to get the to current, inclusive the current nodes group id.
For instance - the 7 root elements would get the path 0/groupid, and these elements children would get 0/groupid/groupid2 etc.
However - say my Groups have IDs 1, 2, 3 I get the result
<br />
Path GroupID<br />
0/1 1<br />
0/1 2<br />
0/1 3<br />
0/2 1<br />
0/2 2<br />
0/2 3<br />
0/3 1<br />
0/3 2<br />
0/3 3<br />
Which is not what I wanted.
I wanted
<br />
0/1 1<br />
0/2 2<br />
0/3 3<br />
Now my question and problem is that I want to get from the first list to the second list.
I have an idea that the problem would be that some variables in my function are of global scope as opposed to what I'd expect from for instance a programming language, so each time I run
-snip-<br />
INSERT @ProductStruct <br />
SELECT @InternalNodePath, T1.PROD_GroupID, T1.PROD_GroupName, T2.PROD_ProductID, T2.PROD_ProductName<br />
FROM @Group AS T1 LEFT JOIN @Product AS T2<br />
ON T1.PROD_GroupID = T2.PROD_GroupID <br />
<br />
-- Build ProductStructure table with Recursive data.<br />
INSERT @ProductStruct SELECT * FROM dbo.fnGroupListByParentRecusive(@GroupID, @InternalNodePath, @LanguageCode)<br />
-snip-<br />
then it updates the same table, instead of using a function scoped table which it returns through my recursive call.
However - until today I've never even used userdefined functions, so my knowlegde of this area is very limited (I'm surprised I've gotten this far).
Can anybody help point me in the proper direction and possible tell me if my fear of scope is correct?
With regards and hopes.
Alsvha
---------------------------
127.0.0.1 - Sweet 127.0.0.1
|
|
|
|
|
Hallo,
I've opened a file from my oracle database via a OracleBlob format into a byte array and it works all very well, except that I would like it not to be saved on the c: drive
How can I do this ?
here is my code : ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//roep functie docDownloadBlob aan en lees document in.
//Response.Clear();
DataSet ds = new DataSet();
Attachements oAttachement = new Attachements();
//Geeft ds terug
oAttachement.USER_ID = int.Parse(Session["userid"].ToString());
oAttachement.DOC_ID = int.Parse(Request.QueryString["DOC_ID"].ToString());
string m = oAttachement.GetBlob();
byte[] MyData = new byte[0];
if (m == "OK")
{
MyData = oAttachement.ByteData;
}
int ArraySize = new int();
ArraySize = MyData.GetUpperBound(0);
String DestinationLoc = "C:/ViewFiles.txt" ;
System.IO.FileStream fs = new System.IO.FileStream(@DestinationLoc, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);
System.IO.FileStream fs = new System.IO.FileStream(@DestinationLoc, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Read);
fs.Write(MyData, 0, ArraySize);
StreamWriter sw = new StreamWriter(fs);
Response.AddHeader("Content-disposition", "attachment;filename=ViewFiles.txt");
Response.AddHeader("Content-Length", DestinationLoc.Length.ToString());
Response.ContentType = "application/txt";
Response.BinaryWrite(MyData);
fs.Close();
Can anyone help me with this please ?
tnx.
Cara
|
|
|
|
|
You may write the file to disk, but you don't do anything else with it. If you don't want it written to disk then just remove that code. Also, by writing the file to the disk in this way (especially to the root of your C drive) you've had to blow open a hole in the security of your application to allow it to write there.
You are also writing the bytes out to the Responce oject. In the past, I've just overloaded the Render method to do this work. It is much easier and, if I remember correctly, it passes you a HttpStream to write into.
By the way, you might get a better response to your question in the ASP.NET forum. Your question has little to do with ADO.NET (even although the data you are writing initially comes from a data source, your question relates to getting that data across to a browser)
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
OK tnx for the answer anyway. i'll keep in mind next time to stick to the point of ado.
|
|
|
|
|
I have a program I'm working with (VB  ) which accesses it's data via ODBC and an Access database. The Access database is currently in 97 format because I had certain strange problems when it was in Access 2002 format, namely updating records and then finding it had written blank fields to the database etc. These problems disappeared with a Access 97 file.
I've recently tried to let this program talk to a SQL server via ODBC which works perfectly, except that it displays the same anomalies as Access databases newer than 97. My suspicion is that the ODBC driver is outdated. Does anyone know if that is possibly the problem, and if so how I can update it. If not, what can it be?
TIA
Paul
modified 18-Jul-18 11:59am.
|
|
|
|
|
whats operatin system you use?
1.)Access 2002 is perfect than 97 no argument for it,this can be the link u using(ODBC)
2.)why u not use oledb connection string,its perfect work with vb and access,sql server
3.)how you update records? r u sure u not update any null value to fields
|
|
|
|
|
Windows XP SP2. The ODBC driver for SQL is version 2000.85.
1) huh?
2) yes, I'm considering changing that from the current method, which is using MS ADO data control 6.0
3) by binding text fields to the correct fields in the DB and calling adodc.Update, I've checked for null field entries, there aren't any, also this problem only occurs after the Primary Key has hit 400 (it appears  can't see anything else that's consistent on failure)
Paul
modified 18-Jul-18 11:59am.
|
|
|
|
|
this is smoething strange with adodc,
|
|
|
|
|
Tell me about it.
Paul
modified 18-Jul-18 11:59am.
|
|
|
|
|
Now I'm re-"coding" the app (;P) to use a ADODB.Recordset instead of one of those silly ADODC's. Seems to have solved the hassle.
Thanks anyway
Paul
modified 18-Jul-18 11:59am.
|
|
|
|
|
Hi
I have some records say 1000 Nos fetched from SQL and stored in a Dataset.
Now I need to fetch and store top 1-50 records in another dataset and top 51-100 records in another dtaset and so on.
Could any one help me how to transfer records from one Dataset to another dataset?
Regards
San
|
|
|
|
|
Create a DataView instead and use a row filter.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
i can't install sql 2005 beta 2 in my pc
it says .net frame work in corrupted or not installed but its running properly in my computer.
|
|
|
|
|
Hello,
We have several reports on the web that require printing. Now as I am sure all of you know it can be a pain to write all the code required to format the lines correctly so they dont wrap.
There is one report that I am thinking of that has caused me much pain in the past. It prints out all the operations and instructions to create a part. Well the operation descriptions can be quite long so I needed a way to accuratly print and count lines of text. I had written a class that would formatt the information breaking at the correct length for printing but the problem was the ammount of time it was costing me when the report would render. So I thought to my self.. Self.. wouldn't it be great if I had a function that would formatt the information for me so when I pulled it from the database all I had to do was display and count?
So I wrote a little function that does just that:
<br />
---------------------------------------------------------------------------<br />
CREATE FUNCTION F_FormatRevisionDescForPrint(@Desc VARCHAR(1000), @DisplayLenghOfString INT)<br />
RETURNS VARCHAR(2000)<br />
AS<br />
<br />
BEGIN<br />
DECLARE<br />
@CharacterCounter INT<br />
,@DescLen INT<br />
,@WhenToBreak INT<br />
,@FormattedDesc VARCHAR(2000)<br />
,@NewLine VARCHAR(2)<br />
,@Space VARCHAR(1)<br />
,@NextCharacter VARCHAR(1)<br />
,@PreviousCharacter VARCHAR(1)<br />
,@CurrentCharacter VARCHAR(1)<br />
,@NewString VARCHAR(2000)<br />
,@NumberOfCharactersToCut INT<br />
--<br />
SELECT @CharacterCounter = 0<br />
,@DescLen = LEN(@Desc)<br />
,@WhenToBreak = 0<br />
,@FormattedDesc = ''<br />
,@NewString = ''<br />
,@NewLine = CHAR(13) + CHAR(10)<br />
,@Space = ' '<br />
--<br />
IF (@DescLen > 0) BEGIN<br />
WHILE @CharacterCounter <= @DescLen BEGIN<br />
--<br />
SELECT @FormattedDesc = @FormattedDesc + SUBSTRING(@Desc,@CharacterCounter,1)<br />
,@CurrentCharacter = SUBSTRING(@Desc,@CharacterCounter,1)<br />
,@NextCharacter = SUBSTRING(@Desc,(@CharacterCounter+1),1)<br />
,@PreviousCharacter = SUBSTRING(@Desc,(@CharacterCounter-1),1)<br />
,@WhenToBreak = @WhenToBreak + 1<br />
--<br />
IF (@WhenToBreak = @DisplayLenghOfString) BEGIN<br />
-- I need to check and make sure that I am not breaking on<br />
-- a word.<br />
IF (@CurrentCharacter = @Space) BEGIN<br />
-- There is a space here so it is ok to break<br />
-- I know that I am not in the middle of a word<br />
SELECT @FormattedDesc = @FormattedDesc + '<BAR />'<br />
,@WhenToBreak = 0<br />
,@CharacterCounter = @CharacterCounter + 1<br />
-- <br />
END ELSE IF (@CurrentCharacter != @Space) BEGIN<br />
-- houston we have a problem I am somewhere in a word<br />
-- I have to go back in the string till I find a space.<br />
-- When I find a space I am going to remove the rest<br />
-- of the characters from the string.<br />
-- EX: String: A T<BR>OOL New String: A<BR> The word tool will be cut off.<br />
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX<br />
-- reverse the string that way it will be<br />
-- easier to find the space<br />
SET @NewString = REVERSE(@FormattedDesc)<br />
-- get the first index of a space<br />
SET @NumberOfCharactersToCut = PATINDEX('% %',@NewString)-1<br />
-- now reverse the string again so it is no longer backwords<br />
SET @NewString = REVERSE(@NewString)<br />
--<br />
-- get the new substring<br />
SELECT @FormattedDesc = '' + SUBSTRING(@NewString,0,(LEN(@NewString)-@NumberOfCharactersToCut)) + '<BAR />'<br />
,@WhenToBreak = 0<br />
,@CharacterCounter = @CharacterCounter-@NumberOfCharactersToCut<br />
END<br />
<br />
END ELSE BEGIN<br />
--<br />
SELECT @CharacterCounter = @CharacterCounter + 1<br />
--<br />
END<br />
END<br />
END ELSE BEGIN<br />
SET @FormattedDesc = ''<br />
END<br />
<br />
RETURN @FormattedDesc<br />
<br />
END<br />
-------------------------------------------------------------------------<br />
Example of use:
<br />
SELECT REPLACE(LTRIM(RTRIM(dbo.F_FormatRevisionDescForPrint(Routing_Revisions_Edit.[Description],47))),'<BAR />','<BR>') AS Description<br />
FROM MyTable<br />
--------------------------------------------------------------------------
Well any thoughts are welcome.
Thanks
Will
|
|
|
|
|
Hi all,
I am getting this error when tring to access my page hosted on fast hosts;
ERROR [42S02] [MySQL][ODBC 3.51 Driver][mysqld-4.1.11-standard]Table 'touchofglass.products' doesn't exist
Now I have changed the products table to 'products' so that the table name is correct but it comes back with the error;
ERROR [42S02] [MySQL][ODBC 3.51 Driver][mysqld-4.1.11-standard]Table 'touchofglass.Products' doesn't exist
WOW, any body have any ideas on this, coz this one has completly thrown me!
I know that tables in MYSQL are case sensitive, but i cant think of anything else to try?
Cheers in advance
Stu
|
|
|
|
|
If a database is dettached and its log file (.ldf) has been deleted,
I am trying to attach this database again , but i am facing some problems.
Do someone have a solution for me ?
|
|
|
|
|
The command is sp_attach_db. Example:
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
I prefer using a backup/restore approach.
|
|
|
|
|
Thank you my friend,
but my problem is that the Log.ldf file is not found (deleted), so @filename2 is invalid.
Do you have another way ?
|
|
|
|
|
Hello,
I am trying to insert a record in an Access database using Asp.Net/Vb.Net.
I am getting the error: "Operation must use an updateable query."
How can I solve this problem?
The code I am using is:
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New OleDbConnection(connectionString)
' Set Query
Dim queryString As String = "INSERT INTO [t_news] ([title_pt-PT], [title_en-GB], [text_pt-PT], [text_en-GB], [publication_date]) VALUES (@title_ptPT, @title_enGB, @text_ptPT, @text_enGB, @publication_date)"
' Set Command
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@title_ptPT", "title PT"))
.Add(New OleDbParameter("@title_enGB", "title GB"))
.Add(New OleDbParameter("@text_ptPT", "text PT"))
.Add(New OleDbParameter("@text_enGB", "text GB"))
.Add(New OleDbParameter("@publication_date", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
' Insert New Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
Thank You,
Miguel
|
|
|
|
|
I have created 1 file browse btn from where i am alowing to select the file which has to be uploaded to sql server.. Now i have the Excel file path..
How do i access the file. through Open data source..
I used. sql statement
"select * into abc_data from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=" & vfilename & "; Extended Properties=Excel 8.0') ...[sheet1$]"
where vfilename is the Excel File Path incl filename.xls.
But it is giving error.. Can anyone help..
Thanks,
Prasanna
|
|
|
|
|
Hello guys!!
What is the minimum time (in ms) required to respond from sql server when data is near @ 1.2 GB
and also where i will get some kind of utility or sql command where i will find
1. Check the space allocated to Database
2. Check the total space remaining in database
1. Check Memory Usage of MSSQL
2. Check CPU Utilization
3. Check Disk contention
1. Use SQL Server profiler
2. Check Trace event, lock event, session event etc
3. Create Trace
1 . Check for memory utilization
2. Check for unwanted process utilizing Memory
3. Check database memory utilization
these factors
T.I.A
Shashank
|
|
|
|
|
Hello
thanks
I checked it with on BOL and got query
SELECT * FROM MASTER..SYSPROCESSES
i got it just few more things i want to clear
1) waittime
2)cpu
3)physical_io
4)memusage
what are units of these columns i.e waittime is in MS (miliseconds or else),
like that cpu?,physical_io? and memusage (i.e in MB or else)
T.I.A
shashank
|
|
|
|
|
Hi all,
I have a Date Format Problem with SQL Server 2000. the Default Date format of Sql server 2000 is MM/DD/YYYY , but i wanna DD/MM/YYYY , So how can i change the Date Format in SQL Server...
Thanx in advance
Sajjad
|
|
|
|
|
I have started storing at as text and casting it to correct date format in the stored proceedures as needed. I find that gets rid of any of the problems with dates which could go either way eg 03/04/2005.
Jon
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
