getting the list of connection strings to display in the SqlDataSource Wizard

I've added an SqlDataSource control to my web page and selected "Configure Data Source" on it. This brings up a "Choose Your Data Connection" wizard, and it asks you to select from a dropdown list of presumably pre-existing connection strings in my web.config file. However none of my connection strings will display in that dropdown list. The only thing i can do is hit the "Create New Connection" button and this adds yet another connection string to my web.config file.

I've already ran through the wizard once, created a new connection string, but when i run through the wizard again, even the new connection string - the one created by the last wizard will not appear in the dropdown list.

Any suggestions?

Jason

Hi Jason,

Are the connection strings actually appearing in the web.config file? Are they actually working - that is, if you call one of the connection strings, does it actually do what you expect? I can't offer any actual solutions to this one, but have you tried creating a new web.config file and checking if this fixes the problem? Personally, I would set up a new application just to test this, and if it works OK in the new application then perhaps something in your current application has caused the issue.

Hope this helps.

Paul

|||

Hi Paul - yes i've tried your suggestions before but nothing works. In fact, even after already running "Choose Your Data Connection" wizard (and selecting the new datasource option) to let it generate a datasource for me, and then running the wizard again to see if the previously created datasource will appear, and it doesn't.

Not a big deal i guess but a bit of a pain. I wonder if its a bug...

Jason

|||

Hi Jason,

Please be sure that you have saved the string (a checkbox for you to select) the first time you use it.

Thanks.

sql

Getting the format bar while using SSRS Web Service

Hi,

I am using the URL method to call reports where you get all the look and feel and report format and export options on the report page itself. When I tried to use the Web Service I couldn't find those options. I can render the reports only one format at a time. Is there anyway to view the reports as in URL methos using web service with all the formatting options? Thank You.

Regards,

Das.

The report toolbar is available only with URL addressability. If you need to use the Web Service, why don't use the Report Viewer controls? Behind the scenes, they call down to the Web service APIs.

getting the columns in sql server table

I want to build a page that will handle all my list table... how can i get the names of the colums in specific table if i got the table name?

Use the ColumnName property in the DataColumn object. Here's an example:

// Populate the the row with the column names

foreach (DataColumn dcin dt.Columns)

{

_ColIndex++;

oSheet.Cells[_RowIndex, _ColIndex] = dc.ColumnName;

// Bold the column headings

Excel.Range oRange = (Excel.Range)oSheet.Cells[_RowIndex, _ColIndex];

oRange.Font.Bold =true;

}

|||You could execute a query, and pull them from the SysColumns tables:
Select
SC.Name
From Syscolumns SC
inner join systypes ST on SC.xtype=ST.xtype
Where OBJECT_NAME(SC.id) = 'MyTableName'

|||Do i use the query as usaul commandtext?
because i get error massage- "invalid object name SC"|||

If you are using sql statements it is recommended that you use the schema views in the database instead of the sysobject tables directly. Here is an example that will output all of the table information for you(using the Northwind database):

declare @.tablename varchar(255)

select @.tablename = 'Employees'

