Friday, February 24, 2012

How to retrieve DATETIME format?

Hello, everyone:

My table has a column that is DATETIME data type. The original format likes "1/21/2004". I want to check data lengh. However when I read the data by SELECT, SQL Server change the format to "Jan.-21-2004" automatically. Does any one has an idea to keep original datetime format? Thanks.

ZYTthere is no original format

you may use a format for input that sql server understands, but internally, the date is stored as an integer (number of days since the base date)

check out BOL (Books Online) for details

if you want to see a specific format when you SELECT a datatime value, then use the CONVERT function

How to retrieve Date fields from an Access MDF on VS c++ Net 2005

I Apologize if this isn't the forum to ask this...
I have a MS Access (MDB) file with a table with 2 date fields, i want to read from a dialog on my app (on MS Visual .NET Studio 2005), here's the code I've been using do far:

  
 
Code Snippet
 hr=theApp.m_cs.Open(theApp.m_ds);
if(SUCCEEDED(hr)) {
theApp.m_cs.StartTransaction();
theApp.m_cs.Commit();
CCommand< CDynamicAccessor > cmd; 
CComBSTR query(_T("SELECT NumContrato, NumClie, FechaC, FechaCob, Inversion, NoCobrador, NoVendedor, Total, Plazo, Pagos FROM Contrato"));
CString string(query.m_str);
cmd.Open(theApp.m_cs,string);
hr = cmd.MoveFirst();
query=static_cast< BSTR >(cmd.GetValue(1));
CString csres(query.m_str);
this->m_numc=(int)*(query.m_str);
query=static_cast< BSTR >(cmd.GetValue(2));
m_numcte=(int)*(query.m_str);
query=static_cast< BSTR >(cmd.GetValue(3));
//m_fecc=(int)*(query.m_str);
MessageBox(csres);
theApp.m_cs.Close();
}

FechaC, FechaCob, are the two Dates I want to retrieve, but when I debug, it reads a 0 (zero) from the date fields, is there a limitation? can they be read? is there a special way to read them?
> thanks in advance!

--
Me!

I'm not experienced in templates, but it looks strange for me and you should check the type of the returned value.

If your field is of the type Date/Time then I'm not sure that simple casting is correct, since I would expect GetValue to return the pointer into the buffer with the actual data and I presume that the datatype there should be DBTYPE_DATE. Perhaps you need to create a specific accessor and explicitly request conversion to a string type.

|||Thanks A lot this is what i've done:

Code Snippet

DATE *d=(DATE*)(cmd.GetValue(3));

COleDateTime D(*d);

m_fec=D;

Sorry for the such a noob question you've been helpful! :)!

how to retrieve datas from a SqlDataSource

Hi! I'm a novice in asp .net

I've a SqlDataSource component on an Aspx page.
In the associated C# file, I would like to use datas from the query stored in the SqlDataSource component.

How to do this?

Thanxs :)

An example

<%@.PageLanguage="C#" %>

<%@.ImportNamespace="System.Data" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

protected void Page_Load(object sender, EventArgs e)

{

DataView dv = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());

DataTable dt = dv.Table;

// display table

bool pagesToDo = true;

int index = 0;

while (pagesToDo)

{

Table t = GetDisplayTable(dt);

t.CssClass = "PageBreakStyle";

Controls.Add(t);

int nextPageEndsAt = index + 10;

while (index < nextPageEndsAt && pagesToDo)

{

TableRow tr = new TableRow();

t.Rows.Add(tr);

foreach (DataColumn dc in dt.Columns)

{

TableCell tc = new TableCell();

tr.Cells.Add(tc);

tc.Text = dt.Rows[index][dc.ColumnName].ToString();

}

index++;

if (index == dt.Rows.Count)

{

pagesToDo = false;

break;

}

Table GetDisplayTable(DataTable dt)

{

Table t =newTable();

TableHeaderRow thr =newTableHeaderRow();

t.Rows.Add(thr);

foreach (DataColumn dcin dt.Columns)

{

TableHeaderCell thc =newTableHeaderCell();

thc.Text = dc.ColumnName;

thr.Cells.Add(thc);

}

return t;

}

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

<styletype="text/css">

.PageBreakStyle

{

page-break-after:always;

}

</style>

</head>

<body>

<formid="form1"runat="server">

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"

SelectCommand="SELECT [ProductID], [ProductName] FROM [Products]"></asp:SqlDataSource>

</form>

</body>

</html>

|||

Thanks, it works!

very cool man ^^

How to retrieve database password

I previously had a web server that was running a application that used a
MSDE database. This server had a motherboard failure and I was not able to
repair it. I did get all the data off the drives so the MSDE SQL databases
did get copied. The problem is the admin password for those database is not
known to me and the previous admin does not remember. I need this password
so that I can reinstall the web application and give it the admin password
for the old databases. The tool the software company gave me to pull the
password does not work, as it pulls the information from the registry (which
is not the original as I have a new boot drive). Does anyone know how to
pull MSDE SQL database passwords without booting from the original drive?
There has to be a way to get all of the databases back since the data is
intact.
If you have the MDF files, just attach the database to a new instance
of MSDE with sp_attachDB.
If you mean a specially created user in the database or server AFAIK
which the application will use for connection and which is not known to
you, this password cannot be recovered. (If you have no access to the
stored password in the source code)
HTH, Jens Suessmeyer.
|||All I have are the old .SQL files no MDF files were created.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1136994106.307693.42900@.g43g2000cwa.googlegro ups.com...
> If you have the MDF files, just attach the database to a new instance
> of MSDE with sp_attachDB.
> If you mean a specially created user in the database or server AFAIK
> which the application will use for connection and which is not known to
> you, this password cannot be recovered. (If you have no access to the
> stored password in the source code)
> HTH, Jens Suessmeyer.
>
|||If it is a SQL Server Authenticaion you should look in that Script
files (which they are supposed to be) for sp_addlogin which is used to
create the SQL Server auth. users.
HTH, jens Suessmeyer.
|||1. Go to Start | Run | type in Command.
2. Type the following command in the DOS prompt.
OSQL S <SERVERNAME> -E =
Note Be sure to replace SERVERNAME with the name of your SQL server.
Typically this is the name of your computer.
Note If this statement does not run successfully try entering it again
excluding the '=' sign.
3. Press Enter.
4. Type the line exactly as it is written below where ok is your new
password:
1> EXEC sp_password NULL, 'ok', 'sa'
5. Press Enter.
6. Type the line below:
2> Go
7. Press Enter. This will change the sa password from a value unknown to
ok (this can be changed in step 4).
=
"J Coldwater" <jams@.purdue.edu> wrote in message
news:u5y$dTsFGHA.2896@.TK2MSFTNGP10.phx.gbl...
> I previously had a web server that was running a application that used a
> MSDE database. This server had a motherboard failure and I was not able to
> repair it. I did get all the data off the drives so the MSDE SQL databases
> did get copied. The problem is the admin password for those database is
> not known to me and the previous admin does not remember. I need this
> password so that I can reinstall the web application and give it the admin
> password for the old databases. The tool the software company gave me to
> pull the password does not work, as it pulls the information from the
> registry (which is not the original as I have a new boot drive). Does
> anyone know how to pull MSDE SQL database passwords without booting from
> the original drive? There has to be a way to get all of the databases back
> since the data is intact.
>

How to retrieve data using a search?

Hello
Is there any way in which i can retirve data using input parameters?you're thinking of the way a dialog box pops up in microsoft access?

no, in sql server you have to do this with application code|||yes , u r right, i was thinking teh way exactly as it can be done in access?
can u pls send me a sample code of writing an application code?
Thanx in advance|||not me, no, maybe someone else

i'm not a programmer|||so its beeen a big ask in retrieving data of a particular choice in MS SQL?|||No. It's pretty easy. Is it a big ask to figure it out yourself?

We don't even know what interface you are using. VB? Access mdb? Access adp? DotNet? C#?

You expect people to not only do your work for you, but to guess what you want? That's a pretty big ask.|||But i am using webassitant wizard to get the reports on the Intranet. and VB is our front end.
where i have an option for reports tab on the VB front end where it fetches data from the webassistant wizard.
every other report works fine, Only thing i am trouble designing in reports is to search in a particular field. as i am new to this developing part in SQL any ones help in solving the search with input parameters would be much appreciated.
Thanx in advance
VEE Yes|||Ummmmm... post your question in a forum for whatever front-end application your using (I was unable to decipher anything other than VB from your description).

You're on the wrong forum, but you might get lucky from a passer-by understanding what you are experiencing. If you don't want to wait around for a ride, then go directly to the right forum, do not pass Go and do not collect $200.|||Just a guess at what you want, but you should probably write a stored procedure on your SQL Server database that takes your search terms as parameters and returns the dataset to your application.|||CAn any one help me with a stored procedure to write to take an input parameters to search and retrieve data?|||Remember two things...
only use this code to benefit man
and
Soylent Green is PEOPLE!!!!!!!!!

/*
this script creates a db, a table, and a stored procedure.
the table shows the level of my malaise
which steadily grows from crappy to the ultimate level
rdjabarov
the proc asks for a parameter to return query rows.
*/

create database crappyDB
go
use Crappydb
go
create table CrappyTable
(
col1 int
,col2 varchar(20)
)
go

insert into crappytable select 1, 'crappy'
union select 2, 'really crappy'
union select 3, 'extra crappy'
union select 4, 'totally crappy'
union select 5, 'RDJabarov'
go
select * from crappytable
go
create procedure Pcrappyproc
@.col1 int
as
select col2 as 'rate my malaise' from crappytable
where col1 = @.col1

go
exec Pcrappyproc 5
go
--drop database crappydb|||brilliant code

especially col2's column alias

:)|||shhhhhhhh

i get no pleasure from doing it....plus it gets him all riled up. :D|||Oh, like THAT is so difficult... ;)|||...select 5, 'RDJabarov'...So you have time to post silly things like this, but you don't have the time to send your resume?

How's VA?|||sent it hours ago dude.

How to retrieve data randomly?

