|
Very simple:
DELETE FROM table1 WHERE something = somethingelse
DELETE FROM table2 WHERE 1 = 2
DELETE FROM table3 WHERE ...
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
I have this function that creates an order. I was missing a couple of things in the columns like weight, cost and profit.
So I wrote this to just grab the cart items to calculate my missing data
List<orders_cart> sC = context.ORDERS_CART.Where(m => m.order_ID == pOrderID).ToList();
But then I came to the ship weight, which I need to get from PRODUCT_INFO.
I forget the nomemclature for my expression above, so I wasn't able to search possibilities.
Possible to do a quick join off sC into another list?
If possible, I would need a little nudge towards getting it right.
Thanks!
If it ain't broke don't fix it
|
|
|
|
|
Presumably you have a navigation property from the cart lines to the product info?
In which case, you should be able to use something like:
context.ORDERS_CART.Include(l => l.Product).Where(... which will load the product details with each line. You can then use the navigation property to access the product details for the line.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I think I used include in a VB version of a situation that I needed extra details.
Let me give that a spin.
Monday I just looped the cart items and asked for the details and added them.
If it ain't broke don't fix it
|
|
|
|
|
hi,
I need a single qurery which satisfy the muliple dynamic fields get to be updated.
1. I am having the multiple fields in the table and I want to consider only the fieldname contanis "_edt"
2. All the fieldname("_edt") get to be updated as "some value" like "XXX"
Looks like :
set @sql = 'UPDATE r SET ' + c.name = ''XXX'' FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id,Record r
where t.name = ''Record'' and c.name like ''%_edt'''
Please help on this.
Thanks,
Arun
|
|
|
|
|
Something like this should work:
DECLARE @SQL nvarchar(max) = N'';
SELECT
@SQL = @SQL + N', ' + QUOTENAME(name) + N' = @value'
FROM
sys.columns
WHERE
object_id = OBJECT_ID('Record')
And
name Like '%_edit'
;
SET @SQL = Substring(@SQL, 3, LEN(@SQL) - 2);
SET @SQL = N'UPDATE Record SET ' + @SQL;
PRINT @SQL;
EXEC sp_executesql @statement = @SQL,
@params = N'@value varchar(10)',
@value = 'XXX';
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Tnks a lot.. it is working for me
|
|
|
|
|
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.
|
|
|
|