Database Discussion Boards

tojamismis5-Feb-05 4:47

5-Feb-05 4:47

I can't think of anything else. It is true that you need these additional things if you aren't using a strongly typed dataset.

But that should be it.

Torin Blair

'In the immortal words of Socrates - "I drank what?".'

Re: i can't to insert a new row

tojamismis5-Feb-05 4:58

tojamismis

5-Feb-05 4:58

That is correct, without strongly typed datasets you should add this line right before you call myAdapter.Fill.

<br />
myAdapter.FillSchema(ds, SchemaType.Source, "MyTable");<br />

Torin Blair

'In the immortal words of Socrates - "I drank what?".'

Enterprise manger

Tom Wright4-Feb-05 5:50

Tom Wright

4-Feb-05 5:50

How come when I want to reset a date field to NULL my Enterprise manger locks up and I have to kill it?

This is after I have right clicked on a table and choose return all rows. Then I click on the date/time field and delete what was in there. Is there another way to do this besides running a SQL statement?

Thanks

Tom Wright
tawright915@yahoo.com

Re: Enterprise manger

David Salter4-Feb-05 6:15

David Salter

4-Feb-05 6:15

I've never seen it do that before. Is it a big table? Does it have the relevant indexes on it?

Database FAQ

Re: Enterprise manger

Tom Wright4-Feb-05 6:43

Tom Wright

4-Feb-05 6:43

small table. with only three records. We use it as a heartbeat table so that other apps know that my app is still working.

Davey wrote:
Does it have the relevant indexes on it?
Yeah.

It's strange. I might see if there is a SP for it.

Tom Wright
tawright915@yahoo.com

Re: Enterprise manger

tojamismis4-Feb-05 6:43

tojamismis

4-Feb-05 6:43

Have you tried Running a Query from enterprise manager on the table? You can get only the row or rows you need and then update from the results pane in the Query builder.

Torin Blair

'In the immortal words of Socrates - "I drank what?".'

Re: Enterprise manger

Tom Wright4-Feb-05 6:45

Tom Wright

4-Feb-05 6:45

yeah. If I do an update on that field, passing NULLS, it works fine. I just cannot click in that field, delete what's in there and click out of it. if I do it freezes up.

Tom Wright
tawright915@yahoo.com

Re: Enterprise manger

WoutL4-Feb-05 22:57

WoutL

4-Feb-05 22:57

Pressing Delete in Enterprise Manager is not the same as resetting to NULL. Delete will update the field with '' (Empty string)
If you want to update the field to NULL in Enterprise Manager press Ctrl-0

Wout Louwers

Extended Procedures question

Tom Wright4-Feb-05 5:45

Tom Wright

4-Feb-05 5:45

If I create and XP, and I want to connect to my
XP. How do I go about doing that? Will i do an execute from my app?

Right now I do all my selects and updates using a connection pointer like this:

m_pConnection->Execute(_bstr_t(mySelect),NULL, NULL);
Will I also connect to my XP this way? I'm making an XP that will alert me to a trigger in a table that I have set. So that any new record placed in there will alert me so that I do not have to loop every 3 seconds to see if there's new data.

Thanks

Tom Wright
tawright915@yahoo.com

Re: Extended Procedures question

David Salter4-Feb-05 6:13

David Salter

4-Feb-05 6:13

As far as TSQL is concerned, an XP is the same as a normal SP. If you can execute SPs using this method (I don't see why you can't), then you should be able to execute XPs also.

Database FAQ

Re: Extended Procedures question

Tom Wright4-Feb-05 6:46

Tom Wright

4-Feb-05 6:46

cool I'll try it. Just gotta write my XP and find out how to return a value. I'm going to assume that, that is the same as SP's too?

Thanks

Tom Wright
tawright915@yahoo.com

Re: Extended Procedures question

David Salter4-Feb-05 7:03

David Salter

4-Feb-05 7:03

I've written an article about XPs (http://www.codeproject.com/database/extended_sp.asp[^]). You may find this helpful.

Database FAQ

Really easy question!

goldoche4-Feb-05 3:50

goldoche

4-Feb-05 3:50

Hi everybody, I'm building my very first database application.

I have an Access database and I work with ADO with C# + Jet 4.0. I have 3 tables in my database.

For now, my dataAdapter is constructed like this, to work with only one table:

myDataAdapter = new OleDbDataAdapter("Select * From Table1", myOleDbConnection);

But now, I want my DataAdapter to work with my 3 tables... what should be my connection string?

"Select * From Table1, Table2, Table3" ??? Probably not...

Thanks!!!

Re: Really easy question!

SimonS4-Feb-05 4:44

SimonS

4-Feb-05 4:44

Your connection string won't change. A connection string is per database, not per table.

In order to return info from more than one table, you will need to use a JOIN.

Here's a SQL Query I am using for a product of mine - also MS Access:

