Re: Mirror changed DataTable to old DataTable (ADO.NET) - Database Discussion Boards

Have a look at this http://msdn.microsoft.com/en-us/library/ms345106(SQL.90).aspx[^]

The need to optimize rises from a bad design.My articles[^]

Can we have Excel like cell merging in SQL? I am looking for something like this:

USER ID	    OTHER FIELDS
	    LoginID
816	    FirstName
	    LastName

C isn't that hard: void (*(*f[])())() defines f as an array of unspecified size, of pointers to functions that return pointers to functions that return void

"Always program as if the person who will be maintaining your program is a violent psychopath that knows where you live." - Martin Golding

You could do this with embedded line feeds and concatenated strings. It is not what SQL is all about, SQL stores and delivers the data, your presentation layer should be where you format it for visual consumption.

Never underestimate the power of human stupidity
RAH

What I am trying to achieve is to export the result set to an excel file and then send that as an attachement in email. I have to do this using SQL only.

I already have my export stored procedure working. So I need to format the data as mentioned. Hence I was wondering if this can be done in SQL.

C isn't that hard: void (*(*f[])())() defines f as an array of unspecified size, of pointers to functions that return pointers to functions that return void

"Always program as if the person who will be maintaining your program is a violent psychopath that knows where you live." - Martin Golding

This will embedd the line feed, don't know what it will be like in Excel

DECLARE @LF CHAR(2)<br />
<br />
SET @LF = CHAR(10)<br />
<br />
SELECT ContactID,<br />
	FirstName + @LF + LastName + @LF + EmailAddress OtherFields<br />
FROM Person.Contact

Output to text instead of grid in QA to see the line feed

Never underestimate the power of human stupidity
RAH

I am having trouble making changes to a DataSet. Here is my situation...

What i have is a DataSet with several tables and all their respective columns loaded from database.

At some point during the program execution a new dataadapter and table are created based a SQL query (to access the same database spoken of above). I then make specific changes to the table/rows. While using the Update method and a OleDbCommandBuilder the changes are saved to the database.

The problem is I want to now update the original dataset with Data now changed.

I tried running the Fill command of the new DataAdapter and reference the old DataSet but this does not work when rows are being modified. Oddly it does work when a row has been added?

I have tried various methods such as BeginEdit of the row and AcceptChanges, but perhaps I am not using them correctly?

Can you help, please?

Thanks You!!

David Hovey wrote:
I tried running the Fill command of the new DataAdapter and reference the old DataSet but this does not work when rows are being modified

Are you getting an exception? If so, please include info about the exception.

David Hovey wrote:
I have tried various methods such as BeginEdit of the row and AcceptChanges, but perhaps I am not using them correctly

It would be a whole lot easier if you could post the code part that's not working, including the refill portion.

The need to optimize rises from a bad design.My articles[^]

I was reluctant to post code since it might not make a lot of sense. Here it is though. Toward the end of EditCongregation is where I want to update m_dsData with the modified DataRow...

Thanks again.

Private m_dsData As DataSet
Public Sub LoadData()

    'MsgBox("Legacy function LoadData() called.")
    Dim dbDataAdapter As OleDbDataAdapter
    dbDataAdapter = New OleDbDataAdapter()

    Try
        m_dsData = Nothing
        m_dsData = New DataSet

        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Congregations", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "Congregations")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM HomeTalks", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "HomeTalks")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM CongregationSpeakers", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "CongregationSpeakers")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM CongregationSpeakerOutlines", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "CongregationSpeakerOutlines")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Outlines", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "Outlines")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM OutlineCategories", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "OutlineCategories")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Readers", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "Readers")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Chairmen", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "Chairmen")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Hospitality", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "Hospitality")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM AwayTalks", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "AwayTalks")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Notes", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "Notes")
        dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Conductors", m_dbConnection)
        dbDataAdapter.Fill(m_dsData, "Conductors")

        dbDataAdapter.Dispose()
    Catch ex1 As Data.DataException
        MsgBox("A data exception occured while loading data." & vbCr & "Description: " & ex1.Message, MsgBoxStyle.Exclamation)

        m_dbConnection.Close()
        m_dbConnection.Dispose()
        DbDataAdapter.Dispose()
    Catch ex2 As System.Exception
        MsgBox("A system exception occured while loading data." & vbCr & "Description: " & ex2.Message, MsgBoxStyle.Exclamation)

        m_dbConnection.Close()
        m_dbConnection.Dispose()
        DbDataAdapter.Dispose()
    End Try

