Query Word Starting With....from String Caught In Web App

Jul 23, 2005

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!!
JMT

View 3 Replies


ADVERTISEMENT

The String Was Not Recognized As A Valid DateTime. There Is A Unknown Word Starting At Index 0.

May 2, 2008

Hi,

I got this error report "The string was not recognized as a valid DateTime. There is a unknown word starting at index 0." at this line of code in BOLD.

For some reasons the datetime format in the database has the (US datetime format - 05/02/2008 06:40 instead of United Kingdom (UK date time format - 02/05/2008 06:40), as I had planned. I have tried to reformat the database and VB date format and all to no success.Could this be the root of my problems?










Code Snippet
Public Class Form1

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

If con.State = ConnectionState.Open Then con.Close()
con.Open()
cmd = New SqlCommand("SELECT REM_NOTE, REM_DATE FROM dbo.MY_CONTACTS", con)


Dim sdr As SqlDataReader = cmd.ExecuteReader
While sdr.HasRows = True

Dim newAlert As New Alert(Reminder_data_reader("REM_NOTE").ToString(), DateTime.Parse(Reminder_data_reader("REM_DATE").ToString()))
Me.collectionOfAlerts.Add(newAlert)


End If
End While
sdr.Close()
con.Close()

End Sub




End Class

View 3 Replies View Related

System.FormatException: The String Was Not Recognized As A Valid DateTime. There Is A Unknown Word Starting At Index 0.

Sep 26, 2004

i cannot seem to get rid of this error. My page works in other functions that have the same syntax and format. just this one doesnt. i tried changing the format but still the same thing.
SQL DB is DateTime. Any Suggestions?


Function UpdateException(ByVal qID As String, ByVal exception_Status As String, ByVal completed As String, byval notes as string, byVal Username as string) As Integer
dim strDate as date = datetime.now()
dim strdate1 as string = strDate.tostring("MM-dd-yy")
Dim connectionString As String = "server='servername'; trusted_connection=true; Database='CPD_Reports'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "UPDATE [Master_CPD_Reports] SET [Exception_Status]=@Exception_Status, [Completed]"& _
"= @Completed, [Completion_Date]= @Completion_Date, [Username]= @Username WHERE ([Master_CPD_Reports].[QID] = @QID)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

sqlCommand.Parameters.Add("@QID", System.Data.SqlDbType.VarChar).Value = qID
sqlCommand.Parameters.Add("@Exception_Status", System.Data.SqlDbType.VarChar).Value = exception_Status
sqlCommand.Parameters.Add("@Completed", System.Data.SqlDbType.VarChar).Value = completed
sqlCommand.Parameters.Add("@Completion_Date", System.Data.SqlDbType.varchar).Value = strDate1
sqlCommand.Parameters.Add("@Username", System.Data.SqlDbType.DateTime).Value = UserName
dim NotesUpdate as string = "INSERT INTO [CPD_Notes] ([qID], [Notes], [Notes_Date], [Username]) VALUES (@qID, @notes, @Date, @UserName1)"
Dim sqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(NotesUpdate, sqlConnection)
sqlcommand1.parameters.add("@qID", system.data.sqldbtype.varchar).value = qID
sqlcommand1.parameters.add("@Notes", system.data.sqldbtype.varchar).value = notes
sqlcommand1.parameters.add("@UserName1", system.data.sqldbtype.varchar).value = Username
sqlcommand1.parameters.add("@Date", system.data.sqldbtype.datetime).value = DateTime.now()
Dim rowsAffected As Integer = 0
trace.warn(strdate1)

sqlConnection.Open
Try
rowsAffected = sqlCommand.ExecuteNonQuery
Finally
End Try

dim rowsAffected1 as integer = 0

Try
rowsAffected1 = sqlCommand1.ExecuteNonQuery
Finally

End Try

Return rowsAffected
return rowsaffected1
sqlConnection.Close
End Function

View 4 Replies View Related

Query For Taking Each Word In A String, And Putting A Comma After It?

Aug 29, 2007

Hey all, i'm making the pages meta keywords on my site dynamic, and i was wondering is there is a string, for example "Dell 17" Monitor Brand New", that would split it into each word for the meta keywords. example "Dell, 17", Monitor, Brand, New" (and possibly to not put a comma on the last word?) 

View 1 Replies View Related

How To Read Third Word From A String?

Feb 6, 2007

Hallow, everyone:

I want to read third word from a string. For example, the string is,

ABCDE fghijk 031 LPN OPQ

I need 031. How to read it?

Any help will be appreciated.

Thanks

ZYT

View 12 Replies View Related

Word Search In String

Feb 25, 2008



Hi All,

Please help, it is urgent

I am having a colume as image datatype, in which candidates resume is stored in binary format.
I have to search for particular words for eg. "ASP.Net, 2 Years" into that column
I have converted that column as varchar and then searching above two words(after spliting them by comma) into that string using CHARINDEX


DECLARE @i int, @j int
SET @i = select MIN(ResumeId) from table_name
SET @j = select MAX(ResumeId) from table_name

WHILE (@i <= @j)
BEGIN
DECLARE @Resume varchar(max)
SET @Resume = CAST((select Resume from table_name where ResumeId=100) as varbinary(max))

--code to search the particular word from @Resume

END

It is working fine, but it is very slow, taking 2+ minutes to scan 15000 records
How can I make it faster or is there any other way to search?

Thanks in advance


View 7 Replies View Related

How To Extract Random Word In A String

Aug 29, 2013

Creating a stored procedure in extracting a word in a sentence.

Example:
String=The quick brown fox JuMp over the lazy dog.

I want to extract a random word on the string.

View 1 Replies View Related

Extract Specific Word From String

Oct 15, 2015

I have a values a column like this 

Morning(07:00-16:30)
Morning(09:30-18:30)
Morning(11:00-20:00)
Afternoon(14:00-23:00)
Afternoon(16:00-01:00)
Morning(08:00-17:00)
Morning(10:00-19:00)
Morning(09:00-18:00)

So i just only need 07:00-16:30 this kind of part from above all these string in sql server.

View 4 Replies View Related

Transact SQL :: Extracting A Word From String?

Jun 30, 2015

Is there a function / way to extract a word from a particular position in the String.

I want to extract a word which is in the 16th position. All words are separated by spaces.

View 6 Replies View Related

How To Check If The Word Is In String (Case Sensitive)

Aug 16, 2013

I have a scenario which i must check if the value of the inputted word is exact (case sensitive) in a string. And print the word if correct. using a user defined function.

Example:The quick brown fox jump over the lazy dog.

If the user enter "jump" it must print "jump".
If the user enter "Jump" it must print "jump" which is the correct word.

View 4 Replies View Related

Format Of The Initialization String Does Not Conform To Specification Starting At Index 0.

Apr 18, 2007

can't figure out this error. i think i'm writing something wrong in the connnection string. i'm using SQL Server Express, if that helps. in my config file:      <connectionStrings>        <add name="OfficialScribblerConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|OfficialScribbler.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>    </connectionStrings>  in my aspx file:Dim DBConnection = New OleDbConnection("OfficialScribblerConnectionString") DBConnection.Open() Dim SQLString As String = "SELECT PostedDate From Entries_tbl ORDER BY PostedDate"   

View 5 Replies View Related

Format Of The Initialization String Does Not Conform To Specification Starting At Index 0

Jun 6, 2007

 Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [ArgumentException: Format of the initialization string does not conform to specification starting at index 0.]   System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue) +1242   System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +128   System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +102   System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +52   System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +24   System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +125   System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) +56   System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +4   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +138   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70   System.Web.UI.WebControls.GridView.DataBind() +4   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69   System.Web.UI.Control.EnsureChildControls() +87   System.Web.UI.Control.PreRenderRecursiveInternal() +41   System.Web.UI.Control.PreRenderRecursiveInternal() +161   System.Web.UI.Control.PreRenderRecursiveInternal() +161   System.Web.UI.Control.PreRenderRecursiveInternal() +161   System.Web.UI.Control.PreRenderRecursiveInternal() +161   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

