|
Hello,
CREATE PROCEDURE dbo.spFindProduct
@searchText varchar(50)
AS
SELECT * FROM Product
WHERE productName LIKE '@searchText%'
GO
When the user enters a letter in the textbox the stored procedure will find all the words that begin with that letter. When they type a second letter the stored procedure will find all the words that begin with those 2 letters etc. All rows that are matched are displayed in datagrid. This is the same as one of those applications that find the closest word for what you are looking for.
My problem is that the above stored procedure does not return anything, even if the right letter has been entered.
The parameter is being passed correctly: WHERE productName = @searchText
That works and fine if l am looking for the exact word.
WHERE productName = 'F%' that works ok and finds all the rows where product name begins with a F.
But the above stored procedure does not work. Can anyone tell me how to correc this.
Many thanks in advance,
Steve
|
|
|
|
|
That is because you have asked it to search for the literal string '@searchText%' so it will only match product names that start with @searchText because you enclosed it in quotes.
What you need to do is something like this
DECLARE @search varchar(51)
SET @search = @searchText + '%'
SELECT * FROM Product
WHERE productName LIKE @search
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
If Not (Session("searchstring") Is Nothing) Then
Session.Remove("searchstring")
End If
str1 = "select uid, firstname+' '+middlename+' '+lastname as name,login,email,Case When Status = 1 Then '../admin/img/img_status_active.gif' When status=0 Then '../admin/img/img_status_inactive.gif' End status from tb_users where 1=1"
If TextBox1.Text <> "" Then
str1 = str1 & " and last_name like '" & TextBox1.Text & "%'"
End If
|
|
|
|
|
Breddy wrote: str1 = "select uid, firstname+' '+middlename+' '+lastname as name,login,email,Case When Status = 1 Then '../admin/img/img_status_active.gif' When status=0 Then '../admin/img/img_status_inactive.gif' End status from tb_users where 1=1"
If TextBox1.Text <> "" Then
str1 = str1 & " and last_name like '" & TextBox1.Text & "%'"
End If
I'm not sure what this post is supposed to demonstrate. It does not appear to have much to do with the original poster's query. It looks like it is some code you are using some place else that is vaguely applicable, but you have not provided an explanation so it is difficult to see where it is supposed to be helpful.
More importantly, your solution contains some pretty horrible security holes that you should patch. You may want to read the article SQL Injection Attacks and Tips on how to prevent them[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
hi guys..
my problem is like this..
i have a query which is :
string query = "select count(*) AS [username] from [" + tablename + "] WHERE username = '"+username+"'";
and my method is :
public void checkIfUserExists(string username,string tablename)
as u can see, i want to check how many user exists with this username in the database, i want to execute the query, and take the result inside an int
( e.g. int result )
if the result is > 0 , then i won`t allow user to choose that username
else user can register..
how can i do this ? it was easy doing that at php but i cant do this in c# because i can`t understand ado.net very well ( datasets, datagrids,etc..)
any help would be great!
thx!
good coding !
|
|
|
|
|
The way you build your SQL string is susceptable to a SQL Injection attack you may wish to read SQL Injection attacks and tips on how to prevent them[^]
You don't say what database you are using so I'll assume SQL Server 2000
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
int result = (int)cmd.ExecuteScalar();
conn.Close();
This is a very basic example and does not take into account error conditions. You have to provide the connection string (as I know nothing about your database or the security you've set up) and the query (which you have above - although I do recommend securing it as shown in the article I've linked to)
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I'm sure this has been asked 1,000 times already so sorry for this but...
Can 2005 & 2000 co-habitate on a server? If it can then is it a good idea?
E=mc2 -> BOOM
|
|
|
|
|
Albert Einstien wrote: Can 2005 & 2000 co-habitate on a server?
Yes. I am have both happilly running right now.
Albert Einstien wrote: If it can then is it a good idea?
Since they both play nice together I don't see why it would be a bad idea. Some things to be aware of:
* Enterprise Manager cannot see SQL Server 2005, but the new SQL Server Management Studio can see SQL Server 2000.
* Query Analyser from SQL Server 2000 can connect to SQL Server 2005 instances.
* Having both SQL Server 2000 and 2005 on one machine is just like having multiple instances of a SQL Server running. Each instance will launch its own service and run as a separate process.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
We discovered that you can't edit a multi-statement table-valued function in SQL Server 2000 from SQL Server 2005 Management Studio. My bug report[^] (I've just found two duplicates submitted before mine which didn't show up when I searched originally).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi,
I have two dataBase with different username and passWord ,
dataBase1 : UserName =u1 , Password=pass1
dataBase2 : UserName =u2 , Password=pass2
I have a Procedure on First dataBase , on this procedure I use a table of Second dataBase , But When I run the Application ,it make error that "u1" don't have access to dataBase2.
How can I use tables of dataBase2 in procedures of dataBase1?
Thanks.
|
|
|
|
|
Well, if you are working on SQL Server then you can operate across databases, even across different servers (if you link the servers)
Anyways you can use the full name of the table when you use two different databases like this:
SELECT t1.myColumn AS table1Column, t2.myColumn AS table2 column
FROM database1.dbo.myTable as t1
INNER JOIN database2.dbo.myTable as t2 ON t1.id = t2.id
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Oops sorry, you were talking about stored procedures.
Well, if you have a stored procedure on database1 you can use a table on database2 like this
CREATE PROCEDURE dbo.myStoredProc
AS
SELECT * FROM database2.dbo.myTable
GO
When you use a table name you can qualify it fully like this
[server].[database].[schema].[table name]
You can obviously miss the bits that are not needed. For example, in normal circumstanced you would only give the table name because everything is on the same server, in the same database, in the same schema.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I have a int ID Field set to auto-increment. When does it run out of room? What's the highest number it can go to?
E=mc2 -> BOOM
|
|
|
|
|
IIRC: (2*10^31)-1 = 2147483647
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thanks. I'm a developer and know some SQL but not the mechanics of SQL Server. I leave that to the experts. Can I change my Auto-Increment field to numeric and if so does that buy me anything?
E=mc2 -> BOOM
|
|
|
|
|
Suppose DataSet myDataSet has a DataTable myDataTable. Do the follwing:
myDataSet.Tables.Remove(myDataTable);
bool tf=myDataSet.HasChanges(); //tf will be false
DataSet tbl=myDataSet.GetChanges(); //tbl will be null
I understand the datatable is removed, thus all the DataRow are removed from the dataset. So dataset does not detect any change and does not get any changes. But what is the common way to track the fact that a whole table is removed from the dataset? Thanks.
webx10
|
|
|
|
|
i am using vs2002 and want to enter data in a sql server 2000 database through vb interface. i am able to enter data if the vb application and sql server both are on same node but i want to enter data to database on other node running sql server. computers are networked under same workgroup and there is no domain controller.
i used the following code for connection.
Dim constudent As SqlConnection
Dim cmdadd As SqlCommand
Dim conn As String
conn = "server=.; UID=sa;PWD=secret;database=student"
constudent = New SqlConnection(conn)
cmdadd = New SqlCommand("insert into student values(@name,@roll,@stream)", constudent)
cmdadd.Parameters.Add("@name", TextBox1.Text)
cmdadd.Parameters.Add("@roll", Val(TextBox2.Text))
cmdadd.Parameters.Add("@stream", TextBox3.Text)
Try
constudent.Open()
cmdadd.ExecuteNonQuery()
constudent.Close()
MessageBox.Show("Data Added", "added", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2, MessageBoxOptions.DefaultDesktopOnly)
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
Catch ex As Exception
MessageBox.Show("error", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2, MessageBoxOptions.DefaultDesktopOnly)
End Try
may any one hlp plz to modify the connection string or tell me how to establish such connection
|
|
|
|
|
server=TheMachineName
or
server=TheMachineName\TheSQLInstanceName
Oh... And logging in as sa through your application is a huge security risk - Don't do it or you could get 0wn3d.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thanks Colin Angus Mackay for reply
but it is not working
I even tried giving port no 1433 wit the ip address of machine but no success.
As there is no domain controller and all the systems are under same workgroup, if your solution works under these circumstances or not????
Reply.........
|
|
|
|
|
I don't think it's a connection string problem.
If the database is on server 2003 or XP SP 2, you needs to change some configuration values for it to work. You need to add sql server to the windows firewalls exceptions, open the ports, and change some registry settings.
It gets more complicated is you use the MSDTC but Microsoft provides step by step procedures. Search under SP 2 for SQL Server.
|
|
|
|
|
Please , can any one confirm that a table type or collection type parameter can be passed to stored procedure in oracle using ADO.NET ?
I , browsed several forum and site but without success
Thanks
love2code
|
|
|
|
|
You need to use, ODP.Net , Data Provider for Oracle supplied by Oracle. Assuming you have that here is the example from Oracle
/********* Example Start *************/
using System;
using System.Data;
using System.Text;
using System.Reflection;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace ODPSample
{
class AssocArray
{
static void Main(string[] args)
{
Console.WriteLine("Demo: PL/SQL Associative Array");
// Connect
string connectStr = "User Id=scott;Password=tiger;Data Source=oracle";
// Setup the Tables for sample
Setup(connectStr);
OracleConnection connection = new OracleConnection(connectStr);
OracleCommand cmd = new OracleCommand("begin MyPack.TestVarchar2(:1, :2, :3);end;",
connection);
OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2);
OracleParameter param2 = cmd.Parameters.Add("param2", OracleDbType.Varchar2);
OracleParameter param3 = cmd.Parameters.Add("param3", OracleDbType.Varchar2);
// Setup the direction
param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.InputOutput;
param3.Direction = ParameterDirection.Output;
// Specify that we are binding PL/SQL Associative Array
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param1.Value = new string[3]{"Input1",
"Input2",
"Input3"};
param2.Value = new string[3]{"Inout1",
"Inout2",
"Inout3"};
param3.Value = null;
// Specify the maximum number of elements in the PL/SQL Associative
// Array
param1.Size = 3;
param2.Size = 3;
param3.Size = 3;
// Setup the ArrayBind Size for param1
param1.ArrayBindSize = new int[3]{13,14,13};
// Setup the ArrayBind Status for param1
param1.ArrayBindStatus = new OracleParameterStatus[3]{
OracleParameterStatus.Success,
OracleParameterStatus.Success,
OracleParameterStatus.Success};
// Setup the ArrayBind Size for param2
param2.ArrayBindSize = new int[3]{20,20,20};
// Setup the ArrayBind Size for param3
param3.ArrayBindSize = new int[3]{20,20,20};
try
{
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.WriteLine("Results:");
Display(cmd.Parameters);
}
public static void Setup(string connectStr)
{
OracleConnection connection = new OracleConnection(connectStr);
OracleCommand command = new OracleCommand("", connection);
try
{
connection.Open();
command.CommandText = "drop table T1";
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
command.CommandText = "CREATE TABLE T1(COL1 number, COL2 varchar2(20))";
command.ExecuteNonQuery();
StringBuilder hdr = new StringBuilder();
hdr.Append("CREATE or replace PACKAGE MYPACK AS ");
hdr.Append("TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;");
hdr.Append(" PROCEDURE TestVarchar2(");
hdr.Append(" Param1 IN AssocArrayVarchar2_t,");
hdr.Append(" Param2 IN OUT AssocArrayVarchar2_t,");
hdr.Append(" Param3 OUT AssocArrayVarchar2_t);");
hdr.Append(" END MYPACK;");
command.CommandText = hdr.ToString();
command.ExecuteNonQuery();
StringBuilder body = new StringBuilder();
body.Append("CREATE or REPLACE package body MYPACK as ");
body.Append(" PROCEDURE TestVarchar2(");
body.Append(" Param1 IN AssocArrayVarchar2_t,");
body.Append(" Param2 IN OUT AssocArrayVarchar2_t,");
body.Append(" Param3 OUT AssocArrayVarchar2_t)");
body.Append(" IS");
body.Append(" i integer;");
body.Append(" BEGIN");
body.Append(" -- copy a few elements from Param2 to Param1\n");
body.Append(" Param3(1) := Param2(1);");
body.Append(" Param3(2) := NULL;");
body.Append(" Param3(3) := Param2(3);");
body.Append(" -- copy all elements from Param1 to Param2\n");
body.Append(" Param2(1) := Param1(1);");
body.Append(" Param2(2) := Param1(2);");
body.Append(" Param2(3) := Param1(3);");
body.Append(" -- insert some values to db\n");
body.Append(" FOR i IN 1..3 LOOP");
body.Append(" insert into T1 values(i,Param2(i));");
body.Append(" END LOOP;");
body.Append(" END TestVarchar2;");
body.Append("END MYPACK;");
command.CommandText = body.ToString();
command.ExecuteNonQuery();
command.CommandText="Commit";
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(command.CommandText + ":" + e.Message);
}
}
public static void Display(OracleParameterCollection collection)
{
foreach(OracleParameter p in collection)
{
Console.Write(p.ParameterName + ": ");
for(int i=0;i<3;i++)
{
if (p.Value is OracleString[])
Console.Write((p.Value as OracleString[])[i]);
else
Console.Write((p.Value as string[])[i]);
Console.Write(" ");
}
Console.WriteLine();
}
}
}
}
/********* Example End *************/
Regards
|
|
|
|
|
I found a really cool function at http://www.devx.com/tips/Tip/20009.
You pass in in a string with what it's deliminated by and it returns a temp table variable of all of the data split into rows..
i.e
select * from fn_SplitByID('oranges|apples|pears|lemons','|')
1 Oranges
2 Apples
3 Pears
4 Lemons
All really nice and pretty. The trouble is I have a table products (around 2000 rows). Each has a product_code (varchar(255) primary key), stock level (int), with a description field that contains deliminated text 'blue|green|pink' (varchar(255)), etc.
I want to extract and split the data into a whole new table:
product_id product_code stock_level description
1 ABC NULL BLUE
2 ABC NULL GREEN
3 ABC NULL PINK
4 DEFG NULL SILVER
5 DEFG NULL GOLD
...
Yeah I am not worried about the stock_level, just need a null value into the new table, as the stock would have to be recounted.
I just keep getting stuck on how to do this. Maybe I've been looking at too many different ways and the answer is straight in front of me. If you have any suggestions I would be grateful.
Many thanks
Kev
|
|
|
|
|
hi all,
In our Application ,we have to use 175 SQL Functions.So there are many dependancies between each other.
That mean ,If we creating a function,we have to use several function to create it.
So I need to create a function relation ship between each other.(like relatinship between Tables)
Please kindly tell me,Is there a way to get dependancies and relation in functions?
thanks in advance.
|
|
|
|
|
You could use sp_depends[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
