Databases

58K

Solutions

40K

Contributors

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Share tech news, updates, or what's on your mind.

Sign up to Post

Experts,

Not sure why I am stumbling here so much but, I need to compare a DateTime field within my database to see if the dates stored in this column are between two user submitted Unix Timestamps.

Example (not working): SELECT * FROM registration WHERE checkin BETWEEN FROM_UNIXTIME('".$_POST['from']."') AND FROM_UNIXTIME('".$_POST['to']."') AND `appointment`=1 AND `deleted` IS NULL ORDER BY checkin ASC;

When attempting to validate by printing my query to the screen, I get the following which seems correct: SELECT * FROM registration WHERE checkin BETWEEN FROM_UNIXTIME('1571011200000') AND FROM_UNIXTIME('1571616000000') AND `appointment`=1 AND `deleted` IS NULL ORDER BY checkin ASC;

I'm not receiving any syntax errors but, not sure I am employing the "FROM UNIXTIME()" function properly???

Cheers!
0
OWASP Proactive Controls
LVL 13
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

I have a database with Table1. My table has two description fileds and two amount fields.  Below is a screen print of the table. I want a report that adds the description 1 and description 2 amounts and gives a total by description. Below is sample of Subtotals.accdbwhat I have and what I want.

Have:

Description1                  Amount_1               Description2                Amount_2
Schedule SA Line 2      50.00            
Schedule SA Line 2      50.00            
Schedule SA Line 2      50.00      Schedule SA Line 9      80.00
Schedule WE Line 6      10.00      Schedule WE Line 9      100.00
Schedule WE Line 6      10.00      Schedule WE Line 9      100.00
Schedule WE Line 6      10.00      Schedule WE Line 9      100.00

Want:

Schedule SA Line 2      150.00      Schedule SA Line 9      80.00
Schedule WE Line 6      30.00      Schedule WE Line 9      300.00
0
Dear Experts,
I use PHP and MYSQL

I select data from database which has a column that has 3 diffrent kind of values.  Some text, empty value and Null value.

when I select that column from database like below

while($row = $result->fetch_assoc()) {
        	
	$mazeret= $row["mazeret"];

if (empty($mazeret)) {$mazeretim="X"; } else {$mazeretim="Y"; }

Open in new window


The result is Y for empty and filled rows,  only the NULL rows are X, however the empty rows must be X too.

what do you suggest I should do? Thank you
0
I have a jpg file that less than 2MB in size. Which one of the solution would you recommend and why?

1). add the jpg file in the table as a BLOB attribute.

2). Store a path/name to the file in the table using a string attribute.

Thanks
0
Using VS C# I populate a dataset and then using a data grid view I display a grid on the screen. There 8 columns and 12 rows displayed at once. The first column is the most significant. In this case it is a job number.  Right now when this code is executed all records are committed to the database. My question is this. Is there a way to only write the records that have a job number? In other words is there a way to filter the committed records to only be the records that have a value for job number? Should I not be using command builder? I could provide additional code if that would be helpful.
            try
            {
                _commandBuilder.GetUpdateCommand();

                DistDataAdapter.Update(DistDataSet);
            }
            catch (Exception ex)
            {
                string eMsg = "View-JL-004: ERROR: " + ex.Message;
                if (Model.stackTraceWanted) eMsg += "\n" + ex.StackTrace;
                MessageBox.Show(eMsg);
            }
0
I am creating a database with three tables. The names of my tables are One_tbl, States_tbl, and Two_tbl.

One_tbl has a primary field called Acode that is a primary key.
States_tbl has a field called "St" that is a primary key.

I want table Two_tbl to have all records from States_tbl filed "St" and all the records from Acode from my table One_tbl

What I want on table Two:

St           Acode

AK         15(a)
AK          15(c )
AL          15(a)
AL           15(c )


The above is an example of two States AK and AL. How do I do this? Attached is my sample databaseAdjustments.accdb
0
I am using the code below on a userform in Excel to run a report stored in access database. It currently prints the report immediately. I am trying to have the report just display without printing. I would prefer that the report opens in excel and the user can then decide to print or discard.

