Re: calculations in sql - Database Discussion Boards

(new replyers please refer previous msg (after signature))

thanks for your help. but we need more information.
in our project we have one table say t1.in that columns are c1, c2 ,c3,c4 .outwhich c4 is a column such that it is another table say nestedtabl1.in that also nc1,nc2,nc3 likewise columns.where nc3 is varrying array.so we are trying to access data of these tables through ado.net but but it is giving error.we are using oledb provider
does it useful to access nested table of oracle.is it important to use "oracle provider" only?
and also we are using disconnected approach then how to read the data of tables ,and bring it into dataset.
please help us as early as possible.

kam_ps

---------------------------------
new replyers please refer following----------

In a database management project ,we have used Oracle at back-end and VB.Net at front-end.
In Oracle ,we used object-oriented features and created nested tables.Now we want to accesss those nested tables using ADO.Net .We are in search of answers of following questions:
1.Does Dataset class of ADO.Net support nested tables ?
2.If yes,can XML be used to specify schema of nested dataset ?
3.If not,how to manipulate nested tables of Oracle in ADO.Net?
As a temporary solution , we are retrieving main table and child table in two different tables;but this mechanism of database handling is affecting preformance of project badly.So we are in need of some other efficient solution.We will be thankful for your response if you will be able to help us to get out of the problem .Please reply as early as possible.

kam_ps
-- modified at 16:21 Tuesday 18th April, 2006

there is nothing like nested table in ado.net. But, the solution is there where the relational features of dataset comes in picture.

fetch both the tables separately and add a foreign key relation between them. Then using methods like getchildrows, etc, you can get ur prob solved.

Best Regards,

Darpan Gogia

hi i went create new data base from my computer to server
when i creat in local is ok
this my code to create data

Private Sub ButCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButCreate.Click
ButCreate.Enabled = False
btnExit.Enabled = False
Dim dmoServer As New SQLDMO.SQLServer
Dim filSqlScript As IO.StreamReader
Dim strPathToFile As String
Dim I As Integer
Dim StrDataScript0 As String
Dim StrDataScript1 As String
Dim StrDataScript2 As String
Dim strDrive As String
If TxtDBName.Text <> "" Then
If txtDBChar.Text <> "" Then
'If IsNumeric(txtDBChar.Text) = False Then

