Need to Covert This SQL database to MS Access Database, Can anybody help please - Database Discussion Boards

Mycroft Holmes15-Dec-13 11:44

15-Dec-13 11:44

Thankfully in all my years as a LOB developer I have not once had to utilise the millisecond information so I have no idea Big Grin | :-D

I'm assuming this is an intellectual exercise only!

Never underestimate the power of human stupidity
RAH

Re: SQL Server date/time oddity.

Jörgen Andersson15-Dec-13 22:34

Jörgen Andersson

15-Dec-13 22:34

Nothing to see here. Sigh | :sigh:

Are you using a datareader?
If so, using DateTime myDate = (DateTime)reader["MyDateTimeColumn"]; will convert the DateTime from the DB into a string and then convert it back to DateTime, and thus losing precision.
~~Instead use reader.GetDateTime(reader.GetOrdinal("MyDateTimeColumn"));~~
<edit>
Instead use DateTime myDate = (DateTime)reader.GetSQLDateTime(reader.GetOrdinal("MyDateTimeColumn"));
or DateTime myDate = (DateTime)reader.GetSQLValue(reader.GetOrdinal("MyDateTimeColumn"));
</edit>

Otherwise you need to update the question with a bit of code.

Wrong is evil and must be defeated.
- Jeff Ello[^]

modified 16-Dec-13 9:01am.

Re: SQL Server date/time oddity.

Richard Deeming15-Dec-13 23:59

Richard Deeming

15-Dec-13 23:59

Jörgen Andersson wrote:
(DateTime)reader["MyDateTimeColumn"]; will convert the DateTime from the DB into a string and then convert it back to DateTime

Er, no it won't. It will take the DateTime value, box it as an Object, and then un-box it to a DateTime. At no point will the value be converted to or from a string.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SQL Server date/time oddity.

Jörgen Andersson16-Dec-13 1:10

Jörgen Andersson

16-Dec-13 1:10

Correct me if I'm wrong here (it's quite possible)
But assuming SQLServer, it will return a SQLDateTime value, as GetValue will only return dotnet types the SQLDateTime will be internally converted to a dotnet type.
The Operator for converting from SQLDateTime to DateTime is an Explicit Operator, and as far as I know this internal conversion will not use explicit operators, so it will be an implicit conversion to String instead.

The solution is to either use GetSQLValue or GetSQLDateTime ~~or GetDateTime.~~ <edit>Apparently not GetDateTime either.</edit>

Wrong is evil and must be defeated.
- Jeff Ello[^]

modified 16-Dec-13 7:50am.

Re: SQL Server date/time oddity.

Richard Deeming16-Dec-13 1:55

Richard Deeming

16-Dec-13 1:55

No, the value will not be converted to a string.

Tracing through the code with Reflector, the GetDateTime method returns the value of the SqlBuffer.DateTime property. The indexer calls the GetValue method, which returns the value of the SqlBuffer.Value property, which contains a switch block based on the type returned from the database. For the datetime type, it returns the value of the SqlBuffer.DateTime property.

GetSqlDateTime returns the value of the SqlBuffer.SqlDateTime property. Both SqlBuffer.SqlDateTime and SqlBuffer.DateTime construct the value from two integers returned by SQL. Neither property converts the value to a string.

It might be slightly more efficient to call GetDateTime if you know that the value is a datetime type, but the indexer and GetValue method will return exactly the same value.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SQL Server date/time oddity.

Jörgen Andersson16-Dec-13 3:11

Jörgen Andersson

16-Dec-13 3:11

Had to download Reflector to check this.

Also had to update my understanding about SqlDataReader. Tonight I'll eventually deepfry an author of an article, if I can find it back.

Sigh | :sigh:

Wrong is evil and must be defeated.
- Jeff Ello[^]

Re: SQL Server date/time oddity.

Richard Deeming16-Dec-13 0:04

Richard Deeming

16-Dec-13 0:04

How are you inspecting the value of the column? The debugger is probably using the default .ToString method, which will not display the full value. If you use the format string "yyyy-MM-dd HH:mm:ss.fff", you should see the full value.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Need to Covert This SQL database to MS Access Database, Can anybody help please

Kamal9614-Dec-13 2:47

Kamal96

14-Dec-13 2:47

Here is the the link of samples of ASP.NET's Membership, Roles, and Profile, where Displaying an Informative Message in the Face of an Invalid Login, but there has been used SQL database file, But I need as it is a MS Access Database file.
Can Anybody help please

http://www.4guysfromrolla.com/articles/050306-1.aspx[^]

Re: Need to Covert This SQL database to MS Access Database, Can anybody help please

PIEBALDconsult15-Dec-13 5:33

PIEBALDconsult

15-Dec-13 5:33

Please don't use Access; consider SQL Server Express or SQL Server CE.

Re: Need to Covert This SQL database to MS Access Database, Can anybody help please

Kamal9616-Dec-13 8:58

Kamal96

16-Dec-13 8:58