SELECT Staff.FirstName, Staff.LastName, ClassSchedule.ClassName, ClassSchedule.Duration, Staff.ID
FROM (StaffClassSignIns INNER JOIN Staff ON StaffClassSignIns.StaffID = Staff.ID) INNER JOIN ClassSchedule ON StaffClassSignIns.ClassScheduleID = ClassSchedule.ID
WHERE (((StaffClassSignIns.ClassScheduleID)=[@ClassSchedule]));

Cheers,
Simon

sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.

article :: animation mechanics in SVG blog:: brokenkeyboards
"Most of us are programmers, but a few use VB", Christian Graus

Replication: Storing snapshot at FTP site.

RuchirD3-Feb-05 19:47

RuchirD

3-Feb-05 19:47

Can any one tell me how to configure FTP to save sanpshot.
I want to store snapshot files to be stored at the FTP site(which is m/c oth
er than the distributor/publisher)

Plz tell me detailed procedure of how to do this.

Thanks n Regards,
Ruchir

RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.

Re: Replication: Storing snapshot at FTP site.

tojamismis4-Feb-05 6:50

tojamismis

4-Feb-05 6:50

FTP has very little capability built into it. You won't be able to execute anything other than a file transfer or directory listing using it. You'll need to write a proc on the server to save a snapshot out of the database to a file. Then you'll need to write a set of code that does a put to the FTP site.

If you are looking to code part of this in .net you can look into the FTP library from edt
http://www.enterprisedt.com/downloads/csftp/csftp.html[^]

Hope that at least points you in the right direction.

Torin Blair

'In the immortal words of Socrates - "I drank what?".'

SQL 2000: Index manager hangs while adding Index

Chris Maunder3-Feb-05 11:15

Chris Maunder

3-Feb-05 11:15

I've got a problem. When adding an index to a table using either the Index manager or index tuning wizard the application hangs and no index is ever created. The table I'm trying to add the index too is in high demand so I figured it was some kind of "leave us alone - we're busy" kinda thing, but still, it's frustrating.

Anyone have experience or suggestions about this?

cheers,
Chris Maunder

Re: SQL 2000: Index manager hangs while adding Index

Mike Dimmick3-Feb-05 23:54

Mike Dimmick

3-Feb-05 23:54

I think you'll have to fall back on Query Analyzer and CREATE INDEX.

Alternatively you could ALTER DATABASE cp SET SINGLE_USER WITH ROLLBACK AFTER 60 but you will obviously get an outage!

The index build process has to inspect every row in the table on which it's defined, so it's going to take a while anyway. If you're trying to build a clustered index it will have to restructure the entire table, so be prepared for this to take a very long time.

If your tempdb is on a separate spindle, you may get better performance by using the SORT_IN_TEMPDB option. Without this option it sorts in the actual pages to be used for the index.

I admit that most of this information came from my copy of Inside SQL Server 2000!

Stability. What an interesting concept. -- Chris Maunder

Re: SQL 2000: Index manager hangs while adding Index

Chris Maunder4-Feb-05 0:23

Chris Maunder

4-Feb-05 0:23

Mike Dimmick wrote:
I think you'll have to fall back on Query Analyzer and CREATE INDEX

Tried that one. It's a small (8000 row) table, non-clustered. Hmmm.

cheers,
Chris Maunder

Re: SQL 2000: Index manager hangs while adding Index

Mike Dimmick4-Feb-05 5:53

Mike Dimmick

4-Feb-05 5:53

We've had some periodic problems at one of our customers' sites where apparently simple queries will appear to block forever, and we've not worked out why. Our solution to that is just to run sp_updatestats. I'm not sure why it works but it seems to.

Stability. What an interesting concept. -- Chris Maunder

Re: SQL 2000: Index manager hangs while adding Index

Chris Maunder4-Feb-05 17:02

Chris Maunder

4-Feb-05 17:02

Hmm - worth a shot. Thanks Mike.

cheers,
Chris Maunder

Creating a subset

numbrel3-Feb-05 9:12

numbrel

3-Feb-05 9:12

For a Windows form using VB.NET and an Access database of community events.

I have a DataAdaptor and DataSet that create a datatable with a row for each event. I also have a DataAdaptor and DataSet that create a datatable for sponsoring organizations.

I would like to have a combo box to choose the organization (bound to the organization table) and then populate the event combo box with a list of events sponsored by that organization. The user would then choose the event and the rest of the form controls would fill with the information for that event.

How would I create a subset of events based on the sponsoring organization? Do I create another DataAdaptor and DataSet through code to create an event table based on the organization chosen?

The books I have describe how to create DataAdaptor and DataSets through code, but once created, is there a way to destroy them and create others based on choosing another sponsoring organization?

Re: Creating a subset

Just Greeky Creek3-Feb-05 21:45

Just Greeky Creek

