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 to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
Cindy B
Hi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Data
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex data
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:

> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of the
> implimentation. I need to identify the complete file structure in order to
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run per
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for your
> time and help!
> --
> Cindy B
|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_P RECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
Cindy BHi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Dat
a
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex dat
a
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:

> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of t
he
> implimentation. I need to identify the complete file structure in order t
o
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run pe
r
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for you
r
> time and help!
> --
> Cindy B|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_PRECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
--
Cindy BHi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Data
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex data
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:
> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of the
> implimentation. I need to identify the complete file structure in order to
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run per
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for your
> time and help!
> --
> Cindy B|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_PRECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

Query time massively different between App and QA

We have a query (a few actually) which runs for about 30 secs via Siebel 6 and the same query takes on 1 or 2 secs in Query Analyser, consistently. Naturally this performance problem is causing issues. We're wondering if the App isn't using the same execution plan, or using the indexes or...
It might be worth noting that the query returns 1 or no rows.
(I can add the query and more detail if anyone really, really wants :rolleyes: )

The devlopers have created a VB app which mimmics to app running the query and we've put it through proflier, results..
Duration Reads CPU
Siebel Query 28300 3661280 23984

It seems a high number of reads there, and the result from QA is SO much faster.
Any ideas welcome, thanks.Mmmmmmm, looks to me like your application is likely to be the root cause. How does Siebel access the DB? Named Pipes, TCPIP?|||I'm told Siebel uses Names Pipes.|||Mmmmm, seen named pipes cause an issue before, I think the app ends up scanning through several ports before finding the correct one?

Will do some head scratching|||There are a number of tools that use an older TDS library (some even using DB-Library) that get lousy performance from queries that run well in Query Analyzer. It seems to me that Siebel can be fixed by simply upgrading the client machine's MDAC (http://msdn.microsoft.com/data/mdac/default.aspx), but I don't regularly run Siebel so I'm not certain of that.

-PatP|||Now that is a smart idea. JamesB, you may want to check which version of MDAC the client machines have. I think the current version of MDAC is 2,7. 2.8 has apparently just been released but judging from all the woes on the forum I doubt that would be the one to use at the moment.

Ideally, the client and server MDACs should be the same version.