Convoluted Query Trouble
Nov 16, 2006
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
View 1 Replies
ADVERTISEMENT
Aug 26, 2007
Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem. I'm relatively new to SQL, so this may not be so tough for some of you. Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him. The list is presented to him via a datalist. Each item of the datalist contains a table of 2 columns and 1 row as pictured below. The first box contains the user photo and user name of the person who SENT him the message (not the logged in user). The second box contains the subject title of the message.
FROM | SUBJECT | User Photo | || | Subject || User Name | |
Here is the list of the relevant 4 tables of my database and the relevant fields.....
aspnet_Users tableUserId (used to link to Member table)UserName
Member tablememberId (int - primary key)UserId (guid - used to link to aspnet_Users table)primaryPhotoId (int - used to link to Photo table)
Photo tablephotoId (int - primary key)photoUrl (string - path to file on local drive)
Message tablemessageId (int - primary key)fromMember (int - connects with memberId from Member table)toMember (int - connects with memberId from Member table)subject (varchar(max))
So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user. The senders will be determined by the Member.fromMember fields. Intuitive enough so far, I guess. This is the SQL statement I have so far.....
SELECT aspnet_Users.UserName, Message.subjectFROM aspnet_Users, Member, MessageWHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember
Note that I'm grabbing the logged in user info from Profile.UserName. So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user. HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)? Do you see the core of my dilemna here? I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER). Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender.
And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ?? I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe. The SQL statement so far asks to retrieve information based on the logged in user. But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained..... and still hang on to the results I have up until now? And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement. This is indeed very very complicated for me lol.
This problem has been giving me migraines this whole weekend. Has anyone been through such a problem before? Any help would be greatly appreciated - thanks in advance.
View 22 Replies
View Related
Mar 2, 2007
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 Related
Jun 10, 2008
I 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
View 2 Replies
View Related
Nov 8, 2007
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
View 5 Replies
View Related
May 28, 2008
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.
View 4 Replies
View Related
Nov 13, 2006
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
View 2 Replies
View Related
Feb 3, 2008
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
View 8 Replies
View Related
Mar 20, 2007
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
View 2 Replies
View Related
Feb 25, 2001
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,
View 2 Replies
View Related
Nov 26, 2007
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
View 3 Replies
View Related
Aug 21, 2007
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
View 1 Replies
View Related
Feb 16, 2006
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
View 1 Replies
View Related
Apr 13, 2006
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
View 1 Replies
View Related
Aug 31, 2007
I 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
View 3 Replies
View Related
Jan 9, 2008
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 Related
Feb 16, 2004
I just got finished developing the company intranet site and thinking that everything was working I boasted about how good it was by getting my boss to login and submit a new job to the db (new job, its a work management app) while i did the same, the pland was to hit the submit button at the same time. He would send one to be read by me and I would send one to be read by him. We both hit submit and the following happened.
The db has somehow fused the two into one. I thought maybe we were to accurate in hitting the submit button together. But I even gave a five second delay between and for some reason the job is being overriden by one user tor the other. In other words we are both sharing the same jobid. I thought this could never happen with sql server supposing that it would lock one request until another was completed and vice versa. But I'm so new to this that I'm just so naieve to think that the db would do this for you. Problem is I'm about to move on and I can't leave the app in this state. Can anyone point to some articles or give some suggestions has to my situation. Most desperately in need!!
Thanks in advance
View 11 Replies
View Related
May 20, 2006
hello
i'm having touble getting my sp done. the problem is as follow..i've found an sp tokenize (which works fine) with the following signature:
CREATE PROCEDURE TOKENIZE (
S VARCHAR(10000),
DELIM CHAR(1))
RETURNS (
ID INTEGER,
TKN VARCHAR(10000))
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE LEN INTEGER;
DECLARE VARIABLE FIRSTCHAR CHAR(1);
DECLARE VARIABLE S2 VARCHAR(10000);
begin
...
SUSPEND;
end^
then i build myself another sp (which also works fine) with this signature:
CREATE PROCEDURE GET_DICENTRIES_BY_ASDSKRPT (
ASDSKRPTINPUT VARCHAR(15))
RETURNS (
ID BIGINT)
AS
begin
...
suspend;
end^
now, what i'm trying to do is write another sp (get_dicentries_by_all_asdskrpts) that accepts a '.'-tokenized string as a parameter (e.g. 'bla.bli.blo.blu'; number of tokens NOT fixed at 4!) and returns the intersection of
GET_DICENTRIES_BY_ASDSKRPT('bla'), GET_DICENTRIES_BY_ASDSKRPT('bli'), GET_DICENTRIES_BY_ASDSKRPT('blo') AND GET_DICENTRIES_BY_ASDSKRPT('blu')
does any of you have an idea how to go about?
thanx,
martin
View 1 Replies
View Related
Jul 23, 2005
Could someone help me get the following SQL statement working?SELECTstandardgame.gamename,standardgame.rowteamname,standardgame.colteamname,standardgame.dollarvalue,standardgame.gameid,standardgame.cutoffdatetime,standardgame.gametype,standardgame.gameowner,(100-COUNT(purchasedsquares.gameid)) AS squaresremainingFROM standardgameLEFT OUTER JOINpurchasedsquares ON standardgame.gameid = purchasedsquares.gameidwhere gametype='$gametype' and dollarvalue = '$dollarvalue' andgameowner = 'GROUP BY standardgame.gameidorder byCASE squaresremaining WHEN 0 THEN 1 ELSE 0 END ASC,squaresremaining ASCThe problem is... MySQL doesn't seem to want to let me usesquaresremaining in that case statement since it's not an officialcolumn name. Any idea how I can reference squaresremaining in the casestatement?
View 2 Replies
View Related
Mar 13, 2007
I have a smalldatetime field in SQL.
For the query of my report, I need any transaction that is like 09/2006 (matching the month and year).
So I wrote something like this:
AND (DATE LIKE '%2006%')
That correctly returns all of the 2006 transactions.
Now why won't this work:
AND (DATE LIKE '%2006-09%')
Or how about this:
AND (DATE LIKE '%09%2006%')
What is the correct syntax??
View 3 Replies
View Related
Nov 2, 2007
am an ASP.net developer and i've stucked in a C# windows application ... and i am the linking part ... between the Database and my application ... in ASP i have a wizard that handles getting the data from the controls ... but is it the same in C# windows application ? am using VS2005 TeamSystem... and widows Vista(framework 3.0) .... and this query is getting executed and return 1 ... and the intellecence is telling me to insert 4 strings
int rowseffected = loginNamesTableAdapter.InsertQuery(textBox1.Text.ToString(), textBox2.Text.ToString(), textBox3.Text.ToString(), textBox4.Text.ToString());
MessageBox.Show(rowseffected.ToString());
i don't know how to link the parameters with the controls ... please show me how ... thx... please help with images ... as i previously mentioned... this is a new experment in C# thx.
__________________
Ahmed Reda
View 1 Replies
View Related
Jul 24, 2006
Using Vs2005 sqlServer 2005 When i try to connect i get this error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Connection string: <connectionStrings> <add name="pubsConnectionString1" connectionString="Data Source=.;Initial Catalog=pubs;User=mk;pw=x" providerName="System.Data.SqlClient" /> </connectionStrings> Calling code : try { using (SqlConnection cn = new SqlConnection(WebConfigurationManager.ConnectionStrings[0].ConnectionString)) { cn.Open(); } } catch (Exception ex) { Response.Write(ex.Message); }
View 1 Replies
View Related
Jun 6, 2007
I am having a most frustrating time getting this working. I have two machines, one named 'REMOTE' and it contains my SQL Server (2005 developer edition). Then I have my main PC, named 'DOUGAL' The server instance on the REMOTE computer is called 'SQLSERVER' and there is only one instance. The server is showing as running etc and I can log in locally on the REMOTE computer easily via the Microsoft SQL Server Management Studio etc. I have two logins that are working fine, sa and a login called 'db_user'.Now I am trying to connect in the server explorer in visual studio. When I try and connection i get the following error; An Error has occured while establishing a connection to the server. When connectiong to SQL Server 2005 this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (provder: Named Pipes Provider, error: 40 - Count not open connection to SQL Server). I have tried connection to 'REMOTE/SQLSERVER' 'REMOTE' and with its IP address to - without any luck. So, to fix this problem I have checked that remote connections are allowed (and restarted the system). I have also checked that named pipes and tcp/ip connections are enabled. I have searched around with the aid of my best friend, Google. However, I've only found suggestions that involve trying the fixes I already tried.
View 4 Replies
View Related
Nov 6, 2007
Ok, what I am tryin to do is quite simple i do believe. Basically I have an aspx page with a textbox, a button and a datagrid linked to my sql db. What I am wanting is for a user to enter in their Social Security Number in the text box and click the button. If the SSN matches a record in the DB then it binds to the datagrid and shows it, IF no match is found then it displays a link to click on to continue onto the next page. This is to stop duplicate entries. I can get one to work by itself but not both of them at the same time. Here is my code.<%@ Page Language="VB" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><script language="VB" runat="server"> Sub btnSearch_OnClick(sender as Object, e as EventArgs) Dim objConnection As SqlConnection Dim objCommand As SqlCommand Dim objAdapter As SqlDataAdapter Dim objDataSet As DataSet Dim strSearch As String Dim strSQLQuery As String ' Get Search strSearch = txtSearch.Text ' If there's nothing to search for then don't search ' o/w build our SQL Query and execute it. If Len(Trim(strSearch)) > 0 Then OK, Here is my first IF, this works. ' Set up our connection. objConnection = New SqlConnection("Data Source=BRADBLACKBUR-PCMSSMLBIZ;" _ & "Initial Catalog=Victorypoint;Integrated Security=True;") ' Set up our SQL query text. strSQLQuery = "SELECT FirstName, LastName, SSN " _ & "FROM Applicants " _ & "WHERE SSN LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "ORDER BY SSN;" ' Create new command object passing it our SQL query ' and telling it which connection to use. objCommand = New SqlCommand(strSQLQuery, objConnection) ' Get a DataSet to bind the DataGrid to objAdapter = New SqlDataAdapter(objCommand) objDataSet = New DataSet() objAdapter.Fill(objDataSet) If objDataSet.Tables.Count < 1 Then Right here is where I am having the problem, I need to count the Rows not the Tables, but I dont know what code i should use here. Button1.Visible = True Else ' DataBind DG to DS dgSearch.DataSource = objDataSet dgSearch.DataBind() objConnection.Close() End If End If End Sub
View 2 Replies
View Related
May 5, 2008
I've tried to post this somewhere else, but I haven't gotten it figured out yet:
I'm passing variables through a URL, and the page that accepts the variables has a where clause like this:"WHERE (name1 LIKE @name1 + '%') and (property_add like @property_add + '%') and (ctlmap = @ctlmap )"On the search page where the variables are entered, the user may not know all the information that goes into each input field. (If the user just knew the name, they would search by name. If the user just knew the address, they would search by address...etc.) Everything works great before I add the ctlmap variable. When the ctlmap variable is added, no search results will turn up unless there is an entry made to the ctlmap field. I know it has to have something to do with the ctlmap being set to equals rather than like, but I can't find a way to make the search work with a null value for ctlmap. Thanks, everyone. Additional Info: <SelectParameters> <asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="name1" QueryStringField="name" Type="String" /> <asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="property_add" QueryStringField="address" /> <asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="ctlmap" QueryStringField="ctlmap" /> </SelectParameters>
Here's another type of where clause that I tried but had no luck with:
WHERE (name1 LIKE @name1 + '%') and (property_add like @property_add + '%') and (@ctlmap IS NULL or @ctlmap = ctlmap)
This is how I would try to do it in php/mysql, but I don't know how to make it work in asp.net/sql:
if ($ctlmap != "") { $whereClause = $whereClause." and ctlmp = '".strtoupper($ctlmap)."' "; } if ($whereClause != "") { $whereClause = " where ".$whereClause; }Here's the MYSQL counterpart:$sqlString = "Select distinct district, map, bill_group, ctlmap, bill_parcel, propertyid, special, property_add, name1 from MUR_bill_master".$whereClause";
Can anyone help?
View 11 Replies
View Related
Jun 9, 2004
Greetings,
I am having difficulty connecting to a SQL DB in my ASP page. Each time I run my application I receive the following error, "Login failed for user 'CX259ASPNET'". This is obviously something to do with permissions. I am all out of ideas but I will list the things I have already attempted to see if I either missed something or somebody can add to what I have already done.
1. In SQL Server I right-clicked the db -> properties -> security tab -> selected the option button to "SQL Server and Windows" instead of "Windows Only".
2. I installed SP3 and changed the sa password.
3. I created a new user under my db using the ASPNET account listed. I then changed the permissions to allow selecting, update, insert and delete access.
4. I also created a new user under my db using the IUSR account listed. I changed the permissions to the same settings as above.
I did a search on google which provided with all of the information above, but I am still stuck and hoping that this is a common problem and will be easy to fix.
Below is the code which I am attempting to use.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim strConnString As String = _
"Data Source=(local);" & _
"Initial Catalog=Names;" & _
"Integrated Security=SSPI;"
Dim objConn As New SqlConnection(strConnString)
Dim strSQL As String = "SELECT * FROM [Names]"
Dim objCmd As New SqlCommand(strSQL, objConn)
objConn.Open()
MyDataGrid.DataSource = objCmd.ExecuteReader
MyDataGrid.DataBind()
objConn.Close()
End If
End Sub
Regards,
Corey
View 11 Replies
View Related
Dec 24, 2004
Hi All,
What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for the @Where. (I tried with DEBUGGING option of Query Analyzer also).
=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"
if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"
DECLARE curs1 CURSOR SCROLL
FOR SELECT
*
FROM
school
WHERE
school ='ABC' + @where
=============================================
It gives me all the Records inside the SCHOOL='ABC' ...
Please check my SQL Above and Could somebody tell me , how can I attach the VARIABLE with CURSOR sql statement ?
Please advice me..(:
Thanks !
View 4 Replies
View Related
Feb 22, 2005
I'm trying to get just the day part of the date - 2/22/2005 (getdate()) but instead of returning '22', it's returning '2'. Can someone please tell me what I'm doing wrong?
Thanks!
Lynnette
Here's the code
declare @thisDay varchar
set @thisDay = Convert(varchar, Datepart(day, getdate()))
View 3 Replies
View Related
Jan 29, 2006
hi..i am kind of new to asp.net n having trouble with the SqlException error message.. n this code works as 1st page pass the id to second page and the second page took the id to execute the query..i dun know the wer the error occurs..can give a help..Thanks.
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection connection = null;
SqlCommand command = null;
string sqlConnection = null;
string sql = null;
string ab = null;
sqlConnection = ConfigurationSettings.AppSettings["MSQLConnectionString"];
connection = new SqlConnection(sqlConnection);
if (!Page.IsPostBack)
{
try
{
if (Request.QueryString["categoryID"] == null)
{
}
else
{
ab= Request.QueryString["categoryID"].ToString(); //getting the id from page that pass this values
sql = "Select groupname, categoryid, description from groups where groups.categoryid=?"; // can this query execute?
command = new SqlCommand(sql, connection);
connection.Open();
command.Parameters.Add(new SqlParameter("categoryid", ab));
reader = command.ExecuteReader(); // error on here "SqlException"
while (reader.Read())
{
group.InnerText = reader["groupname"].ToString();
desc.InnerText = reader["description"].ToString();
}
}
}
finally
{
if (reader != null)
{
reader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
View 1 Replies
View Related
Jun 21, 2001
Hi everybody!
I´m trying to start SQL Mail in SQL7. I configure the exchange client, test the Mailbox, but when i try to start Mail, Console show me the following error:
Error 22030 - A MAPI Error - MapiLogonEx Failed due to MAPI Error 273
NOTE: The Exchange server is in another domain.
I think that i'm forgetting something (i´m new on this)...
Thanks in advance
View 2 Replies
View Related
Nov 17, 2000
Hello,
I've got some trouble when using xp_readmail.I check the email count and saved the attached files in c:winnt.
The problem is when the attached files are saved cause the file name is cut in dos format (8 characters + . + 3 characters) but my file's name is like toto.tutu.titi_tata.txt so is there a way to keep the name unchanged when saving attached files ?
Thanks for your answers
View 1 Replies
View Related
Jan 13, 2000
I've been having this huge problem with date and SQL 7, like lot of people. I use asp with the sql and trying to do this query where startdate should equal or smaller the date today, here are to queries that worked, but not anymore.
SELECT * FROM tbl_date WHERE convert(varchar(10), startdate, 101) = convert(varchar(10), getdate(), 101)
this query worked until the year 2000, the normal y2k bug
then this one, wich i saw here on messageboard:
select * from tbl_date where startdate =< '" & date & "'"
well this worked until today, when it just stop working
so does anybody know what to do?
hope to get answer on this
View 1 Replies
View Related
Sep 20, 2000
Replication problem:
Server A is Publisher/Distributor SQL7
Server B is Subscriber SQL7
Successfully set up a publication for table on Server A
Synchronisation fails with error that 'The network name cannot be found' and the following file could not be created:
The last action is 'Server AC$MSSQLRepldataunc<pub name><date string>ablename.sch'
... which is odd because the file IS created and contains a correct looking table definition.
So, manually sync data on this one table and try again, now it can't connect with my server, which is weird cos I
can connect through EM.
Any thoughts???
G.
View 2 Replies
View Related