I have a table called Questions and I need to retrieve 10 rows of data randomly each time. If I use
SELECT TOP 10 Question FROM Questions
I'll get the same questions each time when I execute the sql statement. Is there a way to get the random data? Thanks.SELECT TOP 10 Question FROM Questions ORDER BY NEWID()

How to retrieve data of a product which falls under two categories?

When a user selects couple of checkboxes, i'm storing both the categories seperated with a comma in a single field of database but then when i want retrieve the all products of a particular category, this approach wont work. Is their any way around for this??

Even though i use CheckboxList control is their any way to retrieve products of a particular category?? Can i store each checkbox text in a different row with the same product ID's in a different table and all the product info in a different table ?? I hope i made myself clear.

Thanks for your help and time in advance.

Can anyone help me out with the above problem please??

How to retrieve data of a product which falls under two categories?

I'm using MS SQL 2000. I developing a shopping cart where on the admin
side when we are inserting or updating the products, there is a chance
that a single product can fall into two categories therefore i'm giving
the users a option of CheckBoxList control for the categories. The
checkboxlist control is Database Databinded with categories.
When a user selects couple of checkboxes, i'm storing both the
categories seperated with a comma in a single field of database but
then when i want retrieve the all products of a particular category,
this approach wont work. Is their any way around for this'
Even though i use CheckboxList control is their any way to retrieve
products of a particular category' Can i store each checkbox text in a
different row with the same product ID's in a different table and all
the product info in a different table ' I hope i made myself clear.
Thanks for your help and time in advance.it's not particularly clear I'm afraid.
It's a good idea to make sure that all data stored in your database is
atomic (just one value). by storing data with comma separators you are
breaking with this, and it is the start of a very bad road to go down,
as you have just discovered.
One possible answer to your question if you are to insist on keeping
with this data structure is to match on WHERE Category LIKE '%' +
@.Category + '%' . However if you have a category that is a subset of
another (e.g. Name and product name) then you'll have problems with
this.
A much better idea is to store your data in a relational fashion, so
from what I can tell (which is a bit of a guess), you'd want 3 tables
here:
tblCategories (CategoryID int, Category varchar(50))
tblProducts(ProductID int, Product varchar(50))
tblProductCategories(ProductID int, CategoryID int)
the first 2 tables store your information about the 2 entities, the
third stores the relationship between the 2.
You could then in your UI call something to insert rows into
tblProductCategories for each checkbox, then you could more easily
query your data.
Hope this helps, otherwise you might need to post a bit more
information about what you need, I especially don't understand how the
checkboxes fit in to all this.
Cheers
Will|||Thank you very much for your time and help
I think i got the way out with the advice you gave me
thank you very much for that
I have worked out the following way and its working
i know there might be more efficient ways of doin this
As said above i got three tables
tblCategories (CategoryID int, Category varchar(50))
tblProducts(ProductID int, Product varchar(50))
tblProductCategories(ProductID int, CategoryID int)
on the UI side whenever I'm inserting the Product info
i'm grabbin the last inserted Product ID using @.@.IDENTITY
now, i'm loopin the each item in checkboxlist
and if Selected then inserting the CheckboxID (CategoryID)and the
previously grabbed ProductID, therefore the product key will
be same for all the categories.
And now by the joining the Products table with the ProductsCategories
i'm able to retrieve all the necessary info about Products
Thanks mate

How to retrieve data from sqldatasource?

Okay, I used the SQLDataSource control to get my data from the databasetable. What or how do I retrieve individual data from thesqldatasource? I want to do some string comparison and manipulationbefore I display it to the browser. How can this be accomplish?

Help is appreciated.

Hi,

The SqlDataSource control designed for data bound controls so you can't access data except by data bound control, for example if your using gridview you can access data item by handle RowDataBound event and make your changes there

I hope this help.

|||

If your DataSourceMode of your SQLDataSource stays as default which is DataSet, you can retrieve a dataview object of your SQLDataSource. If DataSourceMode="DataReader" in yourSQLDataSource, you can retrieve a datareader object.

Here is an example for dataview:

'DataView
Dim mydataview As System.Data.DataView =CType(SQLDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

For Each dr As DataRow In dv.Table.Rows
...

'For a Datareader

Dim myreader as System.Data.SqlClient.SqlDataReader = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.SqlClient.SqlDataReader)

While myreader.reader

...

End While

|||Many thanks for the response. Limno, I will try that. In the mean time,if SQLDataSource is for databound only control then what do I need todo so that I can grab the individual field of my table and do datacomparison and then present it on the web?|||

Hi,

It is a way you can access your datasource programmatically.

This link will give a little more information on this topic.http://aspnet.4guysfromrolla.com/articles/022206-1.aspx

|||Okay, here's what I have.

<asp:SqlDataSource ID="sqlEnewsCate" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString%>"
 SelectCommand="SELECT * FROM [enewscate] WHERE ([id_ecate] = @.id_ecate) ORDER BY [order_ecate]">
 <SelectParameters>
 <asp:QueryStringParameter Name="id_ecate" QueryStringField="id" Type="Int32" />
 </SelectParameters>
 </asp:SqlDataSource>
 <asp:SqlDataSource ID="sqlEnews" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString%>"
 SelectCommand="SELECT * FROM [enews] WHERE ([idmnu_nws] = @.idmnu_nws)">
 <SelectParameters>
 <asp:QueryStringParameter Name="idmnu_nws" QueryStringField="id" Type="Int32" />
 </SelectParameters>
 </asp:SqlDataSource>

And here is my code behind in the Page Load:

protected void Page_Load(object sender, EventArgs e)
 {
string newsID;
string nwsStory ="";
 newsID = Request.QueryString["id"];if (newsID =="")
 {
 Response.Redirect("Default.aspx?id=3");
 }
if (Request.QueryString["stry"] =="" || Request.QueryString["stry"] ==null)
 {
 nwsStory ="";
 }
 OleDbDataReader newsReader = (OleDbDataReader)sqlEnews.Select(DataSourceSelectArguments.Empty);
if (newsReader.Read())
 {
if (nwsStory =="" || nwsStory ==null)
 {
 OleDbDataReader reader = (OleDbDataReader)sqlEnewsCate.Select(DataSourceSelectArguments.Empty);
if (reader.Read())
 {
string pic = Convert.ToString(reader["sidePic_ecate"]);
if (pic =="" || pic ==null)
 {
 imgArticle.ImageUrl ="images/11.jpg";
 }
else
 {
 imgArticle.ImageUrl ="images/" + pic;
 }
 reader.Close();
 }
 }
else if (nwsStory =="full")
 {
string pic = Convert.ToString(newsReader["sidePic_nws"]);
if (pic =="" || pic ==null)
 {
 imgArticle.ImageUrl ="images/11.jpg";
 }
else
 {
 imgArticle.ImageUrl ="images/" + pic;
 }
 }
 newsReader.Close();
 }
 }

And here is the error I received:

erver Error in '/Alumni' Application.

Object reference not set to an instance of an object.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 29: }
Line 30: OleDbDataReader newsReader = (OleDbDataReader)sqlEnews.Select(DataSourceSelectArguments.Empty);
Line 31: if (newsReader.Read())
Line 32: {
Line 33: if (nwsStory == "" || nwsStory == null)

Source File: e:\wwwroot\home\mySite\enews\Default.aspx.cs Line: 31

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
 enews_Default.Page_Load(Object sender, EventArgs e) in e:\wwwroot\home\mySite\enews\Default.aspx.cs:31
 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
 System.Web.UI.Control.OnLoad(EventArgs e) +99
 System.Web.UI.Control.LoadRecursive() +47
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42|||

You need add this to your datasource:

DataSourceMode="DataReader"

|||Thanks for the immediate response. Where in the do I put DataSourceMode="DataReader" in?|||<asp:SqlDataSource ID="sqlEnewsCate" runat="server"DataSourceMode="DataReader" .....|||Thanks! I'll give that a try.|||I still received the same error.|||

You need this in the top of your code behind page too:

using System.Data.OleDb

|||I do have that. I downloaded the source code from the link you gave me.|||

OleDbDataReader newsReader = (OleDbDataReader)sqlEnews.Select(DataSourceSelectArguments.Empty);

and this one:

OleDbDataReader newsReader = (OleDbDataReader)sqlEnewsCate.Select(DataSourceSelectArguments.Empty);

Can you see the problem?

|||Thanks for the response. If you look at my code above, you'll see thatI have both those lines in my code. Unless I did it wrong somehow.

how to retrieve data from sqldatasource to textbox

my problem is i can't retrieve data from my sqldatasource to be displayed in textbox... i try to do it in vb codes. somebody help me here?

Hi firefox3000,

First i would suggest you posting your code here so we can better help you.

And second, what do you mean by "can't retrieve data from my sqldatasource to be displayed in textbox". Do you mean you cannot retrieve data from sqldatasource OR you can get the data but you cannot get them dispalyed? Easiest way is to set up a break point after retrieving data though database query and see if the data has been populated.

If you cannot get the data, tryado.net stuff and if cannot get those data displayed, check if you have bound your textbox.text property to a wrong place.

Hope my suggestion helps

|||

Hello Bo Chen,

I think I can't retrieve my data from my sqldatasource. Pls check my code:

Imports SystemImports System.DataImports System.ConfigurationImports System.WebImports System.Web.SecurityImports System.Web.UIImports System.Web.UI.WebControlsImports System.Web.UI.WebControls.WebPartsImports System.Web.UI.HtmlControlsImports Telerik.WebControlsImports System.Data.SqlClientImports Telerik.WebControls.GridEditFormItemImports System.Data.SqlTypesPartialClass _DefaultInherits System.Web.UI.PagePublic Shared dtTableAs New DataTablePublic sqlConnectionAs New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())Public sqlAdapterAs New SqlDataAdapter()Public sqlCommandAs New SqlCommand()Protected Sub RadGrid1_NeedDataSource(ByVal sourceAs Object,ByVal eAs Telerik.WebControls.GridNeedDataSourceEventArgs)Handles RadGrid1.NeedDataSource sqlConnection.Open()Dim queryAs String ="SELECT * FROM blogpost" sqlAdapter.SelectCommand =New SqlCommand(query, sqlConnection) sqlAdapter.Fill(dtTable) RadGrid1.DataSource = dtTable sqlConnection.Close()End Sub Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load'Dim txt As Label 'For Each item As GridDataItem In RadGrid1.Items 'txt = CType(Item("TemplateColumn").FindControl("Label1"), Label) 'MsgBox(txt.Text) 'Next 'Dim txt As Label = CType(RadGrid1.Items.Item("TemplateColumn").FindControl("Label1"), Label)End Sub Protected Sub RadGrid1_ItemDataBound(ByVal senderAs Object,ByVal eAs Telerik.WebControls.GridItemEventArgs)Handles RadGrid1.ItemDataBound'If TypeOf e.Item Is GridDataItem Then 'Dim dataitem As GridDataItem = TryCast(e.Item, GridDataItem) 'Find the control in the template column using the find control method 'Dim TextBox1 As Label = DirectCast(dataitem("TemplateColumn").FindControl("Label1"), Label) 'MsgBox(TextBox1.Text) 'End If sqlConnection.Open()Dim queryAs String ="SELECT * FROM blogpost" sqlAdapter.SelectCommand =New SqlCommand(query, sqlConnection) sqlAdapter.Fill(dtTable)Dim userControlAs UserControl = TryCast(e.Item.FindControl(GridEditFormItem.EditFormUserControlID), UserControl)If TypeOf e.ItemIs GridDataItemThen Dim dataItemAs GridDataItem = TryCast(e.Item, GridDataItem)Dim lblDateTimeAs Label =CType(dataItem.FindControl("Label1"), Label)Dim divContentAs HtmlGenericControl =CType(dataItem.FindControl("blog"), HtmlGenericControl)Dim lblPostedByAs Label =CType(dataItem.FindControl("Label2"), Label) lblDateTime.Text = dtTable'''' I dont know how to do it here....End If sqlConnection.Close()End SubEnd Class

|||

Hi firefox3000,

Sorry i didn't know your talbe structure so i cannot give you an accurate answer.

Your code should look like this:

lblDateTime.Text = dtTable'''' I dont know how to do it here....

