Multiple connection problem - Database Discussion Boards

SimonS15-Jan-03 23:49

15-Jan-03 23:49

Try and populate the combobox manually.
The delay may be in the data binding itself as painting a 50,000 element combox is very quick.

Cheers,
Simon

"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.

my svg article

How to use datetime fields in SQL server?

Luca Leonardo Scorcia15-Jan-03 2:55

Luca Leonardo Scorcia

15-Jan-03 2:55

Hi!
I want to do a simple SELECT on a table, returning the count of records containing a greater date than a certain date I write in the query expression.

Using Access, I'd simply write:
SELECT Count(1) As nMsg FROM Bacheca WHERE ([Date] > #2003/01/01 13:59:07#)

But that select statement returns an error when used in SQL Server, it says Syntax error near '13'. Can you tell me how to fix it?

And, I'd like to make that query work for both Access and SQL Server - is it possible?

Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)

Re: How to use datetime fields in SQL server?

David Wulff15-Jan-03 4:50

David Wulff

15-Jan-03 4:50

Use ' instead of # and treat it as a string, e.g.:

SELECT Count(1) As nMsg FROM Bacheca WHERE ([Date] > '2003/01/01 13:59:07')

This won't work in Access though, AFAIK you'll need to know before hand and use the correct query for each one. Can anyone confirm or deny that?

David Wulff
http://www.davidwulff.co.uk

Re: How to use datetime fields in SQL server?

Richard Deeming15-Jan-03 7:27

Richard Deeming

15-Jan-03 7:27

Try using the ODBC timestamp format. It works in SQL server, and it should work for Access via ODBC/OLEDB. (I haven't tested it, though.)

SELECT 
    Count(1) As nMsg 
FROM 
    Bacheca 
WHERE 
    ([Date] > {ts '2003-01-01 13:59:07'})

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

Re: How to use datetime fields in SQL server?

Luca Leonardo Scorcia15-Jan-03 10:10

Luca Leonardo Scorcia

15-Jan-03 10:10

Yep it works in SQL Server, but in Access it fails... he takes it as a GUID... BTW, this is my Access connection string, just in case... thanks for your effort anyway Smile | :)

adminDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("_private/db/skakkinostri.mdb")

Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)

How to detect that ADO Connction was failed

ernst.maurer15-Jan-03 2:21

ernst.maurer

15-Jan-03 2:21

How I can define that ADO Connection with MS SQL Server was failed before there are raised exceptions while ADO Command objects will run.

Re: How to detect that ADO Connction was failed

Mazdak15-Jan-03 19:31

Mazdak

15-Jan-03 19:31

Whats your code and when does it happend?

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Multiple connection problem

Hzi14-Jan-03 20:49

Hzi

14-Jan-03 20:49

I've an strange case where a second connection to a Sqlserver database hangs until time out has expired, the schema is below:

<br />
Connection1=New connection(database1)<br />
Connection1.Open<br />
Connection1.Begintrans<br />
...<br />
Connection2=New connection(database1)<br />
Connection2.Open<br />
Command2.Connection=Connection2<br />
Command2.ExecuteSelectionSQL   |-----------------------here it hangs<br />
Connection2.Close<br />
.......<br />
Connection1.Commit<br />
Connection1.Close<br />

The first connection remains opened and in transaction while connection2 is opened with the same querystring, it opens correctly but when I issue a command using connection2 it hangs.
I use System.Data.SqlClient and I've tried different options in querystring (Enlist=false,.....) but I cannot solve the problem.

Thanks for your help, Smile | :)

Hzi.

Re: Multiple connection problem

Anonymous15-Jan-03 20:23

Anonymous

15-Jan-03 20:23

seems like you have locking problems .. do you access the same tables ?
in both connections ?

Re: Multiple connection problem

Hzi15-Jan-03 20:27

Hzi

15-Jan-03 20:27

Yes, when I'm accessing the same table, it hangs, but when I access another table it not hangs, but SQLServer locks a whole table when I insert a single record ?.

Thanks

Re: Multiple connection problem

Hesham Amin15-Jan-03 20:32

Hesham Amin

15-Jan-03 20:32

No..
locking is not this simple..it differs according to how you access the table..
tell me the query so i can help !!

Re: Multiple connection problem

Hzi16-Jan-03 1:25

Hzi

16-Jan-03 1:25

Here is a trace of the steps shown in sqltrace tool:

<br />
with the first connection<br />
exec sp_reset_connection<br />
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION<br />
sp_reset_connection<br />
update objectes set [Caducitat]=NULL, [DataValid]=NULL, [UltimaMod]=CONVERT(DATETIME, '2003-01-16T13:18:00', 126) where id=100225<br />
with the second connection<br />
sp_reset_connection<br />
select * from objectes where idref=''BAINtiA000046'' and IM1=1'	<-------------It hangs here

It's all.

Thanks.

Re: Multiple connection problem

pushpi1-Feb-03 1:30

pushpi

1-Feb-03 1:30

either make ur query serialised or change lock type

P.S. PATWAL

Open XML and SQLServer

dturton14-Jan-03 10:46

dturton

14-Jan-03 10:46

Using Open XML can I join fields in XML document to field in my database via in a stored procedure, perform a check, and return a dataset/XML?

Thanks
David

Help Error "SELECT permission denied on object 'Contacts', database 'Contacts', owner 'dbo'. "

marnicola13-Jan-03 10:00

marnicola

13-Jan-03 10:00

