|
can u send me Example but Syntax is SQLSERVER 2000
|
|
|
|
|
exec splitinsert 'Jonh, Enghland $& Mike, VietName $& Bush, America'
CREATE PROCEDURE SplitInsert
@cNameCountry varchar(500)
AS
declare @nLocNameStart int
declare @nLocComma int
declare @nLocDelim int
declare @cName varchar(100)
declare @cCountry varchar(100)
select @nLocNameStart = 1
while (@nLocNameStart<len(@cNameCountry))
begin
select @nLocDelim = CHARINDEX('$&',@cNameCountry,@nLocNameStart + 1 )
select @nLocComma = CHARINDEX(',',@cNameCountry,@nLocNameStart + 1 ) + 1
if (@nLocDelim=0)
select @nLocDelim = len(@cNameCountry) + 1
select @cName = ltrim(SUBSTRING(@cNameCountry,@nLocNameStart,@nLocComma-@nLocNameStart-1))
select @cCountry = ltrim(SUBSTRING(@cNameCountry,@nLocComma,@nLocDelim-@nLocComma))
insert into tblNameCountry (Name,Country) values (@cName,@cCountry)
select @nLocNameStart = @nLocDelim + 2
end
GO
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
thanks u verry much but @cNameCountry varchar(500)
when u assgin cNameCountry is varchar or nvarchar have only character (4000) but when
larger 4000 only type TEXT or NTEXT but will have error "The text, ntext, and image data types are invalid for local variables."
And Your Example have while (@nLocNameStartbegin is error Syntax can u see again
-- modified at 23:37 Monday 24th July, 2006
|
|
|
|
|
AnhTin wrote: thanks u verry much but @cNameCountry varchar(500)
when u assgin cNameCountry is varchar or nvarchar have only character (4000) but when
larger 4000 only type TEXT or NTEXT but will have error "The text, ntext, and image data types are invalid for local variables."
If this is an issue, than you need to rethink your design.
AnhTin wrote: And Your Example have while (@nLocNameStartbegin is error Syntax can u see again
Look again...there is no variable called @nLocNameStartbegin You are missing a carriage return between Start and begin.
I actually ran the code I posted...and it does work.
|
|
|
|
|
Hello,
I have two tables users and news. Users table contains a column which consists of keywords seperated by "," e.g "C#,Java,OOPS" and in the news table there is a column description. I should search for keywords in description column.I am using freetext predicate to perform searching.Now my doubt is
I have to search for each user the complete news tables. So I have to write a subquery. I should get the keywords from the user table and submit it to news tables in the freetext predicate. freetext predicate is not allowing me to give a subquery.
i am giving u the query here
select newsid from news where freetext(description,' "C#,java"' )
for the above query I will get the list of newsid where the description contains either c# or Java or both.
now in the above query i should get the keywords dynamically from the users tables for each user.
if i am giving it as below it is giving me error
select newsid from news,users u where freetext(description,select keywords from users where userid = u.userid )
I want to retrieve the data in a single query with the help of subqueries.
Can some one give a solution for this problem.
WIth Regards
Uma
|
|
|
|
|
see your code again:
select newsid from news,users u where freetext(description,select keywords from users where userid = u.userid )
you must change it as belows as you want to use:
1-select newsid from news,users u where freetext in (select keywords from users where userid = u.userid )
2-select newsid from news,users u where freetext=(select keywords from users where userid = u.userid )
in your case one of this codes can be used ,
i can't understand what is the description in subquery and i delete it.
Human knowlege belongs to the world
|
|
|
|
|
select newsid from news,users u where freetext(description,select keywords from users where userid = u.userid )
In the above code description is name of the column
and the syntax is select collist from table where freetext(colname,searchlist)
freetext is a phrase used in where clause
|
|
|
|
|
ok.sorry about my less knowledge,
i see syntax of freetext ...
but another way is here:
select newsid from news,users u where description in(select keywords from users where userid = u.userid )
Human knowlege belongs to the world
|
|
|
|
|
Its ok.Even I learnt abt it recently. But when u say col in(list) column cannot contain descriptive text. column should contain single value. It will not help me for this situation.
|
|
|
|
|
Hi,
i have one textbox and one litstbox. If i am typing some thing on the textbox and i am selecting one value from the listbox if i am clicking one buttion then the textbox value and listbox value should be placed in the datagrid. Can you tell how to achive this
|
|
|
|
|
u acn create datatable and insert new rows in it and then
can bind the datagrid with this datatable to show data in datagrid
on button click event.
rahul
|
|
|
|
|
Hi,
I am learning C#/VS2003 and wish to do the following:
Database MyClub contains two tables, MemberDetails and Titles. Both tables have a field TitleNumber. The MemberDetails.TitleNumber has a number that corresponds to the titles description/name that is in the same row as Titles.NameDesc and Titles.TitleNumber. Pretty basic relational database stuff  I know.
What I want to do is bind a textBox to show the Titles.NameDesc that is contained by reference to the number in MemberDetails.TitleNumber.
There's heaps of help on the net relating to datagrids, listboxes, comboboxes (which I can mostly understand and have used elsewhere in my project) but I can't find anywhere that will help me understand how to display this in a textBox.
At present the binding points to MemberDetails.TitleNumber but I want it to display the equivalent Titles.NameDesc.
An example code reference would be most helpful as I am not confident enough that my terminology is pointing me to a reference on the net (via Google) that explains what I want to do.
TIA
Glen Harvy
|
|
|
|
|
Something like this.
Here is a function that returns a dataTable
public static DataTable GetDataTable(string ProcName, Hashtable Paramters)
{
SqlCommand cmd = new SqlCommand(ProcName);
cmd.CommandType = CommandType.StoredProcedure;
SqlConnection conn = UAIG.SQL.ConnectionManager.GetConnection();
cmd.Connection = conn;
ApplyValues(ref cmd, Paramters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
cmd.Connection.Open();
da.Fill(dt);
cmd.Dispose();
return dt;
}
DataRow r = dt.rows[0];
Textbox1.text = r["policy"].ToString();
I'm assuming you can figure out how to get a datatable from this code. Then set the textbox.text property equal to
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
|
|
|
|
|
ToddHileHoffer wrote: DataRow r = dt.rows[0];
Textbox1.text = r["policy"].ToString();
I'm assuming you can figure out how to get a datatable from this code. Then set the textbox.text property equal to
Can you finish the sentence please .. it is the important part 
Glen Harvy
|
|
|
|
|
Is there a document somewhere (yes i've looked) that lists the restrictions/limitations for different cursor types.
I'm mainly looking for something in an ODBC context but would be happy enough with SQLSrv specific.
I have seen: http://blogs.msdn.com/mssqlisv/archive/2006/06/23/644493.aspx
"Generally, a (FAST) FORWARD-ONLY cursor is the most performant, followed by DYNAMIC, KEYSET, and finally STATIC which is generally the least performant.
For example, if a FAST FORWARD ODBC API Server Cursor referenced a text column, it would typically degrade to a DYNAMIC cursor, however if the query also generates an internal work table because an ORDER BY condition is not covered by an index, then the cursor would degrade to a KEYSET cursor. If at this point the query also referenced a table without a unique index the cursor degraded further to a STATIC cursor. This is an extreme example; however it illustrates the sequence of events that previous versions of SQL Server used to degrade a cursor to a progressively 'more expensive' cursor
type."
This is great, but i expect incomplete.
Nor does it explain why a text column would cause a forward-only cursor to
'degrade', or ...
...cmk
Save the whales - collect the whole set
|
|
|
|
|
I think the only decent answer you'll get will be from the Microsoft SQL Server team. I'm assuming that you don't already have a blogs.msdn.com profile. On the top-left of the page click Sign In, then follow the screens to create a profile and log in. Once you've done this you'll be able to leave a comment on that blog entry.
I suspect it's something like that: FAST FORWARD ONLY cursors only work if all the data is in-row - text, ntext and image columns are stored externally, on a different database page, to the normal data (the row contains a pointer to the text/ntext/image data). A DYNAMIC cursor only works if the data is in the order it comes from the database tables but can cope with off-row data. A KEYSET cursor requires that there's a primary key value available for the data (the cursor data consists of the set of keys). A STATIC cursor is just a complete copy of the results retrieved.
|
|
|
|
|
Thanks Mike, your remarks are in line with what i was thinking.
I'll take a look a the blog page sign-in.
The only reason this is an issue as some C++/ODBC code broke (a couple queries) moving from SQL Srv 2000 to Sql Srv 2005. It turns out some cursors that were being silently degraded aren't anymore. As such i want to get a better handle on what they sould be to start with.
Again, thanks
cmk
[EDIT]
FYI, Just fuond:
http://msdn2.microsoft.com/en-us/library/ms190641.aspx[^]
[/EDIT]
-- modified at 18:53 Monday 24th July, 2006
|
|
|
|
|
May i know what is the problem in my code below?
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
If TextBox1.Text <> "" Then
Dslogin1.Clear()
OleDbDataAdapter1.SelectCommand.CommandText = _
"SELECT * FROM Login WHERE username = " &
TextBox1.Text & ""
OleDbDataAdapter1.Fill(Dslogin1, "Login")
DataGrid1.SetDataBinding(Dslogin1,"Login")
End If
Catch e1 As Exception
MessageBox.Show(e1.ToString)
End Try
End Sub
i cant search the username from the textbox1.
|
|
|
|
|
ghost181 wrote: OleDbDataAdapter1.SelectCommand.CommandText = _
"SELECT * FROM Login WHERE username = " &
TextBox1.Text & ""
Include Quotation marks:
OleDbDataAdapter1.SelectCommand.CommandText = _
"SELECT * FROM Login WHERE username = '" &
TextBox1.Text & "'"
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Ignore the last response because he doesn't actually solve your real problem. Your real problem is the code is susceptable to a SQL Injection Attack.
You should use parameterised queries. e.g.
OleDbDataAdapter1.SelectCommand.CommandText =
"SELECT * FROM Login WHERE username = ?";
OleDbDataAdapter1.SelectCommand.Parameters.Add("", TextBox1.Text);
This will go some way to protecting your database from attack.
You will want to read SQL Injection Attacks and Tips on How to Prevent Them[^] which tells you what a SQL Injection Attack is, how to spot code that is susceptable and how to correct the problem.
|
|
|
|
|
Thanks Colin Angus Mackay, i solve the problem already.
|
|
|
|
|
Colin Angus Mackay wrote: SQL Injection Attacks and Tips on How to Prevent Them[^] which tells you what a SQL Injection Attack is, how to spot code that is susceptable and how to correct the problem.
As often as that article is referenced why not just include it in your sig?
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
S Douglas wrote: As often as that article is referenced why not just include it in your sig?
I don't currently have room. I'll consider it when space frees up a bit.
|
|
|
|
|
Hi, i don't know if is possible to do this: I want to obtain by a single query, values from 2 identical tables, ok, i can do a query from multiple tables and that will return values with no problem BUT these values are returned horizontally but how can i get these values vertically, i hope you understand the terms horizontally and vertically. If you think what i'm trying to do, is to execute 2 queries in 1.
Example:
select t1.col1, t2.col1 from table1 as t1, table2 as t2
-> 1, 2
-> 4, 7
-> 8, 9
but how can i get this?
-> 1
-> 2
-> 4
-> 7
-> 8
-> 9
or this
-> 1
-> 4
-> 8
-> 2
-> 7
-> 9
|
|
|
|
|
SELECT t1.col1 FROM table1 AS t1
UNION
SELECT t2.col1 FROM table2 AS t2
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