lblDataTime.Text=dtTable[rownumber][colnumber].ToString();

Any further issues, please let me know. thanks

|||

Hi Bo Chen,

In rownumber or colnumber, can i make it a name of my field in my table?

table:

id - int

title - varchar

blog - varchar

datetime - datetime

lblDateTime.Text = dtTable(0)("title")

is that possible?

how to retrieve data from sql then put it into a label or textbox

i wanna know is there a way to retrieve data from the sql database and then
instead of putting it in the datagrid, can i put a specific field of data to a textbox?

i mean just a data (for example, a username where the password match the username) in a textbox.

thanks

sherlynyes you can use a datareader/dataadapter etc to fill a dataset and then into a textbox or pretty much any control you want..

hth

How to retrieve Data from SQL Server?

Let us suppose that there are 900 rows in One table, and that table contains neither primary key, nor Identity column.

How can I retrieve all the roows from 201 to 250?(like the middle limits)

In oracle there is a property called RowID, but is there any such item in SQL server?

Nope. All tables should have a primary key. If there isn't one, make one. If you can't make one, then you have a data design issue you need to fix.

How to retrieve Data from SQL SERVER 2000 ?

Hello,

Im using Visual Studio 2005 to code ASP.NET

How to query my MS SQL SERVER 2000 to retrieve data from my DataBase?

Ive tried some tutorials, but i kept giving me errors
Can you put a simple source code to retrieve something like "select userID, Name, Age from Users"

Thank you,

Are you able to connect to your database?
|||First have you tried the gridview control that auto creates your select statement by you defining options in VS 2005 wizards?

Check out this tutorial on how to do a grid with a simple select statement.
http://www.devx.com/dotnet/Article/22141

about your request. Can you give an example? What are you
trying to achive?
http://vyaskn.tripod.com/search_all..._all_tables.htm
"SQL noob" <SQLnoob@.discussions.microsoft.com> wrote in message
news:E90610EC-47CC-416D-B4B1-11C18B47E621@.microsoft.com...
> first thing is how to search the keywords from the tables
> Second thing after u search the result ,how to link the result to a number
> related to the result?
>|||something like that?
SELECT Omim_No
FROM av
WHERE Description LIKE '%LIVER%'
ORDER BY Omim_No ASC
SELECT Omim_No
FROM cs
WHERE CS_Description LIKE '%LIVER%'
OR CS_DATA LIKE '%LIVER%'
ORDER BY Omim_No ASC
SELECT Omim_No
FROM ti
WHERE Omim_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
SELECT Omim_No
FROM ti_alt_title
WHERE Omim_Alt_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
SELECT Omim_No
FROM tx
WHERE Omim_Text LIKE '%LIVER%'
SELECT subsnp_id,pop_id,allele_id
FROM AlleleFreqBySsPop
WHERE source LIKE '%LIVER%'
i want to search for the keywords, and display the result as in Omim_No and
link the Omim_No to a table contain Omim number.. i got multiple tables..
some tables contain only number without words..|||oh... come on.. pls help mi......|||Hi
Hmm, I posted the answer and it does not show up
I'd create a temporary table and using your queries with UNION ALL insert
the data into the table. Now that I inserted the data i'd JOIN this table
with your original one ON #Temp.Omim_No=Orig.Omim_No
"SQL noob" <SQLnoob@.discussions.microsoft.com> wrote in message
news:093C517E-2C62-4570-A7DF-BE5495EA7655@.microsoft.com...
> oh... come on.. pls help mi......

How to retrieve data from query and link the data to a data number?

i'm doing a project on SQL Server 2005, i'm stack in searching the data and can't link the data in the data number.. can someone help mi with the programming code? as i can search for the words in the table but i can't link with the data number..

am i doing the correct thing? this command will search for the keyword LIVER. or issit this command? i doing half way through..

SELECT Omim_No

FROM av

WHERE Description LIKE '%LIVER%'

ORDER BY Omim_No ASC

SELECT Omim_No

FROM cs

WHERE CS_Description LIKE '%LIVER%'

OR CS_DATA LIKE '%LIVER%'

ORDER BY Omim_No ASC

SELECT Omim_No

FROM ti

WHERE Omim_Titles LIKE '%LIVER%'

ORDER BY Omim_No ASC

SELECT Omim_No

FROM ti_alt_title

WHERE Omim_Alt_Titles LIKE '%LIVER%'

ORDER BY Omim_No ASC

SELECT Omim_No

FROM tx

WHERE Omim_Text LIKE '%LIVER%'

SELECT subsnp_id,pop_id,allele_id

FROM AlleleFreqBySsPop

WHERE source LIKE '%LIVER%'

SELECT aln_id

FROM b125_Alignments_35_1

WHERE tr_acc LIKE '%LIVER%'

OR ctg_name LIKE '%LIVER%'

OR ctg_acc LIKE '%LIVER%'

OR tr_orient LIKE '%LIVER%'

SELECT ctg_id,exon_number,exon_start,exon_end

FROM b125_ContigExon_1_1

WHERE mrna_acc LIKE '%LIVER%'

SELECT ctg_id,exon_number,exon_start,exon_end,seg_start,seg_end

FROM b125_ContigExon_35_1

WHERE mrna_acc LIKE '%LIVER%'

SELECT b125_ContigInfo_1_1.*

FROM b125_ContigInfo_1_1

WHERE contig_acc LIKE '%LIVER%'

OR contig_name LIKE '%LIVER%'

OR contig_chr LIKE '%LIVER%'

OR group_term LIKE '%LIVER%'

OR group_label LIKE '%LIVER%'

OR contig_label LIKE '%LIVER%'

SELECT b125_ContigInfo_34_3.*

FROM b125_ContigInfo_34_3

WHERE contig_acc LIKE '%LIVER%'

OR contig_name LIKE '%LIVER%'

OR contig_chr LIKE '%LIVER%'

OR group_term LIKE '%LIVER%'

OR group_label LIKE '%LIVER%'

OR contig_label LIKE '%LIVER%'

SELECT b125_ContigInfo_35_1.*

FROM b125_ContigInfo_35_1

WHERE contig_acc LIKE '%LIVER%'

OR contig_name LIKE '%LIVER%'

OR contig_chr LIKE '%LIVER%'

OR group_term LIKE '%LIVER%'

OR group_label LIKE '%LIVER%'

OR contig_label LIKE '%LIVER%'

SELECT b125_CrashedInfo.*

FROM b125_CrashedInfo

WHERE snp_type LIKE '%LIVER%'

SELECT b125_MapLink_35_1.*

FROM b125_MapLink_35_1

WHERE snp_type LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

SELECT b125_MapLinkInfo_35_1.*

FROM b125_MapLinkInfo_35_1

WHERE accession LIKE '%LIVER%'

SELECT b125_ModelInfo_35_1.*

FROM b125_ModelInfo_35_1

WHERE tr_acc LIKE '%LIVER%'

OR donor LIKE '%LIVER%'

OR acceptor LIKE '%LIVER%'

OR alignment LIKE '%LIVER%'

SELECT b125_ProjectedSNP_35_1.*

FROM b125_ProjectedSNP_35_1

WHERE snp_type LIKE '%LIVER%'

SELECT b125_ProteinInfo_34_3.*

FROM b125_ProteinInfo_34_3

WHERE ref_seq_status LIKE '%LIVER%'

OR rna_accs LIKE '%LIVER%'

OR prot_accs LIKE '%LIVER%'

OR nucl_accs LIKE '%LIVER%'

SELECT b125_ProteinInfo_35_1.*

FROM b125_ProteinInfo_35_1

WHERE ref_seq_status LIKE '%LIVER%'

OR rna_accs LIKE '%LIVER%'

OR prot_accs LIKE '%LIVER%'

OR nucl_accs LIKE '%LIVER%'

SELECT b125_SNPContigLoc_34_3.*

FROM b125_SNPContigLoc_34_3

