Determine Whether An Entry Is Validpresent??
Mar 2, 2007
I am writing a select statement to retrieve all data for a particular region. I need to select only the callerid's which are valid for that region.
I have two tables:
tblone contains PbX data, eg regionalid, callerid, donglearea and regionaldialup, tbltwo contains regionalid, and two columns which when concatenated make up a substring of the tblone.callerid...
i need to do a check of the callerid in tblone against tbltwo to make sure it is a valid callerid. (ie. it is present in tbltwo) N.B. the concatenation of the two columns in tbltwo is only a substring of tblone.callerid.
It is easy to concatenate the two rows and place them in a temp table, how do i then search through this table and if it is present, allow my select statement to print this entire row as part of the result set?
if the callerid in tblone is not valid i have to do something similar against a dongle table using the donglearea field and if the dongle area is not valid i have to use the regionaldialup field and by using a predefined value (eg. 0800003554) to determine if the row is valid and should be selected.
I then have to delete everything which was not valid from the table.
I am a junior sql administrator and your help would be much appreciated!
thanks!
View 5 Replies
ADVERTISEMENT
Nov 22, 2005
Can someone please help me solve the following error? I am not sure of the version my hosting company is using and where to get the manual.
Could not add the entry because: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-id, title, entry, date_entered) VALUES ('testing', 'testing', . The query was INSERT INTO blog_entries (blog-id, title, entry, date_entered) VALUES ('testing', 'testing', 'testing'.
PHP Code:
if ($dbh = mysql_connect ('localhost', 'blu6592_money', 'rich')) {
print '<p>Successfully connected to MySQL.</p>';
if (mysql_select_db ('blu6592_myblog')) {
print '<p>The database has been selected.</p>';
} else {
die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>');
}
$query = "INSERT INTO blog_entries
(blog-id, title, entry, date_entered)
VALUES (0, '{$_POST['title']}',
'{$_POST['title']}',
'{$_POST['entry']}', NOW())";
if (mysql_query ($query)) {
print '<p>The blog entry has been added.</p>';
} else {
print "<p>Could not add the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>";
}
} else {
die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>');
}
mysql_close();
Thanks in advance.
View 1 Replies
View Related
Aug 13, 2005
(I don't post here often, so in case I'm violating long-standing taboosof this newsgroup, I apologize in advance for calling a relation atable, using nulls, and other ignorant, destructive, and comtemptibleterminology.)I have a table that's keeping a sort of running log of different typesof changes to pieces of data. The table has a foreign key of the databeing changed, the foreign key for the type of change occuring, someinformation about the change in a couple more columns, and a timestampfor each entry. So it's:dataIDeventIDeventInfotimestampWhat I'd like to do, if at all possible, is a single SQL query that,given a dataID, returns the most recent eventInfo and timestamp foreach eventID. Is this possible?Many thanks.-Eric
View 3 Replies
View Related
Apr 12, 2004
Anyone Got any other advice?
http://www.dbforums.com/t993214.html
View 3 Replies
View Related
May 30, 2007
"High priority system task thread: Operating system error Exception 0xAE encountered."
we are seeing this on one of our development servers, running SQL 2005 Enterprise SP1. The symptom is that after the sql service has been running a while (order of days), this error starts getting written to the sql server log file over and over, and eventually the logfile fills the drive.
Apparently it's fixed by this hotfix, but our ops team doesn't want to install this - they'd rather just cycle the service and delete the log when it happens.
I was wondering if anyone else has seen it might know the root cause so we might be able to workaround it without cycling the service. I know, lame question. The correct answer is "install the hotfix" or "install sp2" but our ops team won't do that for now...
www.elsasoft.org
View 2 Replies
View Related
Jul 24, 2007
Hi All,
I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar]
(2)-ReceivedTime [datetime]
(3)-Status [char].
Now here how to remove duplicate entry of same mobile number to MobileNumber field?
Regards
Shaji
View 4 Replies
View Related
Nov 28, 2006
Hello,
I have a table of names/dates as such:
Class 1 1/1/2007Class 1 1/3/2007Class 1 1/5/2007Class 2 2/1/2007Class 2 2/3/2007Class 3 3/1/2007
What I want to do is select only the nearest entry from the list for each distinct class, so the results are like this:
Class 1 1/1/2007Class 2 2/1/2007Class 3 3/1/2007
So basically, I want the first upcoming class in the list for each distinct class name. How do I do this? I'm using SQL 2005 Express.
Thanks.
View 3 Replies
View Related
Jun 4, 2007
How do i do that... in sch lab, the lecturer said: Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Try SqlDataSource2.SelectParameters("Title").DefaultValue = txtProductTitle.Text GridView1.DataBind() If GridView1.Rows.Count > 0 Then //if no repeated Label1.Text = "Existing Record Found. Please enter another Software." Else 'SqlDataSource1.Insert() 'Label1.Text = txtProductTitle.Text & " successfully added to the database." End If Catch ex As Exception Label1.ForeColor = Drawing.Color.Red Label1.Text = "An error occured!" & ControlChars.NewLine & ControlChars.NewLine & _ ex.Message.ToString() End Try End Sub but its not working for me... the GridView1.Rows.Count is always 0, if there is a repeat, it should be > 0
View 4 Replies
View Related
Nov 9, 2007
Hi, I know this is probably very simple but I am pretty new to this and have tried looking but cant seem to get the search criteria right. I have a database with a storeDate field which is of shortdatetime type. I am connecting to the database (MSSQL) via a stored procedure and returning all the records. I then use the code foreach (DataRow dr in ds.Tables[0].Rows) { DateTime dtTo = DateTime.Now; DateTime dtFrom = DateTime.Parse(dr["storeDate"].ToString()); TimeSpan diff = dtTo.Subtract(dtFrom); } I am basically trying to find out the age of the database entry by subtracting it from the current DateTime so i can delete records over a certain age. The problem (at least one of them!) is retrieving the "storeDate" object from the database and storing it in the dtFrom object. I have tried just assigning it directly as dtFrom = dr["storeDate"] and various other methods but I just don't know enough to assign it! Can anyone help me with this or spot any other mistakes in this process of removing old files automatically. Greatly Appreciated,Sean.
View 4 Replies
View Related
Nov 17, 2007
Hi,I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:IDUserIdDateDescription(UserId is a foreign key to asp_net_Users as I am supporting user accounts)Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).Can anyone point me in the right direction on the steps needed to create this scenario?Thanks for any helpDaniel
View 4 Replies
View Related
Aug 14, 2005
Here's some code that says it should identify if a user already exists in my database. I have changed the code to match my database, but it seems to have somewhat the opposite affect, rejecting all names (even new ones) or accepting all names (including existing ones). The switch in situations occurs in the "if" statement towardsd the end, when I change the sign of objDR.RecordsAffected. Do you have any idea what could be wrong? Thanks.
Function DoesUserExist(ByVal userName As String) As Boolean
Dim connectionString As String = "server='(local)Netsdk'; trusted_connection=true; Database='AuthorizedUsers'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Users].[UserName] FROM [Users] WHERE ([Users].[UserName] = @UserName)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
Dim Cmd as New SQLCommand(queryString, sqlConnection)
With Cmd.Parameters
.Add(New SQLParameter("@username", username))
End With
sqlConnection.Open
Dim blHasRows As Boolean
Dim objDR As System.Data.SqlClient.SqlDataReader = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
if objDR.RecordsAffected > 0 then
blHasRows="True"
else
blHasRows="False"
End If
Return blHasRows
End Function
View 4 Replies
View Related
Feb 22, 2006
How can I select from a table the newest entry.I'm inserting in a table user info and then want to get the users id number
View 9 Replies
View Related
Feb 23, 2001
hi I have a table I need to have a process which prevent a user from entering a name value( company Name ) in a field. how can I do that .
Ahmed
View 2 Replies
View Related
Nov 8, 2005
Hello,
I am trying to select the last entry (by date) for each category in a table.
For example, if my table had the following fields;
id, category, product, datePosted,....
...how would I select the last product for each category posted by date?
Any guidance is appreciated.
Thanks,
AC
Probably a simple solution, but can't find it my brain right now!
View 7 Replies
View Related
Jan 26, 2005
Task:
To insert entries into a table. The table has a primary key based on a field 'ID'. When inserting into the destination table, I want to make sure that the new entry will overwrite the old entry.
What's the quickest/cleaniest way to do this ?
thanks,
Clayton
View 1 Replies
View Related
Aug 2, 2006
MSSQL2K
SP4
Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs.
--drop table #foo
create table #foo
(empID int,
transDate datetime,
transType varchar(10))
insert into #foo values(1, '01/01/06 01:01:01','Insert')
insert into #foo values(1, '01/01/06 01:01:02','Update')
insert into #foo values(1, '01/01/06 01:01:03','Delete')
insert into #foo values(2, '01/01/06 01:01:01','Insert')
insert into #foo values(2, '01/01/06 01:01:02','Update')
select f.empID, Change =
(select count(transDate) from #foo f2
where f2.empID = f.empID
group by empID),
f.transDate, f.transType
from #foo f
Current results:
132006-01-01 01:01:01.000Insert
132006-01-01 01:01:02.000Update
132006-01-01 01:01:03.000Delete
222006-01-01 01:01:01.000Insert
222006-01-01 01:01:02.000Update
Desired results:
112006-01-01 01:01:01.000Insert
122006-01-01 01:01:02.000Update
132006-01-01 01:01:03.000Delete
212006-01-01 01:01:01.000Insert
222006-01-01 01:01:02.000Update
TIA, CFR
View 5 Replies
View Related
Mar 12, 2008
Hi all,
Newbie here, just wanted to get that out of the way first
and foremost.
Im trying to set up a DB in SQL 2000 that allows me to enter
lots of text into a column.
I read one site where it said to use the NTText datatype with a length
of 16. I did that, but everytime I enter alot of text into that column
I get <Long Text> and I dont know how to output that or even
read it.
Do any of you guys know how to either:
1. get the text out of the <long text> or
2. a better way to store that much data.
Thank you in advance!!!
REZ
View 8 Replies
View Related
Nov 24, 2011
i have data like...
userid startdate in_out
1 2011-11-20 9.30 1
1 2011-11-20 10.30 0
1 2011-11-20 12.30 0
1 2011-11-20 12.45 1
1 2011-11-20 6.30 0
See 1-Entry 0-exit for 2011-11-20 12.30 is exit but entry time of that exit is missing....
Can i insert same exit value for entry?
View 3 Replies
View Related
Feb 7, 2004
Hey there, could someone please help me!!
I had created a database in sql server but since then installed windows xp pro and now when i go through the microsoft sql server dsn configuration the database is not valid and therefore i cannot change the default database as it says it doesn't exist.
So then when i type in command prompt use 'db' it says it doesn't exist but when i try to create 'db' it says that the database already exists.
I get the error message, could not locate entry in sysdatabases for 'cti' (database name).
When i look in mysql - data - along with master etc there is my cti.mdf and log files??
I'm stuck???
Is it that the files just aren't registering through sql server???
Please help!
View 2 Replies
View Related
Apr 8, 2008
Hi,
I have the following situation:
I have a table that is log of transactions of useractions. Each time a userstatus changes, a new entry is made into the table.
The table has (currently) about 4 million records, and keeps growing each day with thousands of records. It logs the status of about 150 users (currently active).
What I now want is to get the log-entry from all the distinct users that have a log-entry on the latest day (so current day, 'where TimeStamp > 2008-04-08').
(extremely simplified) Example:
(date is y/m/d)
USERID TIMESTAMP STATUS
----------------------------------------------------
... (thousands and millions of records above)
Ed 2008-04-07 18:00:00 Logged_Out
Jim 2008-04-07 18:30:00 Blabla_Status_5
Jack 2008-04-07 19:00:00 Logged_Out
Jim 2008-04-07 19:30:00 Logged_Out
Jim 2008-04-08 06:00:00 Logged_In (<< notice new day)
Jim 2008-04-08 06:01:00 Blabla-Status_1
Bob 2008-04-08 06:03:00 Logged_In
Fred 2008-04-08 06:05:00 Logged_In
Jim 2008-04-08 06:08:00 Blabla-Status_2
Jack 2008-04-08 06:12:00 Logged_In
Fred 2008-04-08 06:20:00 Blabla_Status_5
Jack 2008-04-08 06:12:00 Logged_Out
Fred 2008-04-08 06:12:00 Blabla_Status_4
Jack 2008-04-08 06:12:00 Logged_In
Jack 2008-04-08 06:12:00 Blabla_Status_7
----------------------------------------------------
The result should be:
USERID TIMESTAMP STATUS
----------------------------------------------------
Jack 2008-04-08 06:12:00 Blabla_Status_7
Fred 2008-04-08 06:12:00 Blabla_Status_4
Jim 2008-04-08 06:08:00 Blabla-Status_2
Bob 2008-04-08 06:03:00 Logged_In
----------------------------------------------------
(How) can this be done in one stored procedure?
View 7 Replies
View Related
May 9, 2008
I have an online store with products and categories. The requirements are: Each product can be in one or more categories; a product must be in at least one category.
To accomplish the first requirement, I have a Product table, Category table, and a many-to-many Product2Category table. (Probably not relevant, but a requirement is the Category table is self-referencing to provide sub-categories.)
My question is for the second requirement that each Product be in at least one category. My assumption would be to add a column to the Product table called "ParentCategoryId", which is a foriegn key of the Category table's identity column.
What would be the best approach to ensure a product is in at least one category?
Thank you.
View 2 Replies
View Related
Jun 12, 2008
While i execute the below query ,Entry is put in status_report table.But the same if i put in scheduling (Sql Agent) no entry is made in the table and the message success is shown in "Agent Job Activity".
Purpose of the sp is to transfer data from One Server to Another Server.
But if see in the Destination Tables No entry is made.
CREATE procedure RBI_Control_sp
as
begin
set nocount on
begin try
BEGIN TRANSACTION
insert into fin_wh..Status_report
(
[object_name],
row,
st_date
)
select
'Data Tranfer',
'Inprogress--',
getdate()
--Data Transfer From the Live Server [RamcoFin02(scmdb)] to Dw-Server[Ramcovm392(fin_ods)]
exec fin_ods..RBI_Data_Transfer_sp
--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp
Update fin_wh..Status_report
set row= 'Success',
[error_message] = 'No Error',
end_date= getdate()
where row= 'Inprogress--'
COMMIT TRANSACTION
end try
begin catch
ROLLBACK TRANSACTION
Update fin_wh..Status_report
set row= 'Failure',
end_date= getdate(),
[error_message]= cast(error_number() as varchar) + '***' + error_message()
where row= 'Inprogress--'
end catch
set nocount off
end
Txs in advance
View 3 Replies
View Related
Feb 18, 2014
How can i keep only one record from duplicate entry.
Example
COLUMN1COLUMN2COLUMN3COLUMN4
AAA121-12
AAA121-13
AAA121-14
Here i want to keep only the top record and want to delete other 2 rows.
View 4 Replies
View Related
Jan 15, 2006
iam new to MS SQL 7 server...i have two tables in my database say Table1 and Table2 having a comman field--- Name String(30). I want that dual data entry should be made for any single entry. That is if a name is entered in Table1, then same entry should be automatically entered in table2
View 8 Replies
View Related
Apr 10, 2007
question... i heard a bit about Control Macro Generator but have not found much reading material on it. here at our company we have employees fill out a weekely timesheet and typically have clerks enter the information into the TM.PTA.00 (Project Timesheet with Rate/Amount Entry) and submit it that way. say we have this timesheet in a nice little excel sheet with columns that match the input boxes on the form, how can we get it to automatically input the data instead of having a person sit there and type it all in?
View 3 Replies
View Related
May 27, 2007
First, let me say I'm really new (and probably dangerous) with SQL.
I want to take a column in my database called "publish_up" and add 4 days to the date and put it in a column in the database called "publish_down." The publish_up date is the date to start publishing and the publish_down is the date to stop publishing. A Joomla! mambot will then come by and archive the items with a publish_down date that matches today's date.
I'm pretty sure my server has SQL 5 if that's an issue.
Thanks for any advice or help you can offer.
View 6 Replies
View Related
Nov 8, 2007
Hi guys,
I have just started studying for an MCTS, to eventually get to MCITP for SQL Server 2k5.
I am currently in an Application Support position, working with a large clinical Oracle Database, but mainly on a simple querying basis.
The plan is obviously to get away from the Tech Support, towards the DBA and I have been researching the current job market and never once found a job offer that said "offering an entry level or Junior DBA position for those that are just starting"... There's always at least a few years of experience already as a DBA required!
I am wondering how one is supposed to get that experience if he doesn't get a beginners position to start off with?
Any stories from experience? Any hints where to look, how to apply and where maybe?
Thanks,
-free-
View 3 Replies
View Related
Jul 23, 2005
Greetings. Today I was testing the sql backup/restore functions on ourprimary server. I was able to backup my database without any problemsbut when I tried to restore it, I received an error "Could not find theindex entry for RID '3610200101a03cc4b49d8bbc84bac50cbe042cecf76' inindex page (1:40), index ID 0, database 'walkthrough'." Thinking thatdata had been corrupted somehow I tried another database and received asimilar error for that database. In the past, our service provider hasattempted to restore data to this server and the restore failed do toanother data corruption. The problem is, I can't find out where thecorruption is located or how to fix it.When I open the restored database in enterprise manager, it returns theabove error and is unable to get a listing of my tables. In queryanalyzer I am able to view my data but receive the index error when Iattempt to look at the information_schema data. When I try to run a DBCCCHECKDB on the corrupted database it reports 0 errors and 0inconsistencies.So my diagnosis of the problem is that something is corrupting my systemtables and that problem shows up whenever I try to restore data from abackup. There could be a hardware failure but I believe the failurewould affect more then just the sql system tables. Can anyone offer anyadvice on how to find the corruption?Thank you in advanceRichard Bailey*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Jul 17, 2007
Hello,I have been having a tough time writing the follow requirement for aquery.On a table that the primary key is a tagId and an hourly timestamp, Iwould like to find out for every hour which tags did not get enteredinto the database. Essentially I am looking for patterns of entriesthat are not making it into tableB.Examples of the tables:TableA TableBTagID and TagName TagId TimestampPK PK1 PK2approx 6000 rows approx 6000 rows per hourI am thinking that I will need to do something like:Select tableB1.time, count(*) from tableB1 group by tableB1.timehaving tableB1.time >= XXXX and tableB1.time <= XXXX and tableB1.tagIdnot in (select tagId from tableA where not exists (selecttableA.tagId, distinct.tableB2.time from tableB2)I have been trying to create an effecient query handle this but havenot had any luck. Any assistance would be more then appreciated.Thanks,Andy
View 1 Replies
View Related
Sep 6, 2007
I'm trying to graduate from Access to SQL and am finding that Access is still meeting my needs better than SQL.
So, I figure I'm doing something wrong.
In Access, I have queries that contain parameters. So far, I've gotten SQL to accept parameters, using =@prompt. But, this seems to be a limited parameter capability. Number one, I can't use a phrase for a prompt. Number 2, I can't do something like Access' Between [upper] And [lower]; I've not found away to use parameters as bounds in SQL.
Any help?
View 5 Replies
View Related
Aug 12, 2006
Hi,
In my Integration Scenario I have a source field 'CompanyTypeCode' which is nullable in 'tblCompany' and There is a specific lookup Table 'tblCompanyType' to map the codes with descriptions for this field. When I want to transform this table, The NULL Entries fail in Lookup and I ran into problem. Please suggest a clean solution for bypassing the lookup when the entry in source is NULL. (If I bypass the lookup, the flow changes since there is not an extra column from the lookup component any more, therefore I must have another OLEDB Destination if the code is NULL).
Regards,
Sassan
View 1 Replies
View Related
Jan 2, 2007
I am trying to delet a row duing run-time but everytime i go to save the program I get an error message saying: "Update Requires a valid DeleteCommand when passed DataRow collection with deleted row.
Anyone any ideas?
View 3 Replies
View Related
Nov 17, 2006
Not sure if this is the right place to post this but hopefully someone can help me. I would like to determine what the last automatically incremented ID is in a table I need to return it as a variable to a VB.NET program but I'm not sure what the SELECT statement would look like for this. Any help would be greatly appreciated.
View 4 Replies
View Related