End Sub
Public Function EditCongregation(ByVal rCongo As Congregation) As Notification

    Dim da As OleDbDataAdapter ', daSpeakers As OleDbDataAdapter
    Dim dt As New DataTable
    Dim dr As DataRow
    Dim commandBuilder As Data.OleDb.OleDbCommandBuilder
    Dim drCongos() As DataRow

    'Dim dtSpeakers As New DataTable

    drCongos = m_dsData.Tables("Congregations").Select("CongregationName = '" & rCongo.Name & "'")

    If UBound(drCongos) > -1 Then
        If UBound(drCongos) > 0 Then
            Return Notification.SevereError
        End If

        If Not drCongos(0).Item("ID") = rCongo.RecordID Then
            Return Notification.CongregationAlreadyExist
        End If
    End If

    'da = New OleDbDataAdapter("SELECT ID, CongregationName, KingdomHallAddress, Coordinator, CoordinatorHome, " & _
    '    "CoordinatorMobile, CoordinatorEmail, MeetingDay, MeetingTime, KingdomHallPhone FROM Congregations " & _
    '    "WHERE ID = " & rCongo.RecordID, m_dbConnection)
    da = New OleDbDataAdapter("SELECT ID, CongregationName, KingdomHallAddress, Coordinator, CoordinatorHome, " & _
        "CoordinatorMobile, CoordinatorEmail, MeetingDay, MeetingTime, KingdomHallPhone FROM Congregations", m_dbConnection)
    da.Fill(dt)
    'da = New OleDbDataAdapter("SELECT ID, CongregationName, KingdomHallAddress, Coordinator, CoordinatorHome, " & _
    '   "CoordinatorMobile, CoordinatorEmail, MeetingDay, MeetingTime, KingdomHallPhone " & _
    '    "FROM Congregations", m_dbConnection)
    'da.Fill(dt)

    commandBuilder = New OleDbCommandBuilder(da)

    drCongos = dt.Select("ID = " & rCongo.RecordID)

    'daCongo = New OleDbDataAdapter("SELECT CongregationName, Speaker, Coordinator, SpeakerHome, " & _
    '    "SpeakerMobile, SpeakerEmail, MeetingDay, MeetingTime, KingdomHallPhone FROM CongregationSpeakers " & _
    '    "WHERE CongregationName = '" & strName & "'", m_dbConnection)
    If UBound(drCongos) = -1 Then
        EditCongregation = Notification.CongregationNotFound
    ElseIf UBound(drCongos) > 0 Then
        EditCongregation = Notification.SevereError
    Else
        dr = drCongos(0)

        dr.Item("CongregationName") = rCongo.Name
        dr.Item("Coordinator") = rCongo.Coordinator
        dr.Item("CoordinatorEmail") = rCongo.CoordinatorEmail
        dr.Item("CoordinatorHome") = rCongo.CoordinatorHome
        dr.Item("CoordinatorMobile") = rCongo.CoordinatorMobile
        dr.Item("KingdomHallAddress") = rCongo.KingdomHallAddress
        dr.Item("KingdomHallPhone") = rCongo.KingdomHallPhone
        dr.Item("MeetingDay") = rCongo.MeetingDay
        dr.Item("MeetingTime") = rCongo.MeetingTime

        da.Update(dt)

        m_dsData.Tables.Remove("Congregations")
        da.Fill(m_dsData, "Congregations")

        EditCongregation = Notification.Successful
    End If

