Database

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

9-Jul-05 21:29

HI all,
I have a table say table1 in which i have column1,column2,column3

I have a variable var1.

On the basis of value in var1 i wana fetch the data.

Like
if var1=1 then I wana select value from column1
if var1=2 then I wana select value from column2

can anyone please suggest the sqlserver2000 query for this.

thanks
Smile | :)

Re: sql query

Colin Angus Mackay9-Jul-05 21:34

Colin Angus Mackay

9-Jul-05 21:34

IF @var1 = 1
BEGIN
SELECT column1 FROM table1
END
IF @var1 = 2
BEGIN
SELECT column2 FROM table1
END

Database project deployment advice

Kunal Chaudhary9-Jul-05 9:08

Kunal Chaudhary

9-Jul-05 9:08

As a college project we are making a database system, which we have planned to give to a school. we are using MySql server now. The question is this, is there a way to implement this project so that we wont have to install mysql server in the schools computer? we dont mind changing the database server or any thing for that matter. please help......

Re: Database project deployment advice

Anonymous9-Jul-05 23:30

How to check on Column value

9-Jul-05 23:30

use ms access

Big Grin | :-D

gharryh9-Jul-05 5:42

gharryh

9-Jul-05 5:42

I selected one Row from a Database and stored it in a DataTable. See code:

If ViewState("DataPeuter") Is Nothing Then
Dim sqlPConnection As SqlConnection
Dim sqlPDataAdaptor As SqlDataAdapter
Dim sqlPCommand As SqlCommand
Dim sqlPConnectionString As String
Dim sqlPSelectString As String

Try
sqlPConnectionString = "server=localhost;uid=sa;pwd=;database=KiddinSys"
sqlPSelectString = "Select * From Peuters Where Peuternummer = " & pNum
sqlPConnection = New SqlConnection(sqlPConnectionString)
sqlPCommand = New SqlCommand(sqlPSelectString, sqlPConnection)
sqlPDataAdaptor = New SqlDataAdapter(sqlPCommand)
dataTablePeuter = New DataTable
sqlPDataAdaptor.Fill(dataTablePeuter)
ViewState("DataPeuter") = dataTablePeuter

Catch exception As Exception
ErrorLabel.Text = exception.ToString()
End Try

Else
dataTablePeuter = CType(ViewState("DataPeuter"), DataTable)
End If

In the dataTablePeuter there is a Column named Geslacht as a Integer. What i would like to do is to convert this Integer to a String like this:

if dataTablePeuter.Geslacht = 1 Then
lblGeslacht.Text = "Jongen"
ElseIf dataTablePeuter.Geslacht = 2 Then
lblGeslacht.Text = "Meisje"
Else
lblGeslacht.Text = "Onbekend"
EndIf

The question is what is the correct syntaxs for if dataTablePeuter.Geslacht = 1 Then

Insert, Update and Delete Problem. Need Help. Thanks.

shapper8-Jul-05 7:34

shapper

8-Jul-05 7:34

Hello,

I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine.

The update is not. I checked and my database has all the necessary records in it when testing it. I get the error "No value given for one or more required parameters." when I try to update the database.

Can you tell me what am I doing wrong?

Thanks,
Miguel

These are my 3 codes:

UPDATE

' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New OleDbConnection(connectionString)

' Set Query and Command
Dim queryString As String = "UPDATE [t_news] SET [news_id]=@news_id, [title_pt-PT]=@title_pt-PT, [title_en-GB]=@title_en-GB, [text_pt-PT]=@text_pt-PT, [text_en-GB]=@text_en-GB, [publication_date]=@publication_date WHERE ([t_news].[news_id] = @news_id)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@news_id", 1))
.Add(New OleDbParameter("@title_pt-PT", "Test UPDATE - title PT"))
.Add(New OleDbParameter("@title_en-GB", "Test UPDATE - title EN"))
.Add(New OleDbParameter("@text_pt-PT", "Test UPDATE - text PT"))
.Add(New OleDbParameter("@text_en-GB", "Test UPDATE - text EN"))
.Add(New OleDbParameter("@publication_date", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With

' Update Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try

INSERT

' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New OleDbConnection(connectionString)

' Set Query and Command
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)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@title_ptPT", "Test INSERT - title PT"))
.Add(New OleDbParameter("@title_enGB", "Test INSERT - title EN"))
.Add(New OleDbParameter("@text_ptPT", "Test INSERT - text PT"))
.Add(New OleDbParameter("@text_enGB", "Test INSERT - text EN"))
.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

DELETE

' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New OleDbConnection(connectionString)

' Set Query and Command
Dim queryString As String = "DELETE FROM [t_news] WHERE ([t_news].[news_id] = @news_id)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

' Add Parameters
dbCommand.Parameters.Add(New OleDbParameter("@news_id", 2))

' Delete Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try

Re: Insert, Update and Delete Problem. Need Help. Thanks.

Rob Graham8-Jul-05 8:08

Rob Graham

8-Jul-05 8:08

I believe oledb parameters are 'placement' only parameters (that is, the name of the parameter is irrelevant, only its order in the statement matters) In this case you have one more parameter (placement wise) in the statement than you are adding to the parameter collection (you can't get away with 'sharing' the news_id parameter just by name matching, you must add another parameter using the same value, but a different name - the names must be unique to satisfy the collection rules, but serve no role in terms of matching parameters in the statement). I suspect you dont really want to CHANGE the value of news_id anyway, so just remove it from your filed list, and move the parameter to last in the colection.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

Re: Insert, Update and Delete Problem. Need Help. Thanks.

shapper8-Jul-05 12:16

shapper

8-Jul-05 12:16

Hi,

I did that:

' Set Query and Command
Dim queryString As String = "UPDATE [t_news] SET [news_id]=@news_id_update, [title_pt-PT]=@title_ptPT, [title_en-GB]=@title_enGB, [text_pt-PT]=@text_ptPT, [text_en-GB]=@text_enGB, [publication_date]=@publication_date WHERE ([t_news].[news_id] = @news_id)"
...
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@news_id_update", 5))
.Add(New OleDbParameter("@news_id", 5))
.Add(New OleDbParameter("@title_ptPT", "Test UPDATE - title PT"))
.Add(New OleDbParameter("@title_enGB", "Test UPDATE - title EN"))
.Add(New OleDbParameter("@text_ptPT", "Test UPDATE - text PT"))
.Add(New OleDbParameter("@text_enGB", "Test UPDATE - text EN"))
.Add(New OleDbParameter("@publication_date", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With

I get the error: "Cannot update 'news_id'; field not updateable."

Remember that news_id is the primary key on my Access database and set as "AutoNumber"

Then I tried the following query as i don't need to change the news_id field:
Dim queryString As String = "UPDATE [t_news] SET [title_pt-PT]=@title_ptPT, [title_en-GB]=@title_enGB, [text_pt-PT]=@text_ptPT, [text_en-GB]=@text_enGB, [publication_date]=@publication_date WHERE ([t_news].[news_id] = @news_id)"

I don't get any error but there is no change in data!!!! And record 5 is there!

I even placed a Response.Write("Done") in the code:

' Update Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
Response.Write("Done")
End Try

And it is displayed "DONE"!

My INSERT and DELETE codes are working fine.

What is going on with this UPDATE code?

Thanks,
Miguel

Re: Insert, Update and Delete Problem. Need Help. Thanks.

Rob Graham8-Jul-05 17:16

Rob Graham

8-Jul-05 17:16

You missed my most important point. The parameters are PLACEMENT (i.e. ORDER) sensitive. You must add the parameters to the collection in the same order that you used them in the statement. You get no update because no news_id value in the table matches the publication date (politly converted to a long integer). news_id MUST be the last parameter added to the collection. To be sure that the ADD method overload is not doing some polite conversion behind your back, you should specify the data type and direction for the parameters, as well, rather than rely on the parameter constructor to determine them for you.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

Re: Insert, Update and Delete Problem. Need Help. Thanks.

jjrdk8-Jul-05 10:18

jjrdk

8-Jul-05 10:18

Instead of the lines:
..." .Add(New OleDbParameter("@title_ptPT", "Test INSERT - title PT"))"

try to use:
".Add("@title_ptPT", "Test INSERT - title PT")

As far as I know there is no need to declare a new OleDbParameter in this statement.

Hope it helps.

JJRDK

Re: Insert, Update and Delete Problem. Need Help. Thanks.

Luis Alonso Ramos9-Jul-05 23:26

Luis Alonso Ramos

9-Jul-05 23:26

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!

SQL Server; Recursive Functions and Scope. (Long-ish post)

Alsvha8-Jul-05 3:45

Alsvha

8-Jul-05 3:45

Hiya all.
I really hope you guys can help me with this question/problem.
First off - sorry for the lenght of the post. Smile | :)

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

opening documents without saving to disk

carabrenna8-Jul-05 0:26

carabrenna

8-Jul-05 0:26

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]

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

Re: opening documents without saving to disk

Colin Angus Mackay8-Jul-05 3:13

Colin Angus Mackay

8-Jul-05 3:13

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

Re: opening documents without saving to disk

Anonymous8-Jul-05 10:29

8-Jul-05 10:29

OK tnx for the answer anyway. i'll keep in mind next time to stick to the point of ado.

Smile | :)

SQL vs. Access and ODBC

-- NA --7-Jul-05 21:17

7-Jul-05 21:17

I have a program I'm working with (VB Unsure | :~

) 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 Smile | :)

Paul

modified 18-Jul-18 11:59am.

RajithCAlwis7-Jul-05 21:45

RajithCAlwis

7-Jul-05 21:45

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

-- NA --8-Jul-05 22:25

8-Jul-05 22:25

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 Confused | :confused:

can't see anything else that's consistent on failure)

Paul

modified 18-Jul-18 11:59am.

Anonymous9-Jul-05 7:41

9-Jul-05 7:41

this is smoething strange with adodc,

-- NA --9-Jul-05 12:51

9-Jul-05 12:51

Tell me about it.

Paul

modified 18-Jul-18 11:59am.

-- NA --9-Jul-05 12:50