3-Feb-05 21:45

Do u mean that u got 2 combobox, 1st lists organisations, 2nd lists events sponsored by selected organisation in 1st ComboBox?

If so; I think u have two tables, one has organisations list, others has events then assume two table is related to each other with OrganisationID.

1. Warn = use Just ONE DATASET, if not, it s painful.

============================================================================

Imports System.Data.SqlClient<br />
Public Class frmOrganisationsAndEvents<br />
    Inherits System.Windows.Forms.Form<br />
<br />
    ' On Form There is two ComboBox named cmbOrganisations and cmbEvents<br />
<br />
    Dim cnOE As New SqlConnection("")<br />
    Dim dsOE As New DataSet<br />
    Dim dRowOE() As DataRow<br />
    Private Sub frmOrganisationsAndEvents_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load<br />
        Dim adpOE As New SqlDataAdapter("SELECT * FROM Organisations", cnOE)<br />
        adpOE.Fill(dsOE, "Organisations")<br />
        adpOE.SelectCommand.CommandText = "SELECT * FROM Events"<br />
        adpOE.Fill(dsOE, "Events")<br />
        For i As Short = 0 To dsOE.Tables("Organisations").Rows.Count - 1<br />
            cmbOrganisations.Items.Add(dsOE.Tables("Organisations").Rows(i)("O_OrganisationName"))<br />
        Next<br />
        If Not cmbOrganisations.Items.Count = 0 Then<br />
            cmbOrganisations.Text = "Select an Organisation"<br />
        Else<br />
            cmbOrganisations.Text = "No Organisation Installed"<br />
        End If<br />
    End Sub<br />
<br />
    Private Sub cmbOrganisations_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbOrganisations.SelectedIndexChanged<br />
        If Not cmbOrganisations.Items.Count = 0 And cmbOrganisations.SelectedIndex = -1 Then<br />
            cmbEvents.Items.Clear()<br />
         'Events SubSet <br />
            drowOE = dsOE.Tables("Events").Select("E_OrganisationID=" & dsOE.Tables("Organisations").Rows(cmbOrganisations.SelectedIndex)("O_OrganisationID"))<br />
            If Not dRowOE.Length = 0 Then<br />
                For i As Short = 0 To dRowOE.GetUpperBound(0)<br />
                    cmbEvents.Items.Add(dRowOE(i)("E_EventAlias"))<br />
                Next<br />
                cmbOrganisations.Text = "Select Event Sponsored by " & dsOE.Tables("Organisations").Rows(cmbOrganisations.SelectedIndex)("O_OrganisationName")<br />
            Else<br />
                cmbOrganisations.Text = "No Event Installed Sponsored by " & dsOE.Tables("Organisations").Rows(cmbOrganisations.SelectedIndex)("O_OrganisationName")<br />
            End If<br />
        End If<br />
    End Sub<br />
<br />
    Private Sub cmbEvents_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbEvents.SelectedIndexChanged<br />
        If Not cmbEvents.SelectedIndex = -1 Then<br />
            GetEventDetails(EventID:=dRowOE(cmbEvents.SelectedIndex)("E_EventID"))<br />
        End If<br />
    End Sub<br />
    Protected Sub GetEventDetails(ByVal EventID As Integer)<br />
        'Read Details From Database<br />
    End Sub<br />
<br />
    Private Sub cmbOrganisationsAndcmbEvents_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cmbOrganisations.KeyPress, cmbEvents.KeyPress<br />
        e.Handled = True<br />
    End Sub<br />
End Class

INNER join question

Imtiaz Murtaza3-Feb-05 1:36

Imtiaz Murtaza

3-Feb-05 1:36

Friends,
I have two tables A and B, the id is the primary key field of table A and is a foreign key of table B. There is one-to-many relation b/w the tables. It means that id can repeated in second table many times.

I am using inner join to join the tables on the basis on id. As such the result is that: for each id of table A, all matching id records of table B gets printed. So far, so simple and so good.

Now the problem is that in the result set, same id is repeated many times because there is one-to-many relationship b/w tables. What i want is that in result set each id should appear only once. How can i do so ???

Imtiaz

Re: INNER join question

Colin Angus Mackay3-Feb-05 2:04

Colin Angus Mackay

3-Feb-05 2:04

Imtiaz Murtaza wrote:
b/w

I'll assume this means "between". Please use only standard abbreviations - it helps make your post much more readable.

Imtiaz Murtaza wrote:
Now the problem is that in the result set, same id is repeated many times because there is one-to-many relationship b/w tables

That is correct. Performing an INNER JOIN is effectively creating a resultset in a denormalised form.

Imtiaz Murtaza wrote:
What i want is that in result set each id should appear only once. How can i do so ???

The question I have is: why? I cannot see how that is beneficial. Unless what you really want is the table on the many side aggregated into only one row.

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

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