|
|
I have created a DataTable from a spreadsheet worksheet and want to UPDATE my SQL table, but am struggling. I cannot update the single table having a Primary Key with the changed column information. Should be easy and straight forward. The ShowResult(dataTable) depicts the correct DataTable information. (Using some DevExpress expressions)
' Create a data table with column names obtained from the first row in a range if it has headers.
' Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
Dim dtpHExportDataTable As DataTable = worksheet.CreateDataTable(range, rangeHasHeaders)
' Create the exporter that obtains data from the specified range, ' 'skips header row if required and populates the specified data ' table.
Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range,dtpHExportDataTable, rangeHasHeaders)
AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError
' Specify exporter options.
exporter.Options.ConvertEmptyCells = True
exporter.Options.DefaultCellValueToColumnTypeConverter.EmptyCellValue = 0
'Perform the export.
exporter.Export()
'A custom method that displays the resulting data table.
ShowResult(dtpHExportDataTable) 'Looks good!
'Now, my code to Update the "Result" column in my sql table with the datatable information
Dim cnSQL As SqlConnection = dbLIMS.GetLIMSConnection
Using adapter = New SqlDataAdapter("SELECT * FROM Analytical_Sample_Log_ResultsInfo", cnSQL)
Using New SqlCommandBuilder(adapter)
adapter.Fill(dtpHExportDataTable)
cnSQL.Open()
adapter.Update(dtpHExportDataTable)
End Using
End Using
|
|
|
|
|
You can get an empty datatable from SQL with Select * from Tablename where 1-1 . This will give you the SQL data type, they may differ from the Excel data type.
An update statement will look like
Update TableName set field1 = ValueFromExcelCell, field2 = ... where PrimareyKeyField = PrimaryKeyValueFromExcel
The update needs to be execute for each row in the ExcelTable where the data has changed
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
So I wrote this expression, when I use the joins, I get the first record in the amount of row count. But when I remove the joins, I get all the records.
could this be an error in my Database, in which I have to change the ID column names to make a match, or am I just missing something in my expression?
So this works
pResults = _
(
From ca In context.Customer_Accounts
Where ca.DateOpened >= DbFunctions.AddDays(Today, -1)
Order By ca.DateOpened Descending
Select New accountIndex With
{
.ID = ca.ID,
.firstName = ca.FirstName,
.lastName = ca.LastName,
.accountName = ca.AccountName,
.secure_EmailAdddress = ca.EmailAddress,
.dateOpened = ca.DateOpened
}
).AsEnumerable()
This return the first row multiple times
pResults = _
(
From ca In context.Customer_Accounts
Join ba In context.CUstomer_BillingAddress On ba.CustomerID Equals ca.ID
Join sa In context.Customer_ShippingAddress On sa.CustomerID Equals ca.ID
Where ca.DateOpened >= DbFunctions.AddDays(Today, -7)
Order By ca.DateOpened Descending
Select New accountIndex With
{
.ID = ca.ID,
.firstName = ca.FirstName,
.lastName = ca.LastName,
.accountName = ca.AccountName,
.secure_EmailAdddress = ca.EmailAddress,
.dateOpened = ca.DateOpened,
.billing_FirstName = If(ba.Name1 Is Nothing, Nothing, ba.Name1),
.billing_LastName = If(ba.Name2 Is Nothing, Nothing, ba.Name2),
.shipping_FirstName = If(sa.Name1 Is Nothing, Nothing, sa.Name1),
.shipping_LastName = If(sa.Name2 Is Nothing, Nothing, sa.Name2)
}
).AsEnumerable()
|
|
|
|
|
I had multiple billing and shipping addresses, in the join.
So I need to redesign that feature.
|
|
|
|
|
1) Version 7 is coming. Any reason I should start with an EF6 book?
2) Can anyone recommend a beginner EF book?
Thanks
If it's not broken, fix it until it is
|
|
|
|
|
Is this a Database issue?
|
|
|
|
|
Really?
If it's not broken, fix it until it is
|
|
|
|
|
Sorry I can't read the title of the EF book having staked it out on the office floor drowned it in oil, put a stake through it and burnt it. Blasted thing just won't die!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes I did write this, I must of been on a roll. I understand what I wrote in TSQL, but I just can't wrap my head around how to write it in Linq.
TSQL
DECLARE @startDate AS DATETIME, @stopDate AS DATETIME;
SET @startDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999);
SELECT
TOP 20
CartID
, PartNumber
, Thumbnail
, SDescription
, Qty
, Price
, Category
, Date
, ProductID
FROM
(
SELECT
sc.CartID
, sc.PartNumber
, sc.Thumbnail
, sc.SDescription
, sc.Qty
, sc.Price
, sc.Category
, sc.Date
, pi.productID
FROM ShoppingCart sc
LEFT JOIN PRODUCTINFO pi ON sc.PartNumber = pi.PartNumber
WHERE sc.Date > @startDate
AND sc.Date < @stopDate
) x
ORDER BY Date
Now I wrote this earlier as pResults = from Shopping cart with a join to products, so I can get the productID from products based on partNumber.
But the join or something was inconsistent according to the error. I was running 2 context. I put the product and cart tables in separate context.
So I was thinking perhaps I need to grab the Shopping cart data first, and then query those results with a join to products for the productID.
I have no clue how to phrase this.
This is what I have so far
Dim pValue As Integer = 0
Dim DateStart As New Date(Now.Year, Now.Month, Now.Day, 0, 0, 0, 0)
Dim DateStop As New Date(Now.Year, Now.Month, Now.Day, 23, 59, 59, 0)
Dim productContext As New ProductContext()
Dim shoppingContext As New ShoppingCartContext()
Dim query = _
(
From sc In shoppingContext.ShoppingCart
Where sc.CartDate >= DateStart _
And sc.CartDate <= DateStop
Take 20
Select
{
sc.CartID,
sc.PartNumber,
sc.ThumbNail,
sc.SDescription,
sc.Qty,
sc.Price,
sc.Category,
sc.CartDate
}
).AsEnumerable()
In hindsight, I should of used the productID in the shoppingCart table.
|
|
|
|
|
By creating two different DbContext classes, you're making things much harder for yourself. Entity Framework can only generate SQL queries for sets in a single context; as soon as you need to join to a set in a different context, you have to pull all of the data into memory first.
Based on your SQL query, both tables are in the same database, so it would make much more sense to have both sets in the same context. That way, you can join them together properly, and Entity Framework will generate more efficient queries.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I was thinking about that last night. Guess I should of made 1 giant context for the entire database?, or done a better job of planning by combining the cart and product tables.
Lesson learned.
|
|
|
|
|
jkirkerx wrote: Guess I should of made 1 giant context for the entire database?
That's the way I'd go. I'd only consider splitting them up if the tables in each context were completely separate, with no cross-context relationships, and would never need to be queried together.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I just made 1 context file with everything in it.
Finally got the DateRanges working, was banging my head against the wall to figure it out.
I had to wrap the Where statement in (), in VB its different to compare.
Where (oh.OrderDate >= startDate And oh.OrderDate <= stopDate)
Hey thanks for your help!, that was hard to figure out.
|
|
|
|
|
So I wrote this to represent the TSQL below
Not sure where I messed up, but I think it's the sum at the end.
I got one result for $46.51, but it should of been $0.00, now I broke it.
Using context As New OrdersContext
Dim DateStart As DateTime = DateTime.Today.AddDays(-1)
Dim DateStop As DateTime = DateTime.Today.AddDays(+1)
pValue = _
(From oh In context.Order_History
Where oh.OrderStatus = "COMPLETED" _
And oh.OrderDate > DateStart _
And oh.OrderDate < DateStop
Select oh).Union _
(From oc In context.Order_Completed
Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
Or oc.OrderStatus = "READY_TO_SHIP" _
And oc.OrderDate > DateStart _
And oc.OrderDate < DateStop
Select oc).Sum(Function(m) m.GrandTotal)
End Using
To represent this
DECLARE @StartDate AS Date;
DECLARE @StopDate AS Date;
SET @StartDate = CONVERT(CHAR(10),GETDATE(),101);
SET @StopDate = CONVERT(CHAR(10),DATEADD(d,1, GETDATE()),101);
WITH sums AS (
SELECT SUM(GrandTotal) GrandTotal
FROM CompletedOrdersHistory
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
UNION ALL
SELECT SUM(GrandTotal) GrandTotal
FROM CompletedOrders
WHERE OrderStatus = 'QUEUED_FOR_FULFILLMENT'
OR OrderStatus = 'OUT_FOR_FULFILLMENT'
OR OrderStatus = 'QUEUED_FOR_SHIPPING'
OR OrderStatus = 'OUT_FOR_PACKAGING'
OR OrderStatus = 'READY_TO_SHIP'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
)
SELECT Sum(GrandTotal) AS GrandTotal
FROM sums
|
|
|
|
|
Got the union working, and sum, just have trouble with the date range.
pValue = _
((From oh In context.Order_History
Where oh.OrderStatus = "COMPLETED" _
And oh.OrderDate = DbFunctions.AddDays(DateTime.Now(), 0)
Select oh.GrandTotal).Union _
(From oc In context.Order_Completed
Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
Or oc.OrderStatus = "READY_TO_SHIP" _
And oc.OrderDate = DbFunctions.AddDays(DateTime.Now(), 0)
Select oc.GrandTotal)).Sum()
|
|
|
|
|
Well I'm closer now. I suspect that I have to redesign this Linq in order for it to work.
More of a From Sum Select then where
The cast to value type 'System.Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.
Dim pValue As Nullable(Of Decimal) = 0
Using context As New OrdersContext
Dim DateStart As DateTime = DateTime.Today.AddDays(-1)
Dim DateStop As DateTime = DateTime.Today.AddDays(+1)
pValue = _
((From oh In context.Order_History
Where oh.OrderDate >= DateStart _
And oh.OrderDate <= DateStop _
And oh.OrderStatus = "COMPLETED" _
Select oh.GrandTotal).Union _
(From oc In context.Order_Completed
Where oc.OrderDate >= DateStart _
And oc.OrderDate <= DateStop _
And oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
Or oc.OrderStatus = "READY_TO_SHIP"
Select oc.GrandTotal)).Sum()
End Using
Return pValue
|
|
|
|
|
Finally
Using context As New OrdersContext
pValue = _
(
From oh In context.Order_History
Where oh.OrderDate >= DateStart _
And oh.OrderDate <= DateStop _
And oh.OrderStatus = "COMPLETED" _
Select oh.GrandTotal
).Union _
(
From oc In context.Order_Completed
Where oc.OrderDate >= DateStart _
And oc.OrderDate <= DateStop _
And oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
Or oc.OrderStatus = "READY_TO_SHIP"
Select oc.GrandTotal
).DefaultIfEmpty().Sum()
End Using
|
|
|
|
|
After testing with a real dataset, I was getting the wrong values.
For doing this in VB, you have to wrap the date range in (), in order to properly evaluate it.
So this produces the correct result, even if the value is 0.
I was banging my head against the wall trying to figure this one out.
Thanks to Richard Deeming for setting me straight on the DateTime.
Dim pValue As Decimal = 0
Dim baseDate As DateTime = DateTime.Today()
Dim dateStart As New DateTime(baseDate.Year, baseDate.Month, baseDate.Day, 0, 0, 0, 0)
Dim dateStop As New DateTime(baseDate.Year, baseDate.Month, baseDate.Day, 23, 59, 59, 999)
Using context As New hx5Context
pValue = _
(
From oh In context.Order_History
Where (oh.OrderDate = dateStart And oh.OrderDate <= dateStop) _
And Not (oh.OrderStatus = "ORDER_CANCELED")
Select oh.GrandTotal
).Union _
(
From oc In context.Order_Completed
Where (oc.OrderDate >= dateStart And oc.OrderDate <= dateStop) _
And
(
oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
Or oc.OrderStatus = "READY_TO_SHIP" _
)
Select oc.GrandTotal
).DefaultIfEmpty().Sum()
End Using
Return pValue
|
|
|
|
|
jkirkerx wrote: Thanks to Richard Deeming for setting me straight on the DateTime. You can up vote his responses to show appreciation (up/down arrows appear on the left of the response).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I haven't been able to do that in years, its like they took that away from me. Seriously, I don't get the vote interface when I log in.
|
|
|
|
|
Hover your mouse pointer over a message and two arrows should appear on the left side. The green upward pointing one is an upvote. The old voting system was removed due to abuse, but now a spam/abuse vote is used as a one vote by wuck fits and trolls. No real change there.
What do you get when you cross a joke with a rhetorical question?
The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism.
Do questions with multiple question marks annoy you???
|
|
|
|
|
I've seen those, never bothered to click on them.
Thanks
Guess you need to delete the message to keep it secret secret!
|
|
|
|
|
Hello friends! How is the day going? I need opinions in this case. I'm working on a social network site and as you all know there are many modules involved e.g friends, chat, messages table etc. My question is should I create database for each e.g chat db, messages db, friends db or I should create one database then create table for each of them e.g tblchat, tblmessages etc. Which of the option above is ok when managing social network?
|
|
|
|
|
While possible, I expect that any of those options will prove equally challenging to you.
|
|
|
|
|