Database

Re: Anyone have any experience with FireBird

22-Jul-04 17:45

Since SQL Servre is kinda expensive (5000 euro's per processor), and we don't want to obligate our customers to do that investment, even though SQL Server is, in my opinion, the only way to go.
One of my colleagues came across an Interbase offspring called FireBird, which is open-source and is based on the Interbase code released a long while back by Borland. It looks promising, but it's not there yet as far as I can see. It has a .NET connector already, so switching shouldn't be a big problem...

michanne124-Jul-04 6:51

michanne1

24-Jul-04 6:51

If it is mission critical I would be more inclined to go with something more proven.

I use sql Server and Mysql. Mysql is a really good open source database with a lot of documentation and users behind it - worth checking out. Were I to start a business today I would most likely go with Mysql over Sql Server.

B

How to find number of student

DotNet22-Jul-04 16:52

DotNet

22-Jul-04 16:52

Hie all, I have 3 tables - Assessment, Unit, Department.

How can I find :
The numbers of those students who, while attending one or more units run by the department named 'computing', are not attending every unit run by the department named 'computing'.

Hope to hear from you soon.
Thanks.

(ASSESSMENT - Table)
s_no u_no final-grade
S001 Q2 95
S002 Q1 61
S003 Q3 65
S004 Q4 53
S005 Q5 63
S006 Q1 78
S007 Q4 95
S001 Q1 45
S001 Q3 75
S001 Q5 98
S001 Q6 85

(UNIT - Table)
u_no d_no uname
Q1 D1 Programming
Q2 D2 French Language
Q3 D1 Hardware
Q4 D2 French Politics
Q5 D1 Logic
Q6 D1 Operating Systems
Q7 D3 Finance
Q8 D2 French Literature

(DEPARTMENT - Table)
d_no d_name hod(head of dept)
D1 Computing Steve
D2 Language Smith
D3 Business Anne
D4 Accountacy Joe
D5 Art Carol

partyganger22-Jul-04 17:28

22-Jul-04 17:28

The solution lies in Set logic (Math). But I don't see a students table? SO how would I know which student got which grade on which course?

Anyway, If you look at the wanted result set, it's the superset of students who attend AT LEAST one unit that's part of the computing department, minus the students that follow all of em. I.e. students who have a COUNT(attended units in computing) = COUNT(units in computing)

But to show the involved SQL, i'll need the students table and how it's 'connected' to the other tables

DotNet22-Jul-04 17:50

DotNet

22-Jul-04 17:50

The student's grade is on the ASSESSMENT table, final-grade - column.

partyganger22-Jul-04 18:18

22-Jul-04 18:18

Sorry, didn't see the s_no column I'll see if I can scribble down some SQL

Bill Dean23-Jul-04 8:57

Bill Dean

23-Jul-04 8:57

How about this:

declare @iCount int
select @iCount = count(1) from unit where d_no='D1'
select 
    s_no, 
    count(1) UnitCount 
from 
    Assessment
where
     u_no in (select u_no from unit where d_no='D1')
group by s_no
having count(1)<@iCount

I didn't test it...but it should be close....

Can't get value from DataSet into variable without explicit casting?

DotNet24-Jul-04 0:07

DotNet

24-Jul-04 0:07

So the output is 3 records.

How can I display as:

No. Students
------------
3

How's the SQL?

mutiple ODBC help!!!

Member 123981322-Jul-04 16:34

Member 1239813

22-Jul-04 16:34

Hi, I want to know how can i add 2 CRecordset Derived class to my application.. I tried to 2 CRecordset class to my application, but when i make pointers to these classes. It gives me runtime error.. Anyone can help me with that??
I just want to open 2 CRecordset classes and each one point to only 1 table. So i can control them sepeartely... Thanks!

Re: mutiple ODBC help!!!

Small Rat23-Jul-04 18:52

Small Rat

23-Jul-04 18:52

if you use VC++6,
you can try this way:
use appwizard to derive 2 classes, and use CRecordset as the base class.
and just follow the wizard.

good luck.

ThomasH122-Jul-04 13:17

ThomasH1

22-Jul-04 13:17

Hey guys, got a question for you all! I've got a DataSet/DataTable, created by using a DataAdapter.Fill call. I'm writing a C# class that handles all the ADO.NET work I need, and a different class will call the public methods of this class.

So; I've created the two public properties I need- Public String _custId and Public DateTime _orderDate. But, I can't get the values out of the DataSet/DataTable/DataColumn without doing (in my mind) useless casting! For instance, this doesn't work:

_custID = dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0];

