Menu
  • HOME
  • TAGS

Insert Into two Tables with Foreign Key relationship

Tag: sql-server,asp.net-mvc,entity-framework,sql-server-2008,stored-procedures

In my MVC application, I have two entities called Ticket and Attachment, and when inserting a new ticket record, I also want to insert new records for attachments with the FK of previously inserted Ticket's ID.

I have a look at some samples as this page, but I need to use a loop for the multiple attachments in a single method so that users can attach multiple files when creating a new ticket. Could you give a sample stored procedure or a sample method for Entity Framework to solve this problem?

Here are these two entities:

Ticket:

public class Ticket
{
    [Key] 
    public int ID { get; set; }

    public string Comment { get; set; }

    //Navigation Property
    public virtual ICollection<Attachment> Attachments { get; set; }

}


Attachment:

public class Attachment
{
    [Key]
    public int ID { get; set; }

    //Foreign key for Ticket
    public int TicketID { get; set; }   

    public byte[] FileData { get; set; }

    public string FileMimeType { get; set; }

    //Navigation Property 
    public virtual Ticket Ticket { get; set; }
}

Best How To :

Try something like this (this is simplified, of course - but it shows the basic mechanism that should be used - instantiating objects and connecting them via their navigation properties, and saving only once for the whole object graph):

// establish the DbContext
using (TicketModel ctx = new TicketModel())
{
    // create a ticket
    Ticket t1 = new Ticket
    {
        Comment = "This is ticket #1"
    };

    // add two attachments to it
    Attachment a1 = new Attachment { FileMimeType = "text/json" };
    t1.Attachment.Add(a1);

    Attachment a2 = new Attachment { FileMimeType = "application/octet-stream" };
    t1.Attachment.Add(a2);

    // add the ticket to the context
    ctx.Ticket.Add(t1);

    // save everything
    ctx.SaveChanges();
}

With this, you should have a ticket in your database, and two connected attachments (with their TicketID column set to the correct value for the ticket)

copy table and drop it

sql,sql-server,sql-server-2008,tsql,stored-procedures

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl2')) BEGIN -- tbl2 exists, so just copy rows INSERT INTO tbl2 SELECT * FROM tbl1; END ELSE BEGIN -- tbl2 doesn't exist, so create new table tbl2 and copy rows SELECT * INTO tbl2 FROM tbl1; DROP tbl1; END This...

INSERT INTO fails due to incorrect conversion T-SQL

sql-server,tsql

The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done: cast('FooBar' as sql_variant) ...

File IO Close() method error in ASP.NET MVC 6

asp.net-mvc,asp.net-mvc-6

Do you use the core CLR? The StreamWriter.Close method are not available in core CLR. You can use Dispose method replace. You also can use using statement: using (var writer = System.IO.File.CreateText("your_path")) { writer.WriteLine("text"); } ...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008

I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...

TSQL - Error in stored procedure due to conversion failure

sql-server,sql-server-2008,tsql

I'd suggest doing it like that: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE INNER JOIN...

How to use OFFSET and Fetch without Order by in SQL Server

sql-server,sql-server-2012,sql-order-by,fetch,offset

By adding an identity column to the temp table variable declare @TempTable table([some columns], rownr int identity(1,1) ) INSERT INTO @TempTable [some columns] select [some columns] from table1 order by col1 INSERT INTO @TempTable [same columns] select [some columns] from table2 order by col2 An automatic incrementing number is added...

ASP.NET MVC posting list from view to controller

c#,.net,asp.net-mvc,razor

