I am trying to add a website address to all the records in a database that match a certain criteria. The statement that I am using is shown below, but surprise surprise, it's not working! I'm new to SQL so any help would me much appreciated! Thanks.
declare @ComNum int
set @ComNum = (select max(communication_number)+1 from communications)
insert into "communications"
(contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
values (NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum);
select name
from organisations
where name ='Abc Limited'
So, I have not included a VALUE for CONTACT_NUMBER as I wish to update all records with the website details as per the INSERT statement where the NAME column in the ORGANISATIONS table is 'Abc Limited'. I know something is wrong but I can't quite work out what!
I'm some what of a noob when it comes to ASP.NET 2.0 and SQL Server 2005 Express editions, so please bare with me. I have created a basic database that is only one table. One of the fields is LogPicture with type of image. I just want to go into the table and manually place a picture in the table, but it seems like I can not get the format down. I have tried including the picture I want into the project, but no dice. I've also tried putting the physical pathname (on the harddrive) and the web address of this field in there. Suggestions please?
I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way? :confused:
I simplified my example to this: ----------------------------- --CREATE PROCEDURE sp_InsertOrders AS
DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT
--DROP TABLE #Orders CREATE TABLE #Orders ( OrderId SMALLINT IDENTITY(1,1), FkCustomerID SMALLINT NOT NULL, OrderDate DATETIME NOT NULL )
--DROP TABLE #OrderDetails CREATE TABLE #OrderDetails ( OrderDetailsId SMALLINT IDENTITY(1,1), FkOrderID SMALLINT NOT NULL, ProductID SMALLINT NOT NULL, UnitPrice SMALLINT NOT NULL )
INSERT INTO #Orders (FkCustomerID, OrderDate) SELECT CustomerID, OrderDate FROM OpenXML(@docHandle, 'Orders/Order', 3) WITH ( CustomerID INTEGER, OrderDate DATETIME )
SET @OrderID = @@IDENTITY;
--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice) SELECT @OrderID AS OrderID, ProductID, UnitPrice FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3) WITH ( ProductID INTEGER, UnitPrice INTEGER ) -----------------------------
All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... :confused: :confused: Anybody out there who knows how to solve the problem?
i have go master records as well child records how do i delete all the master records so that all my corresponding child records get deleted ,as we do in oracle using undeleted cascade
I'm a new user to vwd. If I use a details view control on my page, I have noticed that the "New" link is not visible unless there is at least one record in the table. Is there any way of making it visible where there aren't any records?My web pages are currently hosted at vwdhosting. I have uploaded my database with the record structure onto the web site and I am using a remote connection string to access it. I have had users updating data in another table on the remote database. If I add records to my new table locally and upload the database to the remote site, all the data that my users have been adding will be lost. So, if I can't add my first record using a control on my web page when there are no records in the table, should I be doing it programmatically? If so, how?Thanks,Julie
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
Hi, I started working with SRS from the past one month. I am breaking my head to do this. My report has a single stored procedure. I made it to work all values of a parameter like exec sp_employee 'All' (Returns data for all employees) exec sp_employee '1111' (Returns data for an employee with id 1111) exec sp_employee '1111,2222' (Returns data for an employee with id 1111 as well as 2222)
This part works perfectly. I could successfully pass the values as mentioned above using a code block.
Now my challenge is how to layout the data on the report.
I need to show the report as follows:
Employeeid Employee Name Address etc....... Skills: Skill# Skill Name 1 11111 2 22222 3 33333 4 44444 Contacts: Contact Type Contact# Main 111-1111 Emergency 222-1111 Eductation: Name University Degree UofU Masters UofU
I tried using group by employeeid. But i can only show one group of data either Skills/Contacts/Education. I do not know if I use sub-report, how to pass the parameter from the main, when I pick multiple values for Employeeid.
Any idea how to do this? I appreciate your help, Thank you,
I am trying to find a way to add into a table a flattened (comma seperated list) of email addresses based on the multiple columns of nformation in another table (joined by customer_full_name and postcode.
This is to highlight duplicate email addresses for people under the same customer_full_name and Postcode.
I have done this using a loop which loops through concatenating the email addresses but it takes 1minute to do 1000. The table is 19,000 so this isn't really acceptable. I have tried temp tables, table variables and none of this seems to make any difference. I think that it is becuase i am joining on text columns?
I can add the records using an SQL statement, but when I try to open the recordset and add a record using the AddNew method it fails with the message:
The command contained one or more errors. [,,,,,]
But, if I connect to an Access database (which uses exactly the same tables, etc.) using the commented out connection string in the above sample, the code works fine. What am I doing wrong?
Here's my dilema. I'm using VB.Net 2003 and SQL DB
I'm having a problen adding a record to a table in my SQL DB
My previous experience has been working with Access databases.
When I try to create an SQLDataAdapter using the wizard from the toolbox the wizard will only create the Select statement. it won't create the UPDATE, DELETE or INSERT statements. I'm not sure why, I even tried using the form wizard and it still wouldn't work...
My tables all have primary keys defined.
So I decided that I would try to create everything programatically.
What I need to know is how to create the INSERT statement and which statements to use to add the record to the table.
I know that when using the OLEDataAdapter with the Acces DB I would do something like the following code.
I trying to add records from a table in DBF format (created with Visual FoxPro) into an SQL table created with SQL Server 2005.
I first converted the dbf file into an ascii file and then in the Query Editor in the SQL Server I typed:
use [c:developesqlsqldatapsw.listener]
append from 'c:developesqldatalistener.txt' type sdf
go
and then I pressed F5. I get an error that says:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'c:developesqlsqldatapsw.listener'. No entry found with that name. Make sure that the name is entered correctly.
I checked in the directory c:developesqlsqldata and the file psw.mdf is there and when I look in the psw database, the table listener is also there.
Could someone tell me what is going wrong? Also, how do I append the whole dbf table onto an SQL table. there's about 6000 records.
I'm trying to add a sum function to my query to sum the 12 records I get back in order to see if my total charges match the amounts within a different database table. Unfortunately, now when I run the query using the sum function, I am getting very large numbers back for the charge amt and I can't figure out why. Here is the query I've written:
IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C
select prin_sbb, sub_acct_no_sbb, res_name_sbb, ext_stat_sbb, cur_bal_sbb, VIP_FLG_SBB INTO #TMP1C from Vantage.dbo.SBB_BASE (NOLOCK) where PRIN_SBB in (6000,7500)
[Code] .....
Not entirely sure what I am doing wrong, or why the numbers are coming back so high.
Hey guys. I need a little help once again, here's the problem:
We are using an attendance system software that adds records in an Access 97 table whenever someone logs in or out of the room (Yes, it's old and I hate it, but it came with the hardware).
So we plan to create a program that lets admins check those login/logout data in an SQL Server 2005 database using a Visual Basic as the frontend.
Our only problem is, how do we transfer data from the Access 97 tables to our SQL tables *everytime* it gets updated? We don't need every detail the Access97 table has (it has IDNo, CardNo, Time, Location, plus other useless stuff). We just need to extract the IDNo(int) and Location(varchar) field(we'll just use getdate() for the Time since the update is done in realtime anyway).
So in a way, we are trying to find out a way to technically sync both tables, just like Database Mirroring style.
I have a shopping cart that will get full from time to time becausecustomers click out of the site before they confirm their purchase,therefore leaving a full cart behind. I'd like to have a timestamp onthis table, so that I can delete anything that I find is more than a dayold.How can I do this?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I'm trying to add records from a table in DBF format (created with Visual FoxPro) into an SQL table created with SQL Server 2005.
I first converted the dbf file into an ascii file and then in the Query Editor in the SQL Server I typed:
use [c:developesqlsqldatapsw.listener] append from 'c:developesqldatalistener.txt' type sdf goand then I pressed F5. I get an error that says:
Msg 911, Level 16, State 1, Line 1 Could not locate entry in sysdatabases for database 'c:developesqlsqldatapsw.listener'.No entry found with that name. Make sure that the name isentered correctly. I checked in the directory c:developesqlsqldata and the filepsw.mdf is there and when I look in the psw database, the tablelistener is also there. Could someone tell me what is going wrong? Also, how do Iappend the whole dbf table onto an SQL table. there's about6000 records.
When adding records containing a date field in SQL Server 2005 Express I get an error Input string was not in a correct format. Do datefields have to be converted when adding or editing into strings?
I'm using VS2008 (as shipped with SQL CE 3.5 as shipped). I want to write a database app and started with a quite simple example: One table "Master" has 2 columns: int MasterID, int Value (where the MasterID is the primary key and is an identity column.
For the GUI I used the typed dataset components (based on the namespace: System.Data.SqlServerCe) which were automatically generated by VS2008. I just dropped the table from the datasources view to a form and got a DataGridview, a Navigator and some DB components, quite fine so far.
When i run the app i can add new rows and the identity column is filled with -1, -2 ... as expected. When I hit the save button, the rows are saved to DB, but the values of the identity columns are not updated (the still have -1, -2..)
When I use the exact same example with SQL Express evrything works fine: the identity Column's values are updated automatically and have +1, +2, ... Sure there is a statement "SELECT @@IDENTITY"... but I'm just using automated code so far, and i expect that code to work (automatically). Next problem: if i want to insert that "Select @@identity" stuff (I don't want to do that, but perhaps I have to?) where can I do this, I did not found a OnInsertedRow-Event? This problem is part of a bigger one: I want to use Master/Detail, but inserting the Details fails because the inserting of the master rows didn't return the correct Indentity values, so the detail rows cannot be inserted (the foreign keys -1, -2... will raise an error).
Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew
When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.
I am pretty new to the DB part of this but have built an asp.net web appplication with 2 tables: FORMS and UNITS I have created a web page that will allow users to add forms and associate a unit with that form. I now need to be able to allow users to associate the form with multiple units. I can change the web page list box to allow multiple selections but that doesn't solve the problem. This seems like a pretty simple task but I can't seem to find anything on it. any help??? below is the stored procedure I was using: CREATE PROCEDURE dbo.USP_AddForm
Whats the fastest easiest way to take a select that returns say 4 values for the expression into a single column on defined row
basically I mean i want to do an update to say a persons i dunno ummm places they have traveled and I want it listed like france;usa;germany etc etc and the data would always be in the tables i pull from so I can overwrite the data each time i run it but has to take 3 or more values from a query and put them in separated by say a ; into the same persons coloumn that stores the info.
I did this once before with a cursor and adding a variable to itself with colasce or whatever the command was, but was just wondering if there is a fast way to do this by chance that im not thinking about :P.
i have a package here which updates a DB from a flat file source.now the problem is i may get multiple files.i have used a for each loop to handle this. it takes files based on the files name9(file names has a timestamp in it).but i want to give files in order of its Creation time.
Please help me on this.i have written a script task before the for each loop and i have got the minimum creation date from all the files,i am not able to going forward from here.
I'm trying to put a range of dates into the datatextfield of my ddl. I'm using the query:
string strProps2 = "SELECT sc_id, cast(begin_date AS varchar) + ' - ' + cast(end_date AS varchar) AS XYZ From Archived_Property_Changes WHERE Property_number = " + qryPN + " ORDER BY end_date";
but it gives me an empty ddl. the datavaluefield shows the correct results, but it's not displaying the correct data from the datatextfield. I removed the casts from the SQL query and it returns:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
Below is my ddl code:
SqlCommand objCommandProps2 = new SqlCommand(strProps2, myConnection); SqlDataReader objReaderProps2 = objCommandProps2.ExecuteReader();
My current environment has multiple packages stored in SQL server (MSDB). When working on a set of packages I want to bring them into my local development area Add existing package only allows you to pull one package at a time - anyone have the secret to selecting multiples
I have a a Group By query which is working fine aggregating records by city. Now I have a requirement to focus on one city and then group the other cities to 'Other'. Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Kansas City 800 Columbia 700 Jefferson City 650 Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Other Missouri City 800 Other Missouri City 700 Other Missouri City 650 Other Missouri City 300
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