WHERE snp_type LIKE '%LIVER%'

OR phys_pos LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

SELECT b125_SNPContigLoc_34_3_dup.*

FROM b125_SNPContigLoc_34_3_dup

WHERE snp_type LIKE '%LIVER%'

OR phys_pos LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

SELECT b125_SNPContigLoc_34_3_fix.*

FROM b125_SNPContigLoc_34_3_fix

WHERE snp_type LIKE '%LIVER%'

OR phys_pos LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

SELECT b125_SNPContigLoc_35_1.*

FROM b125_SNPContigLoc_35_1

WHERE snp_type LIKE '%LIVER%'

OR phys_pos LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

SELECT b125_SNPContigLoc_35_1_ss.*

FROM b125_SNPContigLoc_35_1_ss

WHERE snp_type LIKE '%LIVER%'

OR phys_pos LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

SELECT b125_SNPContigLoc_35_1_ss_fixed.*

FROM b125_SNPContigLoc_35_1_ss_fixed

WHERE snp_type LIKE '%LIVER%'

OR phys_pos LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

SELECT b125_SNPContigLocusId_1_1_temp.*

FROM b125_SNPContigLocusId_1_1_temp

WHERE contig_acc LIKE '%LIVER%'

OR locus_symbol LIKE '%LIVER%'

OR mrna_acc LIKE '%LIVER%'

OR protein_acc LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

OR residue LIKE '%LIVER%'

OR build_id LIKE '%LIVER%'

SELECT b125_SNPContigLocusId_34_3.*

FROM b125_SNPContigLocusId_34_3

WHERE contig_acc LIKE '%LIVER%'

OR locus_symbol LIKE '%LIVER%'

OR mrna_acc LIKE '%LIVER%'

OR mrna_ver LIKE '%LIVER%'

OR protein_acc LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

OR residue LIKE '%LIVER%'

OR build_id LIKE '%LIVER%'

SELECT b125_SNPContigLocusId_35_1.*

FROM b125_SNPContigLocusId_35_1

WHERE contig_acc LIKE '%LIVER%'

OR locus_symbol LIKE '%LIVER%'

OR mrna_acc LIKE '%LIVER%'

OR mrna_ver LIKE '%LIVER%'

OR protein_acc LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

OR residue LIKE '%LIVER%'

OR build_id LIKE '%LIVER%'

SELECT b125_SNPContigLocusId_35_1_old.*

FROM b125_SNPContigLocusId_35_1_old

WHERE contig_acc LIKE '%LIVER%'

OR locus_symbol LIKE '%LIVER%'

OR mrna_acc LIKE '%LIVER%'

OR mrna_ver LIKE '%LIVER%'

OR protein_acc LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

OR residue LIKE '%LIVER%'

OR build_id LIKE '%LIVER%'

SELECT b125_SNPMapInfo_34_3.*

FROM b125_SNPMapInfo_34_3

WHERE snp_type LIKE '%LIVER%'

OR assembly LIKE '%LIVER%'

SELECT b125_SNPMapInfo_34_3_fix.*

FROM b125_SNPMapInfo_34_3_fix

WHERE snp_type LIKE '%LIVER%'

OR md5 LIKE '%LIVER%'

OR assembly LIKE '%LIVER%'

SELECT b125_SNPMapInfo_35_1.*

FROM b125_SNPMapInfo_35_1

WHERE snp_type LIKE '%LIVER%'

OR md5 LIKE '%LIVER%'

OR assembly LIKE '%LIVER%'

SELECT b125_SNPMapInfo_35_1_ss.*

FROM b125_SNPMapInfo_35_1_ss

WHERE snp_type LIKE '%LIVER%'

OR md5 LIKE '%LIVER%'

OR assembly LIKE '%LIVER%'

SELECT b125_WithdrawCandidates.*

FROM b125_WithdrawCandidates

WHERE subsnp_id LIKE '%LIVER%'

SELECT Batch.*

FROM Batch

WHERE handle LIKE '%LIVER%'

OR loc_batch_id LIKE '%LIVER%'

OR loc_batch_id_upp LIKE '%LIVER%'

OR batch_type LIKE '%LIVER%'

OR moltype LIKE '%LIVER%'

OR synonym_type LIKE '%LIVER%'

OR linkout_url LIKE '%LIVER%'

SELECT BatchCita.*

FROM BatchCita

WHERE citation LIKE '%LIVER%'

SELECT BatchCommLine.*

FROM BatchCommLine

WHERE line LIKE '%LIVER%'

SELECT BatchCultivar.*

FROM BatchCultivar

WHERE line LIKE '%LIVER%'

SELECT BatchMeExLine.*

FROM BatchMeExLine

WHERE line LIKE '%LIVER%'

SELECT BatchPrivLine.*

FROM BatchPrivLine

WHERE line LIKE '%LIVER%'

SELECT BatchStrain.*

FROM BatchStrain

WHERE line LIKE '%LIVER%'

SELECT BatchValCode.*

FROM BatchValCode

WHERE batch_id LIKE '%LIVER%'

SELECT chk_SNPContigLoc_35_1.*

FROM chk_SNPContigLoc_35_1

WHERE snp_type LIKE '%LIVER%'

OR phys_pos LIKE '%LIVER%'

OR allele LIKE '%LIVER%'

SELECT chk_SNPMapInfo_35_1.*

FROM chk_SNPMapInfo_35_1

WHERE snp_type LIKE '%LIVER%'

OR md5 LIKE '%LIVER%'

OR assembly LIKE '%LIVER%'

SELECT Contact.*

FROM Contact

WHERE handle LIKE '%LIVER%'

OR name LIKE '%LIVER%'

|||

Please tell me this is not one command ? It's huge.

At it's core, what you're doing is right, using the LIKE keyword.

|||how to link a result to a number? i mean inside the table..|||

I don't understand. You're selecting from multiple tables. Don't those tables have ID columns ? Can't you use those ?

|||i wan to search for the word and it will display the Omim number and the Omim number will link to another identical number to display the description.. can u do it?|||

Nothing you're saying makes sense to me. You're selecting multiple tables ? why can't you just use the same ID, isn't that what you're saying you want ?

|||because some tables inside contain only numbers so i have to search for the keyword and display the result(omim number) and link this result number to a table contain numbers must be identical numbers. it is so hard that i can't really explain well sorry.. my tasks is hard..|||

I'm sorry, I do not understand the question. What do you want to do ?

|||

first thing is how to search the keywords from the tables

Second thing after u search the result ,how to link the result to a number related to the result?

|||

you use the LIKE keyword to find data inside strings. You return the ID with the string in the query.

|||can u show an example? i wan to search from the whole tables.. will the code be very long?|||

I can't show you an example because I don't know the table schema you're using. You want SQL, right ?

select id, name from tblSomething where name LIKE '%text%'

|||

SELECT av.*,cs.*,ti.*,ti_alt_title.*,tx.*,AlleleFreqBySsPop.*,OmimVarLocusIdSNP.*

FROM av,cs,ti,ti_alt_title,tx,AlleleFreqBySsPop,OmimVarLocusIdSNP

WHERE

av.Omim_No = cs.Omim_No

AND av.Omim_No = ti.Omim_No

AND av.Omim_No = ti_alt_title.Omim_No

AND av.Omim_No = tx.Omim_No

AND av.Omim_No = OmimVarLocusIdSNP.omim_id

AND (

Description LIKE '%LIVER%'

OR CS_Description LIKE '%LIVER%'

OR CS_DATA LIKE '%LIVER%'

OR Omim_Titles LIKE '%LIVER%'

OR Omim_Alt_Titles LIKE '%LIVER%'

OR Omim_Text LIKE '%LIVER%'

)

how?

|||

What's wrong with this ? It will return all the data you could hope for. Do you want to generate new IDs, unique to the query and not in the DB ?

How to retrieve data from a SQLDataSource control

I have made a SQLDataSource control with the select command:

SELECT COUNT(*) AS 'Antall' FROM Utgivelse WHERE (medieID = @.medieID)

I want to use the "Antall" result programmatically in C# code. I try the following statement:

IDataReader MyReader;

MyReader = CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty),IDataReader);

but it doesnot work. Can somebody help me how to get the data from th control ?

Tom

What do you mean by "it does not work"? Do you get an error message? If so, what is it? Also, what database are you accessing? SQL Server? Access? Did you set the mode of the DataSource to DataReader or leave it as the default DataSet?

Have a look at this:http://www.mikesdotnetting.com/Article.aspx?ArticleID=45

Finally, are you using the result from the datasource for any other purpose? If not, you would be better using plain ADO.NET code and ExecuteScalar() for a Count result. If you are binding the result to a control, it would be simpler to read the value from the control once it has been bound.

|||

I got the answer of my problem i the URL you gave me. My working code:

DataView dvSql = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);

int result;foreach (DataRowView drvSqlin dvSql)

