I want to fill in a field whose name is stored in a variable. This code runs, but the field is not filled in afterward. I think I'm doing something wrong:
SET @field = N'bindery'
SET @ordernum = N'SM38948M08'
UPDATE Orders
SET @field = GETDATE() + 5
WHERE ordernum = @ordernum
My problem is related to using the @field variable in the UPDATE query.
How do I fix this?
Thanks!
Brian
This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
View 1 Replies View RelatedI'm new to SQL and can't figure out how to update my table(StoreItemStatus) that contains the current status for items in eachstore (STORE_KEY, ITEM_KEY, STATUS,...).I get updated status info in a table (I'll call it NewInfo) thathas similar fields. NewInfo may contain multiple records for eachStore/Item, but I will just use the latest status. I'm not sure howto update StoreItemStatus using each record of NewInfo. Any advice isgreatly appreciatedThanks,Paul
View 14 Replies View RelatedI am trying to Insert or Update a record in MSSQL with a datetime variable which is modified using the DateAdd function.
Basically, I have a table of Coupons which need to expire 'x' days in the future. When I use GetDate() for the Issue Date, I have no problems. But then, when I use DateAdd to return a date in the future, I can not Insert or Update this result into the record.
I get various errors having to do with type mismatch or function not found, etc.
Can you see what I might be doing wrong?
Here's the code snippit:
<%
if(Recordset2.Fields.Item("SerialNo").Value <> "") then UpdateExpire__SerNo = Recordset2.Fields.Item("SerialNo").Value
if(DateAdd("d",2,Now) <> "") then UpdateExpire__XD = DateAdd("d",2,Now)
%>
<%
set UpdateExpire = Server.CreateObject("ADODB.Command")
UpdateExpire.ActiveConnection = MM_FreeVB_STRING
UpdateExpire.CommandText = "UPDATE dbo.Coupon SET ExpireDate = " + Replace(UpdateExpire__XD, "'", "''") + " WHERE SerialNo = " + Replace(UpdateExpire__SerNo, "'", "''") + ""
UpdateExpire.CommandType = 1
UpdateExpire.CommandTimeout = 0
UpdateExpire.Prepared = true
UpdateExpire.Execute()
%>
This produces this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '12'.
And it errors at the UpdateExpire.Execute() line.
The code was generated by Dreamweaver.
Thanks.
Lance
I've just finished configuring the SELECT command for the SqlDataSource in my ASP.NET 2.0 web app. It works fine and runs against a SQL Server 2005 database, using a stored procedure that I've written.
So, then I went to configure the SqlDataSource for the UPDATE and INSERT commands, and I've written two SP's for those as well. In the designer the second form of the wizard asks for the Select statement. I've already given that for the SELECT statement in the third form, and I also select the INSERT tab to specify the SP I want to use for inserting data and the UPDATE tab to specify the SP I want to use for updating data. However, there appears to be no way that I can specify what the parameters are supposed to be for anything other than the SELECT command, through the designer. Is that correct, or have I missed something?
Hi..
I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc..
IF Exists(
SELECT
*
FROM
PlanEligibility
WHERE
PlanId = @PlanId
) BEGIN
UPDATE
PlanEligibility
SET
LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,
EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End,
EntryDates = @EntryDates,
EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM,
LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END,
OtherEmployeeExclusions = @OtherEmployeeExclusions
WHERE
PlanId = @PlanId
END
ELSE BEGIN
INSERT INTO PlanEligibility
(
PlanId,
LengthOfService,
EligibilityAge,
EntryDates,
EligiDifferentRequirementsMatch,
LengthOfServiceMatch,
EligibilityAgeMatch,
OtherEmployeeExclusions
)
VALUES
(
@PlanId,
Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,--@rsLengthOfService,
CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge,
@EntryDates,
Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here
CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch,
@OtherEmployeeExclusions
)
END
Any help will be appreciated..
Regards,
Karen
I have a table adapter query with a like clause that I can't get to work. The field is "Type", so I have "LIKE '%@Type%'". When I click the Execute Query button to test, not only does nothing get returned, I don't get the chance to enter the parameter. If I change LIKE '%@Type%' to say, LIKE '%book%', the appropriate records are returned. I actually need to check two parameters. If I ad the second parameter, the where clause becomes(Type LIKE '%@Type%') AND (SendState = @SendState)When I test the query, a screen pops up to let me enter the state, but not the Type. I can't see anything wrong with the query, but something must be. Diane
View 4 Replies View RelatedI am having some trouble using a sub query. I want to use the red part as a sub query because I have to alter some values based on the NcodeM that gets assigned to each record. As a stand alone query the red part works well. When I run the whole thing I get error message:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ')'.
Select SaleYear, SaleMonth, vin10, NewUsed, VehicleYear,
VehicleMake, VehicleModel, VehicleTrim, AlgCode, CashDown, AppZip, ActualSalePrice,
TradeMake, TradeModel,TradeYear, TradeNcodeL, TradeNcodeM, OwingOnTrade, TradeAllowance, NetTradeIn
From D
(select left(consulting.dbo.vw_dds.date,4) AS SaleYear, left((right(consulting.dbo.vw_dds.date,4)),2)
AS SaleMonth,
vin10, NewUsed, VehicleYear, VehicleMake, VehicleModel, VehicleTrim, AlgCode, CashDown,
AppZip, (CashPrice-Rebate-TaxesIncludedInCashPrice) AS ActualSalePrice,
ltrim(consulting.dbo.vw_dds.trademake) AS TradeMake, ltrim(trademodel)TradeModel,
TradeYear, NcodeL AS TradeNcodeL, NcodeM AS TradeNcodeM, OwingOnTrade, TradeAllowance, NetTradeIn
from consulting.dbo.vw_dds
inner join (select distinct make, model, ncodel, ncodem, modelyear from us.dbo.algmaster) Code
on code.make = ltrim(consulting.dbo.vw_dds.trademake) and code.model= ltrim(trademodel) and
code.modelyear = tradeyear) D
Any suggestions would be greatly appreciated.
Thanks,
Tasha
I am having trouble figuring this one out.
I have a table named studentgrades
In it, it contains studentid, teacherid, classgrade
If I do this code it gives me the count of student that made an "A" grouped by teacherid
select
teacherid,
count(grade) as GradeACount
where
classgrade between 90 and 100
from
studentgrades
group by
teacherid
ok this seems simple enough...if I manilpulate the where clause I can get "B","C" and "D" student counts.
My question is how can i get a dataset to return
teacherid,GradeACount,GradeBCount,GradeCCount,GradeDCount
all in one query...I am trying to automate a report so that school districts can pull their own queries via Reporting Services.
I appreciate your time.
Alex Flores
Our Client/Contact database works like this:
CLIENTDB - This table holds the records of all Clients, Contacts, Suppliers, etc. They are defined as one type of record or another by the 'Contact_Type' field - Clients are type 'B', Contacts are 'D', etc.
Contacts are often linked to a Client, but don't have to be. These links are stored in the LINKS table.
So, what I'm trying to do, is generate a report of all Contacts that aren't linked to Clients but possibly should be because they share a company name. The SQL I've used to get this info is as follows:
SELECT *, CLIENTDB.Formal_Name as FN
FROM CLIENTDB
WHERE (CLIENTDB.Contact_Type = 'D')
AND (NOT EXISTS (SELECT * FROM CLIENTDB, LINKS JOIN CLIENTDB as CB ON CLIENTDB.Contact_ID = LINKS.Link_Record_ID))
AND (EXISTS (SELECT * FROM CLIENTDB WHERE (CLIENTDB.Contact_Type = 'B') AND (lower(CLIENTDB.Formal_Name) LIKE '$FN')))
The problem is the final AND condition - I want this to filter the results down to only those Contacts that have a Formal_Name like an existing Client, but when I add or remove this one line, it makes no difference to the output.
Any suggestions greatly appreciated
Thanks, Nick
Hi, I have created a query (using SQL 2005) that will pull the people who have spent the most on tickets purchased:
Select P.Passenger_ID, Passenger_Name, Ticket_Price
From Passenger P, Ticket_Purchase T
Where P.Passenger_ID = T.Passenger_ID
Group By P.Passenger_ID, Passenger_Name, Ticket_Price
Having Ticket_Price >= All (Select Max(Ticket_Price)
From Ticket_Purchase
Group By Ticket_Price);
Passenger_ID Passenger_Name Ticket_Price
---------------------------------------
132812298 Nice,Richard 1750.00
234890032 Franco,Sylvia 1750.00
339209841 Kim,Jongouk 1750.00
385894857 Uribe,Gloria 1750.00
(4 row(s) affected)
I now want to be able to only choose the Passenger_ID's from above who are not listed in another table called Frequent_Flier, which should leave me with only 2 records not 4.
I am wondering if I add the below to the first query to eliminate those passengers in the Frequent_Flier table:
NOT IN (Select Passenger_ID
From Frequent_Flier);
When I add it to the Where clause I get an error. Should I be sub-querying that differently or is there a better way to do this.
Thanks for any help you can offer.
Employee
EMPLOYEE_IDLAST_NAMEFIRST_NAMEMIJOB_ID MANAGER_IDHIRE_DATESALARYCOMMISIONDEPARTMENT_ID PHONE_NUMBER
Job
JOB_IDFUNCTION JOB_TYPE
Department
DEPARTMENT_IDNAMELOCATION_ID
Location
LOCATION_IDREGIONAL_GROUP
Here are the different tables in my database and Im trying to get a list of all the managers last names with the last names of the people that the manager manages. FUNCTION describes who is a manager and who has other positions. You can link the two tables together with Job_ID.
Im stuck on this, Im a noob at SQL. Please help
Hi,
I am having trouble getting my query right. i am using a stored procedure to retrieve this data and bind it to my grid view.
Problem: I can't associate a field with a column that i am returning with my record set.
Details: I have query that i want to return 9 columns (UserName, Pre-Approved, Processing, Underwriting, Conditioned, Approved, Docs Out, Docs Back, Conditions Met). The username column lists all loan agents. I want the other 8 columns to list the name of the borrower (crestline..borrower.lastname) that is associate with that loan agent and that loan state. Each time a record is found where there is a valid loan agent (UserName) that meets the 'where' conditions, there will be a borrower. the 'LoanKey' field is a primary key that lets me get the borrower name from the borrower table. I dont know how to construct my query such that those borrower names get associated with their respective column header.
if the query works, my Gridview should look like this ('Name' refers to the borrower name)
UserName | Pre-Approved | Processing | UnderWriting | Conditioned | Approved | Docs Out | Docs Back | Conditions Met
Bob | | | | Name | | | |
Bob | | Name | | | | | |
Bob | | | | | | | Name |
Steve | | | Name | | | | |
Steve | | | | | | Name | |
Here is my sql call:
SELECT cfcdb..users.username, crestline..borrower.lastname,CASE WHEN crestline..loansp.LoanStatus='Pre-Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Pre-Approved',CASE WHEN crestline..loansp.LoanStatus='Processing' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Processing',CASE WHEN crestline..loansp.LoanStatus='Underwriting' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Underwriting',CASE WHEN crestline..loansp.LoanStatus='Conditioned' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditioned',CASE WHEN crestline..loansp.LoanStatus='Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Approved',CASE WHEN crestline..loansp.LoanStatus='Docs Out' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Out',CASE WHEN crestline..loansp.LoanStatus='Docs Back' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Back',CASE WHEN crestline..loansp.LoanStatus='Conditions Met' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditions Met'FROM cfcdb..usersinner join (crestline..loansp inner join crestline..borrower on crestline..loansp.loankey = crestline..borrower.loankey)on crestline..loansp.fstnamelo=cfcdb..users.firstname AND crestline..loansp.lstnamelo=cfcdb..users.lastnameinner join cfcdb..users_roleson cfcdb..users.username = cfcdb..users_roles.usernamewhere cfcdb..users.active = 1 AND cfcdb..users_roles.groupid = 'agent'AND crestline..loansp.enloanstat <> 'Closed' AND crestline..loansp.enloanstat <> 'Cancelled' AND crestline..loansp.enloanstat <> 'Declined' AND crestline..loansp.enloanstat <> 'On Hold'order by cfcdb..users.username asc
HI, i am trying to make query that has computations with it. but when there's a point computing between int and float i had to use cast() function to convert certain data types. somehow it only works on converting float to integer because when i'm converting an integer into float inorder to be computed with a float it bombs. my query is like this ....
SELECT cast(((cast(((lat - (SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * 69.1) AS int) ^ 2) + (cast((69.1 * (lng - (SELECT Lng FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * (COS((SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210) / 57.3))) AS int) ^ 2)) AS float) ^ .5
FROM TPS_ZIPUSA
.5 is where the query bombs. any idea why is this happenning?
by the way, i'm using sql server 7.0.
francis,
Given the following tables:
[Members]
-memberID (PK)
-memberName
[Questions]
-questionID (PK)
-questionText
[Surveys]
-surveyID (PK)
-surveyName
-surveyDescription
-surveyType (FK)
[SurveyQuestions]
-surveyID (PK/FK)
-questionID (PK/FK)
[SurveyQuestionMemberResponse]
-surveyID (PK/FK)
-memberID (PK/FK)
-questionID (PK/FK)
-yesResponse(bit)
-noResponse(bit)
-undecidedResponse(bit)
How can I write a query to return the results for a given survey for all members (including members who have not given responses) given the surveyID.
In the [SurveyQuestionMemberReponse] table I record survey results for any members who have answered the survey. However, if a member has not responsed to the survey they will not have a record in this table.
I want to return a list of members with their response to each question in the survey. If a member has not given a response I would like to indicate they have not responded to the survey and they should still appear in the list.
When I attempt to write a query to UNION the results of a query aimed at gathering all of the results in the [SurveyQuestionMemberReponse] to all of the people in the [Members] table I recieve an error when I include the questionText field in my result set.
The error indicates:
The text data type cannot be selected as DISTINCT because it is not comparable.
Can someone please point me in the right direction. I suspect I am going about this all wrong.
[NOTE] The 'surveyType' in the [Surveys] table indicates which subset of members a given Survey should be available to. For this example let's just assume that every survey should belong to all members.
Thanks,
Zoop
I'm working with a database that is poorly normalized and am doing my best to increase the speed of our queries (changing the database itself is not an option right now). Any help with the following situation would be very much appreciated.
There are two tables involved, described below.
DataTable:
Code Snippet
CodeA char(4),
CodeB char(4),
CodeC char(2),
ColA float,
ColB float,
ColC float,
...
ColZ float
NameTable:
Code Snippet
Abbr char(2),
FullName varchar(50)
In the DataTable, the columns named ColA, ColB, ColC, etc., are described in the NameTable where Abbr would be A, B, C, etc., and FullName would be a "nice" text version describing what's stored in that column. For instance, a record in the NameTable might include Abbr = 'A' and FullName = 'Computer Science', and that means that ColA in the DataTable refers to 'ComputerScience'.
The table I'm trying to optimize the creation of in our ASP.NET application needs to have column headings that could be retrieved like this:
SELECT DISTINCT CodeB, CodeC FROM DataTable WHERE CodeA = @CodeA
(Note that CodeC is really just a different representation of CodeB--think 4-digit year vs. 2-digit year--for any given CodeA.)
The row headings for the table could be retrieved like this:
SELECT FullName FROM NameTable
The cells of the table at any given intersection of a row heading and a column heading can be retrieved like this:
SELECT Col? FROM DataTable WHERE CodeA = @CodeA AND CodeB = @CodeB (this selects a single value)
Where the ? of Col? is determined by the Abbr version of FullName shown in the row heading, and @CodeB is determined by CodeB from the column heading.
Right now this table is generated using a lot of loops in the code of the page that require it to query the database for nearly every cell in the table. Even with this poor database design, there has to be a better way than that. I'm hoping there is a way to create a stored procedure that uses temporary tables and joins, or views, or something like that to create the table on the SQL Server and just send the data to the ASP.NET page in a form that can be directly databound to a GridView.
Thanks in advance for any help!
-Mathminded
Gary writes "I'm imported the query from Access (where it worked perfectly) to SQL View where the "Iif" statements caused an issue.
Here's a SMALL portion of the original:
SELECT tblTransactionBuyer.TransBuyerID,
tblDefaults.CompID,
([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA,
[TransBuyerPropSalePrice]*
(IIf([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],
IIf([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA])))/100 AS VAFunding, ......
Below, are my changes so far, but I'm stuck.
SELECT tblTransactionBuyer.TransBuyerID,
tblDefaults.CompID,
([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA,
[TransBuyerPropSalePrice]*
If([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],
If([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA]/100)) AS VAFunding, ........
It appears that I may not be able to put an IF into a SQL SELECT. If not, how can I get the same effect ?
Thanks. Gary
Listed Below is the Full Access Query:
SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, tblTransactionBuyer.TransBuyerEntryDate, qryUserAlphaList.UserName, qryUserAlphaList.UserAreaCode, qryUserAlphaList.UserPhone, tblTransactionBuyer.TransBuyerName, tblTransactionBuyer.TransBuyerVaEligible, tblTransactionBuyer.TransBuyerPropAddress, tblTransactionBuyer.TransBuyerPropCity, tblTransactionBuyer.TransBuyerPropSalePrice, ([TransBuyerPropSalePrice]*([DefBasePercentFHA])/100) AS LoanBaseFHA, ([TransBuyerPropSalePrice]*([DefBasePercentCONV])/100) AS LoanBaseCONV, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA, ([TransBuyerPropSalePrice]*([DefBasePercentJumbo])/100) AS LoanBaseJumbo, [LoanBaseFHA]*[DefMipFHA]/100 AS MipFHA, [LoanBaseCONV]*[DefMipCONV]/100 AS MipCONV, [LoanBaseJumbo]*[DefMipJumbo]/100 AS MipJumbo, [TransBuyerPropSalePrice]*(IIf([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],IIf([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA])))/100 AS VAFunding, [LoanBaseFHA]-[DownOverMinFHA]+[MipFHA] AS LoanTotalFHA, [LoanBaseCONV]-[DownOverMinCONV]+[MipCONV] AS LoanTotalCONV, [LoanBaseJumbo]+[MipJumbo]-[DownPaymentJumbo] AS LoanTotalJumbo, [LoanBaseVA]+[VAFunding]-[DownPaymentVA] AS LoanTotalVA, [TransBuyerPropSalePrice]*([DefMinDownPayFHA]/100) AS MinDownFHA, [TransBuyerPropSalePrice]*[DefMinDownPayCONV]/100 AS MinDownCONV, [TransBuyerPropSalePrice]*[DefMinDownPayVA]/100 AS MinDownVA, [TransBuyerPropSalePrice]*[DefMinDownPayJumbo]/100 AS MinDownJumbo, tblTransactionBuyer.TransBuyerIntRate, tblTransactionBuyer.TransBuyerLoanYears, tblTransactionBuyer.TransBuyerHazardIns, tblTransactionBuyer.TransBuyerFloodIns, tblTransactionBuyer.TransBuyerClosingDate, IIf(([TransBuyerConvDownPayPercent]/100)*[TransBuyerPropSalePrice]>[TransBuyerConvDownPayCash],([TransBuyerConvDownPayPercent]/100)*[TransBuyerPropSalePrice],[TransBuyerConvDownPayCash]) AS ConvDownPay, tblDefaults.DefBuyAppraisal, tblDefaults.DefBuyCreditReport, tblDefaults.DefBuyJumboUnderwriting, tblDefaults.DefBuyVAUnderwriting, tblDefaults.DefBuyFHAUnderwriting, tblDefaults.DefBuyCONVUnderwriting, tblDefaults.DefBuyJumboTaxService, tblDefaults.DefBuyVATaxService, tblDefaults.DefBuyFHATaxService, tblDefaults.DefBuyCONVTaxService, tblDefaults.DefBuyFloodCert, tblDefaults.DefBuyAbstractTitle, tblDefaults.DefBuyMiscFedex, tblDefaults.DefBuyRecording, tblDefaults.DefBuySurvey, tblDefaults.DefBuyAttorney, tblDefaults.DefBasePercentFHA, tblDefaults.DefBasePercentCONV, tblDefaults.DefBasePercentJumbo, tblDefaults.DefBasePercentVA, [TransBuyerPropSalePrice]*[DefPropTaxRate] AS PropTax, [LoanBaseFHA]*[DefBuyOrigFHA]/100 AS OrigFHA, [LoanBaseCONV]*[DefBuyOrigCONV]/100 AS OrigCONV, [LoanBaseVA]*[DefBuyOrigVA]/100 AS OrigVA, [LoanBaseJumbo]*[DefBuyOrigJumbo]/100 AS OrigJumbo, [DefTitleInsDollar]*([TransBuyerPropSalePrice]/[DefTitleInsPerX]) AS TitleInsurance, [OrigFHA]+[DefBuyAppraisal
Please help.
Has anyone seen a problem querying Excel or Access database when the "LIKE" clause is used with the "*" wildcard? The problem I'm seeing is that the query fails to return a dataset whenever "*" wildcard is used.
For Example,
sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" 'query WORKS
sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA*'" 'query does NOT WORK
Assume that Table name is "EmployeeData", with "ID", "Name" and "Birthdate" as Fields.
Data:
ID Name Birthdate
AAA
Aaron
5/4/1975
CCC
Charlie
10/14/1948
DDD
Deloris
7/19/1998
The code I use is listed as follows:
Imports System.Data.OleDb
Imports System.Data
Public Class Form1
Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:ExcelData1.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Use a DataSet to Query data from the EmployeeData table.
Dim QryConn As New OleDbConnection(m_sConn1)
'Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" '- works
Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AA*'" ' does not work
Dim da As New OleDbDataAdapter(strQry, QryConn)
Dim ds As DataSet = New DataSet
da.Fill(ds)
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows 'Show results in output window
Debug.WriteLine(dr("Id") & ", " & dr("Name") & ", " & dr("Birthdate"))
'Expected output:
'AAA
Aaron
5/4/1975
Next
QryConn.Close()
End Sub
End Class
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies View RelatedI get this error when I look at the state of my SQLresults object. Have I coded something wrong?Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. conn.Open()
Dim strSql As String
strSql = "INSERT INTO contacts (companyId, sourceId, firstName, lastName, middleName, birthday, dateCreated)" _
& "VALUES ('" & companyId & "', '" & sourceId & "', '" & firstName & "', '" & lastName & "', '" & middleName & "', '" & birthday & "', '" & now & "') SELECT @@IDENTITY AS 'contactId'"
Dim objCmd As SqlCommand
objCmd = New SqlCommand(strSql, conn)
Dim aSyncResult As IAsyncResult = objCmd.BeginExecuteReader()
If aSyncResult.AsyncWaitHandle.WaitOne() = True Then
Dim sqlResults As SqlClient.SqlDataReader
sqlResults = objCmd.EndExecuteReader(aSyncResult)
Dim cid As Integer
cid = sqlResults.Item("contactId")
Me.id = cid
conn.Close()
Return cid
Else
Return "failed"
End If
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedCan I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies View RelatedI have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
Dave
This is my first website done in ASP.NET and SQL Server 2005. I haven't ran into any major problems, until I tried to run an update on some rows. I've searched everywhere online, only to be stuck at the end of the day. I will post the code I have for the button that triggers it. I'll be crossing my fingers! int eid = Int32.Parse(Request.QueryString["id"]);
string updateSQL = "UPDATE Events SET event_title = @event_title, event_date = @event_date, ";
updateSQL += "event_time = @event_time, event_location = @event_location, event_description = @event_description WHERE (eid = @eid)";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
cmd.CommandType = CommandType.Text;
// Add the parameters.
cmd.Parameters.AddWithValue("@event_title", txtEditTitle.Text);
cmd.Parameters.AddWithValue("@event_date", txtEditDate.Text);
cmd.Parameters.AddWithValue("@event_time", txtEditTime.Text);
cmd.Parameters.AddWithValue("@event_location", txtEditLocation.Text);
cmd.Parameters.AddWithValue("@event_description", txtEditDescription.Text);
cmd.Parameters.AddWithValue("@eid", eid);
// Try to open database and execute the update.
try
{
con.Open();
int updated = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
con.Close();
Response.Redirect("./?msg=eus");
}
Hi everyone!I have a update query which all looks good and it looks like it executes, though it does not effect the database for some unknown reason. (No error messages).protected void Button1_Click(object sender, EventArgs e)
{
int areaId = 0;
if (Request.QueryString["doc_area_id"] != null)
{
areaId = Convert.ToInt32(Request.QueryString["doc_area_id"]);
}
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CPS_docshareConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE document_area SET doc_area_name = '" + AreaText.Text + "' WHERE doc_area_id = @areaId", myConnection);
command.Parameters.Add(new SqlParameter("@areaId", areaId));
myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
} Thanks for any help you can give, cheers, Mark.
I am using the update query listed below in my DetailsView. The issue i am having is the ManagerDecision value which gets populated in a DropDownList in EditTemplate, doesnt seem to update correctly. If I choose 'Approved' from the DropDownList, the result for Status_Type ends up being 'Closed' and not 'Open'. Could someone please Help.
Thanks!!
UPDATE ServiceRequest_Table SET ManagerDecision = @ManagerDecision, Comments = @Comments, Priority_Name = @Priority_Name, Status_Type = CASE WHEN (ManagerDecision = 'Approved') THEN 'Open' ELSE 'Closed' END, ManagerDateTime = CASE WHEN (Status_Type = 'Closed') THEN '' END, Closed_Date = CASE WHEN (Status_Type = 'Open') THEN '' END WHERE (ServiceRequest_ID = @ServiceRequest_ID)
OK, so I have this page where a user can edit multiple fields in a database record. My problem is, that once changes have been made and the user hits the "save" button, everything SEEMS to run just fine (no errors, other things that should happen when the button is clicked happen normally), but nothing is changed. It's almost like the update function (which was created with Microsoft ASP.NET Web Matrix) is working, but it's being fed the old values for the page, rather than the updated ones.
Any ideas on what might be causing this? It's not like anything I've seen before.
Thanks
Kaiti
I gave a storedprocedure for gatting data into gridview,just go through that.
Select Ind.Industry_Name,Com.Company_Address from Pcra_Industry Ind join Pcra_Company Com on Ind.Ind_ID_PK=Com.Ind_ID_FK Here iam getting two fields from two different table depending on the Id.
So how to write a Query to change the fields in the different tables.
please Help me...
Im having a problem when using my update statement on button click. I want the field to be updated to be true whenever the button was clicked but it doesn't seem to like my current syntax. Can someone point me in the right direction please? I know its really simple but when you have just been looking at something for so long you can never see it!!! Thanks
SqlCommand myCommand = new SqlCommand("Update Notice SET Resolute = '" + Resolute + "', DateClosed= '" + DateClosed + "', Resolved = 'True' WHERE NoticeID = " + NoticeID + "", myConnection);
Hi Guys
I have a problem with UPDATE query. Here is my Select query.
SELECT d.EID as EID,d.EName as EName,d.EDOB as EDOB,d.EDesignation as EDesignation,d.ESal as ESal,EI .EPhone as EPhone,EI .EEnddate as EndDate FROM Data_EMP d Join Emp_Info EI on d.EID=EI.EIDWhere d.EID ='1001'
According to this query I have to update another query.This is my original Update Query.
"Update Data_EMP set EStatus=0 where EID='" & TempEID & "'"
Now i wrote update query like this
UPDATE d.EID as EID,d.EName as EName,d.EDOB as EDOB,d.EDesignation as EDesignation,d.ESal as ESal,EI .EPhone as EPhone,EI .EEnddate as EndDate FROM Data_EMP d Join Emp_Info EI on d.EID=EI.EIDSet set EStatus=0 where EID='" & TempEID & "'"
Shall i write Update query like this and Is there any issues in this update query can any one let me know?
Thanks