Enterprise Library Data access application block 2.0 - Database Discussion Boards

First Prev Next

Re: sql server 2000 millisecond issue

Pete O'Hanlon16-Mar-07 2:15

Pete O'Hanlon

16-Mar-07 2:15

Deadlocking does not occur because you have a datetime. It occurs because you have two resources which are locking each other.

Deja View - the feeling that you've seen this post before.

a loop that increments a value in the selection clause?

Red_Wizard_Shot_The_Food15-Mar-07 2:30

Red_Wizard_Shot_The_Food

15-Mar-07 2:30

I’m trying to make a loop that will select a count of all entries that have a "TimeIn" value on the date defined by an input parameter with an hour value between x & y.

So the first pass through the loop would select a count of all rows with a TimeIn between 00:00:00 and 01:00:00 on the selected day ...

The second pass will select a count of all rows with a TimeIn between 01:00:00 and 02:00:00.

finally it will return counts for each hour in the day.

This is my first attempt at loops in SQL so any push in the right direction would be much appreciated.

Thanks.

Re: a loop that increments a value in the selection clause?

Marcus J. Smith15-Mar-07 3:00

Marcus J. Smith

15-Mar-07 3:00

There is a better way to do this.

SELECT 
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '00:00:00' AND '01:00:00') AS 'Time1',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '01:00:00' AND '02:00:00') AS 'Time2',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '02:00:00' AND '03:00:00') AS 'Time3',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '03:00:00' AND '04:00:00') AS 'Time4',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '04:00:00' AND '05:00:00') AS 'Time5',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '05:00:00' AND '06:00:00') AS 'Time6',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '06:00:00' AND '07:00:00') AS 'Time7',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '07:00:00' AND '08:00:00') AS 'Time8',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '08:00:00' AND '09:00:00') AS 'Time9',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '09:00:00' AND '10:00:00') AS 'Time10',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '10:00:00' AND '11:00:00') AS 'Time11',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '11:00:00' AND '12:00:00') AS 'Time12',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '12:00:00' AND '13:00:00') AS 'Time13',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '13:00:00' AND '14:00:00') AS 'Time14',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '14:00:00' AND '15:00:00') AS 'Time15',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '15:00:00' AND '16:00:00') AS 'Time16',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '16:00:00' AND '17:00:00') AS 'Time17',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '17:00:00' AND '18:00:00') AS 'Time18',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '18:00:00' AND '19:00:00') AS 'Time19',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '19:00:00' AND '20:00:00') AS 'Time20',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '20:00:00' AND '21:00:00') AS 'Time21',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '21:00:00' AND '22:00:00') AS 'Time22',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '22:00:00' AND '23:00:00') AS 'Time23',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '23:00:00' AND '24:00:00') AS 'Time24'
FROM TableName

_Cle^aKO

"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)

Re: a loop that increments a value in the selection clause?

andyharman15-Mar-07 5:49

andyharman

15-Mar-07 5:49

If you have SQL-Server then

select convert(varchar(13), TimeIn, 120) + ':00', count(*)
from MyTable
where TimeIn between @x and @y
group by convert(varchar(13), TimeIn, 120) + ':00'
order by 1

SQL is best for set operations. You can do loops and cursors but they tend to be slower and are harder to code.

Regards
Andy

Re: a loop that increments a value in the selection clause?

Marcus J. Smith15-Mar-07 6:10

Marcus J. Smith

15-Mar-07 6:10

Ohh so that is better than mine? Cool... Cool | :cool:

Re: Connecting SQL Server Express 2005 to remote database file

BRShroyer15-Mar-07 2:27

BRShroyer

15-Mar-07 2:27

I sort of figured that it wouldn't work. It would be a shame for something to be easy once.

Does linking the SQL Servers only work if they are on the same machine? If not, could you give some keywords I can do some searching with. There's no reason to be humble about my ignorance now Smile | :)

Thanks for your help.

OT: I see you are from Glasgow. Any ideas for some good day trips for someone visiting? I've been there twice last year for work and will going back at least once this year.

Brad

The secret to creativity is knowing how to hide your sources. - Albert Einstein

Re: Connecting SQL Server Express 2005 to remote database file

Colin Angus Mackay15-Mar-07 5:33

Colin Angus Mackay

15-Mar-07 5:33

BRShroyer wrote:
Does linking the SQL Servers only work if they are on the same machine?

You can link any SQL Servers together.

BRShroyer wrote:
could you give some keywords I can do some searching with

Your starter for 10: sp_addlinkedserver[^]

BRShroyer wrote:
I see you are from Glasgow. Any ideas for some good day trips for someone visiting?