{

result = (int)drvSql["antall"];

if (result > 0)

Label1.Text ="The record is in use and can not be deleted";

Thank a lot for your help !!!

Tom

How to retrieve data between two dates

For e.g
I want the amount of a customer between 2nd March 2004 and 2nd October
2004.
Basically How to filter this as i cannot put it in a where clause.i.e
customer name in rows amount in columns.
Is this in SQL Server or AS?
If the former, please post DDL, sample data, and sample output.
http://www.aspfaq.com/etiquette.asp?id=5006
If the latter, I will let someone else handle this question

Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Amit Asawa" <amit.asawa@.capgemini.com> wrote in message
news:814276fc.0412022212.7d57e2da@.posting.google.c om...
> For e.g
>
> I want the amount of a customer between 2nd March 2004 and 2nd October
> 2004.
> Basically How to filter this as i cannot put it in a where clause.i.e
> customer name in rows amount in columns.
|||If u have got solution to this problem can u please forward it to me at mailketna@.yahoo.com.
I have same problem in asp.net in crystal reports.
Thankyou
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Can you post DDL, sample data, and sample output? I'll help you out, but
not via e-mail. Let's keep this public so that others can benefit from the
discussion as well.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"jalpa patel" <mailketna@.yahoo.com> wrote in message
news:u%23l$ihn4EHA.3388@.TK2MSFTNGP15.phx.gbl...
> If u have got solution to this problem can u please forward it to me at
mailketna@.yahoo.com.
> I have same problem in asp.net in crystal reports.
> Thankyou
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

How to retrieve data between two dates

For e.g
I want the amount of a customer between 2nd March 2004 and 2nd October
2004.
Basically How to filter this as i cannot put it in a where clause.i.e
customer name in rows amount in columns.Is this in SQL Server or AS?
If the former, please post DDL, sample data, and sample output.
http://www.aspfaq.com/etiquette.asp?id=5006
If the latter, I will let someone else handle this question

Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Amit Asawa" <amit.asawa@.capgemini.com> wrote in message
news:814276fc.0412022212.7d57e2da@.posting.google.com...
> For e.g
>
> I want the amount of a customer between 2nd March 2004 and 2nd October
> 2004.
> Basically How to filter this as i cannot put it in a where clause.i.e
> customer name in rows amount in columns.|||If u have got solution to this problem can u please forward it to me at mail
ketna@.yahoo.com.
I have same problem in asp.net in crystal reports.
Thankyou
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Can you post DDL, sample data, and sample output? I'll help you out, but
not via e-mail. Let's keep this public so that others can benefit from the
discussion as well.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"jalpa patel" <mailketna@.yahoo.com> wrote in message
news:u%23l$ihn4EHA.3388@.TK2MSFTNGP15.phx.gbl...
> If u have got solution to this problem can u please forward it to me at
mailketna@.yahoo.com.
> I have same problem in asp.net in crystal reports.
> Thankyou
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

How to retrieve data as XML from SQL database

Hi,

I have a website which is designed to search for employee information. I have the search system working which does exactly what I want to, but as an added feature I want there to be a button which, when someone clicks on it, it takes whatever the previous search was and generates a set of data in XML format which is based on the results. For example:

User searches for all entries with Forename = John; Results are listed in a gridview as per expected.

User then presses button with XML on it, and page pops up with just the XML output on it, i.e. whatever results are on the gridview but in a nested XML format

<records>
<record>
<Forename>John</Forename>
<Surname>Smith</Surname>
<Email>j.smith@.blah.com</Email>
<Ext>1234</Ext>
<DeptList>History</DeptList>
</record>
</records>

I have created a stored procedure which will take the parameters from the search boxes and return the above information, but I don't know if this is the best way. Here it is for those interested:

CREATE

PROCEDURE ps_record_SELECT_NameSurnameEmailExtDeptasXML

@.Forename

varchar(50),

@.Surname

varchar(50),

@.Email

varchar(50),

@.Ext

varchar(4),

@.DeptList

varchar(50)

SELECT

Forename, Surname, Email, Ext, DeptList

FROM

dbo.record

WHERE

ForenameLIKECOALESCE(@.Forename,Forename)AND

Surname

LIKECOALESCE(@.Surname,Surname)AND

LIKECOALESCE(@.Email,Email)AND

Ext

LIKECOALESCE(@.Ext,Ext)AND

DeptList

LIKECOALESCE(@.DeptList,DeptList)

FOR

XMLAUTO,ELEMENTS

If someone could be kind enough to help me out with this, I'd be really grateful.

Many thanks,

Tom

Hi tomosap,

Yes, this is a good way to get that.

Another way is to fill everything to run the query and fill data into a DataSet. You will be able to get the DataSet's data in XML format by calling DataSet.WriteXml() method.

Both these methods are good.

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

|||

Hi,

Thanks for your reply - I'm afraid that my needs have slightly changed since this was posted. I now have an XML file which I transform using XSL so it displays all the data in a gridview. What I want to do is to have a search form so that I can search the data that's in the gridview. I.e.

Name: [INPUT]

Results in Gridview...

User enters name in input, clicks search, then all matches in Name column on the gridview is displayed. I've successfully managed to do this with a basic SQL database using the object source control and passing paramaters, but I don't know how to do this when the data source is XML.

All help would be appreciated.

Thanks,

Tom

How to retrieve current date and time from SQL2005 server.

well i'm using java to connect to SQL 2005 server. i need to retrieve it's current time and date on the sql 2005 server and use it on my remote desktop

thanks.

Hi,

You can use the following query :

SELECT getdate() as myDate

wich will give you somthing like "2006-10-27 11:29:15.373"

Regards

HOW to Retrieve an image from sql server and display it in ASP.net using "imagemap or image

Ok, the problem is that , i have a field called "Attach" in sql of type image, when selecting it , the field is getting data of type BYTE(). which am being unable to display them on an Image on the panel.

using the following vb.net code:

'Dim sel2 As String

'Dim myCom As SqlCommand

'Dim conn As New SqlConnection

'Dim drr As SqlDataReader

'Dim image As System.Drawing.Image

'sel2 = "select * from attach where att_desc = '" & DropDownList1.SelectedItem().Text & "' and doc_code = " & w_doc_code & " and subcode = " & w_doc_subcode & " and doc_num= " & w_doc_num & " "

'conn.ConnectionString = ("server=developer01;uid=sa;password=aims;database=DVPSOC;timeout=45")

'myCom = New SqlCommand(sel2, conn)

'conn.Open()

'drr = myCom.ExecuteReader()

'If drr.Read Then

' Me.ImageMap1.ImageUrl = drr.Item("attach")

'End If

'conn.Close()

Am getting an exeption on the following line Me.ImageMap1.ImageUrl = drr.Item("attach")

saying: Conversion from type 'Byte()' to type 'String' is not valid.

knowing that i tried converting using ToString but it's not getting any output then.

thanks for your help.

a example in C# as below:

try

{

con = new SqlConnection(constr);
cmd = new SqlCommand("select photopath,Photo from employees where employeeid=14", con);
con.Open();
dr = cmd.ExecuteReader();
while(dr.Read())
{
if (!dr.IsDBNull(1))
{
byte[] photo = (byte[])dr[1];
MemoryStream ms = new MemoryStream(photo);
pictureBox1.Image = Image.FromStream(ms);
}

}
}
catch (Exception ex)
{
dr.Close();
cmd.Dispose();
con.Close();

MessageBox.Show(ex.Message);
}

hope can help u a little

|||

Sorry but that is not the answer to the question. picturebox cannot be used in ASP.Net.

HOW to Retrieve an image from sql server and display it in ASP.net using "imagemap or image

using the following vb.net code:

'Dim sel2 As String

'Dim myCom As SqlCommand

'Dim conn As New SqlConnection

'Dim drr As SqlDataReader

'Dim image As System.Drawing.Image

'sel2 = "select * from attach where att_desc = '" & DropDownList1.SelectedItem().Text & "' and doc_code = " & w_doc_code & " and subcode = " & w_doc_subcode & " and doc_num= " & w_doc_num & " "

'conn.ConnectionString = ("server=developer01;uid=sa;password=aims;database=DVPSOC;timeout=45")

'myCom = New SqlCommand(sel2, conn)

'conn.Open()

'drr = myCom.ExecuteReader()

'If drr.Read Then

' Me.ImageMap1.ImageUrl = drr.Item("attach")

'End If

'conn.Close()

Am getting an exeption on the following line Me.ImageMap1.ImageUrl = drr.Item("attach")

saying: Conversion from type 'Byte()' to type 'String' is not valid.

knowing that i tried converting using ToString but it's not getting any output then.

thanks for your help.

a example in C# as below:

try

{

}
}
catch (Exception ex)
{
dr.Close();
cmd.Dispose();
con.Close();

MessageBox.Show(ex.Message);
}

hope can help u a little

|||

Sorry but that is not the answer to the question. picturebox cannot be used in ASP.Net.

HOW to Retrieve an image from sql server and display it in ASP.net using "imagemap or i

using the following vb.net code:

'Dim sel2 As String

'Dim myCom As SqlCommand

'Dim conn As New SqlConnection

'Dim drr As SqlDataReader

'Dim image As System.Drawing.Image

'sel2 = "select * from attach where att_desc = '" & DropDownList1.SelectedItem().Text & "' and doc_code = " & w_doc_code & " and subcode = " & w_doc_subcode & " and doc_num= " & w_doc_num & " "

'conn.ConnectionString = ("server=developer01;uid=sa;password=aims;database=DVPSOC;timeout=45")

'myCom = New SqlCommand(sel2, conn)

'conn.Open()

'drr = myCom.ExecuteReader()

'If drr.Read Then

' Me.ImageMap1.ImageUrl = drr.Item("attach")

'End If

'conn.Close()

Am getting an exeption on the following line Me.ImageMap1.ImageUrl = drr.Item("attach")

saying: Conversion from type 'Byte()' to type 'String' is not valid.

knowing that i tried converting using ToString but it's not getting any output then.

thanks for your help.

a example in C# as below:

try

{

}
}
catch (Exception ex)
{
dr.Close();
cmd.Dispose();
con.Close();

MessageBox.Show(ex.Message);
}

hope can help u a little

|||

Sorry but that is not the answer to the question. picturebox cannot be used in ASP.Net.

How to retrieve all information abt all constraints inorder to drop and recreate

Hello,
I need to retrieve all the information about all the foreign key constraints,inorder to store them temporarily (to be deleted later)and then recreated after making the necessary modifications to the concerned tables.
The stored proc sp_helpconstraint shows all the constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines them.But I don't know whether it can be manipulated to get what I want.I need to get it done programmatically..so that I can integrate it in my program which I'm building up progressively.
Any help or scripts would be appreciated!First step - dropping:

select 'alter table '+TABLE_NAME+ ' DROP CONSTRAINT '+CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'|||i first need to store the information about the constraints before dropping them, so that they can be recreated later after the necessary changes have been made..i have 200+ tables...any tips?|||select *
INTO TempConstraintStore
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'

select 'alter table '+TABLE_NAME+ ' DROP CONSTRAINT '+CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'

INSERT INTO INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM TempConstraintStore

or something like that|||INSERT into system tables...hmmmmmmm

No thanks...

Just go in and Script the contraints and save the script

