|
Hello 
Would be really grateful if someone can help me out. I'm no pro in SQL, but currently I have to build a search function. I'm using ASP and Access 2000 database.
Currently the search i've managed to built can search thru all the tables in the database. Unfortunately the search is not flexible enough.
Here is an example of one of my tables.
--------------------------------------
Product | Model |
------------|-------------------------|
BenQ | FP767 |
Samsung | SyncMaster LCD 192T |
Samsung | SyncMaster LCD 172B |
LG | L1710s |
---------------------------------------
strSearch = Request.form("strsearch")
& "SELECT Product, Model " _
& "FROM LCDMonitor " _
& "WHERE Product LIKE '%" & strSearch & "%' " _
& "OR Model LIKE '%" & strSearch & "%' " _
& "ORDER BY Product;"
Here's the situation. If my search term is samsung then 2 rows will be returned. If I search for syncmaster, 2 rows will also be returned. But if i search for samsung syncmaster, no records are returned. I want it to be able to search for samsung syncmaster as well. And i want it to work even if i search for syncmaster 192T. Currently no records are returned if search for syncmaster 192T.
Do I need to use arrays? If so, how do i do that? Need help with coding.
Quite new to all this stuff, so need all the help I can get. Urgent. If need more information or wat i've posted is not clear, let me know. Thanks
|
|
|
|
|
you should try to parse the search string, I mean split the string to single words, and then try to build different possible sql statement, run all of them, and analyse the result set and pick one or more to the client.
|
|
|
|
|
Hi
Is there any chance you could help me with the coding on how to split the words then build sql statement for each word?
Your help will be very much appreciated.
|
|
|
|
|
Sorry if it's wrong forum but there isn't one for OLEDB... Anyway. Is OLEDB simultaneous-multithread-access safe? Also ... using OLEDB - if different threads need to access the same database is it better to open that database from each thread or is it OK to use the same opened database object from different threads at the same time - reuse the connection?
Sorry for such a basic question. 
*Reposted from ATL forum*
|
|
|
|
|
It depends on your provider. When you use OLE DB, you use some system objects to locate a provider, but after that you're talking directly to the provider object, a component supplied by the vendor.
If they've implemented it properly, if the provider is sensitive to threading issues it should be marked as Apartment model threading. If it isn't, it should be marked as Free or Both.
A connection can usually only process one command at a time, so if you want commands to run concurrently, you'll need to open another connection.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Ok. I need to use Access (assuming microsoft's provider), SQL server (assuming microsoft's provider) and Oracle (assuming oracle's provider). Do you know how good are they? I'm guessing Access is not as good as the two big ones, right? But the big ones should be fine with multithreading, aren't they?
|
|
|
|
|
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...
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
The student's grade is on the ASSESSMENT table, final-grade - column.
|
|
|
|
|
Sorry, didn't see the s_no column I'll see if I can scribble down some SQL
|
|
|
|
|
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....
|
|
|
|
|
So the output is 3 records.
How can I display as:
No. Students
------------
3
How's the SQL?
|
|
|
|
|
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!
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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();
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.
|
|
|
|
|
If the error says the string will be truncated, the size of the field you want to store it in, is to small.
|
|
|
|
|
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.
|
|
|
|
|
By using "blob" as the field's type:
http://www.winnetmag.com/SQLServer/Article/ArticleID/20461/SQLServer_20461.html
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
It's the same output as
select *
from employee_table
-Thomas
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