Right now if the user runs the report it locks the access dba until the print job is complete. I need to prevent that from happening since we have multiple writing to the database from the excel userform. Any suggestions or perhaps a better method?


Private Sub CommandButton2_Click()
       Dim objAcc As Object
       Dim strWhere As String
       strWhere = "IDnNo = " & Me.txtBrNo & ""
       Set objAcc = CreateObject("Access.Application")
       objAcc.OpenCurrentDatabase FilePath:=TARGET_DB
       objAcc.DoCmd.OpenReport "rptClasss", acViewPreview, strWhere
       On Error GoTo ErrHandler
       objAcc.RunCommand 340 
       On Error GoTo 0
       objAcc.Quit
       Exit Sub
ErrHandler:
       If Err = 2501 Then 
           ' Ignore
       Else
           MsgBox Err.Description, vbExclamation
       End If
       Resume Next

End Sub

Open in new window

0
Hi

I'm attempting to clean up a database and need to remove duplicate entries

manually I'm doing this
select * from person where person.tmdb_id = 17867;
-- Gives 
id            name                       tmdb_id      profile_path
412	Thomas McCarthy	17867	/bz5ppWS58ryvKG4QQpIsqRXY68w.jpg
28522	Tom McCarthy	17867	/bz5ppWS58ryvKG4QQpIsqRXY68w.jpg

-- Pick 1 usually the lowest id 

update movielinkperson set personId = 412 where personId = 28522;
delete  from person where id = 28522;

Open in new window



Is there a way I can do this via script?