View 2 Replies View Related

Format Of The Initialization String Does Not Conform To Specification Starting At Index 0.]

Feb 4, 2008

I received this error my server:
[ArgumentException: Format of the initialization string does not conform to specification starting at index 0.]   System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue) +1242   System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +128   System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +102   System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +52   System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +24   System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +125   System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) +56   System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +4   System.Data.SqlClient.SqlConnection..ctor(String connectionString) +21   admin_ratings.Page_Load(Object sender, EventArgs e) +1345   System.Web.UI.Control.OnLoad(EventArgs e) +99   System.Web.UI.Control.LoadRecursive() +47   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
Here's my code, and this worked fine with the 3rd connection string on my local machine. It did the update to the proper fields in my db:
 Dim Original_WebsiteID As Integer = tblwebsiteRow.WebSiteID Dim conRatings As SqlConnection
Dim strUpdate As StringDim cmdUpdate As SqlCommand conRatings = New SqlConnection(ConnectionString = "Connect Timeout=8; Integrated Security=True;Database=linkexchanger; Server=************;uid=****; pwd=********; ")
'conRatings = New SqlConnection(ConnectionString = "Connect Timeout=8; Initial Catalog=linkexchanger; Data Source=******; uid=*****; pwd=*****;")
'conRatings = New SqlConnection("Server=SCOTSQLEXPRESS;Initial Catalog=linkexchanger;Integrated Security=True;Connect Timeout=8;")
strUpdate = "UPDATE tblWebSite SET Rating = " & Rate & "WHERE (WebSiteID = " & Original_WebsiteID & ")"cmdUpdate = New SqlCommand(strUpdate, conRatings)
conRatings.Open()
cmdUpdate.ExecuteNonQuery()
conRatings.Close()
Next
End Sub