Loch Lomond, Isle of Arran (aka Scotland in Miniature), Edinburgh (45 mins on the train), Falkirk Wheel.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Enterprise Library Data access application block 2.0

saltcode15-Mar-07 1:36

saltcode

15-Mar-07 1:36

Hi guys,
Please help,I am having an issue with data access block connection to the database from my class library project.
using the data access block to connect to database works fine in my website project. but i try creating a separate class library project to connect to the database i now receive an error message.

"The value can not be null or an empty string."

and this is the code:

<br />
Database motomandb = DatabaseFactory.CreateDatabase();<br />
DbCommand motomandbCommand = motomandb.GetSqlStringCommand(query);<br />
<br />
using(DbConnection motomanConnection = motomandb.CreateConnection())<br />
{<br />
DbTransaction motomanTransaction = motomanConnection.BeginTransaction();<br />
try<br />
{<br />
rowsaffected = motomandb.ExecuteNonQuery(motomandbCommand, motomanTransaction);<br />
motomanTransaction.Commit();<br />
}<br />
catch (Exception e)<br />
{<br />
motomanTransaction.Rollback();<br />
}<br />
}<br />

thanks in advance

how can i store jpg files in sql or oracle (blob)

Member 392135215-Mar-07 1:22

Member 3921352

15-Mar-07 1:22

any one here for giving me help that i get a problem to store store jpg files in sql or oracle (blob).