Hi developing my WEB application win ASP.NET (C#)

i got this error message in my browser

SELECT permission denied on object 'Contacts', database 'Contacts', owner
'dbo'.

this is the code where it stopped

Line 30: sqlDataAdapter1.Fill(dataSet11);
Line 31: //Update the data grid
Line 32: DataGrid1.DataBind();

Now i finally i grant the access to the database for user ASPNET but it
seems not working yet.

I am running MSDE (Sql Desktop engine)
so i am using osql dos command and scripts to grant the accesses

What i am missing ?????

Thanks

Re: Help Error "SELECT permission denied on object 'Contacts', database 'Contacts', owner 'dbo'. "

Terry Denham13-Jan-03 10:22

Terry Denham

13-Jan-03 10:22

If you are logging into SQL Server with the ASPNET account you need to make sure that the login exists :

sp_helplogins

Verify that the ASPNET user account is created in the Contacts database

sp_helpuser

And that the uid of the ASPNET login is the same uid as the ASPNET user. (logins are to a server, users are to a database)

Then check the permission that the ASPNET user has against the table in question:

sp_helprotect

If you don't have sufficient permission, use the GRANT SELECT ON <object> TO <user>.

Determine column's datatype

Sarvesvara (BVKS) Dasa13-Jan-03 2:49

Sarvesvara (BVKS) Dasa

13-Jan-03 2:49

Is this possible... to determine the datatype of the column, in ACCESS database... like text/date/memo etc...

I was born intelligent
Education ruined me!.

Re: Determine column's datatype

leppie13-Jan-03 7:22

leppie

13-Jan-03 7:22

If you are using .NET i'm aware that the OleDbDataAdapter has a functio to retrieve the schema of the database.

WebBoxes - Yet another collapsable control, but it relies on a "graphics server" for dynamic pretty rounded corners, cool arrows and unlimited font support.

Re: Determine column's datatype

Sarvesvara (BVKS) Dasa13-Jan-03 19:43

Sarvesvara (BVKS) Dasa

13-Jan-03 19:43

Mine is MS Access database

I was born intelligent
Education ruined me!.

Re: Determine column's datatype

leppie14-Jan-03 6:24

leppie

14-Jan-03 6:24

SPS wrote:
Mine is MS Access database

I know that. Smile | :)

WebBoxes - Yet another collapsable control, but it relies on a "graphics server" for dynamic pretty rounded corners, cool arrows and unlimited font support.

Re: Determine column's datatype

quannum14-Jan-03 12:21

quannum

14-Jan-03 12:21

you could use ADOX which allows you to access an databases data definition language. I have some code which you could use, i'll post when i get into work.

"We don't understand the software, and sometimes we don't understand the hardware, but we can *see* the blinking lights!"

Re: Determine column's datatype

Sarvesvara (BVKS) Dasa14-Jan-03 19:52

Sarvesvara (BVKS) Dasa

14-Jan-03 19:52

I use the .type to extrract the type of the field in the database. It works fine....

Thanks to.. the UTE[^].. where I got this idea

I was born intelligent
Education ruined me!.

Skeletons in my SQL closet

Megan Forbes13-Jan-03 1:21

Megan Forbes

13-Jan-03 1:21

I have a query which returns a recordset for a statement which is displayed in ASP. It works very nicely - except for one thing. I suddenly noticed that although my

if rstDisplay.BOF or rstDisplay.EOF then - display error msg

else - display statement

works really well unless the firm has never had a single transaction (iow. if it has had transactions, just not within the current requested dates). The page then errors. After investigation I have found that it is because of fields sitting in the background which have been returned (eg. the firm name does exist in one of the tables, so that field is hiding in the background somewhere)

so the following returns:

Response.Write "state: " & rstDisplay.State -Displays "1"

Response.Write "count: " & rstDisplay.Fields.count -Displays "18"

so now I want to play with joins in query analyser. I am using @@rowcount to see how many rows are displayed, but don't know what to use to show the fields (like asp .fields.count). Can anyone enlighten me please?

Thanks Smile | :)

I knew it would end badly when I first met Chris in a Canberra alleyway and he said 'try some-it won't hurt you'... -Christian Graus on Code Project outages

His thoughts tumbled in his head, making and breaking alliances like underpants in a tumble dryer.
It hurt the way your tongue hurts after you accidentally staple it to he wall-Shaun Wilde

Re: Skeletons in my SQL closet

Jeremy Oldham13-Jan-03 1:32

Jeremy Oldham

13-Jan-03 1:32

Megan,

If you are using Query Analyzer, it should show you the number of rows returned in the lower right hand corner without having to use rowcount. Under the results tab, there should be a boxes that show the following:

General Message, Server Name, User Name, Database, Execution Time, Number of Rows, and Caret Position

Additionally, if you want to see the @@Rowcount Variable in Query Analyzer, you mut declare a variable, set the variable = @@Rowcount and then print the variable.

<br />
DECLARE @myRows Int<br />
Select * from myTable<br />
Set @myRows = @@RowCount<br />
PRINT @myRows<br />

Hope this helps.

Jeremy

Jeremy Oldham

Re: Skeletons in my SQL closet

Megan Forbes13-Jan-03 9:10

Megan Forbes

13-Jan-03 9:10

Hi,

Thanks - that's exactly the problem. The rowcount returned is 0. But if I click on the messages tab there appears to be an empty row there (I can go up to an empty line with my arrow keys). There is actually no row returned that meets my criteria, but info does exist that does meet some of the criteria, but not all (perhaps I should use a join instead of "and"?) and so the recordset is returned empty.

It's all very odd, but I'm probably just doing something silly.

Thanks again Smile | :)

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