View 10 Replies View Related

Script Component Task: Function That Returns Last Word In A String

Sep 7, 2006

I have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task

create a function called CleanString (ByVal CustomerName as String) As String

CleanString Returns  the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd

ie parse 'Mr John Tools'  and the function returns 'Tools'

ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'

ie parse 'Microsoft Corp' return 'Microsoft Corp'

ie parse 'Digidesign Ltd' return 'Digidesign Ltd'

Any idea of a regular expression or existing piece of existing code I can have

thanks in advance

dave

 

View 3 Replies View Related

Transact SQL :: Find Exact Word In String Within Case Statement

Aug 6, 2015

I am required to find or check that a specific word exists in string or not.

Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.

1. 'St is valid;'    -> true
2. 'DOB is valid;ST is invalid;'    -> true
3. 'DOB is valid; ST is invalid;'    -> true
4. 'DOB is valid;invalid ST;'    -> true
5. 'DOB is valid; invalid ST;'    -> true
6. 'DOB is valid; invalid STate;'    -> false

Means the exact ST should be search. It is  not free text search.

T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.

View 10 Replies View Related

What About Batch Jobs Caught In A Cluster Failover?

Jul 30, 2002

What happens to batch jobs running in a SQL Server 2000 clustered environment during the failover process?

Assuming the jobs fail during the failover process, how should they be handled?

View 1 Replies View Related

Fatal Exception C0000005 Caught / EXCEPTION_ACCESS_VIOLATION

Feb 27, 2007

Hi,

I want to share an experience

Here are our server specs:
Intel Xeon 3.40Ghz [Duo]
4 Gb Memory
Raid 1 120Gb HDD
Windows 2000 Server
SQL Server 2000 Enterprise Edition


we tweaked the configuration in sp_configure

changed the following parameters
sp_configure 'max server memory (MB)',3000
go
reconfigure with override
go
--old value: 2002


sp_configure 'min server memory (MB)', 2500
go
reconfigure with override
go
--old value: 2048

