Another idea was XML.(also only low experience) Is it possible for me to work with SQL statements in it?
It's possible, but it would require third party libraries AFAIK.
How fast is XML? (compared to mySQL and MS Access)
Not fast at all, we're talking unindexed text parsing here.
okay, now I need only a tip which kind of LOCAL database I should use.
For the best support you should probably go for SQLServer as the other have suggested.
For a small footprint, easy distribution and portability, I would have a look at SQLite, just add one library and one datafile.
The beauty of SQLite it that your database and the sql engine are all contained within one file.
What I would also do is archive the data to xml as a backup for the sake of your sanity and hard work of inputting the data - an archive on each closing of the application, to a file with a datestamp within its name, is the way I tend to handle things with a deletion of old xml files every 30 days.
“That which can be asserted without evidence, can be dismissed without evidence.”
I am trying to create an ER diagram for online auction for smart phones.
The brand new phones are only for sale. The sale is in auction style. A new phone can be listed with a starting price and available numbers etc for a few days (e.g. 3 days). During this period, a buyer can put a bid with an offered price for a listing. At the end of the period, the listing can have a number of bids. Based on the bids of a listing, sale transactions may happen between the sellers and buyers.
2. The used phones are only for rent. A buyer is to submit an application for renting a phone. As a result, rent transactions may happen between sellers and buyers.
3. As described above the customers are either sellers (selling or leasing phones) or buyers (buying new phones or renting the used phones). A seller must have a registered payment method (e.g. PayPal) for the income from selling and a registered payment method (e.g. PayPal) for paying management fees to the Best Deal. A buyer could have a registered payment method (e.g. PayPal) for paying any purchased items or just paying by credit card directly when buying. However either a seller or a buyer must have at least a phone or a mobile or an email contact.
Hi Friends I have to join 4 tables in SQL Server and need to Show Specific Coulmns in Data Grid View
From First Table User Tabel and feild User_ID, User_Name, User_Email, User_Password, UserR_ID, UserS_ID, Co_ID, User_Remark
from Second Table User_right Tabel and feild UserR_ID, User_Right
.from third Table User_Status Tabel and feild UserS_ID, User_Status
From Fourth Table Company and feild Co_ID, Co_Name
From First Table i need to show User_ID, User_Name, User_Email, User_Password, User_Remark
2.from Second Table i need to show User_Right
3.from third Table i need to show User_Status
4.From Fourth Table i need to show Co_Name
Try to Reply to this as soon as possible Friends...Thanks..
I have built a few SSIS packages over the years, most of which contain a design which will fit within the VS window with little scrolling (i.e. not hugely complex). Although I have largely got my head round logging and configuration, I have never really fully settled on a standard for error handling.
I often handle errors via control flow (i.e. the red connectors) but have dabbled once or twice with the event handlers. While I have got the latter to work the implementation of them never seemed quite right (apologies for vagueness).
Does anyone have any thoughts on, or real world experience of, which route is better?
...or does it depend on the nature of the error? i.e. data-related errors via flow but critical failures via handlers.
Hoping this is not another one of those bun-fight topics...
I wrote this years ago for a report on total items sold, and I'm trying to convert it to a SQL Linq Statement in VB, but I think I'm way off here. Plus I'm not sure if I got the joins right, pretty sure that part should work.
, SUM(coH.Qty) as TotalQty
, CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost
, CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPrice
there must be a shorter way of writing the sum statement, and also I can't figure out how to sum the column within the select new. My original thought was to just think sql, but on the SUM, I think I need throw that idea out the window.
From cohc In context.Order_History_Cart
Join pi In context.ProductInfo On pi.PartNumber Equals cohc.PartNumber
Join vi In context.ProductInfo_Vendors On vi.VendorID Equals pi.VendorID
Where cohc.OrderDate >= m_startDate _
And cohc.OrderDate <= m_stopDate _
And cohc.PartNumber = p_PartNumber
Select New productItem With
.m_partNumber = cohc.PartNumber,
.m_manPartNumber = cohc.ManPartNumber,
.m_totalQty = (From x In context.Order_History_Cart Where x.OrderDate >= m_startDate And x.OrderDate <= m_stopDate And x.PartNumber = p_PartNumber Select x.Qty).Sum(),
.m_totalCost = (From y In context.Order_History_Cart Where y.OrderDate >= m_startDate And y.OrderDate <= m_stopDate And y.PartNumber = p_PartNumber Select (y.Qty * y.Cost)).Sum(),
.m_totalPrice = (From z In context.Order_History_Cart Where z.OrderDate >= m_startDate And z.OrderDate <= m_stopDate And z.PartNumber = p_PartNumber Select (z.Qty * z.Price)).Sum(),
.m_cost = pi.Cost,
.m_price = pi.Price,
Why do you have SQL embedded in your VB code? As a DBA that is a no no. You can get SUM in T-SQL, but you can also SUM in SSRS and Crystal Reports. As for your new I have never seen that. I am not a very experienced VB developer so it may be something I have not seen there. I would do it in a stored proc.
I got thtis to work, but I can't figure out how to insert my 2 joins, and get those values all in 1 result set
Dim pResults = _
From cohc In context.Order_History_Cart
Where cohc.PartNumber = "24-FLP44"
Group By cohc = New With
} Into group
Select New With
.totalQty = group.Sum(function(q) q.Qty),
.totalCost = group.Sum(function(c) c.Qty * c.Cost),
.TotalPrice = group.Sum(function(p) p.Qty * p.Price)
Been there done that, but in WCF.
In design time you're apparently not using the app.config you think you are.
There's a description and kind of solution to one of the ways this problem can occur here[^].
1/ How can you add transaction functionality to your stored procedure without locking anything ( I know there is different isolation level, but I'm not sure which one to use )
2/ I need to use transaction just for the sake of rolling back the changes if there was an exception in the middle of the procedure. so I really don't care about locking tables. which isolation level should I use.
How can you add transaction functionality to your stored procedure without
locking anything ( I know there is different isolation level, but I'm not sure
which one to use )
Locking is done when writing. The isolation levels are for specifying what you want to read.
I need to use transaction just for the sake of rolling back the changes if there
was an exception in the middle of the procedure. so I really don't care about
locking tables. which isolation level should I use.
Sounds like the procedure consists of multiple steps that can fail individually, and those updates might lock multiple tables while processing.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
READ UNCOMMITTED is the isolation level you are looking for I think. This will allow dirty reads, but almost eliminate all locking. Writing will lock the affected tables, but if others READ UNCOMMITTED they should be fine. If you have to lock the table in order the rollback the process then you really have no choice but to lock it.
It's a Navigator fly out tab, in which if your working on a product in the editor, you can activate the tab, and the tab will populate with lets say 4 products before and 4 products after the selected product in the database table. This is so you don't have to go back to the index to load the next or previous product.
Since SQL Linq doesn't support the custom SQL Server Function, I need to think or create an alternative way to do this.
I guess I can create a List(Of with a blank column, and then go back and fill in the row numbers
get the row number of the item and ask for -3 and +3 of the row.
Just looking for some suggestions.
Here's the old TSQL
WITH CTE AS
, ROW_NUMBER() OVER(ORDERBY PartNumber DESC) AS RowId
FROM PRODUCTINFO p
), CTE1 AS (
SELECT RowId FROM CTE WHERE PartNumber = @PartNumber
SELECT * FROM CTE WHERE RowId BETWEEN (SELECT RowId-4 FROM CTE1) AND (SELECT RowId+3 FROM CTE1) ORDERBY PartNumber
This is what I was building in Linq Pad. It's pretty weak now, but I'm getting an idea of what I can do. But before I go off in some strange direction that will lead nowhere or be super slow, I thought I would just try and get a general consensus on this.
I tried to mark it as code but it gets chopped off, end of the day for me, going home now.
Dim p_partNumber as string = "06-SM4"
Dim context as new DBcontext
Dim ordersAsList = _
From pi in context.ProductInfo
Dim orders = _
From pi in ordersAsList
I didn't know you can do that.
I know how slow it can get when the record count get high. That's why I didn't want to do the list.
I'll give that a try for now, and then redesign the whole thing later. I knew that using special DB function would take a tool in the future when I implemented it.
Works like a charm. Took awhile to figure out how to implement it.
Did you write that? it's pretty elegant and wild in how it works.
It would of taken me months to figure that out! But that should be the most complex TSQL remaining in my program.
Oh, this conversion I'm making is mentally draining on me every day, but I'm 1/2 way there now.
The reward will pay off for me when done. Thanks again for taking the time to write that, that was beyond my current knowledge level.
I have this report for sales rep margin in Account Mate for DOS, that uses FoxPro for database table file, .dbx
The item tables or DB files does not have a column for sales rep and order dates, so I can't go to the table directly to get my data.
So without knowing or even where to start to write this in pure SQL, I wrote 2 functions
1. Get Invoices that match the sales rep and start and stop dates, and store it in a List(of invoices) 2. loop a function that targets items by invoice number, and get the cost, price, qty, etc.
When you have 500 invoices, it takes forever to run, slowing down fixing my other problems, which may be fixed now.
Do you think it's possible to merge these statements into a single statement. Plus if so, a nudge in the right direction. I hate this old Fox Pro stuff, it was before my time.
FROM ARINV01H.dbf h
h.FSALESPN = @FSALESPNAND
h.FSHIPDATE >= @startDateAND h.FSHIPDATE <= @stopDateUNIONALLSELECT
FROM ARINV01.dbf v
v.FSALESPN = @FSALESPNAND
v.FSHIPDATE >= @startDateAND v.FSHIPDATE <= @stopDate
, SUM(FCOST * FSHIPQTY)
, SUM(FPRICE * FSHIPQTY)
, (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC
FROM ARTRS01H.dbf h
h.FINVNO = @FINVNOUNIONSELECT
FROM ARTRS01.dbf v
v.FINVNO = @FINVNO
GROUPBY FITEMNO "
The AccountMate for DOS works. The sales rep for them in Dallas sort of dicked them around with the price to upgrade, and raised the price 15K after he paid a visit, so they decided to just stick it to them.
So I made an overlay program for them that performs most of the daily functions for the front office. Hey its income for me, they pay fast.
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)
'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)