End Function

David Hovey wrote:
da.Fill(m_dsData, "Congregations")

I take it, that's the point where you expect the datatable (Congregations) to refresh. Are you getting an exception or what are the symptoms?

The need to optimize rises from a bad design.My articles[^]

Sorry forgot to clarify...
No exception occurs. It is very odd. I feel like I'm doing something wrong that is dumb and simple.

No exception occurs and after running the Fill method, I've tested the table to see if a value is updated and it is...But then. After routine is finished the value is back to previous.

Do I need to set a property for run a method on the table or dataset to allow the rows to update?

The way I can fix it is by delete the table altogether and then using the Fill method. That obviously works.

But in other circumstances the new table may not contain that columns that existed before.

Okay, some guesses:
Have you noticed this on documentation:
When using subsequent Fill calls to refresh the contents of the DataSet, two conditions must be met:
1. The SQL statement should match the one initially used to populate the DataSet.
2. The Key column information must be present. If primary key information is present, any duplicate rows are reconciled and only appear once in the DataTable that corresponds to the DataSet. Primary key information may be set either through FillSchema, by specifying the PrimaryKey property of the DataTable, or by setting the MissingSchemaAction property to AddWithKey.

Also I'm not sure what's the status of each row after refresh so after refilling the dataset you could have a look with the debugger and if the states are modified, accept changes after fill (on the whole dataset). Somehow the behaviour sounds like the changes might have been rejected somewhere in the other parts of the code.

The need to optimize rises from a bad design.My articles[^]

Thanks for your help Mika.

You were right all along. There were a couple things I was doing wrong during my debugging to figure this out. But in the end Number 2 of your last post was the problem.

I didn't think the PrimaryKey was the problem because when I open the database in Access 2007 (using 2007 version database) it shows my column ID as the primary key. This is the column that Access automatically created when I first created the tables.

Shouldn't the OleDbDataAdapter read this information about the primary key??

Thanks again!

David Hovey wrote:
Thanks for your help

No problem.

David Hovey wrote:
Shouldn't the OleDbDataAdapter read this information about the primary key

When using just Fill-method it won't read anything else than basic properties (column names, data types for columns etc). If you want to have a data table which corresponds to the schema in Access, I think when creating the table for the first time, you should do something like this (before the first fill):

da.FillSchema(m_dsData, SchemaType.Mapped);

The need to optimize rises from a bad design.My articles[^]

Hello Friends,

I wrote a query for a purpose like this;I have a field by name "CustomerName".I want it to be split as FirstName and LastName as shown below:

CustomerName FirstName LastName

Jack Daniels Jack Daniels
Jack,Daniels Jack Daniels

My query looks like this;

select substring(customername,1,CHARINDEX(',', customername) -1) as [firstname ],
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) AS [LastName]

from TableName

My Problem is i have two conditions the splitter could be "," or "" in the customername field

I need to integrate this conditions to my query either using "case" or "iif".
ie if the charindex is " " do this and if the charindex is ", " do this

I am not sure how i can do this.Any help is welcome

Use CASE[^] and test if CHARINDEX for comma or space is greater than 0 and based on that use SUBSTRING to split the string. Something like:

... CASE
          WHEN CHARINDEX(',', customername) > 0 THEN SUBSTRING(customername,1, CHARINDEX(',', customername) -1)
          ELSE SUBSTRING(customername,1,CHARINDEX(' ', customername) -1)
    END AS FirstName ....

The need to optimize rises from a bad design.My articles[^]

Hi Mika,

I modified my query like this;It still is not working Frown | :(

select customername,

case when CHARINDEX(',', customername)>0 then

substring(customername,1,CHARINDEX(',', customername) -1) as [firstname ],
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) AS [LastName]

else

substring(customername,1,CHARINDEX(',', customername) -1) as [firstname ],
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) AS [LastName]

from TableName

Trustapple wrote:
It still is not working

Getting and error message?

I think it should be more like (may contain several typos):

select customername,
case 
   when CHARINDEX(',', customername)>0 then
      substring(customername,1,CHARINDEX(',', customername) -1)
   else 
      substring(customername,1,CHARINDEX(' ', customername) -1) 
end as [firstname ],
case 
   when CHARINDEX(',', customername)>0 then
      SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) 
   else 
      SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername)) 
end AS [LastName]
from TableName

The need to optimize rises from a bad design.My articles[^]

Hey Mika,

Thanks for you trying to help.My server i unavailable now.I will be able to access it only after 5hours.As soon as i try thi s query i will post a reply and let you know.

Many Thanks for your help.... Smile | :)

Merry Christmas

Trustapple wrote:
Thanks for you trying to help

No problem Smile | :)

Trustapple wrote:
I will be able to access it only after 5hours.As soon as i try thi s query i will post a reply and let you know

At that time it's 3:30 AM here so I'll be Sleepy | :zzz:

But I'll have a look at your reply in the morning. If you encounter problems I believe that someone else will help you forward in the meantime.

Trustapple wrote:
Merry Christmas

Merry Christmas to you too Smile | :)

The need to optimize rises from a bad design.My articles[^]

Hey Mika,

Thanks again to you and Ben....It worked.I made slight modification in the query though...a very slight one.
The credit goes to you guys ....

Please see the working query and let me know if any tweaking is necessary...becuase i will be cghnaging this select to update query for around 9million records..... Smile | :)

select customername,
-- get the last name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter

when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
else
'' -- neither comma nor space found
end as [lastname],
-- get the first name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then

SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
else
'' -- neither comma nor space found
end AS [firstname]
from TableName

Hey Mika,

The time now is 7:59 AM for you,i beleive....Happy sleeping Smile | :)

.

Thanks again to you and Ben....It worked.I made slight modification in the query though...a very slight one.
The credit goes to you guys ....

Please see the working query and let me know if any tweaking is necessary...becuase i will be cghnaging this select to update query for around 9million records..... Smile | :)

select customername,
-- get the last name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter

when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
else
'' -- neither comma nor space found
end as [lastname],
-- get the first name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then

SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
else
'' -- neither comma nor space found
end AS [firstname]
from TableName

I think it should be:

select customername,
-- get the last name
case
    when CHARINDEX(',', customername) > 0 then
      SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
    when CHARINDEX(' ', customername) > 0 then
      SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
    else
      '' -- neither comma nor space found
end as [lastname],
-- get the first name
case
    when CHARINDEX(',', customername) > 0 then
      SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
    when CHARINDEX(' ', customername) > 0 then
      SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
    else
      '' -- neither comma nor space found
end AS [firstname]
from TableName

Keep It Simple Stupid! (KISS)

Yeah, you're rught. I misplaced the commas. Corrected now.

Ben Fair wrote:
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter

Although it's 'cleaner' to use the rest of the length as a parameter, it's not necessary. Basically if you want the rest of the string you can pass any value as long as it's at least the rest of the length.

The need to optimize rises from a bad design.My articles[^]

Oh, I wasn't aware of that; I'll have to remember that! Thanks!

Keep It Simple Stupid! (KISS)

Hey ,

Thanks a lot to you and Mika it worked.

You guys have been wonderful.

Hey i ran into another problem....

I might need to integrate one more condition;ie along with our query i want it to also do this that is if the customername is Mika (ie the name is only one word and it doesnot contain any ',' or ' ')Then it should update to FirstName.....Am pasting the working query below can any of you integrate this to my query.....

Name FirstName LastName

Mika Mika

Ben Ben

QUERY:update TableName
set lastname=
-- get the last name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
else
'' -- neither comma nor space found
end,
-- get the first name
firstname = case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
else
'' -- neither comma nor space found
end

modified on Thursday, December 11, 2008 2:23 AM