sp_configure 'open objects', 2147000000
go
reconfigure with override
go
--old value: 2147483647

sp_configure 'awe enabled', 1
go
reconfigure with override
go
--old value: 0

sp_configure 'max worker threads', 255
go
reconfigure with override
go
--old value: 255

shutdown the database and out came this:

* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 00000008
*
************************************************** *****************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 00408E8B Module(sqlservr+00008E8B)
* 00408F5E Module(sqlservr+00008F5E)
* 00409056 Module(sqlservr+00009056)
* 00408E5E Module(sqlservr+00008E5E)
* 004020A8 Module(sqlservr+000020A8)
* 00401FCB Module(sqlservr+00001FCB)
* 00572114 Module(sqlservr+00172114) (SQLExit+0000A61D)
* 005655FD Module(sqlservr+001655FD)
* 7C2DCF43 Module(ADVAPI32+0000CF43) (GetNamedSecurityInfoW+000000E6)
* -------------------------------------------------------------------------------
2007-02-27 13:08:53.43 server Stack Signature for the dump is 0x0323EE71
2007-02-27 13:08:53.43 server SQL Server is aborting. Fatal exception c0000005 caught.

-------------------------------------------------------------------------------



database won't start, what we did was to start the sql server with -f parameter and went back to the old settings

View 4 Replies View Related

I Had Set My Windows Service's Startup Type As Automatic But It Is Not Starting Up Automatically When The System Is Starting.

May 18, 2007

Hello,



I had created a windows service using C#. I set its Startup Type as Automatic but it is not getting started automatically when my System Starts.

View 3 Replies View Related

Query On A Single Word

Aug 30, 2005

