|
Boy that was a mouth full. Let me explain.
I have a table of products with names and descriptions. So take a word like kneepads. The correct way to spell it is knee pads and the column contains that correct spelling. But users will type in kneepads to search. I solved the plural issue with a custom function that I wrote.
But I'm wondering if I can query the database in Linq, and say something like without removing, replacing a value in the database
from pr in context.PRODUCT_ITEMS
WHERE pr.Name.Replace(" ", "").Contains("kneepad")
Basically I'm just looking for ideas to handle this.
My older program had another table that contains names and descriptions that were pre-stripped for searching and I really don't want to go back to that.
If it ain't broke don't fix it
|
|
|
|
|
It would be a very slow solution.
Assuming t-sql, I would add a computed column and put an index on it.
|
|
|
|
|
I would suggest holding a table of synonyms, with possible search terms pointing to the actual terms in the product description
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Add a normal search without the gimmicks and explain that if "kneepads" don't give the correct results, they should search for "knee".
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Guess the original idea I had might be the best. A separate table of straight text with no white spaces and just do a join.
Or maybe a table of words with white space that are parsed out with conjunctions removed and verbs fixed.
Alright Thanks!
If it ain't broke don't fix it
|
|
|
|
|
As I read this you have decided on a solution and now are attempting to implement that.
My take on the problem, not your solution, is that you should investigate it first and then decide on a solution to implement.
Your problem is not new. It has been around for decades and I doubt your solution will work. For starters because it doesn't really deal with misspellings. Not to mention synonyms.
However there are solutions that do work fairly well. So you should see if you can find them first.
|
|
|
|
|
I created an EF6 data context by following this.
I need to change the connection string at runtime. I found this article but my data context does not have an overloaded contstructer:
public partial class MyEntities : DbContext
{
public MyEntities()
: base("name=MyEntities")
{
}
What am I doing wrong? How to I set the EF data Context connection string at runtime??
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Add the required constructor overloads to your context:
public partial class MyEntities : DbContext
{
public MyEntities() : base("name=MyEntities")
{
}
public MyEntities(string nameOrConnectionString) : base(nameOrConnectionString)
{
}
public MyEntities(DbConnection existingConnection, bool contextOwnsConnection) : base(existingConnection, contextOwnsConnection)
{
}
EDIT: If you're using the .tt file to generate the context from the database, you'll probably need to add the extra overloads in a separate file; that's why the class is declared as partial.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello everybody, i'm writing here because i'm really becoming crazy, i read every kind of internet pages but I'm not able to find a solution but i'm also sure that the solution is behind the corner.
I have an Access database with different tables. The main table has different foreign keys to other tables. I do an example with 2 tables to make the problem easier:
Table Products
Field1 - Primary Key: Product Code - text
Field2: Description - text
Field3: Product Type - integer
Table ProductTypes
Field1: Primary Key - ID - integer - FK on Field3 of "Table Product"
Field2: Description - Text
Now, I open Visual Studio, I add my database and I drag&drop the table "Product" on my Windows Form as a DatagridView. The wizard create for me, the tableAdapter, bindinsource ecc....
If I start my app the DatagridView is populate with the correct data but in the column "Product Type" I see the number from the PK of the table "ProductTypes" while I'd like to see the Description.
I created a new TableAdapter query that give me the result i want to see but when I confirm I get the message that i'm created a query that give a different result from the original schema. If I continue the query is created but i'm not able to retrieve data using this new query.
THe only way I've to retrieve datas as I want is to modify the original Fill query but in this case the relation between the 2 tables will be removed and also the TableAdapter Insert method will be removed.
So, I will see the data in datagridview as i'd like to see but I don't have anymore the insert method.
Can someone help me by telling I could I retrieve the data in the way I desider without losing the chance to insert new record in the table?
Thanks
Giacomo
|
|
|
|
|
Can you create a view in Access and use the view?
|
|
|
|
|
Yes, I already did it.
I created a View in Access and then I imported it in my Dataset. In this way I'm able to show the correct data in my DataGridView but the Wizard didn't create for me the Insert, Delete and Update method.
Do you think I could be able to update the Access database through the view?
|
|
|
|
|
A view will only let you see the data.
It has been so long since I used Access I can't help you on the inserts, updates, deletes.
|
|
|
|
|
I think to have solved the problem....maybe I'm using a bad solution but the result is good.
In my dataset I have the Main table that is filled with the original database records. In the same datased I inserted a second table that is filled with a my personal Fill query.
In my form I have inserted both table adapter. The first one is only in background while the second one is shown in datagridview.
I insert, delete and modify the records one the datagrid and when I click "Save", I reflect the changes in the background table adapter....
Thank
Giacomo
|
|
|
|
|
Sometimes, we use an 'ugly' solution until we find a 'nice' one.
But, results matter.. if it works, great!
|
|
|
|
|
This question was originally asked over a week ago in quick answers.
Edit: (2018-01-06) There is no problem getting the sqloledb driver to connect to an availability group. The problem cited below has been resolved. (possibly by forcing protocol as in tcp: ag-listener_name for the server) The client's dba tweaked some settings and it all works now...not sure what he did. Thanks to all responders. 
https://www.codeproject.com/Questions/1222282/OLEDB-connection-to-a-SQL-server-database-hosted-i[^]
I am posting it again here due to bad timing (right before holiday break) which possibly resulted in this question not being noticed by an expert before getting pushed back to page 50+. @OriginalGriff did post a solution, albeit not a viable one. The original question is as follows:
I've been connecting to SQL Server databases for almost 20 years, but my latest client has me baffled. For the first time that I know of, our database has been made part of an Availability Group under SQL Server 2012. This is a new client and so far, their impression of me is probably not that great.
Quite simply, the problem is that I am unable to connect a legacy application using the OLEDB provider to a database hosted in a SQL 2012 Availability Group. Despite 2 hours of working with a junior tech. on their end, and trying many different combinations, my application is not able to find the server. What I'm wondering, is if there is any special parameter required in the connection string when connecting to an availability group?
The current form of the connection string that is failing is:
Provider=sqloledb;Server=servername\instance;Database=databasename;User ID=username;Password=password
While on the remote with the junior tech., we were able to remote into the server and verify that the database was available, and that our sql login was setup correctly...also verified that sql authentication was enabled. (I've been bit by that dog before) Everything seems to be setup correctly...even made sure that the client firewall was disabled. No joy!
This fiasco took place on the last business day of this year for the client, so I have been unable to try anything since learning a little more about AGs/Listeners. It would be ideal to replicate the customer's environment for testing, but setting up a failover cluster and AG seems like overkill when there's probably a simple parameter that I am missing.
Other info: From the workstation having the problem, I can successfully ping the database servers. (primary and replica) The error message returned is 'The Server does not exist or access is denied'. It seems more like a timeout than a permissions issue. Also, if it matters, the application uses ADODB connections/objects.
It will be over two weeks until the customer returns from the holidays. In the meantime, I'm charged with explaining to their lead tech. (who was unavailable when we were having problems) what the problems are, and what we are going to do to fix it. (in addition to a lot more crap they want now)
What I have tried:
0) Searched connectionstrings.com
1) Spent the last two days reading up on Availability Groups and client connections. I have a few things to try, but it'll be trial by fire unless I invest the time to replicate the environment.
Things I would try if I were able:
0) Prepend the server name (in the connection string) with tcp: (is this important?)
1) Use the listener name instead of server/instance (even though all the documentation I read says this should work)
2) Add a connection string parameter (MultiSubnetFailover=True)
3) Check the port numbers being used
4) Try a different variation of OLEDB connection string
What I'm hoping is that someone here may have access to an AG test environment and can help confirm that it is even possible to connect with the OLEDB provider per the connection string example given. Any help or hints are greatly appreciated!
Update: The customer is requesting another remote and conference tomorrow to try to resolve this problem. I have a few things now to try, and they have senior staff on hand that probably will know exactly how to reach the server/AG. What I'm still in the dark about is if I will need to add another connection parameter for it to work. If that's the case, it will require an update to the code that constructs/saves/recalls the connection details. Thanks again for any hints!
"Go forth into the source" - Neal Morse
modified 6-Jan-18 15:15pm.
|
|
|
|
|
|
|
I had a similar problem a while ago. Sometimes it would connect and other times it would time out depending on which server was the current primary. I believe there is a DNS setting you can use to get around this but that was not an option in our case. The only solution I came up with was to set a long timeout (300 seconds) in the connection string. So now sometimes it connects right away and the rest of the time it will take about a minute. Not ideal but we've been able to live with it.
Hope this helps.
|
|
|
|
|
|
Have your cluster manager review
Create or Configure an Availability Group Listener (SQL Server) | Microsoft Docs
In a powershell admin window, run the following command to get a listing of RegisterAllProvidersIP
Get-ClusterResource "Network" | Get-ClusterParameter | Where-Object {$.name -eq "RegisterAllProvidersIP" -and $.Value -ne "0"}
If that setting is not 0 then all IPs associated with the cluster resources are published to DNS.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Interesting, I've setup a couple of Availability groups for applications but never had any issues (well once I got the devs to stop trying to use the IPs).
I wonder if the issues you faced are related to
Create or Configure an Availability Group Listener (SQL Server) | Microsoft Docs
[Snippet]
Issue: If your Availability Group or Failover Cluster Instance has a listener name (known as the network name or Client Access Point in the WSFC Cluster Manager) depending on multiple IP addresses from different subnets, and you are using either ADO.NET with .NET Framework 3.5SP1 or SQL Native Client 11.0 OLEDB, potentially 50% of your client-connection requests to the availability group listener will hit a connection timeout.
Workarounds: We recommend that you do one of the following tasks.
If do not have the permission to manipulate cluster resources, change your connection timeout to 30 seconds (this value results in a 20-second TCP timeout period plus a 10-second buffer).
Pros: If a cross-subnet failover occurs, client recovery time is short.
Cons: Half of the client connections will take more than 20 seconds
If you have the permission to manipulate cluster resources, the more recommended approach is to set the network name of your availability group listener to RegisterAllProvidersIP=0. For more information, see "RegisterAllProvidersIP Setting” later in this section.
Pros: You do not need to increase your client-connection timeout value.
Cons: If a cross-subnet failover occurs, the client recovery time could be 15 minutes or longer, depending on your HostRecordTTL setting and the setting of your cross-site DNS/AD replication schedule.[/Snippet]
In all likely hood the issue was the RegisterAllProvidersIP. If the HA Group is running in a multi subnet cluster. Then for every Node in the HA group its IP is published to DNS, So if there are 5 nodes that it can live on, there are five different IPs listed in DNS. Traversing them all can take some time to find the right one currently active.
Easy fix, set RegisterAllProvidersIP to 0.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Imagine this pseudo-code:
SP_WRITE_MESSAGE
INSERT INTO MSG
SP_WRITE_COURSE
INSERT INTO COURSE
SP_WRITE_RESPONSE
IF(@SOME_COMPUTED_VALUE = 0)
BEGIN
RAISERROR('Some error message', 16, 1)
RETURN
END
EXEC SP_WRITE_MESSAGE
EXEC SP_WRITE_COURSE
Now there is an FK error in SP_WRITE_COURSE and I can see it (and fix) when running SP_WRITE_RESPONSE from SSMS, but running from the application (IIS hosted) with the same parameters I get 'Some error message', even I can see that SP_WRITE_MESSAGE executed...
Anyone?
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
The return statement is never reached, because the raiserror exits the statement block.
So you should either add an else statement or add try and catch blocks. And if you use SqlServer 2012 or newer you should also consider using throw instead of raiserror.
You should also be careful with adding the sp_ prefix to your stored procedures, it doesn't mean "stored procedure".
Is the sp_ prefix still a no-no? - SQLPerformance.com[^]
This post should also be taken with a grain of salt, my brain is still pretty well baked.
|
|
|
|
|
It was true, but the RAISERROR statement itself never reached as the if before it resolves to FALSE...
Pay attention to the flow:
1. IF is FALSE
2. Does EXEC SP_WRITE_MESSAGE with success (can see the record in DB)
3. Does EXEC SP_WRITE_COURSE and fails. This is the point of interest... Running from SSMS I receive the expected FK violation error, running from ASP.NET (C#) I receive 'Some error message'...
And I do not prefix my stored procedures SP_ in real life, just done it here to identify them as SPs... 
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Which means the IF behaves differently in the two environments.
One common difference between SSMS and dotNet connection is the environment settings. The most interesting in this case is probably the ARITHABORT setting. It's normally ON in SSMS and OFF in a client connection.
Try to set ARITHABORT ON or OFF in your code to check if there is a difference in behavoiur.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
<sarcasm>Of course the problem had to be that old sqloledb driver! Why are you still using that ancient technology? So I spent many hours modifying the available sql connection options to include the sql native driver/provider (both with and without ODBC) only to find that the changes were not really necessary.
You may be on to something here...extending the timeout. It was the one thing I didn't try. Still, it's strange that their app server is able to connect instantly...maybe a hosts file entry???