Wrong Record Retrieved After Move To SQL Svr
Jul 20, 2005
I've moved the database tables from the .mdb file to Sql Svr and now I have
an *intermittent* problem. When I select a record from a combo box, it will
intermittently pull up the wrong record. The code that does the search is
below. I have a column named 'ProsId' that is a unique number (identity
column). The error -- when it happens -- seems to only happen on higher
ProsId numbers. At first it appeared to me as some sort of data conversion
thing, but I'm not so sure now. Here's some examples:
Record selected Record actually displayed
---------------- -------------------------
100,000 41,859
59,073 15,794
57,000 11,273
57,001 11,274
45,000 45,000 <--- appears to return correct
value every time. Numbers lower than this also seem to return the correct
record every time.
Private Sub cboFindProsName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
If Len(Trim(Me.cboFindProsName.Text)) > 0 Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[ProsId] = " & (Me![cboFindProsName])
Me.Bookmark = rs.Bookmark
End If
End Sub
Many Thanks!
View 5 Replies
ADVERTISEMENT
Apr 7, 2008
Hi,
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks,
Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
View 1 Replies
View Related
Feb 18, 2006
hi, i have no problem to select all records from the table, but how to copy or move these selected records to other table which contain same field as the orriginal.thanks
View 5 Replies
View Related
Jul 18, 2007
Hi,
i have simple requirement...but dont know how to proceed. I need to move the first record from a flat file (file1) to another flat file(file2) using SSIS, file1 will have many records but i just need the first one alone to be moved. Any pointers on this would be of much help.
Thanks,
raj
View 4 Replies
View Related
Oct 17, 2007
Hello,
I am using Access with tables linked to sql server 2005. I need the identity to be displayed directly when entering data so that i can enter enter data into other tables using this identity on the form without the need to go back and forward to other records to display the identity. Is there a feature in Sql server 2005 which permits this?
Thank you
View 2 Replies
View Related
Feb 19, 2006
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies
View Related
Aug 12, 2005
I need to return the "autoID" number that is created at the time of an insert. Is there way of doing that?
Thanks,
Ed
View 4 Replies
View Related
Mar 15, 2004
I am trying to do this in MS Access actually but it is an SQL question.
This query:
SELECT LoanNo, COUNT(LoanNo) AS 'Count'
FROM DedupTest031504
GROUP BY LoanNo
HAVING COUNT(LoanNo) > 1;
returns:
LoanNo Count
46690128 2
46861821 2
47762138 3
47762154 3
48257239 2
48257663 2
48257719 2
48258143 2
48258167 2
which is correct but how do you SUM the COUNT field? In other words, I want the total number of duplicate records in the table. Is there another way alltogether?
I tried:
SELECT LoanNo, SUM(COUNT(LoanNo))
FROM DedupTest031504
GROUP BY LoanNo
HAVING COUNT(LoanNo) > 1;
but I get a "CANNOT HAVE AGGREGATE FUNCTION IN 'SUM(COUNT(LoanNo))' error.
Thanks.
ddave
View 12 Replies
View Related
Jul 12, 2007
hi,
i really need ur help..
I am creating an a stored procedure which could insert new records, the identity of the primary key in my table is not incremented. i want to increment the id when adding a new record in the stored procedure. what would be the system for that?
thanks
Funnyfrog
View 10 Replies
View Related
Mar 23, 2006
some time back i had problem with sending the service broker messages on remote machine,
I had some security issues and they were resolved by the help of Remusu.
since the ip of the remote machine was changed in between so i just re-executed the same scrips which used to successfully send messages on the remote machine.(I just updated the IP in the route )
To my surprise same script did not work now where as no change have been made.
I am doing the following:
1.Created the certificate and end point on the sender side. back up the certificate in a file and copied to the other machine.
same step was repeated for receiving side as well.
2.both the side i created the certificates using the back up files from other sidend proper authorization
3.Then I created the database,route,messagetypes,contract,queues,services etc both the side.
4. then i setup dialog security(ie.created the dialog security certificates both the sides and back up them). Later I copied these back up files to each other, create some dialog user and create certificate using authorization to these remote dialog users created.
5.I also created remote service binding on both the sides and granted send permission to the remote dialog user.
When i send the message from sender to the receiver, and run the profiler, I see that on the sending side none of the broker event gives any error.
In the recever side I get the followng Event:
Broker:Message Undeliverable
This message could not be delivered because the security context could not be retrieved.
Error 11229.
I m surprised that the same script was run in the same order,Why was it running before and not now.
I also checked the End points using telnet and they seem to be fine. Also the firewall was "Off" on both the machines i.e. there was no change in system state also.
Please provide the solution. Thanks in advance.
View 12 Replies
View Related
Feb 15, 2008
Hello,
I am retriving records on a page, I am able to make changes cuase the data is displayed in textboxes, my question is how to save the changes I made in the database.
Also in my below code for retriving the data what statement do I need to add so that I can use a QuerryString to filter the results. ex (default.aspx?CID=1) and only records that
match that would display.
Here is my code: for retriving data
Private Sub displayrecord()SqlConnection1.Open() Dim SqlDataAdapter1 As New SqlDataAdapter("SELECT * FROM table1", SqlConnection1) Dim objReader As SqlDataReader Dim SqlCommand1 As New SqlCommand("SELECT * FROM table1", SqlConnection1) SqlDataAdapter1.Fill(dsMember) objReader = SqlCommand1.ExecuteReader() objReader.Read() txtbox_confirmpass.Text = objReader("MemberPassword") txtbox_name.Text = objReader("MemberName") txtbox_birthdate.Text = objReader("MemberDOB") txtbox_email.Text = objReader("MemberEmail") Dropdown_Gender.SelectedValue = objReader("Gender") objReader.Close() SqlConnection1.Close() End Sub
Private Sub btnUpdate_Click
View 2 Replies
View Related
Jun 29, 2004
Hi,
I have a table full of items which can be searched.
I also have another table with the ID of each item and columns for no of times details shown, no of times saved etc.
What I would like to do is increment a value in this second table for each item every time it is returned in a search.
What would be the best way to do this?
(Im using a Stored Procedure)
Thanks in advance,
Pete
View 5 Replies
View Related
May 17, 2008
Hi,
I need to save all the rows that are retrieved from the sql server 2005 db into any external file such as excel.
Can any one give me any clue?
Gaurish Salunke
Software Developer
OPSPL
View 3 Replies
View Related
Feb 27, 2008
hi, could anyone tell me how to display data retrieved using sql in DataGridView?
View 6 Replies
View Related
Apr 13, 2007
HI
I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found
On the Target Server:
on Broker: Message undeliverable --- This message could not be delivered because it is a duplicate
On the Source Server
on Broker: Message undeliverable --- This message could not be delivered because the security context could not be retrieved,
I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this
conversation_handle to_service_name is_end_of_dialog message_body transmission_status
E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL
I reinstalled the broker several times, but always get this problem.
thanks
Paul
View 9 Replies
View Related
Aug 14, 2014
How can I get the data retrieved from the exec function below into Excel
DECLARE @columns NVARCHAR(MAX) ,
@columns_n NVARCHAR(MAX) ,
@sql NVARCHAR(MAX);
SET @columns = N'';
SET @columns_n = N'';
SELECT @columns += N', X.' + QUOTENAME(aaTrxDim)
[code]....
View 5 Replies
View Related
Nov 10, 2014
Following is my db table
student_id student_code student_parent_id student_name
1 11 0 a
2 111 1 b
3 1111 2 c
4 11111 3 d
I want to generate following op
student_id student_code student_parent_id student_name Hierarchy
1 11 0 a 11
2 111 1 b 11-111
3 1111 2 c 11-111-1111
4 11111 3 d 11-111-1111-11111
Following is the query
i want to retrieve around 10000 in one go.. its taking around 8 seconds.. how to make it faster?
even if i retrieve 1 record or 10000 records, its taking around 8 seconds...
--- create table
create table test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))
---- insert records
insert into test values (1, '11', 0, 'a')
insert into test values (2, '111', 1, 'b')
insert into test values (3, '1111', 2, 'c')
insert into test values (4, '11111', 3, 'd')
---- result query
;WITH SInfo AS
(
SELECTsId
,scode
,ParentId
,sName
,CONVERT(nvarchar(800), scode) AS Hierarchy
[Code] .....
View 7 Replies
View Related
Jul 20, 2005
When I try to connect to a MS Access 2000 database inenterprise Manager , i getan error "Login failed -Catalog information cannot be retrieved". I get thiserror message in a dialog box about 5 or 6 times. Thedatabase file location ends up in the file location box. Ican click "Test Connection" and the connection succeedswith a series of dialog boxes with the message "Loginfailed - Catalog information cannot be retrieved"Any help is appreciated greatly.--Posted via http://dbforums.com
View 2 Replies
View Related
Jan 21, 2008
Hi,
I have a report with 5 filters which can be applied to it. The records are grouped by the Rotation programme there are on, with a subtotal for each unique programme name.
The report seems to work fine, but upon closer inspection - we noticed that 2 of the records are not being displayed. As a result, the total count is out by 2.
We tracked down the missing records so I ran the SQL query with a Where clause, and it was able to find the two records.
What could possibly cause this behaviour? Please see included SQL statement :
Code Block
SELECT Posts.PostNumber,COUNT(Posts.PostNumber) AS RPCount, Incumbents.Name AS IncumbentName, Grades.GradeTitle, Specialties.SpecialtyTitle,
Hospitals.Name AS Hospital, Genders.Gender, [Incumbent History].YearGraduated, COUNT([University Origins].Origin) AS OriginCount,
Incumbents.Nationality AS NationalityID, Countries.[Country Name] AS Nationality, [Rotation Programmes].[Programme Name],
Posts.[Post Approved for Training], [University Origins].Origin, Posts.OldPostNumber, [Rotation Programmes].[Programme ID]
FROM Posts INNER JOIN
Incumbents ON Posts.PostNumber = Incumbents.PostNumber INNER JOIN
[Incumbent History] ON Incumbents.[Incumbent ID] = [Incumbent History].IncumbentID INNER JOIN
Grades ON Incumbents.[Official Grade] = Grades.GradeID INNER JOIN
Specialties ON Posts.Specialty = Specialties.SpecialtyID INNER JOIN
Hospitals ON Posts.HospitalID = Hospitals.[Hospital ID] INNER JOIN
Genders ON Incumbents.GenderID = Genders.GenderID INNER JOIN
Countries ON Incumbents.[Country Of Birth] = Countries.[Country ID] INNER JOIN
[Rotation Programmes] ON Posts.[Rotation Programme] = [Rotation Programmes].[Programme ID] INNER JOIN
[University Origins] ON [University Origins].[Uni Origin ID] = Incumbents.[University Origin]
GROUP BY [Rotation Programmes].[Programme Name], Posts.PostNumber, Incumbents.Name, Grades.GradeTitle, Hospitals.Name, Genders.Gender,
[Incumbent History].YearGraduated, [University Origins].Origin, Incumbents.Nationality, Countries.[Country Name], [University Origins].Origin,
Posts.[Post Approved for Training], Posts.OldPostNumber, Specialties.SpecialtyTitle, [Rotation Programmes].[Programme ID]
View 12 Replies
View Related
Aug 26, 2008
I am trying to connect to a SQL database on a Windows Server 2003 machine via a Microsoft Data Link on an XP machine.
I select Microsoft OLE DB Provider for SQL Server on the provider tab of the Data Link.
On the connection tab I input the IP of the server and select "Use Windows NT Integrated securty"
However when I test the connection I get the following errors:
[DBNETLIB][ConnectionOpen(COnnect()).]SQL Server does not exist or access denied.
followed by the error: "Login failed. Catalog information cannot be retrieved"
The firewalls on both machines have been dropped but this did not work. The XP machine can ping the Server 2003 machine and connect to a C$ share.
I am using SQL Server Express 2005 on the Server. I can connect to the database using SQL Server Management Studio Express on the server. However the XP client machine is not having any luck connecting via the Microsoft Data Link.
View 4 Replies
View Related
Aug 8, 2006
Hi,
This may be a really silly question, but I am starting to experiment with stored procedures for the first time and I am not really sure about how they work. I am working in SQL Server Management Studio Express...
When putting a SELECT query statement into a stored procedure and executing it, how or where can you get or find the actual record set supposedly retrieved?
Thanks for any help..
--------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = 1ASBEGIN SET NOCOUNT ON; SELECT ID, TaxonID, Taxon FROM QueryView WHERE ParentID1 = COALESCE(@taxparent, ParentID1) AND ParentID2 = COALESCE(@taxparent, ParentID2) AND ParentID3 = COALESCE(@taxparent, ParentID3) AND ParentID4 = COALESCE(@taxparent, ParentID4) AND ParentID5 = COALESCE(@taxparent, ParentID5) AND ParentID6 = COALESCE(@taxparent, ParentID6) AND ParentID7 = COALESCE(@taxparent, ParentID7) AND ParentID8 = COALESCE(@taxparent, ParentID8)END
View 1 Replies
View Related
Feb 25, 2007
I'm using MSSQL with PHP and this works fine on a Windows server.
When i move to a FreeBSD server, the date formatting is not working.
FreeBSD retrieves the date as: mon dd yyyy hh:mi:ss:mmmAM - and php's functions for formatting date fails.
I've tried using: Convert(varchar(10), Date, 103) AS Date, and the date is formatted fine - BUT sorting on date does NOT work.
Are there any way i can do changes to datetime behaviour on server side? I NEVER wants the date in mon dd yyyy hh:mi:ss:mmmAM. I don't need milliseconds, and i want 24h format - not AM/PM. Are there any settings on the SQL server for this?
View 7 Replies
View Related
Jan 6, 2006
Hi,
This is my problem :
I have listBox1, listBox2 controls bound to sqldatasource1 and sqldatasource2.
I wrote one stored procedure to retrieve data from my sql database like :
select Id, Name,Address,Amount from KalakaDB
Now I want my listBox1 to display Name By name and my listBox2 to display Amount by decrease order
how can i connect my sqldatacontrol to the stored procedure ?
Thanks
View 1 Replies
View Related
Sep 5, 2007
Hi,
In Crystal Reports, you could suppress the printing of a report if no rows were retrieved from the database server (ie: zero pages would be sent to the printer). This was done by opening the Report Properties dialog in the report designer and setting the "Suppress Printing if No Records" to true.
Is there a way I can reproduce this behavior in SQL 2005 Reports?
Thanks!
Stephen
View 2 Replies
View Related
Jul 20, 2006
Hello,
I've been busy all night searching and reading trying to figure out how I can do the following.
I have a table that tracks user IDs in multiple fields. When I select
records from this table I need a way to resolve those ID fields back
into user names by referencing the users table. SQL statement thus
far...
SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???
How do I resolve theTable.UserIDA and theTable.UserIDB back to
Users.Username so that the records returned fill the fields NameA and
NameB?
Your help is greatly appreciated.
Thanks,
-Mike
View 5 Replies
View Related
May 4, 2015
I'm trying to replace a view with stored procedure for faster performance. the View is called by end user using a query as below, I need to pass the date as parameter for sp to execute with quotes for it to execute with correct results. I tried to pass the date as parameter but could not execute stored procedure with correct results. Is there any way to put quotes around returned date from sub query :
Execute statement is like
Exec dbo.storedProc1 select max(date) from table
I need to pass the above as :
Exec dbo.storedProc '2015-03-24' .
Somehow passing the date as parameter is giving me empty result set.
View 2 Replies
View Related
May 19, 2015
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
View 8 Replies
View Related
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
Jul 20, 2005
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Apr 6, 2007
Hey all!
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
Thanks in advance,
Johan
View 5 Replies
View Related
Feb 26, 2008
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
View 3 Replies
View Related
Mar 1, 2006
Hi
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks
View 6 Replies
View Related