I get the message "Cannot implicitly convert type 'object' to 'string' ". To make it work, I have to do:

_custID = (String)dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0];
....or....
_custID = dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0].ToString();

I'm confused as to why? The following line displays "System.String":

Console.WriteLine(dsOrders.Tables["tblOrd"].Rows[iCurrentRow].Table.Columns[0].DataType);

So, shouldn't that mean that I don't have to do anything else? Why do I need to use (String) or .ToString() ?

And I also can't get the DateTime into a variable. Trying

_orderDate = dsHousing.Tables["tblOrd"].Rows[iCurrentRow][1];

gives me "Cannot implicitly convert type 'object' to 'System.DateTime' ". And, again, if I display the value for Columns[1].DataType, it shows me "System.DateTime". Worse, there's no ".ToDateTime()" method of the Column object, so the only way I can get the DateTime field out is to cast it to a string, and then convert it back to a DateTime. Compared to my problem with the String column, I'm doing twice the un-necessary work just to get the DateTime column!

Is this really what's supposed to happen? If the DataType shows string, why do I need to perform an explicit conversion from object to string? It seems like I'm doing un-necessary work; if the value is stored as a certain datatype, I should be able to pull it out of the DataTable and put it into a variable of the same type.

I even toyed with the idea of .NET assuming I wanted the DataColumn being put into a String- but I can't find a property of DataColumn that just gives me the value. In classic ADO, there was a Field object, which had a Value property. I can understand "DataColumn" being an object, but is there a specific property for pulling the value out of DataColumn? For instance, if those code samples I just gave would be in classic ADO, I would be trying to say _CustID = rs.Fields(x).Value.

Oh I'm using VS 2003.NET EA, with WinXP, .NET 1.1, and have "Using System.Data" and "Using System.Data.OracleClient" at the top of my C# class definition. At first I thought this might be Oracle related, but DataSet/DataTable/DataColumn is part of the System.Data namespace.

Thanks!

-Thomas

Parsing, Processing, And Inserting Multiple Records Into a Database Design Question

Member 66660722-Jul-04 10:47

Member 666607

22-Jul-04 10:47

Hello all:

I have an XML file I am parsing. I will need to have some sort of "signal" or event that will notify the databaseautomatically start the table insert process.

Thought A: I could pull data from the XML file and store this data in an ASP.NET data storage object. Some of the data is associated with look up tables, so I need to do some queries, therefore I am using the term processing to mean: getting data id's associated with the string values parsed from the XML file. So processed data are now id's (that are associated with the string values parsed from the XML file). After the data has been processed I want to insert the id's into the appropriate tables.

Thought B: Use some sort of DTS package or another method for the parsing and inserting of data.

Additionally: I will need to have some sort of "signal" or event that will notify the SQL Server that the file has been saved to the proper location and is ready for parsing. If I use Thought A then I can do the aforementioned programatically. I don't know how to do the aforementioned using Thought B. Any suggestions?

Here are my questions:
Thought A:
What type of ASP.NET data storage object would you use (a datatable, dataset etc.)?

How would you insert data in the appropriate database tables with ASP.NET (e.g. some type of bulk load or call a SPROC and load the dataset info)

Thought B:
Or would you skip the ASP.NET stuff, and use some sort of DTS package or another way of inserting the data? If so, any pointers on how would that would be done?

TYIA,
lonelobo

store a c# byte array

pelos22-Jul-04 6:38

pelos

22-Jul-04 6:38

hello forum,

i want to store c# objects in a MS SQL database. I think the best way is to serialize the object to a byte array but i have problems to insert a c# byte array into the database...

First, the equivalent type of datum in MS SQL is the binary or varbinary type, isn´t it?

well, my code:

<br />
<br />
MyObject mo = new MyObject();<br />
MemoryStream ms=  new MemoryStream();<br />
BinaryFormatter bf = new BinaryFormatter();<br />
b.Serialize(ms,mo);<br />
<br />
SqlConnection sc = new SqlConnection("server=localhost;uid=sa;pwd=;database=myDB");<br />
sc.Open();<br />
<br />
SqlDataAdapter sda = new SqlDataAdapter("select * from myTable",sc);<br />
DataSet ds = new DataSet();<br />
ad.Fill(ds,"tableFile");<br />
DataTable dt = ds.Tables["tableFile"];<br />
DataRow dr = dt.NewRow();<br />
<br />
dr["object"] = ms.GetBuffer(); //object is binary or varbinary<br />
                               // and GetBuffer() returns a byte[]<br />
