SQL Like with Parameter

I have a stored procedure that will do a search on records based on a value passed in. To use a parameter value with the SQL ‘Like’ command use this format:

   SELECT * FROM clients WHERE FirstName LIKE ‘%’ + @sWhere + ‘%’ OR LastName LIKE ‘%’ + @sWhere + ‘%’

 

SelectedValue which is invalid because it does not exist in the list of items

To populate drop down lists I bind the list to a lookup table in the database (i.e. States). Occasional the parent table (i.e. Customers) has a value that is “not on the list” and this error is genreated:

    ‘ddlState’ has a SelectedValue which is invalid because it does not exist in the list of items. Parameter name: value

The only thing I’ve been able to figure out to do is add the parent table value to the drop down list. This only works when the value saved in the parent table is the actual value and not the foreign key (the ID of the child table such as StateID). Here is my code:

<asp:DropDownList ID=”ddlState” runat=”server” DataSourceID=”SqlDataSource1″ DataTextField=”StateName” DataValueField=”StateAbbr”></asp:DropDownList> <asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:DATABASE_01 %> SelectCommand=”SELECT [StateAbbr], [StateName] FROM [States] ORDER BY [StateName]”></asp:SqlDataSource>

Then in code:

Sub Page_Load()

ddlState.AppendDataBoundItems = True
Dim Items As New ListItem
Items.Text =
“Select One”
Items.Value = “”
ddlState.Items.Add(Items)

If Not IsPostback Then

… ‘ read data from database

… ‘  load values into controls

Items = New ListItem
Items.Text =
“” & rdUser(“State”).ToString
Items.Value =
“” & rdUser(“State”).ToString
ddlState.Items.Add(Items)
ddlState.SelectedValue =
“” & rdUser(“State”).ToString

 

I’ve tried putting a ‘Try/Catch’ around setting the ddlState.SelectedValue but that is not where the error occurs. The error occurs as the page is being rendered. When using the SqlDataSource control is used the data gets bound as the page is being rendered. The page load event occurs before the page is rendered so the drop down selected value is set before the data is bound! This method prevents the “does not exist” error messages all the time. If you have a better solution I’d sure like to hear it!

 

Restore Database and Database User

When restoring an SQL Server 2005 database from a different database instance the database login will be orphaned. Use this SQL script to fix the orphaned login.

sp_change_users_login ‘Auto_Fix’, ‘user’, NULL, ‘password’

Actual syntax:

sp_change_users_login [ @Action = ] ‘action’     [ , [ @UserNamePattern = ] ‘user’ ]     [ , [ @LoginName = ] ‘login’ ]         [ , [ @Password = ] ‘password’ ]

See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp