How To Retrieve Id From That Query Line
May 30, 2008
Hey.
I have two sets of tables.
Machine Warranty table (primary key=machine_warranty_id)
("INSERT into machine_warranty(Job_No, Cust_ID, Machine_SerialNo, Product_ID, Unit_Price, Payment_Type, Date_Installed, Freebies, Remarks, Unit_Location, Date_Paid_MW, Cheque_CC_No, Date_TrialStart, Waterblock, Pressure_Regulator, Rental) VALUES ('" + job + "','" + txt_CustID.Text + "','" + txt_MachineSerialNo.Text + "','" + txt_ProductID.Text + "','" + txt_UnitPrice.Text + "','" + dd_PaymentType.SelectedValue + "','" + txt_DateInstalled.Text + "','" + txt_Freebies.Text + "','" + txt_Remarks.Text + "','" + txt_UnitLocation.Text + "','" + txt_DatePaid_MW.Text + "','" + txt_ChequeCCNo.Text + "','" + txt_DateTrialStart.Text + "','" + dd_WaterBlock.SelectedValue + "','" + dd_PressureRegulator.SelectedValue + "','" + dd_Rental.SelectedValue + "')", connectionOne);
//machine warranty id is a primary key is auto generated already therefore not in the query line.
Warranty Period table (primary key=warranty_period_id/ foreign key=machine_warranty_id)("INSERT into warranty_period(Warranty_Period_ID, Warranty_Start) VALUES ('"+ txt_Warranty.Text +"','"+ txt_Warranty_Start.Text +"')", );
How to retrieve the machine_warranty_id from the first sql query line? So that I could use it for Warranty Period Insert statement.
View 2 Replies
ADVERTISEMENT
Oct 5, 2006
Hi folks,I'm trying to write a simple SELECT statement that will execute inquery analyser but will just have the data with no column names, or thedotted line between them and the data. I also want to avoid thestatement at the end which says nnn rows affected. any ideas? I want todo this because I intend to write the results to a flat file.Thanks for your helpDanny....
View 2 Replies
View Related
Mar 5, 2014
ItemIDItemNamePrice
1Item1120.00
2Item2234.00
3Item3250.00
4Item4300.00
5Item5300.00
6Item6290.00
7Item7170.00
8Item890.00
9Item9170.00
the above is the existing table and i need a query to retrieve max price with out using max function and sub query
View 6 Replies
View Related
Jul 27, 2007
Hello all
Trying to delete some data from a SSCE (2005) DB produces the exception:
SqlCeException
There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C]
Here is the code I am using
string dsc = Application.StartupPath + "\FCDB07.sdf";
conn = new SqlCeConnection("DataSource = " + dsc);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM DataContainer WHERE FileName =" + dgContainers[0, SelRowIndex].Value.ToString();
cmd.ExecuteNonQuery(); //There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C ]
conn.Close();
Any Idea on What causes this?
TIA
Trophus
View 3 Replies
View Related
Nov 23, 2007
Hey all-
I'm trying to insert some values into an SQL Compact database on a WM6 device but there is something apparently wrong with my SQL statement...
The program is going to allow users to schedule an SMS message to be sent at a certain date and time. I'm using a database to keep track of the scheduled SMS messages. The database has 3 rows: phone number, message, and the date/time to be sent.
Here is the relevent code:
private void scheduleMenu_Click(object sender, EventArgs e)
{
//connect to DB and do our scheduling magic
string message = messageBox.Text; //should rename messageBox...
string phoneNum = phoneNumBox.Text;
string dataBase = @"Program FilesSMS_Scheduler2SMSDatabase.sdf";
//SqlCeEngine eng = new SqlCeEngine(dataBase);
SqlCeConnection conn = new SqlCeConnection("Data Source=" + dataBase);
conn.Open();
//insert phone number, message text, and date/time into DB
string cmd = "INSERT INTO Scheduler(phoneNum, message, date) VALUES("+ phoneNum + ", "+ message + ", "+ dateTimePicker1.Value +")";
SqlCeCommand cmdPhone = new SqlCeCommand(cmd,conn);
cmdPhone.ExecuteNonQuery(); //error occures here...
messageBox.Text = "";
MessageBox.Show("Message Scheduled!");
}
I'm guessing it doesn't like how I am trying to get the data from the different text boxes and the DateTimePicker to go inside the SQL command. Does anyone have any ideas on how to fix my SQL command or how to get data from a textbox and DateTimePicker to be inserted into a database a different way?
View 3 Replies
View Related
Oct 5, 2006
Hi,
I was wondering if there is any way I can place a new line inside a query...
e.g.
select field1 + 'NEWLINE' + field2 from tablename
I want to place a new line between field1 and field2
Thanks in advance
View 9 Replies
View Related
Feb 12, 2004
I would like to use a dynamic query to obtain a value...a recordcount specifically.
I have two options so far. The 2nd option works fine, but seems like it would be slower. The first option doesn't recognize that i have declared my variable (@int). Any ideas how i would use the first method...or if it's worth trying?
Method 1
--# produces error: Must declare the variable '@int'.
DECLARE @int as int
EXEC ('SELECT @int = COUNT(ProductID) FROM Products')
print @int
Method2
--# works fine...but seems less efficient.
EXEC ('SELECT ProductID FROM Products')
print @@rowcount
View 4 Replies
View Related
Sep 9, 2014
Here is the sample xml
<ClinicalDocument xmlns:sdtc="urn:h12-org:abcd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:h12-org:v3">
<realmCode code="US" />
<typeId extension="POCD_HD000040" root="2.16.840.1.113883.1.3" />
<templateId root="2.16.840.1.113883.10.20.22.1.1" />
<id root="226ff30f-3b1f-11e3-a969-005056bb0109" />
[code]....
how to write a sql query.
View 2 Replies
View Related
Aug 24, 2007
I am using SQL Server 2005.
I have a table with records like this:
Date EmpID ADCode ADAmount
----------------------------------------------------------------------
01-Jul-07 101 GPF 150
01-Jul-07 102 GPF.ADV 100
01-Jul-07 103 GPF 250
01-Jul-07 103 GPF.ADV 200
01-jul-07 104 GPF 300
I want to show results like this using a single SQL query:
Date EmpID GPF GPF.ADV
-----------------------------------------------------------
01-Jul-07 101 150 0
01-Jul-07 102 0 100
01-Jul-07 103 250 200
01-Jul-07 104 300 0
I tried:
select PaySlipDate,EmpID,
case ADCode when 'GPF' then ADAmount else 0 end GPF,
case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV'
from EmpSalaryRecord
It is showing like this:
Date EmpID GPF GPF.ADV
-----------------------------------------
01-Jul-07 101 0 0
01-Jul-07 101 150 0
01-Jul-07 102 0 100
01-Jul-07 103 0 0
01-Jul-07 103 250 0
01-Jul-07 103 0 200
It is showing multiple records of each employee for each date. First a record with GPF and GPF.ADV both zero and then records with values. I want a single record for each date and employee.
View 7 Replies
View Related
Jan 9, 2006
How to retrieve only nth row as a result of execution of one query?
For example:
Lte the table be:
SNo SudentName Marks
001 Ashok kumar 67
002
Anderson
70
003 Alfred
60
004 Ameeruddin
65
005 Kalyan
Kumar.
69
Now the Query is: How is the 3rd ranker. The answer must be Ashok kumar.
How to write this query in SQL Server.
View 4 Replies
View Related
Jan 13, 2012
I want to join two tables of information together. I want to bring the order information together with the order charge. But since the OrderCharges table can have multiple values, I just want to retrieve the sum of those charges for a specific OrderID+ItemID.
My data looks something like this:
CREATE TABLE #OrderItems
(
OrderItemsID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
ItemID INT
)
SET IDENTITY_INSERT #OrderItems ON
[Code] ....
So I'm looking to see a result set that would like this this:
OrderItemsID-----ItemID-----OrderID----OrderDescription--OrderCharge
----1------------124---------1------------Shipping----------6.55
----2------------156---------1------------Shipping---------16.85
----3------------156---------2------------Shipping----------7.40
----4------------158---------1------------Shipping----------7.85
----5------------158---------2------------Shipping---------15.25
View 2 Replies
View Related
Jun 7, 2008
I am new to SQL, still learning.
If I want to retreive unique records for e.g. VendorName,City and State however no 2 vendors must be of the same city or state.
How can I do this?
I have used Select and distinct but that still brings unique vendornames in the same state and city. I only need one vendor per city and State.
Help!
View 4 Replies
View Related
Oct 24, 2007
how do you write a query to retrieve an attribute value from xml?
example xml:
<PassportResponse><Passport><RequestID>200409201037256748130032433</RequestID><Status>COMPLETE</Status><CustId>33625</CustId><UserId>00000000000000000000</UserId><RequestTimeStamp>9/20/2004 10:37:25 AM</RequestTimeStamp><CompletedTimeStamp>9/20/2004 10:37:26 AM</CompletedTimeStamp></Passport><Errors /><Reports><Report product="DetailPPC"><ProductCode>LOCNF110</ProductCode><Status>COMPLETE</Status><ReportName>PPC DETAILED NETWORK</ReportName><CriticalResult /><OrderTimeStamp>9/20/2004 10:37:26 AM</OrderTimeStamp><CompletedTimeStamp>9/20/2004 10:37:26 AM</CompletedTimeStamp><RiskAddress /><ADDRESS><ScrubberCode> C1 </ScrubberCode><AddressChanged> Y </AddressChanged><HomeStreetNum>339</HomeStreetNum><HomePreDir /><HomeStreetName>ALL PEAT BEDS</HomeStreetName><HomePostDir /><HomeStreetType>RD</HomeStreetType><HomeCity>EARLTON</HomeCity><HomeState>NY</HomeState><HomeZip>12058</HomeZip><HomeZip4>2707</HomeZip4><HomeCountry>USA</HomeCountry></ADDRESS><PERSONAL><PPC><PRODHEAD><ProductName>PPC</ProductName><PPCErrorCode>L000</PPCErrorCode><PPCErrorMsg>success</PPCErrorMsg><PPCReturnSrc>ADDRESS</PPCReturnSrc><PPCRecordCnt>1</PPCRecordCnt></PRODHEAD><PRODBODY><RECORDNUMBER><PPCVal>7/9 </PPCVal><FireDistrName>COXSACKIE FPD NO 2 </FireDistrName><StateSpecificCode /><FireSubscribeDistrCode /><PPCCountyFIPS>039</PPCCountyFIPS><PPCCountyName>GREENE </PPCCountyName></RECORDNUMBER></PRODBODY></PPC></PERSONAL><COMMERCIAL><PPC><PRODHEAD><ProductName>PPC</ProductName><PPCErrorCode>L000</PPCErrorCode><PPCErrorMsg>success</PPCErrorMsg><PPCReturnSrc>ADDRESS</PPCReturnSrc><PPCRecordCnt>1</PPCRecordCnt></PRODHEAD><PRODBODY><RECORDNUMBER><PPCVal>7/9 </PPCVal><FireDistrName>COXSACKIE FPD NO 2 </FireDistrName><StateSpecificCode /><FireSubscribeDistrCode /><PPCCountyFIPS>039</PPCCountyFIPS><PPCCountyName>GREENE </PPCCountyName></RECORDNUMBER></PRODBODY></PPC></COMMERCIAL></Report></Reports></PassportResponse>'
I want to get back a table of all products. Example
Report product="DetailPPC">
want to return DetailPPC the xml may contain multiple reports I want a table with all the report products:
SELECT PassportResponse.Reports.query('.') as result FROM
@xml.nodes('/PassportResponse/Reports') PassportResponse(reports)
the sql will return the reports but I want to get what the product = only.. I'm hoping to do this without a substring function.
any help would be appreciated.
thanks
View 1 Replies
View Related
May 28, 2015
The below is my query in SQL SERVER 2012
DECLARE
@PN_INC INT,
@POSITION_DESC VARCHAR(15)
SELECT @PN_INC= MAX(PositionNumberInc) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
SELECT @PN_INC is returning null when there is no record in the table;
1. how can I make it return 0 when @PN_INC is null else it should pick the MAX(PositionNumberInc) value
2. I have written the below query to return 0 when @PN_INC is null but I need query to select the MAX(PositionNumberInc) value when @PN_INC is not null
SELECT @PN_INC= coalesce(MAX(PositionNumberInc), 0) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
View 6 Replies
View Related
Aug 13, 2007
Hi,
I want to insert line feed. I have used char(10) and char(13) function. But I could not insert line feed. Ex: set error1= 'error description' + char(10)+ 'Employee'
Output must be: error description Employee
Any suggestions are welcome.
View 8 Replies
View Related
Jul 20, 2005
How do I detect with a query which database is off-line.And how do I detect with a query the recovery model of a database.ByeArno de Jong
View 2 Replies
View Related
Jun 24, 2004
Hi, does anyone know how to retrieve mistmatched records across 2 tables.
To clarify, I have table A with 1175 records and Table B with 894 records.
The records from table A match exactly some the records in table B.
I want to create another table with the extra 281 records from Table A which does not match that of Table B.
I have tried the query with Select where fields_1.a<>fields_2.b AND fields_2.a<>fields_2.b etc
but that doesn't seem to work.
*desparate*
View 2 Replies
View Related
Oct 31, 2006
Hi,
I need help to build a query that shows me how many columns inside a range on columns are null.
Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Which columns are null?
Thanks in advance
View 6 Replies
View Related
May 18, 2012
I really need creating a query that will retreive all records from a table where the dbo.CorpAdv.AcctNum field equals a specific value (for this example "0023"), the TranCode = "R" and the sum of the records, starting with the latest, equals the value of a field in another table (dbo.Master.TotalAdv)
dbo.Master.TotalAdv is numeric (dollar amount) and in this example the value is $1,850.00
dbo.CorpAdv.pID is an integer and unique ID for each record, later records have higher numbers
dbo.CorpAdv.AcctNum is text field
dbo.CorpAdv.AdvAmt is numeric (dollar amounts)
[code]....
View 3 Replies
View Related
Dec 16, 2013
I need a query which retrives the firstnames of the person where last name is john. I am considering the performance as the main factor.
Is there any better query than the following?
Select firstname from tablename where lastname='John'
Considering performance as the major factor....
View 2 Replies
View Related
Mar 24, 2015
I need to have an automated process to generate and send out csv data on a set schedule. Using the sp_send_dbmail routine is working great except for one small problem I am having trouble getting around.
I am specifying a query to be run and including the results as a file attachment. The customer wants a csv file with column names.
The attachment always includes a 'separator line' of dashes between the column header and the actual data. Is there any way to eliminate this line of dashes? When I use @query_result_header = 0 flag, it removes the line of dashes, but the column names are also removed as well.
I want to keep the column names, but just remove the line of dashes.
View 1 Replies
View Related
Feb 27, 2007
Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
View 1 Replies
View Related
May 4, 2012
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
View 5 Replies
View Related
Feb 3, 2005
Having two tables.
1. ID Year
----------
1 1990
2 1991
3 1992
2. Name Year
--------------
ABC 1
XYZ 2
123 3
By passing year as an argument, Want to retrieve all the Names that does not have records for that particular year.
For example, if pass 1990, then the result set should be 'XYZ' and '123'
Thanks in advance
View 2 Replies
View Related
May 14, 2015
I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?
View 9 Replies
View Related
Aug 6, 2015
I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
SELECT
s.facilityid,
s.sessionid,
s.MRN,
[code]....
View 2 Replies
View Related
May 12, 2015
I know how to use Linked server query to retrieve data from two different sql on premise instances.I would like to try the same on sql server instances hosted on azure.When I connect to sql instance, I don't see ServerOBjects->Linked server. I just see Database and security.Is this possible on sql azure, if so how can we achieve it
View 4 Replies
View Related
Jul 16, 2015
I need a query to list the tables in SQL sever with respective database they belong across databases.
I can obtain list of tables from Sys.Tables / Sys.Objects Views but how do I correlate to which DB a table belongs to?
The Sys.Databases View list the databases with their respective Ids. But, the DBID is not part of Sys.Tables / Sys.Objects Views.
Which View will allow me to fetch database to table mappings?
View 5 Replies
View Related
Aug 31, 2007
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
View 4 Replies
View Related
Nov 8, 2006
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
View 3 Replies
View Related
Jun 27, 2005
I have table1 with orderID and demographic info.
Table2 with orderID and items.
I would like to have a results display like this:
OrderIDDemographicInfo Item1Item2Item3....ect
One line per order. When I do a join I displaying all items in different rows.
View 1 Replies
View Related
Mar 2, 2015
I have SQL Server 2012, and I'm trying to execute a bcp with a queryout and it is not working with line break, I will send you an example of a simple query my real case is a very long query that contains line breaks but this example behaves the same:
DECLARE @StoredProcedure AS NVARCHAR(4000) ,
@sql AS NVARCHAR(4000)
SET @StoredProcedure = N'"SELECT *
from clarity.[dbo].[ACCESS_DEP]"'
[code]...
Of course that is not the solution as I have a very long query that requires line breaks.
View 1 Replies
View Related
Sep 23, 2015
How do I execute below scripts using CMD:
Please Note: SQL server 2008r2, default instance with window Authentication.
1. Select * from Adventurewoks2008.Person.Person where Id = 5
2. Exec usp_getemployee (with parameter Manager_id = 100)
3. I have a folder named 'MyFolder' which contains 3 store procedures (usp_A, usp_B and usp_C).
How do I execute all 3 using CMD against AdventureWorks database.
View 9 Replies
View Related