Do the drops, necessary changes have been made..then rerun the script

What are the changes?

If RI is out of wack the constraints will fail....|||i'm aware that scripting the constraints using EM would make life easier,but no such luck for me..it has to be done using TSQL..i'm using the information schemas to retreive info,like referential_constraints, columns,constraint_column_usage..is that enough??|||try to use SQL-DMO with something like that (VBS):

Set dmoSQLServer = CreateObject("SQLDMO.SQLServer")
dmoSQLServer.Connect "MyServer", "MyLogin", "MyPSWD"

For Each dmoObj In dmoSQLServer.Databases("MyDBName").Tables
If dmoObj.SystemObject = False Then
dmoObj.Script 134348800, dmoObj.Name & ".fky"
End If
Next

dmoSQLServer.Disconnect
Set dmoSQLServer = Nothing

One file storing all FK creates for each table (TableName.fky). If table have no FK this file will be empty|||Similar problem?

I simply want to add a foreign-key constraint to an existing column.

This is what I have but no luck so far.

alter table table_name with check
alter column column_name
add constraint foreign_key_name
references Reference_Table (Reference_column)

I know it can't be far from this - I think I've even done it before!

John|||I'd use something like:ALTER TABLE myTable
ADD CONSTRAINT myConstraint
FOREIGN KEY (myColumn)
REFERENCES anotherTable (differentColumn)-PatP

How to retrieve a value from the Inserted table

I'm writing a trigger for my DotNetNuke portal that requires me to read the value of a just inserted record. I believe I'm doing this right, still I'm unable to retrieve the desired value from the Inserted table. What am I doing wrong? Here is my code:

CREATE TRIGGER tr_addEditorRole ON [dbo].[UserPortals]
AFTER INSERT
AS
Declare @.Portal int
set @.Portal = (select PortalId FROM inserted)

Declare @.TabId Int
set @.Tabid = (select TabID from Tabs where Tabs.PortalID = @.Portal
and Tabs.TabName = 'MyTab')

Declare @.ModuleId int
set @.ModuleId = (SELECT ModuleId FROM Modules WHERE Modules.TabID = @.TabId
and Modules.ModuleTitle = 'MyModule')

update Modules
set
AuthorizedEditRoles = '-1;'

where ModuleId = @.ModuleIdtry changing this:
set @.Portal = (select PortalId FROM inserted)

to this:
SELECT @.Portal=PortalID FROM Inserted|||Thanks for the advice, but I've tried that syntax too - without any luck.

I'm getting no compilation errors, so the sytax is OK. I don't have any fancy tools to check what value (if any) that is being returned from the Inserted table. The problem is somewhere else...|||if PortalID is an identity column then use

SET @.Portal = @.@.Identity

@.@.Identity is the value from the LAST record inserted so as long as your only inserting one record at a time, that should work.|||I've tried that too...and get an error in my application. Also, being a newbie, I don't know if PortalID is an identity column, and given that I get an error it might not be. Can I make it an identity column? If so, could that cause my app to break somewhere else?

Furthermore, I am only inserting one record at a time, but there might be other users performing the same transaction (although with other unique values being inserted).

hmm...?

how to retrieve a row by specifying row number

Hi,
Is there any sql command to retrieve a particular row by specifying its
row number."supriya" <santu@.hotmail.com> wrote in message
news:OriSXyJTGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any sql command to retrieve a particular row by specifying its
> row number.
Oh I can't wait for the replies to this one :-)

>|||hi
you can use this type of Query
This Query is from Pubs DB
SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2
WHERE e2.lname <= e.lname AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY lname
Hope this helps you
Regards
Shrinivas|||Hi
What vesrion are you using?
SQL Server 2005
CREATE TABLE SpeakerStats
(
speaker VARCHAR(10) NOT NULL PRIMARY KEY,
track VARCHAR(10) NOT NULL,
score INT NOT NULL,
pctfilledevals INT NOT NULL,
numsessions INT NOT NULL
)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron', 'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy', 'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe', 'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert', 'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike', 'DB', 8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian', 'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin', 'DB', 7, 25, 4)
--1 (ROW_NUMBER())
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, track, score
FROM SpeakerStats
ORDER BY score DESC
--2
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, speaker
--3
DECLARE @.pagenum AS INT, @.pagesize AS INT
SET @.pagenum = 2
SET @.pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@.pagenum-1)*@.pagesize+1 AND @.pagenum*@.pagesize
ORDER BY score DESC, speaker
"supriya" <santu@.hotmail.com> wrote in message
news:OriSXyJTGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any sql command to retrieve a particular row by specifying its
> row number.
>

How to retrieve a report in SQL Reporting Server

Is there a way to read the rdl file that is already deployed on the Report
Server to modify it? I have some reports on RS but don't have the original
rdl file.
Thanks
AyadAyad,
Open Report Manager (http://localhost/reports), select your report and
click the 'Properties' tab. You'll see a section on the page called
"Report Definition". Click the Edit link and you'll have the RDL.
Andy Potter|||To extract multiple disparate reports at the same time you could also use
Reporting Services Scripter
http://www.sqldbatips.com/showarticle.asp?ID=62
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Ayad" <ashammout1@.hotmail.com> wrote in message
news:O5WPL1LDGHA.740@.TK2MSFTNGP12.phx.gbl...
> Is there a way to read the rdl file that is already deployed on the Report
> Server to modify it? I have some reports on RS but don't have the original
> rdl file.
> Thanks
> Ayad
>

how to retrieve a password saved by SQL server

Hi,

I am able to connect to a SQL server and have checked the "remember my password" box in the connection diaglog window. Unfortunately, I forget what the password is, and now have to use it elsewhere. How can I get it? (I tried copy and paste, but it won't work).

Thanks,

Hi,

Unfortunatly I am beginer in this but it happens for me to have a corrupted master database and I couldn't login to SQL. The solution it was that I restore the master database using the command: rebuildm.exe from the SQL CD.

If I would have a problem like you, I would make a screen shots of all the settings which I have, I will detache the database I will stop the SQL service and I will restore the master database and I will put a new password and redo all the settings.

Good luck,

Diana

|||

Diana, thanks a lot for reply. I am still hoping there might a way to extract the password from my computer (since i have no trouble connecting to the server). Does anybody have any idea?

Thanks,

June

|||MSSQL stores not passwords but password hashes. There is no way to retrieve clear password from its hash: the hash operation is irreversible.

how to retrieve a col definition NULL or NOT NULL

Hello,
I usually retreive information on columns using:
SELECT * FROM syscolumns order by name asc
SELECT * FROM systypes
systypes.names defines the type for instance.
But I am trying to find what defines NULL or NOT NULL for each columns.
Would someone know what defines it?
thanks for your helpDid you check the isnullable column in syscolumns?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Wilfrid" <grille11@.yahoo.com> wrote in message news:479723a7$0$7041$426a74cc@.news.free.fr...
> Hello,
> I usually retreive information on columns using:
> SELECT * FROM syscolumns order by name asc
> SELECT * FROM systypes
> systypes.names defines the type for instance.
> But I am trying to find what defines NULL or NOT NULL for each columns.
> Would someone know what defines it?
> thanks for your help
>
>
>|||Next time I will open my eyes, sorry to bother.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eVhTlfbXIHA.1188@.TK2MSFTNGP04.phx.gbl...
> Did you check the isnullable column in syscolumns?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Wilfrid" <grille11@.yahoo.com> wrote in message
> news:479723a7$0$7041$426a74cc@.news.free.fr...
>> Hello,
>> I usually retreive information on columns using:
>> SELECT * FROM syscolumns order by name asc
>> SELECT * FROM systypes
>> systypes.names defines the type for instance.
>> But I am trying to find what defines NULL or NOT NULL for each columns.
>> Would someone know what defines it?
>> thanks for your help
>>
>>
>>
>|||No worries. Sometimes the obvious isn't so ... obvious. :-)
Also, if you are on 2005, consider using the catalog views instead, for instance sys.columns.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Wilfrid" <grille11@.yahoo.com> wrote in message news:479736f1$0$10823$426a74cc@.news.free.fr...
> Next time I will open my eyes, sorry to bother.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eVhTlfbXIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Did you check the isnullable column in syscolumns?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Wilfrid" <grille11@.yahoo.com> wrote in message news:479723a7$0$7041$426a74cc@.news.free.fr...
>> Hello,
>> I usually retreive information on columns using:
>> SELECT * FROM syscolumns order by name asc
>> SELECT * FROM systypes
>> systypes.names defines the type for instance.
>> But I am trying to find what defines NULL or NOT NULL for each columns.
>> Would someone know what defines it?
>> thanks for your help
>>
>>
>>
>>
>

How to retrieve @@Rowcount variable

I'm trying to output the number of rows that were effected by my stored proc.

Here is the stored proc:

ALTER Proc Update_IndividualMoves_GTPhone_NCOAPhone_Differ
AS
Update Results
SET Results.home_phone = Results.NEWPhone,
Results.Address1 = Results.NCOAADDRESS1,
Results.CITY = Results.NCOACITY,
Results.ST = Results.NCOAST,
Results.ZIP_OUT = Results.NCOAZIP5,
Results.ZIP4_OUT = Results.NCOAZ4
Where AddressServiceStatus = 'I' AND home_phone IS NOT NULL AND NEWPhone IS NOT NULL AND home_phone <> NEWPhone
Return @.@.Rowcount

Here is the code from the DAL class that I'm calling the stored procedure from (I'm using the SQL Helper Class.)

Public Shared Function GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ()

Dim Rowcount As Integer
Dim GlobalConnString As String = AppSettings("ConnectionString")
''Put proc in that gets this data out for household moves that have both
''GTPro and NCOA update phone numbers however they differ. Does not apply
''to DRC donors / < 12 month donors. Update to latest and greatest phone number
''from NCOA listing.

Try
Return ExecuteDataset(GlobalConnString, CommandType.StoredProcedure, "Update_HouseholdMoves_GTPhone_NCOAPhone_Differ", New SqlParameter("@.@.Rowcount", Rowcount))

Catch ex As Exception
Throw New ApplicationException("An error occured when calling this stored proc out Update_HouseholdMoves_GTPhone_NCOAPhone_Differ")

End Try
End Function

I want to post how many rows were effected in a label that is located on my aspx page through referencing the function above:

What I'm doing is activating the function through an asp:button control and then I want to display the @.@.Rowcount result in the label next to it.

Here is what I have now:

<code
Private Sub cmdHouseholdMove2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdHouseholdMove2.Click
'Dim rowcount As Integer
GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ(New SqlParameter("@.@.rowcount", lblHouseholdMoves2.Text))

End Sub

If anyone knows how to do this please let me know:

Thanks in advance everyone.

Regards,
RByou can use an OUTPUT parameter to return the rowcount...check books on line for sample code.
some sample code for retreiving the output parameter from asp.net..


dim result as integer
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.bigint
mycommand.Parameters.Add(myParam)
result = convert.toint16(mycommand.Parameters("@.result").Value))