Hi i am using Access as my database (i know its an MS SQL forum but that's the closest i could find, and maybe its more of an sql question).

I am trying to select all rows that contain i.e. the word "Art"

SELECT * FROM table1 WHERE column1 LIKE %Art%

as you can see column1 contain some words and not just "ART"
however i am getting also the following:

CART , ARTI, DARTS - since all has arts.

( i can use '=' since the column consist of other words)
The query should also load : Art. Art, Art! Art? and "ART"

Is there an easy solution or i need to create a huge condition ?

View 2 Replies View Related

Difference In Starting Rep Service Vs. Stopping And Starting The Rep Job?

Feb 1, 2007

When our rep distribution services time-out (several times a day) some dba's just restart the service while others restart the rep job which starts the service. Are there any differences between these two methods? Does restarting the service inherit any changes made to the job's attributes?



Thanks!

View 1 Replies View Related

Help: MS-Word Apostrophes In Query Analyzer

Jul 20, 2005

After creating an IN clause from a bunch of character strings created by aWord macro, Query Analyzer complains about a syntax error. The macro takes acolumn full of character strings and wraps apostrophes (single-quotes)around each string and adds a comma to the end of each line, ready to pasteinto a Query Analyzer session. The problem is that Query Analyzer doesn'trecognize the MS-Word apostrophes. It has nothing to do with the standardapostrophe/quote problems that some people face when dealing with embeddedquotes.Can anyone suggest how to produce single-quote characters in MS-Word thatQuery Analyzer understands?Thank you.

View 2 Replies View Related

Can I Change Null Word In Report To Another Word

Feb 24, 2008

i have report with parameter and he can have a null in parameter ther is null word can i change it to another word like all or any thing else

View 4 Replies View Related

Simple Query To Get Exact Match Word

Apr 6, 2007

Hi can anybody give me a simple example to check exact match word using sql sever
 Ex: my word is like this : welcome to sql sever2005
Now i want to find server how it is possible?
I tried like this
select * from test where content like %server% . but is gives me possible result. But i dont want like this if i have an exact match with that key word then only i want possitive result.
 Actually there is no word with server, i'm having only server2005 . So how can I get it?
 
Thank you

View 3 Replies View Related

Procedure To Save Query Out Put In Word Format

Jul 15, 2004

I would like to know , is there a way to save query results in word format.
Liek we use sp_makewebtask to save query output in html format.

Thanks

View 2 Replies View Related

T-SQL (SS2K8) :: OR Query - Search For Items And Separate Each Word

Oct 17, 2014

I have this query currently:

select updatedb.callref, updatedb.updatetxt, updatedb.udsource, opencall.suppgroup
from updatedb
left join opencall
on updatedb.callref=opencall.callref

where udindex = '0'
and suppgroup = 'SUPPORT'
and (updatetxt like '%' + @Word + '%')

And opencall.status <> '17'This means that when they search for items and they separate each word it is "and" between each one.

They would like it to be more fuzzy with "and" and "or". How can I adapt this?

View 8 Replies View Related

Fulltext Index Error With Query Word BARBE

Jul 30, 2007

For demonstration I created a fulltext index on table employee in Northwind database.

The following query gives an error:

SELECT * FROM employees
WHERE CONTAINS (FirstName, 'Barbe')

Replacing 'Barbe' by 'Barb' or other words it works fine.

The error message is (I have a french version of SQL installed, here the translation: "A clause in the query contains only ignored words"
Une clause de la requête ne contient que des mots ignorés)

Language for wordbreak in fulltext index is French and the error happens only with French, with English it works.

Is this a Microsoft bug?

View 1 Replies View Related

SQL Agent Is In Starting Status - But Not Starting

Mar 6, 2002

We have SQL 2000 (SP2) , Our SQL server agent showing green light as started from EM , It is succussfully restarting from NT Services too . But When I open the properties of the jobs from EM its gives error message SQL DMO error " SQl Agent is starting , try later " . And in EM Next run Date & Time not available .

MDDB database is accessable & in Log file its recovered fully

Thankx in Advance

Sha

View 1 Replies View Related

SQL 2012 :: How To Write Query To Search Only For Any Entry That Has Word LVAD

May 11, 2015

What I really need is to find the entries that have the word LVAD but on either side there should be a space chartacters.In other words I don't want to see the following coming from the query "SILVADENE ([PHI] SULFADIAZINE)"How can we write this query to search only for any entry that has the word " LVAD " ?

Select top 100 * FROM tbl_abc
where
AIMS_Value like '%LVAD%'

View 2 Replies View Related

Sql Search Command Word By Word?

Mar 30, 2005

hi!
I'm just wondering does anyone know how to create an sql command that can search word by word?
what i mean is like I have a product with name 'harry potter broom'.
I want an sql command where if i type only 'harry broom' this 'harry potter broom' product will show up.
Does anyone have any idea?
Here's my sql comand: (I'm using asp.net vb script do develop this system)
-------------------------------------------------------------------------
query = "select distinct * from product where " & _
"(pname like '%" & keyword & "%' or " & _
"pdesc like '%" & keyword & "%' ) and " & _
"(price >= " & price1 & " and price <= " & price2 & _
") and status <> 'out of stock' order by price asc"
-------------------------------------------------------------------------
Thank you.

View 4 Replies View Related

Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?

May 22, 2007

Hello to all,
I have a problem with ms sql query. I hope that somebody can help me. 
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)).  Example Datas for Table Relationships:                               IDMember     Relationships              .
                                                                                                                3387            (2345, 2388,4567,....)
                                                                                                                4567           (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query: 
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
 
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
 
Best Regards
Pinsha

View 9 Replies View Related

Is The Word Name A Reserved Word?

Sep 24, 2007

 
Is the word "Name" a reserved word in SQL? look at line 10 of my stored procedure. When I use the word "Name"it is highlited in blue by SQL Server?
Note I only list part of the stored proc
1  CREATE PROCEDURE [dbo].[GetXMLPeopleNames] 23 (4 @Status nvarchar(3)5)6 AS7 SELECT8  PersonId,9  PersonDescription,10  Name,11  UpdateDate,12  UpdateAppUser13 FROM14  Customer WHERE Customer.PersonDescription=@Status15 ORDER BY

View 2 Replies View Related

Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.

Feb 13, 2006

We have the following two tables :

Link  ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )

