Help! Migrated from Access to SQL Server 2005 - Can't see newly inserted records
-
Please help,
I am trying to scale up my database from Access to SQL Server 2005.
When I run my 'Insert' statements I can not see the record in the table using SQL Express Management Studio.
I can query the db and the records are all there and are returned, just not visible in Management Studio.
What is strange is that the first insert statement returns the row id, and then using the row id I insert details in another table. All the details that are inserted are visible in the Management Studio, just not records from the original insert statement.
Thank you!
Private Function AddToShipmentDatabase()
'Record id of newly inserted shipment
Dim intRecord As Integer = Nothing
'Prevent null errors from database by changing empty fields to empty strings
If ShipmentInformation.CreditCardInfo = Nothing Then
ShipmentInformation.CreditCardInfo = ""
End If
If ShipmentInformation.Tracking = Nothing Then
ShipmentInformation.Tracking = ""
End If
If ShipmentInformation.TransactionId = Nothing Then
ShipmentInformation.TransactionId = ""
End If
'Connect to local database and upload the information
'stored in the shipment information structure
Dim objConnection As SqlConnection = New SqlConnection("Server=Server;Database=ShippingLog;User Id=user1;Password=password")
Dim objCommand As New SqlCommand
objCommand.Connection = objConnection
objCommand.CommandText = "INSERT INTO [MailLog] ([CustomerId], [ShipDate], [CustomerName], [Service], [Tracking], [Address], [RxCount], [Charge], [CreditCardInfo], [TransactionId]) VALUES(@CustomerId, @ShipDate, @CustomerName, @Service, @Tracking, @Address, @RxCount, @Charge, @CreditCardInfo, @TransactionId)"
objCommand.Parameters.AddWithValue("@CustomerId", ShipmentInformation.CustomerId)
objCommand.Parameters.AddWithValue("@ShipDate", ShipmentInformation.ShipDate)
objCommand.Parameters.AddWithValue("@CustomerName", ShipmentInformation.CustomerName)
objCommand.Parameters.AddWithValue("@Service", ShipmentInformation.Service)
objCommand.Parameters.AddWithValue("@Tracking", ShipmentInformation.Tracking)
objCommand.Parameters.AddWithValue("@Address", ShipmentInformation.Address)
objCommand.Parameters.AddWithValue("@RxCount", ShipmentInformation.RxCount)
objCommand.Parameters.AddWithValue("@Charge", ShipmentInformation.Charge)
objCommand.Parameters.AddWithValue("@CreditCardInfo", ShipmentInformation.CreditCardInfo)
objCommand.Parameters.AddWithValue("@TransactionId", ShipmentInformation.TransactionId)
'Connect to database and add record
objConnection.Open()
objCommand.ExecuteNonQuery()
objCommand.CommandText = "SELECT @@IDENTITY"
intRecord = objCommand.ExecuteScalar
objConnection.Close()
'At this point the package is logged and verified.
Return intRecord
End Function
-
Anyone?
-
adocity (6/26/2011)
Please help,I am trying to scale up my database from Access to SQL Server 2005.
When I run my 'Insert' statements I can not see the record in the table using SQL Express Management Studio.
I can query the db and the records are all there and are returned, just not visible in Management Studio.
So you can query the table and return results. So, how are you trying to see the records in SSMS?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
You post is very confusing. Let's start simple.
When I run my 'Insert' statements I can not see the record in the table using SQL Express Management Studio.
Exactly how are you running the insert statements? Exactly how are you trying to see the records using SSMS?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics. -
Are you sure your ODBC connection points to the very same database as you're looking at with SSMS? I'd compare the connectiong string and the SSMS database location.
-
Thank you for replying. I am sorry for being confusing... still learning the SQL lingo 😀
SQLRNNR (6/28/2011)
So you can query the table and return results. So, how are you trying to see the records in SSMS?So yes, I can return results when I query the table, but when I open SSMS and go to the database in question and select 'Open table' I do not see any new records that I've inserted since the conversion.
LutzM (6/28/2011)
Are you sure your ODBC connection points to the very same database as you're looking at with SSMS? I'd compare the connectiong string and the SSMS database location.I am sure that the connection points to the correct DB because I can query the db and return results. Please see pics below for clarification.
Notice in screen one the records stop at id 4391.
Now here I execute a select * for records with id > 4391 with the results shown below
Thank you all.
-
So from SSMS you can query the table and return results, but when trying to "Open" the table you don't get results.
Do you get an error?
Are you trying to perform both of these actions with the same account?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (6/28/2011)
So from SSMS you can query the table and return results, but when trying to "Open" the table you don't get results.Do you get an error?
Are you trying to perform both of these actions with the same account?
Yes that is correct. In fact the records that are shown when I open the table are the records from the conversion. Everything I am adding to the table after the conversion is not showing, but it is being recorded. I do not get any errors. The accounts are the same. I did try logging in with my Windows and SQL credentials and the view is the same with both accounts.
Like I said earlier I insert other records to a different table after this initial insert. All the other records are visible in the other table when viewed with SSMS.
Thanks!
-
You also may want to check your settings since your "opening" the table instead of running T-SQL statements. I do not have an "open table" option in SSMS. I have "select top xx rows" and "edit top xx rows"
Have you checked the settings here?:
TOOLS-OPTIONS-SQL Server Object Explorer-Commands
-
Steve-3_5_7_9 (6/29/2011)
You also may want to check your settings since your "opening" the table instead of running T-SQL statements. I do not have an "open table" option in SSMS. I have "select top xx rows" and "edit top xx rows"Have you checked the settings here?:
TOOLS-OPTIONS-SQL Server Object Explorer-Commands
I will check the settings when I get into office. I am using SSMS 2005. I believe they did away with the 'open table' command with newer versions of SSMS
-
adocity (6/29/2011)
Steve-3_5_7_9 (6/29/2011)
You also may want to check your settings since your "opening" the table instead of running T-SQL statements. I do not have an "open table" option in SSMS. I have "select top xx rows" and "edit top xx rows"Have you checked the settings here?:
TOOLS-OPTIONS-SQL Server Object Explorer-Commands
I will check the settings when I get into office. I am using SSMS 2005. I believe they did away with the 'open table' command with newer versions of SSMS
That is correct. However, since you are getting results back, you are probably returning too many records and would need to modify the query that is available in SSMS Open Table. There is a query button after you open the table. Click that to display the query being used to return your result set. Once done, you will probably see that it is only returning top 50 or so records.
Returning results in this fashion is not the best practice. Any accidental changes made when looking at these results can easily commit to the database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Correct, I see "open table" when I bring up SQL 2005 SSMS.
I also do not see the options that I posted earlier. It's only for SQL 2008 SSMS.
I'm not too sure what it could be. That said, why do you prefer to use the "open table" anyway. I believe most people just write the queries for the data they want to retrieve/update.
-
Steve-3_5_7_9 (6/29/2011)
Correct, I see "open table" when I bring up SQL 2005 SSMS.I also do not see the options that I posted earlier. It's only for SQL 2008 SSMS.
I'm not too sure what it could be. That said, why do you prefer to use the "open table" anyway. I believe most people just write the queries for the data they want to retrieve/update.
It was just something I noticed. I am just being cautious cause I am new to SQL Server and just expect to see my records when I open the table. The query was 'Select * From MailLog'. I don't think its an issue with too many results. There are only 4000 records. That being said the records are there, so everything seems to be working. I guess it's 'just one of those things.' Thank you for your help!
-
You're welcome.
Since you say you're a "newbie", I'd recommend always writing queries instead of using the designer. You'll learn sql much better.
Good luck.
-
Solved: The records were there, but were not being inserted in order by record id. 'Open table' is SSMS 2005 doesn't sort the records returned by primary id. My new records were being inserted in the middle of the table. Weird. Thanks!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply