|
I am trying to create a search for a sql database i have been writing. i have managed to get the search to search "firstName". if i replace this with any of my other column names it also works fine, but i am trying to get it to search multiple columns at once.
i have tried searching on google but dont really know what to search for.
i thought it might have been an AND or an OR statement but couldnt get these working either.
this is my code;
Sub BindDataGrid1()
Dim CS As String
CS = _
"data source=(local)\SQLEXPRESS;" & _
"initial catalog=mus_search_test;"
Dim objCN As SqlConnection
objCN = New SqlConnection(CS)
Dim objDA As SqlDataAdapter
objDA = New SqlDataAdapter("Select * from Musician_Search", objCN)
Dim DS_SearchMusician_Search As DataSet
DS_SearchMusician_Search = New DataSet
objDA.Fill(DS_MS11, "Musician_Search")
Dim objDV As DataView
objDV = DS_MS11.Tables("Musician_Search").DefaultView
' sql statement for row filter
MySearch = "firstName LIKE '*" & txtSearch.Text & "*'"
objDV.RowFilter = MySearch
RecNum = objDV.Count.ToString
John Michael Kinsella
kinsellajohn@hotmail.com
|
|
|
|
|
Hey
Can the SQL not be something like this
SELECT * FROM T_TABLE
WHERE FIRST_NAME LIKE @PARAM1 AND DESC LIKE @PARAM2
You could then have the user select the filters and call the correct prodedure/pass the correct dynamic SQL.
In the case of the latter row filter would be :
"firstName LIKE '*" & txtSearch.Text & "*' AND lastname LIKE '*" & txtSearch.Text & "*'"
(The syntax of quotation marks may be incorrect as i just copied and pasted yours)
Hope this helps.
PS you may want to use stored procedures instead of dynamic sql as they help with security
Dan
Edit* There are many ways to search on more than one field such as making temporary tables etc this is just a simple example.
|
|
|
|
|
many thanks for your reply, i will give it a shot..
it sounds perfectly plausible but i am not too familiar with SQL yet..
John
John Michael Kinsella
kinsellajohn@hotmail.com
|
|
|
|
|
Glad to be of assistance.
If it doesnt work as expected just give us a shout.
Dan
|
|
|
|
|
Hi, Dan..
i am having troubles with the same thing.
am i supposed to be using both the statements you suggested?
the syntax for the second part was functional the way i sent it to you and it doesnt seem to work with your additions..
i brainstormed all the obvious problems, like i have secondName insted of lastName.. and extra/missing quotation marks... but i cant work it out.. i just get error messages when i try to search.
see the issue im finding really challenging is that i havent handled much SQL. my course deals with one part at a time.. ie. asp.. then ado.. then sql...
but i am having trouble understanding one and not the other.. its a home study course and so help from tutors is limited..
so, any other suggestions would be much appreciated
John Michael Kinsella
kinsellajohn@hotmail.com
|
|
|
|
|
Hi
What you need to do is allow the user to have a search filter - for example we will use firstname and age.
If we wanted to know all the people whose firstname began with an Dan and were older than 18 we would have a quesry like:
SELECT * FROM T_EMPLOYEES
WHERE FIRSTNAME LIKE 'Dan%' AND AGE > 18
If you can post your errors i will be able to help more
Dan
Edit** Try using a stored procedure instead of dynamic SQL too.
Try this URL http://www.w3schools.com/sql/default.asp
-- modified at 10:16 Tuesday 3rd July, 2007
|
|
|
|
|
This is the actual result from my procedure
18-Mar-03 AUD -7.4505805969238281E-9
07-Apr-03 AUD -7.9162418842315674E-9
09-Apr-03 AUD 5.2386894822120667E-10
17-Apr-03 AUD -7.9162418842315674E-9
but My expected result is
18-Mar-03 AUD -7.45058
07-Apr-03 AUD -7.91624
09-Apr-03 AUD 5.23868
17-Apr-03 AUD -7.91624
Please help the do this process how to perform this?
Regards,
Magi
|
|
|
|
|
You just have to round the value using ROUND(Value,5)
|
|
|
|
|
Is there a setting that can change the default behavior of an oledbadaptor updating a dataset to access so that if one of the rows in the dataset errors it will still push the remainder of the changes through? If not I can loop through updating one datarow at a time, but was wondering about a more graceful implementation.
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
|
There's the ContinueUpdateOnError property you could set to true.
|
|
|
|
|
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
|
Yeah, I've been having plenty of those days recently.
|
|
|
|
|
I want to secure my DB by block or disable the dbo schema and the login using windows a count
merwa
|
|
|
|
|
|
I need a constant update of one SQL Server 2000 database into another SQL Server 2000 database and vice versa. My purpose is if one server somehow down for some time i want to switch to another server. i also need updated data of my back up server to original server when that is up.
Is there any solution available?
Thanks in advance.
ACDev7
|
|
|
|
|
Database mirroring is a new feature of SQL Server 2005 (SP1 and later).
SQL Server 2005 database mirroring is based around log shipping. Clients connect to the Principal (active) node and submit their queries; data modifications are recorded to the transaction log on the Principal, which sends log updates to the Mirror (standby) node. In the asynchronous (high-performance) mode, the Principal reports success to the client as soon as the modifications are recorded to its own log, it does not wait for the Mirror to write to its log. In this mode, some updates can be lost on failure of the Principal node, if the corresponding log operations have not yet been recorded in the log.
In synchronous (high-safety) mode, the Principal waits for the Mirror to complete recording the modifications to its log before reporting success to the client, when committing the transaction. This causes a delay to the client as all the log records have to be transferred over the network and written to the log file. This prevents lost updates.
For automatic failover, a third node, the Witness, is required. This doesn't keep a copy of the database, it only informs the other two instances which it can see. If the Mirror and Witness can see each other but neither can see the Principal, the Mirror assumes that the Principal has failed and takes over the Principal role. The Principal, if it loses contact with both Mirror and Witness, takes its database offline.
The standard SQL Server 2000 high-availability solution is clustering. I would recommend investigating that before venturing into trying to implement your own solution. It may be possible to implement a manual 'mirroring' solution using Transactional Replication. I've not tried to do this.
|
|
|
|
|
Mike Dimmick wrote: (SP1 and later).
I'm sure I saw them demo database mirroring at the SQL Server 2005 launch. I take it that it didn't work properly in real life until the service pack got released.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
They left it at 'experimental' - you had to turn on a trace flag to get it to work. With this level of confidence in the feature I would recommend applying the service pack before trying it.
I'd recommend not using mirroring for a database with a high level of write activity (say, ASP.NET session state). For a start, you need to use the Full recovery model, which means that the transaction log just grows until you back it up. We're using a SQL Server database as a shared state store for our thin client product, when used in a server farm mode (for load balancing and failover), but due to the requirements of the protocol the database gets updated multiple times per client request. I think I can probably get this down to two. Anyway, because the Mirror partner has to acknowledge all transactions, you get a delay of several milliseconds (normal cross-machine call overhead) on committing every transaction, which slows down your client quite a bit.
In fact I found in in-house testing that if the Mirror went offline for a while, the transaction rate went up a lot. When the Mirror came back online it could never catch up with the transactions that had occurred while it was offline. At least, on the low-end white-box hardware that I was testing with.
For mostly-write databases, clustering is a better solution than mirroring.
|
|
|
|
|
Hi friends!!!
I have a table where i have a set of tutor ids and the relevant points they get according to a student.
table is like
student_name // tutor1_id // points // tutor2_id // points ....
john // 101 // 4 // 102 // 5
jacob // 102 // 3 // 105 // 4
and another table
tutor_id // tutor_name
101 ron
102 reid
103 abc
...
I need to show the points the tutor name and the student name together with a join among the two tables..
Right now i am using union operator for getting all the records
Is there any other method how i can get all the details together
Thanks for your time...
I was born dumb!!
Programming made me laugh !!!
--sid--
|
|
|
|
|
hi
try this query
select t1.student_name,t2.tutor_name ,t1.points from table1 t1,table2 t2 where t1.tutor1_id=t2.tutor_id ......
but i think u should not maintain the table1 like this (as u did)
try this way...
studentname//tutor//points
john // 101 // 4
john// 102 // 5
and so on
if u feel its voilating normalization then split it into two tables.
happy coding
|
|
|
|
|
Hi,
Can u please tell me what is the exact output u require.
Also can u please provide me with the table structure of the first table
student_name // tutor1_id // points // tutor2_id // points ...
what does this ... indicate
Thanking you in Advance
Regards
Pratik Shah
|
|
|
|
|
Hi Forum,
I am having some really iritating problems with an simple input page which should pass parameters into a stored procedure from the text boxes first and last name.
I keep getting the error during exection of sp_myinsert, pfirstname not defined, every time i try to submit the form
Has anyone got any suggestions I have giving my code for procedure and page
many thanks
boy
--------------------------------
CREATE DEFINER=``@`localhost` PROCEDURE `sp_myInsert`(pFirstName varchar(20), pLastName varchar(30))
BEGIN
INSERT INTO Names (FirstName, LastName) values (pFirstName, pLastName);
END
-------------------------------------------
<%@ Page Language="VB" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" runat="server">
Sub page_load()
End Sub
''' <summary>
''' Page_load
''' Recognised by ASP and must be provided on loading of page.
'''
''' Creates connection to database, passes stored procedure into test database
''' and fills a table, which is showing in web browser
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Sub sendData(ByVal sender As Object, ByVal e As EventArgs)
Dim litErr As New LiteralControl
'Create connection string to pass database, string holds login information to mySQL,
Dim connectionString As String
connectionString = "Server=; uid=; pwd=;database=test;"
'Builds .net mysql connection and passes connection string into method
Dim connection As New MySqlConnection(connectionString)
'Open connection to DB
connection.Open()
'Create mySql command string for passing query or SPROC(Stored Procedure)
Dim cmdString As New MySqlCommand
'Set Command to equal mySql connection,t so can pass SQL query
cmdString.Connection = connection
Try
'Set command string to equal SPROC
cmdString.CommandText = "sp_myinsert"
'ONLY PLACE THIS IF SPROC, sets the command to a SPROC
cmdString.CommandType = CommandType.StoredProcedure
Dim param As New MySqlParameter
param = cmdString.Parameters.Add("p_firstname", MySqlDbType.VarChar)
param.Direction = ParameterDirection.Input
param.Value = txtFirstName.Text
param = cmdString.Parameters.Add("p_lastname", MySqlDbType.VarChar)
param.Direction = ParameterDirection.Input
param.Value = txtLastName.Text
cmdString.ExecuteNonQuery()
connection.Close()
Catch ex As Exception
litErr.Text = ex.Message
MsgBox(ex.Message)
End Try
End Sub
</script>
<!DOCTYPE html PUBLIC
<head id="Head1" runat="server">
<title>Untitled Page</title>
<script language="vbscript" type="text/vbscript">
</script>
</head>
<body>
<form id="form1" runat="server">
ENTER FIRSTNAME<asp:TextBox runat="server" ID="txtFirstName">
</asp:TextBox><br /><br />
ENTER LAST NAME
<asp:TextBox runat="server" ID="txtLastName"></asp:TextBox>
<asp:Button runat="server" ID="submit" Text="Submit" onclick="sendData" />
</form>
</body>
</html>
|
|
|
|
|
hi
i think u have made a mistake while declaring the parameter you require in SP_insert
it should be like this
CREATE PROCEDURE getDesc
@Firstname varchar(200),
@Lastname varchar(50)
AS
begin
---- insert Statment-----------
end
|
|
|
|
|
cheers for help but i solved the issue
just in case you have lost sleep over it the problem was
the stored procedure in mySql doesn't support the @ for parameters so u declare the procedure like this
Create procedure sp_insert(IN p_firstName varchar(20), IN p_lastName varchar(20)
AS
BEGIN
INSERT
END
Need to declare them as input parameters
But the reason it wasn't insert as when ur passing the parameters into the procedure from asp you need to provide ? as the prefix, as this is mySQLs prefix.
So its done parameter.add("?p_firstname".........
Thanks
boyind
|
|
|
|
|
hi,
I want to know is it possible to access sql server db from client machine without the client sql loaded??
thanks in advancd
regards
mamatha
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