SELECT INFORMATION_SCHEMA.COLUMNS.*,
(SELECT COLUMNPROPERTY(OBJECT_ID(@.tablename),
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed')) AS IsComputed,
(SELECT COL_LENGTH(@.tablename, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME)) AS ColumnLength,
(SELECT COLUMNPROPERTY(OBJECT_ID(@.tablename), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')) AS IsIdentity,
(SELECT COLUMNPROPERTY(OBJECT_ID(@.tablename), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol')) AS IsRowGuidColumn,
(ISNULL((SELECT TOP 1 CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN 1 END
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @.tablename AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY'), 0)) AS IsPrimaryKey,
(ISNULL((SELECT TOP 1 CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'FOREIGN KEY' THEN 1 END
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME=@.tablename AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'), 0)) AS IsForeignKey,
(ISNULL((SELECT TOP 1 CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'UNIQUE' THEN 1 END
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @.tablename
AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'UNIQUE'), 0)) AS HasUniqueConstraint
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.tablename

|||Can you show me the code you used to execute the query? I'll check it for ya.
Thanks,
Tyler
|||this is the code:

PrivateSub settablecolumn()

Dim cmdAsNew SqlCommand()

Dim firstcolumnAsString

If oConnection.State = ConnectionState.ClosedThen oConnection.Open()

cmd.Connection = oConnection

cmd.CommandText = "Select L.Name() From Syscolumns L inner join systypes R on L.xtype=R.xtype Where OBJECT_NAME(L.id) = '" & table.SelectedItem.Value & "'"

Dim readerAs SqlDataReader = cmd.ExecuteReader

If reader.Read =TrueThen

firstcolumn = reader.Item(0)

EndIf

oConnection.Close()

EndSub
great thanks...

|||Should work if you remove the parentheses from Name:
cmd.CommandText = "Select L.Name From Syscolumns Linner join systypes R on L.xtype=R.xtype Where OBJECT_NAME(L.id) = '"& table.SelectedItem.Value & "'"
If not, let me know what the text of the Error message is.
Thanks,
Tyler

|||working great now, thank you very much.

getting sum group by page number

hi all,
i got a table, how can i get the sum of total ONLY for each page?(not sum of
the whole table)
thanks in advancegot the answer from the previous post...
please refer to thread "sum by page" if you guys have the same problem
"Jasonymk" wrote:
> hi all,
> i got a table, how can i get the sum of total ONLY for each page?(not sum of
> the whole table)
> thanks in advance

Getting SQL or Database status on webpage.

Hi is it possible to have a page of a website display the status of a database or MS SQL with .Net code ?, if so what code would i use.

Any help would be appreciated.

Thank You

Do you want see in your webpage SQL Server status (running, paused, etc) ?|||

Idealy yes that would be great but it dosent matter particuarlly what kinda status, it would be great to get the server status as well as the database status but eather is fine.

Thank You

|||

This just an idea, you can try to simulate to connect and disconnect in the EM and trace the whole process. Using this trace information you could figure out if there is any information about the status in a table or if there is something related to it.

Because as far as I know, SQL server is data in tables and instructions in system procedures. whatever you do in SQL Server is data and instructions. So the status must also be somewhere in a system table and you can try to retrieve it.

I was involved in something similar to this and this kind of work around gives results ( time consuming though to understand SQL procedures to extract information you want)

Good Luck!

|||

Thanks for the idea but i need a quick solution, im really short on time and need a solution fast.

thanks anyway

|||Cant remember off the top of my head but check out some of the procs (and also extended procs) in the master DB. Perhaps you can find one that gives you the info you need..|||

Hi shadowmaster,

Actually, to get the status of a database, you can use SQL Server SMO(SQL Management Objects).

The Database.Status property will return the database status.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.status.aspx

However, we cannot get the server status, because when a server is stopped, the service will not be available. In this case, we cannot even connect to that server. So we can only get the running status.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

sql

Getting results from sqldatasource in codebehind

(New to ASP.net 2.0 and database connection)

I have created an sqldatasource on the aspx page, which works fine, but how do I get the results from it in the codebehind?

hmm..you should create the datasource in the code behind page. I mean that's what the page is for. Place all your asp.net code in it. If you use a code-behind page then aspx page is just for displaying the data.
|||

I can't seem to get it working. The connection works fine (I believe), but I can't seem to give the parameters a value

I have this code: (I know it's Insert instead of select, but that's what I'm working on now)

SqlDataSourceSupplierList.InsertCommand ="INSERT INTO dbo.ProdSup(SupID, ProdID) VALUES (@.SupID, @.ProdID)";

SqlDataSourceSupplierList.InsertParameters.Add("@.SupID", SqlDbType.Int) = 4;

SqlDataSourceSupplierList.InsertParameters.Add("@.ProdID","8");

SqlDataSourceSupplierList.Insert();

As you can see I have tried two ways to give them values, but none of them works. The fields in the DB are both integer, which I guess is why the second try doesn't work (it sends '8' in as a string)... The error I get here is that it "cannot insert the value NULL into column"

The line with @.SupID gives me this error:

CS1502: The best overloaded method match for 'System.Web.UI.WebControls.ParameterCollection.Add(string, string)' has some invalid arguments

What's wrong here?

Getting report to create new page for each record retrieved from

I have a stored procedure to pull out the data of all the employees at
a company, and if I use a table, it lists the all the profiles.
However, the table is too big to view on one page and print, so I
created a report with textboxes and fields that fits onto one page
instead.
Using the same stored procedure, all I could see is the profile of the
first employee returned by the stored procedure.
Is there a way to get the report to create a new page for each person
returned by the stored procedure so that the final report would
consists of all the profiles?
Not sure if I should mention this, but I'm working with a web form on
VS2005.
Thanks.You put the text boxes right on the drawing surface. You need to put them on
a list control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"k-chan" <kitty.sham@.gmail.com> wrote in message
news:c83e7712-1c9e-49fc-b551-4399c96e6936@.e4g2000hsg.googlegroups.com...
> I have a stored procedure to pull out the data of all the employees at
> a company, and if I use a table, it lists the all the profiles.
> However, the table is too big to view on one page and print, so I
> created a report with textboxes and fields that fits onto one page
> instead.
> Using the same stored procedure, all I could see is the profile of the
> first employee returned by the stored procedure.
> Is there a way to get the report to create a new page for each person
> returned by the stored procedure so that the final report would
> consists of all the profiles?
> Not sure if I should mention this, but I'm working with a web form on
> VS2005.
> Thanks.|||Oh! That worked!
Thank you!
On Jan 14, 3:23 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You put the text boxes right on the drawing surface. You need to put them on
> a list control.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "k-chan" <kitty.s...@.gmail.com> wrote in message
> news:c83e7712-1c9e-49fc-b551-4399c96e6936@.e4g2000hsg.googlegroups.com...
>
> > I have a stored procedure to pull out the data of all the employees at
> > a company, and if I use a table, it lists the all the profiles.
> > However, the table is too big to view on one page and print, so I
> > created a report with textboxes and fields that fits onto one page
> > instead.
> > Using the same stored procedure, all I could see is the profile of the
> > first employee returned by the stored procedure.
> > Is there a way to get the report to create a new page for each person
> > returned by the stored procedure so that the final report would
> > consists of all the profiles?
> > Not sure if I should mention this, but I'm working with a web form on
> > VS2005.
> > Thanks.

Getting Report page Count

Hi All
We have an ASP.NET application that lists all the reports hosted in our SQL
RS by using RS webservices. When user selects on a particular report from the
list the corresponding parameters get shown again using web service calls.
After the selection of parameters, the user gets to view the report in the
report viewer control. We have also designed a custom tool bar complete with
export options and with prev/next functionalities. In order to get the
pagecount we are calling the render method and get the streamid count. We are
passing the device info for Image i.e.
<DeviceInfo><OutputFormat>EMF</OutputFormat></DeviceInfo>. The streamId count
however is very random and does not match the actual total page count of the
report.
Can anyone please help or guide here...
Thanks !
PRI just wanted to add that the report gets shown in the reportviewer by URL.
The render method is used to purely generate the pagecount. Shouldn't there
be an easier way...
"PR" wrote:
> Hi All
> We have an ASP.NET application that lists all the reports hosted in our SQL
> RS by using RS webservices. When user selects on a particular report from the
> list the corresponding parameters get shown again using web service calls.
> After the selection of parameters, the user gets to view the report in the
> report viewer control. We have also designed a custom tool bar complete with
> export options and with prev/next functionalities. In order to get the
> pagecount we are calling the render method and get the streamid count. We are
> passing the device info for Image i.e.
> <DeviceInfo><OutputFormat>EMF</OutputFormat></DeviceInfo>. The streamId count
> however is very random and does not match the actual total page count of the
> report.
> Can anyone please help or guide here...
> Thanks !
> PR

Getting error, while trying to connect oracle

We are getting following error,while trying to connecct oracle using oledb provider.

Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from the propoerty will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page fromt he componnet's locale ID will be used.

Could you please tell me the reason for the error. is it problem regarding oracle client version installed on the system?

Thanks & Regards

S.Nagarajan

Are you sure its an error? Are you sure its not just a warning?

Did you try googling it? http://www.google.co.uk/search?hl=en&q=Cannot+retrieve+the+column+code+page+info+from+the+OLE+DB+Provider.+If+the+component+supports+the+%22DefaultCodePage%22+property%2C+the+code+page+from+that+property+will+be+used.+Change+the+value+of+the+property+if+the+current+string+code+page+values+are+incorrect.+If+the+component+does+not+support+the+property%2C+the+code+page+from+the+component%27s+locale+ID+will+be+used&meta=

This will definately help: http://blogs.conchango.com/jamiethomson/archive/2005/10/25/2303.aspx

-Jamie

|||

Thanks for your reply

Regards

S.Nagarajan