this found 58 entries (OK not a big number but I'm lazy)

select COUNT(*) from person GROUP BY person.tmdb_id  HAVING  COUNT(*)>1;

Open in new window

0
On receiving data from a Json format from the RS 232 COM, I seem to have some a bit of a challenge, I have no problem in sending
(1)      I’m not sure at what point the received data (In Json string) from the gadget will  looped into  or iterated until passed through the table , I cannot see the actual connection here, my maths does not seem to balance. Kindly see if you can spot something here.
Below is the code requiring checking , currently there no errors whatsoever , but the connection on how that data which will be received will looped into the database that is where I’m behind.

Code requiring your attention

Private Sub CmdEmp_Click()
    Dim json As Object
    Dim i As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim item As Object
    Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
    Dim lngStatus As Long
    Dim strError  As String
    Dim strData   As String
    Dim lngSize As Integer
    ' Read maximum of 64 bytes from serial port.
    lngStatus = CommRead(intPortID, strData, 64)
    If lngStatus > 0 Then
        ' Process data.
    ElseIf lngStatus < 0 Then
        ' Handle error.
    End If

    ' Reset modem control lines.
    lngStatus = CommSetLine(intPortID, LINE_RTS, False)
    lngStatus = CommSetLine(intPortID, LINE_DTR, False)

    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Contact")
    
    i = 2
    For Each item In json
        
        With rs
            .AddNew
            ![id] = 

Open in new window

0
I have a form in an Access database. In the form I have five commend buttons that open different tables. The commands are not working. It seems that that the module/object with the code disappeared. I do have a back up. How do I restore the command buttons? Below is sample of two command buttons.

Option Compare Database

Private Sub Command11_Click()
DoCmd.OpenTable "Filings_tbl", acViewNormal
End Sub

Private Sub Command13_Click()
DoCmd.OpenTable "Vendor_tbl", acViewNormal
End Sub
0
PMI ACP® Project Management
LVL 13
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

The variables lRow and lCol will only show the correct information on the first loop, the following loops is a report on the first loop. A similar thing happens if I use LastRow variable.


Public Function ExportToExcel(ReportFilterType)
' Tools > References > Microsoft Excel Object Library

On Error GoTo Error_Message
'Requires Reference to Microsoft Excel Object Library
 Dim rsMainList As DAO.Recordset
 Dim rsExport01 As DAO.Recordset
 Dim rsExport02 As DAO.Recordset
 Dim rsExport03 As DAO.Recordset
 Dim sqlExport01 As String, iCol01
 Dim sqlExport02 As String, iCol02
 Dim sqlExport03 As String, iCol03
 Dim xlObj As Object
 Dim xSheet01 As Object 'Upload File
 Dim xSheet02 As Object 'Batch Summary
 Dim xSheet03 As Object 'Batch Detail
 Dim strFolder As String
 Dim LastRow As Long
 
 On Error Resume Next
 DoCmd.Hourglass True
 Set rsMainList = CurrentDb.OpenRecordset("SELECT DISTINCT UPLOAD_ID, VISUAL_BATCH_ID, VISUAL_BATCH_TYPE, VISUAL_DATABASE  FROM VMTBL_NI_VM_ORCL_GL_UPLOAD_STAGING")
 If rsMainList.EOF Then Exit Function
 rsMainList.MoveFirst
 
 strFolder = CurrentProject.Path & "\"
 
 Do Until rsMainList.EOF
   
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add

    sqlExport01 = "SELECT Database, [Posting Date], [Account Combination], Co, Div, Fun, Rig, Job, AFE, Maj, Min, [I/C], [Debit Amount], " + _
    "[Credit Amount], [Total Amount],[GL Description], [Upload ID], [Batch ID], [Currency ID] " + _
    …
0
Can anyone explain to me about linear hashing. I am kind of confuse. If you can provide me an example to help me to get understand that will be great.
0
I am getting the following error while trying to save my Access database as an ACCDE file. There is VBA code but nothing running

1
0
What is computed attribute mean?
0
I have inherited a database where the Access objects are hidden and only a login form appears.  Where do I look to see how the other developer did this. I can see the tables etc by holding the shift key down
0
In the following query I have two calculated columns SlotTime and SlotBreak and I wish to subtract them to create SlotWorked but SQL Server claims SlotTime and SlotBreak are invalid column names
select R.site_id,L.Activity,L.PT,L.Rate,'1/' + DateName(month,R.week_commencing) + '/' + Cast(Year(R.week_commencing) as varchar) as FDM,
L.D1AB AS DayAbs,
L.D1F-L.D1S as SlotTime,
Case when L.D1F-L.D1S < 6.02/24 then 0 else 0.5/24 end as SlotBreak,
([SlotTime]-[SlotBreak]) as SlotWorked
from
tblRotas R inner join tblRotaLines L on R.Rota_ID=L.rota_id
0
Hello Experts,
Here is my issue.  I have a MySQL database that a report is being derived from. There is a field in one of the tables that is stored as XML. Previously, an expert gave me some great advice and I was able to create a formula that broke out one of the 3 pieces of data that I need.

StringVar strTest := {_invoice_transaction1.Extra};
ExtractString (strTest,'"CheckDate">',"</v");

While this formula gives me the CheckDate, as I suspected, I could not create a parameter field from the formula.
Unless there is a workaround that I have not yet deduced, my latest attempt is to go to the SQL query and modify the query prior to the data reaching the report.

I know I need to go to: Database:database expert: odbc: add command... and there I can modify the SQL.

So, that leaves me 2 questions: Where and or how can I see the SQL statement output so I know if my modifications are going to have the desired results. If I was using straight SQL I would probably add a line like: ExtractString(_invoice_transaction1.extra, '"Checkdate">',"</") as checkdate     -  to my query which I have copied from Crystal below.

 SELECT _invoice_transaction1.TransactionDate, _customer1.LastName, _customer1.FirstName, _customer1.MiddleName, _insurancecompany1.Name, _invoice1.ID, _invoicedetails1.BillingCode, _user1.Login, _invoice_transaction1.Amount, _invoice_transaction1.Taxes, _invoicedetails1.ReviewCode, _invoice_transactiontype1.ID, _invoice_transaction1.Extra
 FROM   …
0
I double clicked on my database that is split.  It asks me a password and i am successful with that.  It then loads and when I go to copy an object in it gives me a "The database has been placed in a state by user"Admin" on machine.....

I click on ok and close the database it then has another database open behind it even though I did not open it.  Password does not work on that one then it is closed.  What can I do?
0
If I need to efficiently locate all records that have zip code 79915. Should I use a B+ tree index or Hash index or neither?
0
Become a CompTIA Certified Healthcare IT Tech
LVL 13
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

I have an SSIS solution which has grown over the past few months to the stage where I need to break it out into separate solutions.
A bit of background: The project is migrating data from one database to a new system. Data is extracted from the old database into flat text files which I then import to SQL Server using SSIS Workflows. The data is then transformed and output to Excel templates ready for importing to the new system.

All of this works fine. Now we are coming up to the cut off date for data from the old system. This will be the dataset that forms the Live data in the new system. There is a period of one month between the data cut off and the use of the new system, during which time changes will be made on the old system. So, we have a requirement to produce delta output which generates records that have been Inserted, Deleted, or Updated. All of this is fine and the scripts are ready.

However, I've run into serious issues with memory with having both the main data migration and delta workflows in the same project. Therefore, I want to move the Delta workflow to a new solution. The way I've gone about doing this is to set up a new solution in VS 2017 (15.9.16), and adding an existing package to the solution. When I do this, it loses the Connection to the database. So, I've tried creating the connection prior to adding the existing package, yet I get the same result.

I could go through every data flow task and update the connection, but this also requires …
0
Hi
In order to prevent another $thousands mess with Godaddy, I thought I'd get some advice on my new circumstance.

A Godaddy tech is calling me tomorrow to discuss my unused policies and perhaps a refund on my giant mistake.

If you'd like a remembrance of the  previous blunder question, briefly, it is here

Anyway, the tech suggested I might be refunded this $3,000, he said -
$1,000 dedicated server purchase (and some other stuff) and we'll discuss it tomorrow. I'll let you know how it goes here, and on my other question.

Discussion with Godaddy tech -
I'll say - Experts advised me to stop using godaddy. - for hobbyists, which I have already done a hobby type blog.
Sadly, I have a bad case of loyalty tendency....no more...
I mentioned on the setup call today that experts had advised me that Godaddy was inadequate for top-shelf .io game delivery.
How pitiful.
I don't even know why I fell for the $3,000 thing. She convinced me it would be optimal for game delivery and speed. My blog's performance seemed acceptable, but it wasn't NYT. I saw no reason to be contemptuous.
Is there any chance the tech could offer something decent tomorrow? Don't I want to keep my godaddy for the parked domains and my many files and backups in my public folder, ability to do new websites quickly? The refund will only be into my Godaddy account, which might …
0
Hi EE,

Using the following tutorial for visual guidance:

https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/

I am trying to import some data into an table. One excel tab allowed me to import the data with no hassles the other excel table gives me the error message Error: 0xC002F210  the table already exists.

The table already existed previously and I don't have create permissions against the database when I did the first import what is going on?

Any assistance is welcome.

Cheers
0
I have a stored procedure in one database that writes to another database. Security is assigned using SQL Authentication.
For each user I have issued
GRANT DELETE,UPDATE,INSERT TO JOBCOSTDETAIL TO USERNAME

This stored procedure is called via a  VS C# program. Whenever a user clicks the button to run the stored procedure they get:
The INSERT permission was denied on the object 'JOBCOSTDETAIL' ,database 'DATABASE',schema 'dbo'

I could change my code to run the sp as 'sa' but I should not need to do that. When I check the properties of this user on this database they do have INSERT permission as dbo. What else do I need to set to get an average user the ability to write to this table in this other database.
0
Hi All,

Hope someone can help. Half way through a 2010-2013 SharePoint Migration. I have run the SPtest against the old database and it has come up with a few error. One of which being that reporting services is not configured. I have been looking at the guide here: https://docs.microsoft.com/en-us/sql/sql-server/install/install-reporting-services-sharepoint-mode-for-sharepoint-2013?view=sql-server-2014 

Which says the SQL feature needs installing before enabling within SharePoint, I have the install files for SQL 2014. When I run them I do not get the SharePoint reporting options, either when running a fresh sql install or adding new features.

What am I missing?

Cheers,
Paul
SQL-Install.png
0
Can anyone answer these Power BI questions:

1)      Can we transform data files that are natively stored as name/value pair and convert them into a file that stores the name in nam/value pair as column names? This way we can then use the new column name easily for new visualizations and Q & A.
2)      Does PowerBI support the use of Synonyms/Aliasse ? For example, for a data field called “Asset Weight” could we add synonyms to it such as “Holdings Weight”, “Security Weight” so it can be easily picked up in Q &A
0

Databases

58K

Solutions

40K

Contributors

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.