please Frown | :(

i am student of ignou .

Re: how can i store jpg files in sql or oracle (blob)

coolestCoder15-Mar-07 2:16

coolestCoder

15-Mar-07 2:16

Please post the complete error / problem details.

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

coolestCoder

Re: how can i store jpg files in sql or oracle (blob)

Rocky#15-Mar-07 6:18

Rocky#

15-Mar-07 6:18

here's the link for u. read it and follow the procedure.
http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp[^]

Rocky

Saving File in Oracle BLOB datatype directly from client location.

Ram Shankar14-Mar-07 17:20

Ram Shankar

14-Mar-07 17:20

I am making an control which saves xls file in database attached by user. file size will be of 3 MB and there will be 3 such files. Can someone please help me and tell me how can i directly save the file in database rather then saving it in some location on server.

Ram shankar tripathi
Software Engineer
Telstra
Australia

Correct deleting from MS SQL

turtle_box14-Mar-07 11:09

turtle_box

14-Mar-07 11:09

I have the next problem: in the table with the primary key field "ID" (it is identity with automatic incrementation) when I delete row, the row isn't completely removed - the ID field remain (although it looks like row was removed) - and as a result, next created row has ID wich is equal to ID of previous deleted row + 1. How to delete row from table with autoincrement primary key correctly?

...fear makes people manageable...

Re: Correct deleting from MS SQL

kubben14-Mar-07 12:14

kubben

14-Mar-07 12:14

When you have an identity column you do not get deleted id's back. If you delete all rows from the table and want to reset the identity back to a value you need to do this:
DBCC CHECKIDENT('TableName', RESEED, 1)

Hope that helps.
Ben

Re: Correct deleting from MS SQL

N a v a n e e t h15-Mar-07 1:20

N a v a n e e t h

15-Mar-07 1:20

Or you can force SQL server to insert values to identity column when you perform insertion..

check this

http://www.w3hearts.com/showsnippet.aspx?show=26[^]

string Cheers = "Navaneeth!!"

www.w3hearts.com

Connecting SQL Server Express 2005 to remote database file

BRShroyer14-Mar-07 10:18

BRShroyer

14-Mar-07 10:18

Noob question:

If I have several (4-5) computers with SQL Server 2005 Express on them, can they all access the same database file located on a remote/network drive? Would they be able to access it at the same time?

Brad

The secret to creativity is knowing how to hide your sources. - Albert Einstein

Re: Connecting SQL Server Express 2005 to remote database file

Colin Angus Mackay14-Mar-07 23:31

Colin Angus Mackay

14-Mar-07 23:31

No.

SQL Server opens the file exclusively and controls it completely. Only one instance of SQL Server can open a database file. Also, the files must be on the same physical machine as the SQL Server.

However, you can link SQL Servers together so they can share the data. The SQL Server that owns the file will control all access to the data.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Synchronizing Sql 2000 database. Need help.

JustmeNick14-Mar-07 6:51

JustmeNick

14-Mar-07 6:51

Hey everyone,

I am working on this project, mostly database driven.
this is what it is about:- The user interface is install on three computer in different location while the database is kept on another computer in a seperate location, which means all three computers has to connected to this sql server to enter and retrive data. That means if the server goes down.. neither of these location will be able to continue there work.

What i am thinking about now, is to install sql server on all three machine so that data is stored at each location and at the end of each day all the data from the three location would be uploaded or Synchronized to the main database.

Problem.. how do i get all three database to Synchronize with the main database? Do i have to hard code this to happen or is there a feature that does this in sql 2000...

your help would be highly appreciated...

thanks.

Nab

Re: Synchronizing Sql 2000 database. Need help.

Hayder Marzouk16-Mar-07 3:42

Hayder Marzouk

16-Mar-07 3:42

Hi,
U can use the Replication feature built in SQL Server 2000. Here a goog article about it :
http://www.devarticles.com/c/a/SQL-Server/Replication-SQL-Server-2000--Part-1/[^]

HTH

Problem generating a typed dataset with xsd schema [modified]

goldoche14-Mar-07 4:20

goldoche

14-Mar-07 4:20

Hi, I am trying to generate a typed dataset with an xsd schema, but I seem to do it only half right.

In one application, I read my MySql database (4 data tables) into a simple dataset, I define the relations and I export it with the WriteXmlSchema method. This works fine.

Then, I import my newly created xsd file to another project, in which I want to create a typed dataSet. In this project, I'm able to view my schema, and when I right-click and select Preview DataSet, everything looks great, I have my 4 tables and all the Properties are shown.

However, when I select Generate DataSet and go write some code, I only have one table for which a FindBy method has been generated. No property is defined in my other 3 tables.

Can somebody please help me, this is driving me nuts!
Thanks!

-- modified at 10:27 Wednesday 14th March, 2007

Regarding saving and deleting record

roma_sayed14-Mar-07 1:29

roma_sayed

14-Mar-07 1:29

Hi,
I am doing a project that involves Ms Access database connectivity in vb.net.I can do the insertion and updation of record by the code as mentioned below:

Imports System.Data.OleDb
Public Class Form2 Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim icount As Integer
Dim str As String

Private Sub Add_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button2.Click
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;")
cn.Open()
str = "insert into table1 values(" & CInt(TextBox1.Text) & ",'" & TextBox2.Text & "','" &_
TextBox3.Text & "')"
'string stores the command and CInt is used to convert number to string
cmd = New OleDbCommand(str, cn)
icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
'displays number of records inserted
Catch
End Try
cn.Close()
End Sub
End Class

Updation of records

Me.BindingContext(ds, "Employee").EndCurrentEdit()
Me.da.Update(ds)

But I cannot do the saving and deletion of record.I would be very thankful if anybody can help me out in this difficulty.If possible plz provide me the code.
Thanking You.

Romana Sayed

Re: Regarding saving and deleting record

N a v a n e e t h14-Mar-07 4:07

N a v a n e e t h

14-Mar-07 4:07

Make sure that you have dataadapter update command specified. This might prevent updation using dataset

string Cheers = "Navaneeth!!"

www.w3hearts.com

Re: Regarding saving and deleting record

roma_sayed14-Mar-07 20:30

roma_sayed

14-Mar-07 20:30

Hi ,
If u can plz provide me the code for click event of a button(ie save or delete).I am not able to delete record from an access database using OleDb provider.

SQL Server 2005 Compact Edition (3.1)

Dmitry Ivanov14-Mar-07 0:42

Dmitry Ivanov

14-Mar-07 0:42

Hi,

I'm trying to experiment with the newest version of former 'sql everywhere' and found the following problem (the way to reproduce in VS 2005, SP1):
1) in 'server explorer' I made new database (sdf file) with a couple of tables an some columns
2) new c++/cli project, trying to add new datasource project
3) select table(s) in the wizard ... everything guess goes well ...
4) on the stage of creating dataset:

Creating DataSet class using XSD ...
Writing file 'c:...DataSet.h'.
Error: There was an error processing '.\...DataSet.xsd'.
- Error generating code for DataSet ''.
- Failed to generate code. Failed to find or load the registered .Net Framework Data Provider. Failed to find or load the registered .Net Framework Data Provider. <---- ?!?!?!

Oops, dataset.h file is absolutely empty Frown | :(

Has anybody experienced same problems?

Dmitry

Table Permissions

N a v a n e e t h14-Mar-07 0:32

N a v a n e e t h

14-Mar-07 0:32

Hello

I have one table say table1. I want to restrict deletion and updation for this table. Any idea ? I use SQL Server 2000

string Cheers = "Navaneeth!!"

www.w3hearts.com

Last Visit: 31-Dec-99 18:00 Last Update: 28-Apr-24 15:08

Refresh

ᐊ Prev 1...1632 1633 1634 163516361637 1638 1639 1640 1641 Next ᐅ

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