Query word starting with....from string caught in web app

Hi all
This should be easy for one of you pros out there...
I take a single string(one letter) from a textbox and want to query my
database for a name that starts with that letter.
I know that the correct synthax when you know the letter is:

WHERE FirstName LIKE 'O%'

but when I have this:
*****************
Dim lastname As String
lastname = txtlastname.Text

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like ' txtlastname % ' "
**********************************************
It doesn't seem to work since my datagrid is empty afterwards. I also
tried:
***************
Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like '" & txtlastname & " % ' "
****************
without succes...
It's probably just a synthax error on my part but I don'T know were..
THanks guys!!
JMTI got it to work with my variable 'lastname'
Got the answer form another forum, here's how:

And Last_Name like '" & lastname & "%'"

If it can help someone!!
JMT|||It may just be a posting error, but it looks as if you have a couple of
spaces in your string construction; the second looks closer to correct:

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE
Last_Name
like '" & txtlastname & "%'

Assuming that PUBNAME is a table named TABLE, and txtLastName = A, then
the final SQL String should look like:

SELECT * FROM [TABLE] WHERE Last_Name LIKE 'A%',

Stu|||I got it to work with my variable 'lastname'
Got the answer form another forum, here's how:

And Last_Name like '" & lastname & "%'"

If it can help someone!!
JMT

Query word starting with....from string caught in web app

Hi all
This should be easy for one of you pros out there...
I take a single string(one letter) from a textbox and want to query my
database for a name that starts with that letter.
I know that the correct synthax when you know the letter is:

WHERE FirstName LIKE 'O%'

but when I have this:
*****************
Dim lastname As String
lastname = txtlastname.Text

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like ' txtlastname % ' "
**********************************************
It doesn't seem to work since my datagrid is empty afterwards. I also
tried:
***************
Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like '" & txtlastname & " % ' "
****************
without succes...
It's probably just a synthax error on my part but I don'T know were..
THanks guys!!
JMTIt may just be a posting error, but it looks as if you have a couple of
spaces in your string construction; the second looks closer to correct:

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE
Last_Name
like '" & txtlastname & "%'

Assuming that PUBNAME is a table named TABLE, and txtLastName = A, then
the final SQL String should look like:

SELECT * FROM [TABLE] WHERE Last_Name LIKE 'A%',

Stu

Query with quotation mark.

I have problem with sql query with aspnet.

In my web page, i have a text box for input name or string to search in database. I have problem if the search string consist of quotation mark =>' .

Any suggestions? Thanks.

Use a parameterized query instead of building a SQL String to execute. If you must continue building a SQL string, then you need to replace all the quotes in any string field with two quotes.

Example:

strSQL="SELECT * FROM table WHERE field1='" & textbox1.text & "'"

You can change that to:

strSQL="SELECT * FROM table WHERE field1='" & textbox1.text.replace("'","''") & "'"

or:

dim conn as new sqlconnection(configurationmanager.connectionstrings("{your key here").ConnectionnString)

conn.open

dim cmd as new sqlcommand("SELECT * FROM table WHEREfield1=@.field1",conn)

cmd.parameters.add(new sqlparameter("@.field1",sqldbtype.varchar))

cmd.parameter("@.field1").value=textbox1.text

' Execute the following line if you want a datareader

dim dr as sqldatareader=cmd.executedatareader

' Execute the following 4 lines if you want a dataset

dim ds as new dataset

dim da as sqldataadapter=new sqldataadapter(cmd)

da.fill(ds)

conn.close

|||

a parameterized query is definately the way to go.

If you continue to use concatenated SQL statements, you are opening yourself up to sql injection attacks.

|||Another option is to use a Stored Procedure - this way you don't have to write an SQL statement at all in your web application. You also gain a performance benefit since the Procedure is already compiled in SQL Server.

dim objCmd as sqlcommand
dim objRdr as sqldatareader