dmoServer.LoginSecure = True
Try
dmoServer.Start(True, MainMod.serverName)
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.UnauthorizedAccessException
Dim objDbLogin As New frmDbLogin
If objDbLogin.ShowDialog(Me) = DialogResult.OK Then
Try
dmoServer.Start(True, MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exc As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exce As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exce As System.UnauthorizedAccessException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
End Try
End If
End Try

filSqlScript = IO.File.OpenText(Application.StartupPath & "\DataScript.sql")
StrDataScript0 = Replace(filSqlScript.ReadToEnd, "Amazon", TxtDBName.Text)
strDrive = dmoServer.Properties.Application.FullName.Substring(0, 3)
StrDataScript1 = Replace(StrDataScript0, "C:\", strDrive)
StrDataScript2 = Replace(StrDataScript1, "rplc", txtDBChar.Text)
'Try
dmoServer.ExecuteImmediate(StrDataScript2)
filSqlScript.Close()

22222222222

hi i went create new data base from my computer to server
when i creat in local is ok
this my code to create data

Private Sub ButCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButCreate.Click
ButCreate.Enabled = False
btnExit.Enabled = False
Dim dmoServer As New SQLDMO.SQLServer
Dim filSqlScript As IO.StreamReader
Dim strPathToFile As String
Dim I As Integer
Dim StrDataScript0 As String
Dim StrDataScript1 As String
Dim StrDataScript2 As String
Dim strDrive As String
If TxtDBName.Text <> "" Then
If txtDBChar.Text <> "" Then
'If IsNumeric(txtDBChar.Text) = False Then

dmoServer.LoginSecure = True
Try
dmoServer.Start(True, MainMod.serverName)
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.UnauthorizedAccessException
Dim objDbLogin As New frmDbLogin
If objDbLogin.ShowDialog(Me) = DialogResult.OK Then
Try
dmoServer.Start(True, MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exc As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exce As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exce As System.UnauthorizedAccessException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
End Try
End If
End Try

PrgBarCreate.Value = 10
Label3.Text = PrgBarCreate.Value & "%"
filSqlScript = IO.File.OpenText(Application.StartupPath & "\DataScript.sql")
PrgBarCreate.Value = 20
Label3.Text = PrgBarCreate.Value & "%"
StrDataScript0 = Replace(filSqlScript.ReadToEnd, "Amazon", TxtDBName.Text)
strDrive = dmoServer.Properties.Application.FullName.Substring(0, 3)
StrDataScript1 = Replace(StrDataScript0, "C:\", strDrive)
StrDataScript2 = Replace(StrDataScript1, "rplc", txtDBChar.Text)
PrgBarCreate.Value = 30
Label3.Text = PrgBarCreate.Value & "%"
'Try
dmoServer.ExecuteImmediate(StrDataScript2)
PrgBarCreate.Value = 40
Label3.Text = PrgBarCreate.Value & "%"
filSqlScript.Close()
PrgBarCreate.Value = 50
Label3.Text = PrgBarCreate.Value & "%"
For I = 51 To 100
Thread.Sleep(100)
PrgBarCreate.Value = I
Label3.Text = PrgBarCreate.Value & "%"
Next

22222222222

And the problem/question is.....?

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Hi , I have a problem ! I have interviewers working on cases and I need to calculate the following:

Calculation per case :
1. wait time between two cases for each interviewer ( I am clueless on this one Frown | :(

)

Summary Calculation by group:
2. Average wait time for each interviewer
3. Total cases worked by each interviewer

My table has this information : ID,CaseId,CaseStarttime,CaseEndTime,InterviewerId

Primary Key is ID, only that is Unique in table.

For each InterviewerId I want to calculate the wait time.
Wait time= CaseEndTime(of last case) - CaseStartTime(of next case)

Minoo S

Minoo,

The answer is:
1) Convert (CaseEndTime-CaseStartTime) to get the seconds/minutes as integer.
2) Then you can simply get the average(minutes/seconds) by grouping the interviewers.

If you still need further help I will send the query, do let me know.

Best Regards,

Darpan Gogia

Hi Darpan,

I got the others thank you. Just the wait time I need more information on.

So if I group cases by interviewid, how do I calculate wait time, if wait time is (CaseEndTime of first case/first row in group by Interviewer)-(CaseStarttime of next case/second row in group by Interviewer)

If you could give me the full query that will certainly help.

thx

Minoo S

Help

anyone ?? I still need to calculate the wait time.

Minoo S

Hi how to make one varable local for some store procedure pleas give me example??
good bye
best regards

To make a local variable inside a stored procedure use DECLARE, e.g.

DECLARE @myLocalVar INT

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Have you looked at this?

T-SQL Programming Part 1 - Defining Variables, and IF...ELSE logic[^]

Paul

Hi!!

How do return the error code and description from a stored procedure to the .cs code? Lets say an INSERT statement was not successful then I want to display the error code, and error description to the user on the.aspx page.

Hope some one can help me.

Regards,
ma se

The SqlException that is thrown will contain this information. The Message will, naturally, contain the error message and the Number will contain the number of the error. However, it is possible for the SQL Server data provider to return multiple errors. In which case you can use the Errors property to get a collection of SqlError objects.

You can find more information on MSDN: SqlException[^]

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

In T-SQL (e.g. in a trigger) , if you use RAISERROR with an error severity of 16 or higher it gets returned to the client application.

'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd

Aloha to all,

I have thrown together some code that generates a file with a lot of data (something like 200,000 rows). I would like to load all this data in a SQL Server table, but my attempt to execute BCP from C# doesn't want to behave.

I do as follows (more of less stealing everything from http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/08/19/22566.aspx Poke tongue | ;-P

):
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.FileName = "bcp";
proc.StartInfo.Arguments = @"DATABASE_NAME.dbo.TABLE_NAME in c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t',' -Sservername";
proc.EnableRaisingEvents = true;
proc.Start();
string s1 = proc.StandardOutput.ReadLine();
string s2 = proc.StandardOutput.ReadLine();

All I get from this code is:
s1 = "SQLState = 28000, NativeError = 18456";
s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'username'."

I have off course checked the username/password a thousand times and it is correct. If I copy my Arguments string to the command prompt, I can execute BCP without any problem OMG | :OMG:

Thank you and roger over, Mads

phi
phimix.com OMG | :OMG:

It works now. Some 'Rob Farley' from microsoft.public.sqlserver.programming group got the idea to put everything in a .bat file and that helped!

Thanks to Rob Farley.

phi
phimix.com OMG | :OMG:

I want to do a project in Sql Server. Iam new to Sql server and actually i dont know Oracle also.But The Ray of Hope is I know the Database concepts.
1.Is it Oracle Really necessary to learn Sql Server ?
2.What are the books I need to read for this ?

krishna

Krishnatv wrote:
1.Is it Oracle Really necessary to learn Sql Server ?

No. Oracle is just another database system - It has nothing to do with SQL Server.

Krishnatv wrote:
2.What are the books I need to read for this ?

There are many books and I couldn't really recommend one because all the books I have are reference books rather than teaching books. Also, everyone learns differently: Go to your local bookshop and see what they have, flick through the pages and see if it is the type of thing you would feel comfortable with.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Thank u Colin

Hi
I have a report for printing order delivery labels. Each order can have multiple delivery addresses, so this is a nested report with the order report running first and then the delivery address report running as a sub-report.

The problem is that I want to set the labels so that up to 4 different delivery addresses for the same order could be printed on a single page.

I am not sure how to set the layout of the sub report, currently it prints each delivery address 4 times on a page as opposed to 4 different delivery addresses.

If the order had 5 different delivery addresses, I would need the first page to show the first 4 and the second page to just show 1 with 3 blank labels.

Has anyone any experience of this or does anyone know how to set this up?

Go to report layout and set it to break the page (Page Break) after every "parent" record.

Its much possible with reporting services. I dont have it installed rite now, but i know its possible. Try to search in properties box for the layout items.

Best Regards,

Darpan Gogia

Hi.

I want to retrieve the list of indexes against a table, and the fields in the index. Also if posible the ordinal of that index.

I know that sp_helpindex, will retrieve that information, but I want to do it through TSQL. Does anyone know the query or queries to retrieve this information, please post it here. Also performance is an issue, so this info must be retrieved fast.

:->:->:->

.NET Rules

Sk8tzz wrote:
I know that sp_helpindex, will retrieve that information, but I want to do it through TSQL.

sp_helpindex is using TSQL. I suspect you mean you don't want to use a stored procedure?

You may want to take a look at the sysindexes[^] table. This will be the fastest way to retrieve the information you want - however, you'll have to join it to some of the other system tables to get the names of the columns and so on.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

I know that, I looked at that sp and I'm still fuzzy about what exactly its doing. Is there a simpler way of quering this info. Unsure | :~

.NET Rules