The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.

The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).

We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.

For instance,

Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.

Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.

View 1 Replies View Related

Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?

Mar 25, 2008

Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:


USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SalesByCategory]

@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

AS

IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'

BEGIN

SELECT @OrdYear = '1998'

END

SELECT ProductName,

TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)

FROM [Order Details] OD, Orders O, Products P, Categories C

WHERE OD.OrderID = O.OrderID

AND OD.ProductID = P.ProductID

AND P.CategoryID = C.CategoryID

AND C.CategoryName = @CategoryName

AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear

GROUP BY ProductName

ORDER BY ProductName

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.Common

Imports System.Diagnostics

Public Class ConnectionPoolingForm

Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

'Force app to be available for SqlClient perf counting

Using cn As New SqlConnection()

End Using

InitializeMinSize()

InitializePerfCounters()

End Sub

Sub InitializeMinSize()

Me.MinimumSize = Me.Size

End Sub

Dim _SelectedConnection As DbConnection = Nothing

Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged

_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub DisableAllButtons()

btnAdd.Enabled = False

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

btnClearAllPools.Enabled = False

End Sub

Sub EnableOrDisableButtons(ByVal cn As DbConnection)

btnAdd.Enabled = True

If cn Is Nothing Then

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

Else

Dim connectionState As ConnectionState = cn.State

btnOpen.Enabled = (connectionState = connectionState.Closed)

btnQuery.Enabled = (connectionState = connectionState.Open)

btnClose.Enabled = btnQuery.Enabled

btnRemove.Enabled = True

If Not (TryCast(cn, SqlConnection) Is Nothing) Then

btnClearPool.Enabled = True

End If

End If

btnClearAllPools.Enabled = True

End Sub

Sub StartWaitUI()

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

End Sub

Sub EndWaitUI()

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub SetStatus(ByVal NewStatus As String)

RefreshPerfCounters()

Me.statusStrip.Items(0).Text = NewStatus

End Sub

Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click

Dim strConn As String = txtConnectionString.Text

Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()

Try

bldr.ConnectionString = strConn

Catch ex As Exception

MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)

Return

End Try

Dim dlg As New ConnectionStringBuilderDialog()

If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then

txtConnectionString.Text = dlg.ConnectionString

SetStatus("Ready")

Else

SetStatus("Operation cancelled")

End If

End Sub

Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click

Dim blnError As Boolean = False

Dim strErrorMessage As String = ""

Dim strErrorCaption As String = "Connection attempt failed"

StartWaitUI()

Try

Dim cn As DbConnection = _ProviderFactory.CreateConnection()

cn.ConnectionString = txtConnectionString.Text

cn.Open()

lstConnections.SelectedIndex = lstConnections.Items.Add(cn)

Catch ex As Exception

blnError = True

strErrorMessage = ex.Message

End Try

EndWaitUI()

If blnError Then

SetStatus(strErrorCaption)

MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

SetStatus("Connection opened succesfully")

End If

End Sub

Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click

StartWaitUI()

Try

_SelectedConnection.Open()

EnableOrDisableButtons(_SelectedConnection)

SetStatus("Connection opened succesfully")

EndWaitUI()

Catch ex As Exception

EndWaitUI()

Dim strErrorCaption As String = "Connection attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click

Dim queryDialog As New QueryDialog()

If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

Try

Dim cmd As DbCommand = _SelectedConnection.CreateCommand()

cmd.CommandText = queryDialog.txtQuery.Text

Using rdr As DbDataReader = cmd.ExecuteReader()

If rdr.HasRows Then

Dim resultsForm As New QueryResultsForm()

resultsForm.ShowResults(cmd.CommandText, rdr)

SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))

Else

SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))

End If

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Using

Catch ex As Exception

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

Dim strErrorCaption As String = "Query attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Else

SetStatus("Operation cancelled")

End If

End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.

Thanks in advance,
Scott Chang

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved