|
cbenan wrote: while adding a new datafiled in dataset and i kept the expression field as rownumber("dataset")..it automatically closes the application
I don't understand this. If you simply add a column to the datatable and put the numbers in the rows, you would have the data you want when launching the report. I don't see how this would close the application unless you have an error.
cbenan wrote: suggest me any other solution to keeppa autonumber in report and i want to keep tat filed in grouping row
That was the only solution that came into my mind.
|
|
|
|
|
Hi
I am developing a website that will have an advertising bussiness model, I am wondering if I should store flash animated adverts in the DB, I think that storing them as files will greatly reduce the load on the DB and allow it to serve more people -> more efficient design decision.
Am I right with this or do you think otherwise? Please explain your answer.
Thaaaaaaaaanks alot
And ever has it been that love knows not its own depth until the hour of separation
Mohammad Gdeisat
|
|
|
|
|
Mohammad A Gdeisat wrote: if I should store flash animated adverts in the D
Few considerations:
- are the advertisements changed often
- does the changing need to be transactional
- do you want to have a backup of your advertisements when you backup the database
Based on those questions you could decide whether to use db or not.
Mohammad A Gdeisat wrote: storing them as files will greatly reduce the load on the DB and allow it to serve more people
They still got to be stored somewhere and propably be backed up. Depending on your configuration and layers, the database may not be any slower than the file system.
|
|
|
|
|
Hi,
Thanks for the answer. About these considerations:
1. are the advertisements changed often? Well, I do not expect them to change so often, it is a typical website, companies are expected to buy several thousands of ad impressions that will last for sometime, new companies may buy as time passes.
2. does the changing need to be transactional? No, at all.
3. do you want to have a backup of your advertisements when you backup the database? No, not necessary at all.
Mika Wendelius wrote: They still got to be stored somewhere and propably be backed up. Depending on your configuration and layers, the database may not be any slower than the file system
Well, they have to be stored somewhere, but I am worried that if I store them in a DB (which is mainly used to store text, and is expected serve thousands of users per minute) then this will make DB queries take much longer time, which will lead to limitation to the overall number of users I can serve/minute.
In short, I am thinking of move this load from the database resources (which has limited # of simultanous connections, for example) to the server file system..
Thanks
And ever has it been that love knows not its own depth until the hour of separation
Mohammad Gdeisat
|
|
|
|
|
Based on your answers, I wouldn't the ads to the database.
Mohammad A Gdeisat wrote: I am worried that if I store them in a DB (which is mainly used to store text, and is expected serve thousands of users per minute) then this will make DB queries take much longer time
Sounds like you are retrieving the ad from the database with every call? Regardless of where the ad is, this would be slow. Could you fetch a new ad for example every 30 seconds and in the meanwhile cache it in the memory of the IIS server.
|
|
|
|
|
I will almost retrieve ads with half of the requests, which makes it a heavy load on the DB.
and I think I will not store the ads in the DB.
And ever has it been that love knows not its own depth until the hour of separation
Mohammad Gdeisat
|
|
|
|
|
Hi,
So the problem is I have one table which contains 1 column and 3 rows. Like This:::
Description
-----------
HR
PM
Faculty
Now what I want is to store these columns in another table in one row. Like::::
Staff
------
HR,PM,Faculty.
Please Help me to this and Don't give any other suggestion to complete this task. I just want the same.
Thanks in Advance. 
|
|
|
|
|
1. First Create a temp tabel.
2. Then count the now of row in the first table.
3. use loop while < rownum
Then add the column in the temp table using the below dynamic query
( set @colname = @colname + ' ALTER TABLE #Temp ADD col'+
cast(@count as varchar(10)) + ' decimal(10, 2) NULL '
exec(@colname) )
4. using cursers update the different column values.
|
|
|
|
|
If you're using SQL 2005 you could have a look at the PIVOT command.
|
|
|
|
|
I have created following assembly. This is an strong named assembly and it intends to start another process. I need to register it in SQL server 2005 database
namespace AutoRunProcess
{
public static class BaseFunctionClass
{
public static void Run()
{
Process myProcess = new Process();
myProcess.StartInfo.FileName = @"E:\RDBCH32\bin\AutoRun.exe";
myProcess.Start();
}
}
}
When I try to register it, it generate following error
"CREATE ASSEMBLY for assembly 'AutoRunProcess' failed because assembly 'AutoRunProcess' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. (.Net SqlClient Data Provider)"
Please help me to solve this matter.
modified on Thursday, December 11, 2008 1:01 AM
|
|
|
|
|
|
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.
|
|
|
|
|
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?
|
|
|
|
|
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.
|
|
|
|
|
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);
|
|
|
|
|
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 ....
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