objCmd = new sqlcommand(spNameHere, dbConnObjectHere)
objCmd.commandType = commandtype.storedprocedure
objCmd.parameters.Add("@.SearchField", textBox1.text)

objRdr = objCmd.executeReader

When you use the parameters collection in this way there is no need to worry about single quotes, percent signs, or other characters messing with your query. You can add as many parameters as you need to.|||

rich freeman wrote:

You also gain a performance benefit since the Procedure is already compiled in SQL Server.

Beginning with SQL Server 2000, the execution plan of a parameterized sql statement is also cached just like with a stored proc. Therefore performance differences between stored procs and parameterized sql statements should not be used as a significant factor when choosing one technique over the other.

|||

Thanks you everybody for the reply. All the replies are really informative (thumbs up!)! With the suggestions from you all, I have finally solved the problem. Thanks a lot!!!

Query with changing table names

We have a web tracking program that came with our firewall that writes it's
data to a MSDE database.
Unfortunately it writes each day's data to a different table. It names them
connection_events_2005_10_20 then connection_events_2005_10_21 etc... I need
to create a report by the w from all of these tables. Is there away to
query all of the tables that start with "connection_events_ " at the same
time? Is there a different way to deal with this?
I an trying to do this through an Access 2003 .ADP. I posted to that user
group and was refered here.
Thanks in advance for your suggestions.
SteveYou could set up a job to create a new view each day that would select from
the "ellusive" tables.
Tables names must be listed explicitly - wildcards are not applicable.
Basically, what you need is a view like in this example:
select <columns>
from <table_name>_2005-10-27
union all
select <columns>
from <table_name>_2005-10-28
union all
select <columns>
from <table_name>_2005-10-29
...
ML|||>> it writes each day's data to a different table. It names them connection
_events_2005_10_20 then connection_events_2005_10_21 etc... <<
You have just re-discovered 1950's magnetic tape processsing! You
almost mimicked the IBM naming conventions which gave tape labels the
markers 'yyddd' that we used when we did not have RDBMS!
Steve, you reallllllllllly need to stop programming and catch up with
RDBMS technology. You have missed the basics of RDBMS.
You keep the same data in the same table, period. A table is a set of
ALL --repeat ALL-- of those data elements. This is founjdations, not
rocket science. In RDBMS, you have a column (NOT a field like in the
1950's) that gives a duration, then you create VIEWs or derived tables
or subqueries.|||> You keep the same data in the same table, period.
well we have been using partitioning and UNION ALL views for quite a
while, not because we like doing it, but because it really boosts
performance. The performance price of following the advice to "keep the
same data in the same table" would be so huge - anyone actually doing
so might be fired on the spot...
Makes sense?|||I agree with your observations, however the program was written by the
firewall manufacturer so I have no control over it. I was surprised to see
how they setup this up but they aren't going to make changes for little 'ol
me. Their reports are lacking to say the least, so that's why I'm stuck with
this problem. Since I can't fix their program, are there any suggestions on
how to create a query that can pull the data from these tables together so
it's useful?
Thanks
Steve
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130449917.894438.155260@.z14g2000cwz.googlegroups.com...
> You have just re-discovered 1950's magnetic tape processsing! You
> almost mimicked the IBM naming conventions which gave tape labels the
> markers 'yyddd' that we used when we did not have RDBMS!
> Steve, you reallllllllllly need to stop programming and catch up with
> RDBMS technology. You have missed the basics of RDBMS.
> You keep the same data in the same table, period. A table is a set of
> ALL --repeat ALL-- of those data elements. This is founjdations, not
> rocket science. In RDBMS, you have a column (NOT a field like in the
> 1950's) that gives a duration, then you create VIEWs or derived tables
> or subqueries.
>|||I have hacked a script togethor that might do what you want. Let me
know what you think. I used a couple of previous posts on this group to
help. I am a bit of a newbie myself. I have tested the script as far as
the creation of the UNION SQL but not the overall procedure.
Celtic_Kiwi
/* Script begins */
CREATE PROCEDURE upLogViewCreate
AS
declare @.tablename varchar(32)
declare @.sql varchar(4000)
declare tnames_cursor cursor
for
select name
from sysobjects
where type = 'U'
and left(name,3) = '200'
open tnames_cursor
fetch next from tnames_cursor into @.tablename
set @.sql = ''
while (@.@.fetch_status <> -1)
begin
if (@.@.fetch_status <> -2)
begin
if @.sql = ''
begin
set @.sql = 'select * from ' + @.tablename
end
else
begin
set @.sql = @.sql + ' union select * from ' + @.tablename
end
end
fetch next from tnames_cursor into @.tablename
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
if ( (exists
(select *
from dbo.sysobjects
where id = object_id(N'[dbo].[vwLogView]')
and OBJECTPROPERTY(id, N'IsView') = 1
)
and
(@.sql<>'')
)
drop view [dbo].[qryNotesCandiac]
EXEC('CREATE VIEW dbo.vwLogView
AS ' + @.sql )|||>> we have been using partitioning and UNION ALL views for quite a while, no
t because we like doing it, but because it really boosts performance <<
No, we did it until SQL engines did a good job of implemenation that we
did not have to. Have you seen what DB2 does to detect a DW (fact and
dimension tables) ?
That is why we do not ever confuse implementation with logical models.
A partitioned table is not like multiple table at all. You are still
thinking that logical = physical because you are priogramming as if
this was the 1960's.|||You can do this with dynamic sql it goes like this;
steps;
1. select all tables from sysobjects table one after another
2. put that in to variable and use union query in dynamic sql
3. use exec(that query string) or sp_executesql
4. you have all rows in one view/derived table
Regards
R.D
--Knowledge gets doubled when shared
"Steve Roberts" wrote:

> We have a web tracking program that came with our firewall that writes it'
s
> data to a MSDE database.
> Unfortunately it writes each day's data to a different table. It names the
m
> connection_events_2005_10_20 then connection_events_2005_10_21 etc... I ne
ed
> to create a report by the w from all of these tables. Is there away to
> query all of the tables that start with "connection_events_ " at the same
> time? Is there a different way to deal with this?
> I an trying to do this through an Access 2003 .ADP. I posted to that user
> group and was refered here.
> Thanks in advance for your suggestions.
> Steve
>
>|||>> The performance price of following the advice to "keep the same data in
It depends on the specific schema/DBMS/physical implementation and the
performance price cannot be generalized. However the integrity issues
involved in doing so can be generalized. For some details, google for
"Principle of Orthogonal Design"
Anith|||Could you try creating views in advance of the firewall creating the tables?
then point all the views at the same table and have it log into a single
place?
IIRC, MSDE comes with sql agent and you could schedule a job to create the
views 7 days in advance, and destroy them once they're "old".
"Steve Roberts" <Stever@.Discussiongroups.com> wrote in message
news:OMwmRM12FHA.3296@.TK2MSFTNGP09.phx.gbl...
> I agree with your observations, however the program was written by the
> firewall manufacturer so I have no control over it. I was surprised to see
> how they setup this up but they aren't going to make changes for little
'ol
> me. Their reports are lacking to say the least, so that's why I'm stuck
with
> this problem. Since I can't fix their program, are there any suggestions
on
> how to create a query that can pull the data from these tables together so
> it's useful?
> Thanks
> Steve
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1130449917.894438.155260@.z14g2000cwz.googlegroups.com...
<<
>

Query timeouts

I've created a report in RS 2006 that uses an XML data source. The source
uses a web service that takes sufficiently long to return that my query times
out.
can anyone tell me how can I increase the timeout value of the query?Click on the ... of the dataset. The timeout is on that page.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ekkis" <ekkis@.discussions.microsoft.com> wrote in message
news:CC99FCCF-D3B5-4AB6-AD31-BE35A002748E@.microsoft.com...
> I've created a report in RS 2006 that uses an XML data source. The source
> uses a web service that takes sufficiently long to return that my query
> times
> out.
> can anyone tell me how can I increase the timeout value of the query?
>

Query Taking too Long

Below is my query which is taking a long time to execute, DB is SQL Server 2005 through a web Application
I have downloaded the latest MS SQL 2005 driver 1.xxx and still the query takes long to execute

The Description field is a Full_text indexed catalog column
the p.vendornumber is a primary key same with c.ID

Any one have an idea why it is taking this long to run

The Execution Time is: 13640 ms Which I think is very long

SELECT Upper(p.Type) Type,p.Modelname,p.partno,Upper(p.description) description,
Upper(p.classification)classification,p.vendornumber,p.mfg,
p.price,c.CompanyName,c.City,c.State,p.thumbnail
FROM P_all p, Acts c
WHERE p.vendornumber = c.ID
AND CONTAINS(p.Description, '"helmet*"')
Order by p.VendorNumber

Thanks

First off, since you are using a WHERE criteria of [ p.VendorNumber ], the ORDER BY is pointless, and depending upon the resultset, perhaps even wasting time.

Then, I think the most significant part of the query is

FROM P_all p, Acts c

which 'may' have the effect of causing a CROSS JOIN.

Use the 'proper' form for a JOIN, including the JOIN conditions. (I've assumed that VendorNumber is common to both tables, if that is incorrect, then use the correct linking column.)

Code Snippet

SELECT
Type = upper( p.Type ),
p.ModelName,
p.PartNo,
Description = upper( p.Description ),
Classification = upper( p.Classification ),
p.VendorNumber,
p.Mfg,
p.Price,
c.CompanyName,
c.City,
c.State,
p.Thumbnail
FROM P_All p
JOIN Acts c
ON p.VendorNumber = c.ID
WHERE contains( p.Description, '"helmet*"' )
ORDER BY p.VendorNumber

|||Thanks Arnie,

The changes still did not solve the problem.
|||

what are indexes on these table. And also remove Upper functions from all column and see the performance. and post back the result of the query without upper function

Madhu

|||There are indexes on the table. The culprit column "Description, is a Full-text indexed catalog column. The main searches are on this column based on character search. I was using the "like" search before creating the "Full-text indexed catalog.

The Upper function is not the problem, the query takes longer to run without the Upper function.

Are there more things I need to do in the SQL 2005 Database? I have installed the latest MS JDBC drivers for MS SQL 2005.

Thanks
|||If by chance, this database was migrated to SQL 2005 from SQL 2000, you would need to rebuild the indexes, and update statistics.

Query String using Web Matrix

Hi folks,

I have two tables in one database.

One table has an automatic numbering primary key named ID and is named rfi.

The other table has a field named rfinumber and is named discussion.

Both ID and rfinumber have a datatype of number.

Upon using the querybuilder with Web Matrix, I issue a select command to get all records from the discussion table that have a rfinumber field equal to the ID field in the rfi table.

Problem is that I am getting the entire discussion table when I use the following query:

"SELECT [discussion].* FROM [discussion], [rfi] WHERE ([discussion].[rfinumber] = [rfi].[ID])"

For example

DISCUSSION TABLE
rfinumber
1
1
1
2

RFI TABLE
rfi
1
2

Given the query, I should get a discussion table only listing the rfinumber = 1.

When I run the query from my database package it runs fine??

Any clues?

thanks,
glenn

gmeadows:

Problem is that I am getting the entire discussion table when I use the following query:

That's exactly what I would expect. You are returning a row from discussion matched with each row from rfi where the rfinumber from discussion is the same as the ID from rfi. Why would you expect it only to return those rows where rfinumber = 1? You need to use a parameter of some sort to make that happen.