hth

how to retriece single record in database by using SqlDataSource??

Is me again,and now i facing problem to retrieve a single record from the database.

here is my code:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim user As String
user = TextBox1.Text
Dim varpassword
Dim mydata As SqlDataSource

mydata.SelectCommand = "Select * from tbluser where uLogin = '" & user & "'"
varpassword = mydata.SelectCommand.uPassword

End Sub
End Class

but i get the error : 'uPassword' is not a member of 'String'

i wan to retrieve the password of that user,can anyone help me?

thanks Smile

First create a select parameter "uPassword" of type "String".

|||

Girijesh:
First create a select parameter "uPassword" of type "String".

i not really understand about creating the select parameter.

can u write the line of code for me?thanks Smile

|||

Hi there,

Use this:

imports System.Data.Sql

Dim dt as new DataTable

Dim query As String = "Select * from tbluser where uLogin = '" & user & "'"

Dim conn as new SqlConnection(connection)

Dim adapter as new SqlDataAdapter

adapter.SelectCommand =new SqlCommand(query, conn);
adapter.Fill(dt);

// this DataTable only has one row, the one for this username

Dim password As String = dt.rows(0)("uPassword")

hope it helps you out,

gonzzas

How to retreive set of rows from stored procedure

hi,

i am new to SQL. i have created a stored procedure which gets a input parameter "Category" and it selects datas which falls under this category. when i run this procedure it returns only the last row. it doesnt retreive the entire set of rows. which method should i follow to solve my problem.. i want all the rows which comes under the category to be returned ....

MY PROCEDURE

CREATE PROCEDURE [dbo].[Items_Category_sorted]

(

@.Category varchar(10),

@.ProductID Char(10) OUTPUT,

@.Name Char(50) OUTPUT,

@.UnitPrice Numeric(9) OUTPUT,

@.Stock Numeric(9) OUTPUT

)

BEGIN

SET NOCOUNT ON;

SELECT @.ProductID=ProductID,

@.Name=Name,

@.UnitPrice=UnitPrice,

@.Stock =Stock

From

ProductDetails

Where

Category=@.Category

END

As you're using output parameters, you can only have one value per parameter and this will be set to values in the last row returned by your query.

To return a recordset of multiple values, try:

CREATE PROCEDURE [dbo].[Items_Category_sorted]

(

@.Category varchar(10)

)

BEGIN

SET NOCOUNT ON;

SELECT ProductID,

Name,

UnitPrice,

Stock

From

ProductDetails

Where

Category=@.Category

END

Hope this helps!

How to retreive data from Excel to SQL SERVER Database table

Hi Friends,

I am Using SQL SERVER 2005

I need to retreive data from Excel file to SQLSERVER table..

Thanks in advance

Regards

Rajkumar.M

There are many ways to do this. You may use SQL Server integration services, or you may create a persistent connection to any data source supporting OLE DB (including MS Excel) via linked servers, or you may use OPENDATASOURCE clause in your query.

http://support.microsoft.com/kb/321686 will help you start.

How to retreive data from Excel into SQLSERVER database table

Hi friends,

I need to retreive data from Excel into SQLSERVER database table

give me reply asap.

Thanks in advance...

you can do it, using SqlBulkCopy using ADO.NET

I have developed a small tool in C# that can work for you,

I have tested it using sql server 2000/2005 and works ok
but you require Framework 2.0

so if you wanna test it, just mail me
vish4forum at yahoo dot com. I will send you that exe

|||

Hi Vish,

I found ur reply useful..

please send me sample code that will be better

|||

Hi Vish,

I tried using SQLBulk copy..But i got error 'could not find installable ISAM'

Here is my code