That's probably a good candidate for an EditorTemplate to be honest, that way you don't have any issues with prefixing: @Html.EditorFor(m => m.TechnologyFilters) Without using an editor template though, a technique you can use is to specify the prefix in your partial declaration within the ViewDataDictionary, by doing: Html.RenderPartial("_TechnologyFilters", Model.TechnologyFilters,...

Get unique row by single column where duplicates exist

sql,sql-server

SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...

left join table, find both null and match value

sql,sql-server,join

Try FULL OUTER JOIN. This is the sqlfiddle. It will produce the op you are expecting SQLFiddle select t1.years, t1.numOfppl, t2.years, t2.numOfppl from t1 full outer join t2 on t1.years=t2.years ...

Merging two tables into new table by ID and date

sql,sql-server,phpmyadmin

You can use a SELECT statement when inserting into a table. What I would do here is write a select statement that pulls all of the columns you need first. You will have to do a full outer join (simulated by a union of left and right joins) because some...

Implement reference key in SQL Server

sql-server,sql-server-2008

It's called a "one-to-zero-or-one" relationship, as one Line might be associated to zero or one TestPacks. You can implement it by using a FK that allows NULL values. CREATE TABLE TestPack (id INT, PRIMARY KEY (id)) CREATE TABLE Line (id INT, TestPackId INT NULL, FOREIGN KEY (TestPackId) REFERENCES TestPack(id)) By...

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql

I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...

SQL Server / C# : Filter for System.Date - results only entries at 00:00:00

c#,asp.net,sql-server,date,gridview-sorting

What happens if you change all of the filters to use 'LIKE': if (DropDownList1.SelectedValue.ToString().Equals("Start")) { FilterExpression = string.Format("Start LIKE '{0}%'", TextBox1.Text); } Then, you're not matching against an exact date (at midnight), but matching any date-times which start with that date. Update Or perhaps you could try this... if (DropDownList1.SelectedValue.ToString().Equals("Start"))...

Improving work with SQL DataTime

sql,sql-server,database,tsql

You can do it like this: SELECT IIF(DAY(@A) >= 25, DATEADD(d, 25 - DAY(@A), @A), DATEADD(d, 25, EOMONTH(@A, -2))) Here's a sample fiddle as well: sqlfiddle Note: EOMONTH requires SQL Sever 2012 or above - it returns the End-Of-Month date given a start date and a month offset....

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like

WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...

How do I convert this tSQL statement to LINQ using group by in a sub query

c#,sql-server,linq,tsql

I think there is opportunity to rewrite your query, but for information purposes I rewrote your sql into linq verbatim. If you explain what you are trying to achieve we can provide alternative sql / linq var eqnums = new[] { "M0435", "Z0843" }; var testdate = "2008-06-01"; var query...

Foreign key in C#

c#,sql,sql-server,database

You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...

Avoid EF update the null image to database in .Net MVC

asp.net-mvc,entity-framework,null,edit,httppostedfilebase

Mark Image property as not modified: db.Entry(sach).State = EntityState.Modified; if (image == null) db.Entry(sach).Property(m => m.Image).IsModified = false; db.SaveChanges(); ...

Join SQL query Results and Get-ChildItem Results

sql-server,sql-server-2008,powershell

OK so if the SQL query does not have results then NULL is returned and, in essence, nothing is added to the $dbResults array. Instead lets append the results to a custom object. I don't know what PowerShell version you have so I needed to do something that I know...

Take thousand value in SQL

sql,sql-server

SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...

Sql injected code is inserted to my database . How to remove it

sql-server

You can use the fact that html code starts with symbol <. Then: UPDATE TableName SET SomeColumn = CASE WHEN CHARINDEX('<', SomeColumn) > 0 THEN SUBSTRING(SomeColumn, 1, CHARINDEX('<', SomeColumn) - 1) ELSE SomeColumn END If this is not true then we will need more information about data. May be it...

When adding a user to a role in asp.net mvc 4.5, i'm getting an error- “user (user name) not found”

c#,asp.net-mvc

The problem is you are not setting roleManager properly in webconfig. change your webconfig as below. <roleManager enabled="true" cacheRolesInCookie="true" cookieName=".ASPXROLES" cookieTimeout="30" cookiePath="/" cookieRequireSSL="false" cookieSlidingExpiration="true" cookieProtection="All" defaultProvider="AspNetSqlRoleProvider" createPersistentCookie="false" maxCachedResults="25" /> ...

Looping distinct values from one table through another without a join

sql,sql-server,tsql,while-loop

So you want all distinct records from table1 paired with all records in table2? That is a cross join: select * from (select distinct * from table1) t1 cross join table2; Or do you want them related by date? Then inner-join: select * from (select distinct * from table1) t1...

Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures

The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

Recursive Lag Column Calculation in SQL

sql,sql-server,recursion

Edit In hindsight, this problem is a running partitioned maximum over Column1 * 2. It can be done as simply as SELECT Id, Column1, Model, Product, MAX(Column1 * 2) OVER (Partition BY Model, Product Order BY ID ASC) AS Column2 FROM Table1; Fiddle Original Answer Here's a way to do...

Connecting to database using Windows Athentication

sql-server,vb.net,authentication,connection-string

You need to add Integrated Security=SSPI and remove username and password from the connection string. Dim ConnectionString As String = "Data Source=Server;Initial Catalog=m2mdata02;Integrated Security=SSPI;" ...

Retrieve data from one table and insert into another table

sql,asp.net,sql-server

INSERT INTO tbl2 ( Name ,parentId ) SELECT DISTINCT manager ,0 FROM tbl1 WHERE manager NOT IN ( SELECT employee FROM tbl1 ) INSERT INTO tbl2 SELECT DISTINCT employee ,0 FROM tbl1 UPDATE tbl2 SET parentid = parent.id FROM tbl2 INNER JOIN tbl1 ON tbl2.Name = tbl1.employee INNER JOIN tbl2...

Unable to find the auto created Database

c#,asp.net,asp.net-mvc,entity-framework

If you don't specify a database name then the connection will use the default database for the user, in this case it's integrated security so it's your Windows login. As you likely have full system admin on the server the default database will be master so you will find all...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008

You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...

Error connecting to MSSQL using PHP

php,sql-server,pdo,odbc,sqlsrv

Change it to: $this->link = new PDO( "sqlsrv:Server={$this->serverName},{$this->port};Database={$this->db};", $this->uid, $this->pwd ); The default SQL Server port is 1433. Note the curly brackets, they allow for class variables....

Angularjs resource with scope parameter

javascript,asp.net-mvc,angularjs,single-page-application

If this is an AJAX call then the varialble initialization should be into the callback methods: Fakturi.fakturi.get({ id: $routeParams.id }, function (data) { $scope.faktura = data; }); Fakturi.komintenti.get({ id: $scope.faktura.KomintentID }, function (data) { $scope.komintent = data; }); According to this link, if you would like to get response immediately...

SQL Customized search with special characters

sql,sql-server,sql-server-2008

Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...

Microsoft SQL Insert into subset of table columns fails [on hold]

sql-server,sql-server-2008

Check for constraints or triggers that would attempt to insert a value too large for a given column. This can happen when over time schema changes occur, and constraints or triggers escaped the scope of impact review. In this case (varchar(3)) column status had a default constraint that was attempting...

Checkbox to be checked on having value Y

asp.net-mvc,knockout.js

Use a computed writable observable: var ViewModel = function() { var self = this; self.actualObservable = ko.observable('Y'); self.relatedObservable = ko.computed({ read: function() { return self.actualObservable() === 'Y'; }, write: function(x) { self.actualObservable(!!x ? 'Y' : 'N'); } }); }; ko.applyBindings(new ViewModel()); <script src="https://cdnjs.cloudflare.com/ajax/libs/knockout/3.2.0/knockout-min.js"></script> <input type="checkbox" data-bind="checked: relatedObservable"> = Checked<br />...

MVC 5 OWIN login with claims and AntiforgeryToken. Do I miss a ClaimsIdentity provider?

asp.net-mvc,asp.net-mvc-4,razor,asp.net-mvc-5,claims-based-identity

Your claim identity does not have ClaimTypes.NameIdentifier, you should add more into claim array: var claims = new List<Claim> { new Claim(ClaimTypes.Name, "Brock"), new Claim(ClaimTypes.Email, "[email protected]"), new Claim(ClaimTypes.NameIdentifier, "userId"), //should be userid }; To map the information to Claim for more corrective: ClaimTypes.Name => map to username ClaimTypes.NameIdentifier => map...

Cannot Browse in sql to backup bak file

sql-server

Try this query RESTORE DATABASE Databasename FROM DISK = 'Z:\SQLServerBackups\tms.bak' ; ...

How to select next row after select in SQL Server?

sql,sql-server

The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON...

Show/hide tinymce with radio buttons

c#,asp.net,asp.net-mvc,tinymce

Your missing an @symbol for the id attribute: Modify your script as well like this: ***EDIT some thing seems off about the radio buttons only one should be checked and they should have the same name ** you can use the # to denote and ID in Jquery by the...

How to use ajax to post json string to controller method?

jquery,asp.net-mvc,visual-studio-2013,asp.net-mvc-5

Your action method is expecting a string. Create a javascript object, give it the property "data" and stringify your data object to send along. Updated code: $.ajax({ type: 'post', dataType: 'json', url: 'approot\Test', data: { "json": JSON.stringify(data) }, success: function (json) { if (json) { alert('ok'); } else { alert('failed');...

Default the year based on month value

sql,sql-server

SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...

System.net.http.formatting causing issues with Newtonsoft.json

c#,asp.net,asp.net-mvc,json.net

Does the assemblyBinding tag have proper xmlns schema? Check if the issue you are encountering is same as Assembly binding redirect does not work

Setting time limit in SQL Query

sql-server

Assuming that you want between 10:00 AM and 5:00 PM, you can use this SELECT CASE WHEN CAST(GETDATE() AS TIME) BETWEEN '10:00:00' AND '17:00:00' THEN 1 ELSE 0 END In this context, Select * from table makes no sense, unless you have a time column and want to evaluate that....

Best approach to upgrade MVC3 web app to MVC5?

c#,.net,asp.net-mvc,asp.net-mvc-5

There's a handy documented guide by Rick Anderson which he wrote to upgrade from MVC4, the same applies to you with the exception of the fact that the "Old version" of DLLs he mentions will be different to the ones that you will have, but the outcome will still be...

Select count Columns group by No

sql,sql-server

this will work. you have to provide separate case statement to each condition SQLFIDDLE for the same SQLFIDDLE SELECT EMP_NO, sum(CASE WHEN Emp_Shift = 'AL' THEN 1 ELSE 0 END) AS COUNT_AL, sum(CASE WHEN Emp_Shift = 'S' THEN 1 ELSE 0 END) AS COUNT_S, sum(CASE WHEN Emp_Shift = 'H' THEN...

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables

sql-server,join

If I understood correctly this should be what you're looking for SELECT A.UserName, A.[Email ID], A.[Supervisor Email ID] FROM A INNER JOIN B ON A.UserCode = B.UserCode WHERE B.ACTIVE_FLAG = 'Y' ...

How to map between two entities before paging

c#,asp.net-mvc,entity-framework

You just want to make sure you don't pull anymore records out of the database than you need. As you can't use your ProviderViewModel constructor in LINQ to Entities you will have to retrieve the requested page yourself before creating your view model objects: public ActionResult Index(int? page) { List<ProviderViewModel>...

Return 0 in sum when no values to sum - sql server

sql,sql-server,sql-server-2008-r2,sum

You need a table of all the statuses. If you don't already have a table, you can do this in the query itself: SELECT ClientDeliveryStatus, COUNT(t.ClientDeliveryStatus) AS Total FROM (SELECT 'Past Due' as cds UNION ALL SELECT 'Due Tomorrow' UNION ALL SELECT 'Due Today' UNION ALL SELECT 'Due Beyond' )...

check if file is image

c#,asp.net,asp.net-mvc

You can't do this: string.Contains(string array) Instead you have to rewrite that line of code to this: if (file == null || formats.Any(f => file.Contains(f))) And this can be shortened down to: if (file == null || formats.Any(file.Contains)) ...

Creating a viewmodel on an existing project

c#,asp.net,asp.net-mvc

You are using a namespace, your full type name is Project.ViewModel.ViewModel (namespace is Project.ViewModel and class name is ViewModel) so use this using instead: @model Project.ViewModel.ViewModel ...