dt.Rows.Add(dbRow);<br />
<br />
SqlCommandBuilder scb = new SqlCommandBuilder(sda);<br />
sda.Update(ds, "tableFile");<br />
<br />

The instruction:
dr["object"] = ms.GetBuffer();
throws the message exception "String or binary data would be truncated"

could you help me, please?
thanks in advance.

partyganger22-Jul-04 17:30

22-Jul-04 17:30

If the error says the string will be truncated, the size of the field you want to store it in, is to small.

pelos22-Jul-04 23:41

pelos

22-Jul-04 23:41

ok. thank you. that was the problem! but ... another question:
if the maxium size of a varbinary is 8000, how could i store an object with a greater lenght (for example, a file)?

thank you. bye.

Anonymous23-Jul-04 9:29

Anonymous

23-Jul-04 9:29

By using "blob" as the field's type:

http://www.winnetmag.com/SQLServer/Article/ArticleID/20461/SQLServer_20461.html

partyganger23-Jul-04 9:30

Re: Very Urgent SQL query

23-Jul-04 9:30

By using "blob" as the field's type:

http://www.winnetmag.com/SQLServer/Article/ArticleID/20461/SQLServer_20461.html

Even better, just save a reference to the file i.e. save it's path as a string in the database. If you have a file server to store your files on, just have your frontend app copy the images there and then save the path (//FILESERVER/SQLSERVERFILES/file1.jpg) in the database. Although SQL server has better performance serving up images (streamed), if a lot of people start using the app at once, the server might get stuck at just streaming files all the time. By just saving the reference, you place the task of getting the image at the client.

datagrid

viviansm21-Jul-04 21:26

viviansm

21-Jul-04 21:26

I create a frmCustomer that contains of txtCustID, txtCustFName,txtCustLName,txtCustDob and txtCustIC.... and the Add button, Search button and Exit button. When the user click on the Search button, the frmSearch will show. The user can search for the customer by select the alphabet in a combobox. The details of the customer whose last name start with that alphabet will display in the datagrid.

The datagrid in ths frmSearch contains of CustID, CustName, CustDoB. When the user double click on the specific row of the datagrid. The data in the selected row will bind to all the textbox in frmCustomers. Then, the user can do the edit or delete of the record by pressing the button in frmCustomers.

But,i am having a problem that it always get the original datasource instead of the filtered datasource when i double click on the selected row in the datagrid. Please help if you know how to solve this problem.

part of the coding is as follows:

Private Sub btnSearchName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchName.Click

With Me.DataSet11.Tables("Customers")

'carrier()

With DataGrid1
.CaptionText = "Search by Last Name"
End With

If cboLastName.Text = "ALL" Then
.DefaultView.RowFilter = "CustLName like '%'"
Else
.DefaultView.RowFilter = "CustLName like '" & cboLastName.Text & "%'"
End If

If .DefaultView.Count = 0 Then
MessageBox.Show("Data not found !.", "VideoMate", MessageBoxButtons.OK, MessageBoxIcon.Information)
'Else
'MessageBox.Show("Number of data : " & .DefaultView.Count.ToString(), "VideoMate", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If

If BindingContext(Me.DataSet11.Customers).Position < 0 Then
With DataGrid1
.CaptionText = "Database is empty !"
End With
End If

Me.DataGrid1.DataSource = .DefaultView

End With

End Sub

Private Sub DataGrid1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.DoubleClick

Dim CM1 As CurrencyManager = CType(Me.BindingContext(Me.DataGrid1.DataSource, Me.DataGrid1.DataMember), CurrencyManager)
If CM1.Position > -1 Then
Me._CM.Position = CM1.Position
Me.Close()
End If

End Sub

Thank you Cool | :cool:

ThomasH122-Jul-04 13:20

ThomasH1

22-Jul-04 13:20

It's the same output as

select *
from employee_table

-Thomas

Re: Very Urgent SQL query

partyganger22-Jul-04 18:23