string excelConnectionString = @."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=192.168.0.25;Extended
Properties=""Excel 11.0;HDR=YES;IMEX=1;""";

OleDbConnection con = new OleDbConnection(excelConnectionString);

con.Open();
OleDbCommand cmd = new OleDbCommand("select * from [test$]", con);
dr = cmd.ExecuteReader();

SqlConnection con1 = new SqlConnection();
con1.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["strcon"];
con1.Open();
SqlBulkCopy bulk1 = new SqlBulkCopy(con1);
bulk1.DestinationTableName = "tblexcel";
bulk1.WriteToServer(dr);
Response.Write("Loaded");

|||

Checkout this two articles

1)http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

2)http://support.microsoft.com/kb/321686

hope this helps !!!

how to retreive data depends on another data ...

my application will add and delete and update records in db

my problem is when to update and delete ..

I have two dropdownboxes one to retreive servers and one to retreive dbs in the server ...

SqlDataAdapter daa =

new SqlDataAdapter("SELECT * FROM DB_tbl"+","+"Servers_tbl where Servers_tbl.Server_Name == '"+ DropDownList4.SelectedValue +"' ", sqlConnection1 );

this is the structure of each table >>

servers_tbl : SRV_ID,Server_name

DB_teb : DB_ID,SRV_ID,DB_name

the dropdown box retreive just the names ... so what is the base of where part of the command

hint : the DB_tbl does not have the server name but has the sever id

Hi Adorer,

Based on my understanding, you need to know when to issue a data bind on the dropdownlist which displays db names.

I think you can handle the SelectedIndexChanged event of the Server DropDownList to fill the db names.

You can use 2 SqlDataSources and use a ControlParameter to achieve this. Also, make sure you have set AutoPostback property to true.

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

How to retieve data using cellset

Dim cs as cellset
cmd = con.CreateCommand()
cmd.CommandText = "Select [Geography].[Geography].[Country].&[United States] ON ROWS, [Date].[Calender Year].[Calender Year] ON COLUMNS FROM [Adventure Works]"
cs = cmd.ExecuteCellSet()
Dim tuplesOnColumns As TupleCollection
tuplesOnColumns = cs.Axes(0).Set.Tuples
Dim column As Tuple
For Each column In tuplesOnColumns
result.Append(column.Members(0).Caption, "/t")
Next column
Response.Write(result.ToString)

i cannot get anydata when i run this , my connection with the database is good can anyone help me how to get the data using cellset

hello Aravind,

i think an example below should work:

Dim cs As CellSet
Dim cmd As AdomdCommand

cmd = con.CreateCommand()
cmd.CommandText = "Select [Geography].[Geography].[Country].&[United States] ON ROWS, [Date].[Calendar Year].members ON COLUMNS FROM [Adventure Works]"

cs = cmd.ExecuteCellSet()

Dim result As StringBuilder
result = New StringBuilder()

Dim tuplesOnColumns As TupleCollection
tuplesOnColumns = cs.Axes(0).Set.Tuples

Dim column As Tuple

For Each column In tuplesOnColumns

result.Append(column.Members(0).Caption)
result.Append("\t")

Next column

System.Diagnostics.Debug.WriteLine(result.ToString())

hope this helps some,

How to retain text formatting in a table

What is the best way to parse large amounts of formatted text datainto a table so that it can be retrieved with as much formattingretained as possible - particularly paragraphs? Will eachparagraph need to be inserted into its own row to be retrieved as aparagraph?

Wrap each paragraph in an HTML paragraph goes here section. They can sit together just like this

123

|||

Thanks, Bryan.

|||

To follow up, I am having trouble retrieving the text data in aparagraph format. For example, I input123into a text field in the database and dropped the table onto a page inVisual Web Developer. The data format looked exactly like it wasinput into the db (i.e.,123) with theparagraph tags showing but no actual paragraphs. Is there a trickto getting the paragraph formatting to show up properly in the controlon my web page?

|||

It depends on what kind of control you are trying to show the value in. Some controls render output to the page as HTML. You have to understand what really happens when an ASPX page is rendered. Browsers dont know what asp.net is. They dont care, they never see it. This is why you need an asp.net server and IIS. The engine on the server downrenders your .net code to HTML in a way the browser can understand. If you view the source of one of your pages, you'll notice all you have is a bunch of HTML and javascript.

The example below points this out by using a label (which just throws values into the HTML stream). And a textbox (which renders literal output)

<asp:LabelID="label1"runat="server"></asp:Label>

<asp:TextBoxID="textbox1"runat="server"/>

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

label1.Text ="123"

textbox1.Text ="123"

EndSub

|||

Bryan - thanks for the input. But I am a little baffled sinceall three controls on my page are ASP (and not simply html) and all arerunat="server" (which I understand to mean that the server converts theasp to html for the browser). But only the ASP label has theparagraph formatting. Since I don't expect to be using the labelcontrol as a primary means of displaying the extensive text data fromthe db, is it simply a matter of setting control properties on othercontrols (like the gridview dataformatstring property) to get them tohandle the tags? Or am I simply limited in my choice ofcontrols that can do this job?

|||

Not all controls will render ...for lack of a better term...renderable HTML from their data / text property. Inside gridviews for example, you'll probably want to use Bound labels, or literal controls. I think you may be missing something conceptually here. Explain to me what type of gridview you are rendering, maybe I can shed some light on the subject - and give you an example of how you'd do what you're trying to do.

|||

The project I am considering involves providing searchcapability on numerous, various documents (mostly magazine articles andresearch papers) each of which is several pages long with faily complexformatting (if I retain the original look). The product needs to beavailable online and on CDs. The actual search results willprobably contain document titles or abstracts that can be selected sothat the full article will be displayed - probablly a gridview orsimilar control for the brief results list with some type of detailsview counterpart for the selected text content display. My choiceof controls may need to include Windows forms controls as well as webpage controls.

At this early stage I am wondering if Ishould put the formatted documents into the database as varbinary datathat can be searched and retrieved maintaining the formatting. Or, just put the text data into the db with simple paragraph formattingthat will give a uniform look to the search output. I have done abit ofdatabase work involving simple data searching, etc., but never withextensive, complex text fields such as this where the formatting in theoutput is important.

Thanks for the insights so far and for any additional help you can provide.

|||

Ok, I think you've done a pretty good job thinking through this so far, but I'll offer up a little advice to you. First of all, probably the best / most accepted web format for complex documents is PDF. There are numerous .NET utilities to encode input into a PDF, including converters that could take word documents, excel spreadsheets, star office...just about anything you can think of - and turn it into a PDF for your purposes. Now, my advice would be to NOT put all this information in the database. To truly allow a full text search - you are going to cause yourself a huge amount of work, and headache.

Store a path to the PDF in your database, so you know where it's at, and render a title, maybe document iinformation like creation time, last modified, owner, short description...etc.

Now the magic comes in. Tie into the MS Indexing service. It can allow a really slick full text search, and there is a connector that allows the indexing service to search inside pdfs. So all the text inside the PDF itself will be indexed, and included in the search criteria. What's returned from a call to the indexing service is a location to the document (your aspx form). My advice is to break it out like this

Site > Document List > Document Information Page with a link to the document (derived from the path in your database)

When the MS indexing service returns, it will return the location of your Document Information Page (as a link) or a list of them depending on how many results were returned. Clicking it will take you directly to the pertitant information.

Here is a great article on accomplishing this.

http://aspnet.4guysfromrolla.com/articles/033005-1.aspx

|||

Thanks again Bryan. I have been looking at that article and will try that approach.

Followupquestion on the previous discussion on controls. Are you aware ofany web controls that render html besides the label? I wouldthink the textbox would have a property that would support htmlrendering but can't find any.

|||

A textbox will not. You'll need an HTML based textbox that can support it. Much like the one you use to post comments on this site. There are several out there. the FCK Editor is the one I usually use, but there's also FreeTextBox, which is the other big free one.

An ASP:Literal control gives you options to render HTML formated output, transform it, or passthrough. That may be a great display control for you, but .NET has no controls that are directly editable, that support HTML nativly.

|||

Bryan. Thanks for all your help in sorting this out. Good to have some options as I move forward with this project.

How to Retain Text and Paragraph Formatting

Is it possible to retain text or paragraph formatting in a SqlServer 2005 Express edition table? If so, how?

I am particularly interested in keeping a linebreak between paragraphs and the only way I can think to do that is to put each paragraph in its own row. But I want some input before I undertake that substantial task.

Thanks for any help provided.
You can use char(10) which is the hard break in SQL.

eg.

Code Snippet

Print 'Microsoft' + char(10) + 'Website'

The Output is like below:
Microsoft
Website

You can as many as char(10) to provide line break in your query.

Even the below syntax will also work.

Code Snippet

select 'Microsoft' + char(10) + 'Website'

|||Hi Vidhya. Thanks for your advice.

I am trying to understand how to insert the data into a table and retrieve to a control on a VB form. In Visual Basic Express I tried including the char(10) with the data by pasting it into my table like this:

Paragraph 1 text here. + char(10) + Paragraph 2 text here. + char(10) + Paragraph 3 text here.

But all I get for output in my datagrid control is the literal string as one block of text.

I use a similar technique with html tags (

) if I am outputing to a webcontrol and it seems to work okay if the control renders html (ie.,

Paragraph 1 text here

Paragraph 2 text here

Paragraph 3 text here

). But this doesn't work for Windows forms unless I am using a webbrowser control and html controls (which I don't want to do).

So I opened up SQL Server Management Studio Express and created a database with a table (text) and column (paragraph) and ran these scripts:

INSERT INTO Text (Paragraph)
VALUES ('Paragraph 1 text here.' + char(10) + 'Paragraph 2 text here.' + char(10) + 'Paragraph 3 text here.')

SELECT *
FROM Text

select 'Microsoft' + char(10) + 'Website'
From Text

But my output is still a single line of text without paragraph formatting.

When I run the code you provided (Print 'Microsoft' + char(10) + 'Website'), that works perfectly. But I don't know how to insert and retrieve the formatted text data in my db.

I am relatively new to SQL, so I realize that I am missing something simple in my implementation of your advice. Any suggestions on how to apply this concept is greatly appreciated.|||To Insert into table jus insert as usual

insert into <tablename> values('col1','col2')

To select from the table with char(10) you can use

select col1,char(10),col2 from <tablename>

Run the above query in text format(ctrl+t)

|||Thanks for the follow-up. I have had some success (I think). Here is what I have tried:

CREATE TABLE Paragraph
(ID int Primary Key IDENTITY(1,1) NOT NULL,
FormattedText nvarchar(500) NOT NULL)

INSERT INTO Paragraph (FormattedText)
VALUES ('Paragraph 1 text here.' + char(10) + 'Paragraph 2 text here.' + char(10) + 'Paragraph 3 text here.')

select FormattedText from Paragraph

If I have the "Results to Text" button clicked, the output looks great, like this:

Paragraph 1 text here.
Paragraph 2 text here.
Paragraph 3 text here.

If I output with "Results to Grid" clicked, it loses the format and looks like this:

Paragraph 1 text here. Paragraph 2 text here. Paragraph 3 text here.

So I think I am making progress. I am getting the data into my table ok. Getting it out in the proper paragraph format is the challenge, which is easy in Management Studio.

But when I use the same table in Visual Basic and output the row of data to a datagrid, it comes out like this, without the paragraph formatting:

Paragraph 1 text here. Paragraph 2 text here. Paragraph 3 text here.

So my problem now is how to get it to display properly on my Windows Form. Any thoughts on this, or do I need to switch to the VB forum?|||Im not sure abt VB. But i think there is a command "Break" in VB. Pls check
|||I will look into it further. There is a break property for creating menus, but I am not sure if it can be applied to datagrids or other controls for displaying formatted data.

Thanks again for your helpl.

How to retain content of TempDB even if SQL Service restart?

Gurus,
I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreated.
Is there any option / way to retain the content of TEMPDB even if SQL Server
/ SQL Server Services restarted?
Help much appreciated.
Thanks
Javed Iqbal
On Mon, 30 Jul 2007 12:32:01 -0700, Javed Iqbal
<JavedIqbal@.discussions.microsoft.com> wrote:

>Gurus,
>I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreated.
>Is there any option / way to retain the content of TEMPDB even if SQL Server
>/ SQL Server Services restarted?
>Help much appreciated.
>Thanks
>Javed Iqbal
No.
Roy Harvey
Beacon Falls, CT
|||> Is there any option / way to retain the content of TEMPDB even if SQL
> Server
> / SQL Server Services restarted?
Yes, re-create the content manually in a startup procedure.
Or, store it in a "real" user database.
Aaron Bertrand
SQL Server MVP
|||Thanks Aaron,
This is what I was looking for...
Thanks a lot
Regards
Javed Iqbal
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, re-create the content manually in a startup procedure.
> Or, store it in a "real" user database.
> --
> Aaron Bertrand
> SQL Server MVP
>
>

How to retain content of TempDB even if SQL Service restart?

Gurus,
I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreated.
Is there any option / way to retain the content of TEMPDB even if SQL Server
/ SQL Server Services restarted?
Help much appreciated.
Thanks
Javed IqbalOn Mon, 30 Jul 2007 12:32:01 -0700, Javed Iqbal
<JavedIqbal@.discussions.microsoft.com> wrote:
>Gurus,
>I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreated.
>Is there any option / way to retain the content of TEMPDB even if SQL Server
>/ SQL Server Services restarted?
>Help much appreciated.
>Thanks
>Javed Iqbal
No.
Roy Harvey
Beacon Falls, CT|||> Is there any option / way to retain the content of TEMPDB even if SQL
> Server
> / SQL Server Services restarted?
Yes, re-create the content manually in a startup procedure.
Or, store it in a "real" user database.
--
Aaron Bertrand
SQL Server MVP|||Thanks Aaron,
This is what I was looking for...
Thanks a lot
Regards
Javed Iqbal
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Is there any option / way to retain the content of TEMPDB even if SQL
> > Server
> > / SQL Server Services restarted?
> Yes, re-create the content manually in a startup procedure.
> Or, store it in a "real" user database.
> --
> Aaron Bertrand
> SQL Server MVP
>
>

How to retain content of TempDB even if SQL Service restart?

Gurus,
I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreate
d.
Is there any option / way to retain the content of TEMPDB even if SQL Server
/ SQL Server Services restarted?
Help much appreciated.
Thanks
Javed IqbalOn Mon, 30 Jul 2007 12:32:01 -0700, Javed Iqbal
<JavedIqbal@.discussions.microsoft.com> wrote:

>Gurus,
>I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreat
ed.
>Is there any option / way to retain the content of TEMPDB even if SQL Serve
r
>/ SQL Server Services restarted?
>Help much appreciated.
>Thanks
>Javed Iqbal
No.
Roy Harvey
Beacon Falls, CT|||> Is there any option / way to retain the content of TEMPDB even if SQL
> Server
> / SQL Server Services restarted?
Yes, re-create the content manually in a startup procedure.
Or, store it in a "real" user database.
Aaron Bertrand
SQL Server MVP|||Thanks Aaron,
This is what I was looking for...
Thanks a lot
Regards
Javed Iqbal
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, re-create the content manually in a startup procedure.
> Or, store it in a "real" user database.
> --
> Aaron Bertrand
> SQL Server MVP
>
>

Friday, February 24, 2012

erver Error in '/Alumni' Application.

Object reference not set to an instance of an object.

Blog Archive

About Me