OK Good Suggestion, I tried and got success, Thanks

Select from one database table where column value equals a column value in a different database

moordoom13-Dec-13 5:52

moordoom

13-Dec-13 5:52

I am trying to pull all data from SQL Database A, Table 1 where column name is X, and the value in column X equals the value in Database B, Table 2, column name Y.

So I have 2 databases on the same server.
One named A, the other named B.
I need to get all the data from A, Table1, where the values of Column Name X are equal to the values in in Column Name Y in B, Table2, and insert/write them to a new table in Database B.

Any and all help is appreciated. My SQL writing skills are very sub par.

If the title, and the subject differ, I apologize. I am just trying to put the thought thru my head as I write.

Re: Select from one database table where column value equals a column value in a different database

GuyThiebaut13-Dec-13 6:06

GuyThiebaut

13-Dec-13 6:06

This is the general gist of where I think you need to be heading:

SQL

insert into B.NewTable
select a1.*
from A.Table1 a1
join B.Table2 b2
on a1.X = b2.Y

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Re: Select from one database table where column value equals a column value in a different database

moordoom13-Dec-13 8:13

moordoom

13-Dec-13 8:13

a1 and b2 are columns? what is X and Y?

Re: Select from one database table where column value equals a column value in a different database

GuyThiebaut13-Dec-13 8:52

GuyThiebaut

13-Dec-13 8:52

a1 and b2 are aliases for tables.

moordoom wrote:
what is X and Y?

They are the columns you mentioned in your question.
I think you need to read a book on sql or get some basic training, since if you cannot make sense of my answer you need to start reading.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Re: Select from one database table where column value equals a column value in a different database

joginder-banger15-Dec-13 8:05

joginder-banger

15-Dec-13 8:05

you can solve your problem with the help of SQL join(Inner Join).Sql Join on W3school

Re: Select from one database table where column value equals a column value in a different database

moordoom16-Dec-13 3:35

moordoom

16-Dec-13 3:35

Looking at the W3 school, it shows table to table joins, not database to database joins.

Here is what I have so far... (minus the insert, and it works)

SELECT * from Table1
join Database2.Table2
on ColumnX = ColumnY

X is a Column in Database1.Table1, and Y is a Column in Database2.Table2.

This gets me my info when I run the query on Database1.

But now I want write these info into Database2.Table3 and delete what was in there before.

So would my statement then be...??

DELETE * FROM Database2.Table3
INSERT INTO Database2.Table3
SELECT * from Table1
join Database2.Table2
on ColumnX = ColumnY

I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program)

sunil_david11-Dec-13 23:44

sunil_david

11-Dec-13 23:44

I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program)

Re: I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program)

Simon_Whale12-Dec-13 0:07

Simon_Whale

12-Dec-13 0:07

Is there any error message?

Is there any sample code that you can show?

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Re: I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program)

noblepaulaziz12-Dec-13 2:18

noblepaulaziz

12-Dec-13 2:18

I have rechecked and have realised that problem emanate from crystal report.

I have crystal report based report that uses Store Procedure with parameters(that includes date) as its datasource.

I realised that when computer is on US culture , the report displays but when I try to display the report in UK culture it raises exception.

I used the same Store Procedure for DataGridView in both cultures (ie US and UK) and it worked with same parameters that did not work in crystal report.

Re: I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program)

thatraja12-Dec-13 2:56

thatraja

12-Dec-13 2:56

You have replied to wrong message(also wrong thread). Get a Java | [Coffee]

thatraja

Code converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute

Re: I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program)

sunil_david17-Dec-13 19:57

sunil_david

17-Dec-13 19:57

Message: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method) at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at LanUpload.BntLan_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Re: I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program)

Simon_Whale17-Dec-13 22:13

Simon_Whale

17-Dec-13 22:13

Are you able to show the code where the exception occurs? I think its something to do with the number of connections that you create.

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

What Collation is for best for handling date Internationally?

noblepaulaziz11-Dec-13 13:35

noblepaulaziz

11-Dec-13 13:35

My app runs perfectly with MSSQL Server in client/server architecture when my computer is in US culture.

But I experience problem when I switched my computer to UK culture. My investigation revealed that it is the date fields in the test data which I created earlier in US culture that is creating the problem

My MSSQL Server collation is Latin1_General_CS_AI.

I want my app to allow for culture transparency at least for US and UK.

modified 11-Dec-13 19:52pm.

Re: What Collation is for best for handling date Internationally?

PIEBALDconsult11-Dec-13 16:22

PIEBALDconsult

11-Dec-13 16:22

It is to be hoped that you are storing dates as DATE or DATETIME, not as strings, so that should not be a problem.
If you are storing dates as string, I strongly reccommend you refactor and do it the right way.

Re: What Collation is for best for handling date Internationally?

noblepaulaziz11-Dec-13 21:55

noblepaulaziz

11-Dec-13 21:55

Thanks for the reply.

Yes I am storing data as DATE.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.