ZombieCheck: March 2012

Sunday, March 11, 2012

Year only data from daily dates

Try:
select distinct
Year (obsDate) as 'Year'
from
tblObs
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Web learner" <beginner@.learning.edu> wrote in message
news:OSTG4DFWGHA.3492@.TK2MSFTNGP05.phx.gbl...
I have daily data from year 1995 to 2004. The following query
SELECT [obsDate] FROM [tblObs]
returns 3653 datevalue records for each day.
1995-01-01 00:00:00
1995-01-02 00:00:00
1995-01-03 00:00:00
--
--
2004-12-29 00:00:00
2004-12-30 00:00:00
2004-12-31 00:00:00
I just want a recordset having years. That means with only 10 records as
follow.
1995
1996
1997
--
--
2004
What should be the SQL query?
I do not know anything about objectDataSource of ADO.NET. Is it worthwhile
or better for this kind of problem?
Thanks for reading this ...
Regds,
Web learner
Yes, it worked. This is what I wanted for the moment.
Thanks.....
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ufjVPHFWGHA.4132@.TK2MSFTNGP04.phx.gbl...
> Try:
> select distinct
> Year (obsDate) as 'Year'
> from
> tblObs
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Web learner" <beginner@.learning.edu> wrote in message
> news:OSTG4DFWGHA.3492@.TK2MSFTNGP05.phx.gbl...
> I have daily data from year 1995 to 2004. The following query
> SELECT [obsDate] FROM [tblObs]
> returns 3653 datevalue records for each day.
> 1995-01-01 00:00:00
> 1995-01-02 00:00:00
> 1995-01-03 00:00:00
> --
> --
> 2004-12-29 00:00:00
> 2004-12-30 00:00:00
> 2004-12-31 00:00:00
> I just want a recordset having years. That means with only 10 records as
> follow.
> 1995
> 1996
> 1997
> --
> --
> 2004
> What should be the SQL query?
> I do not know anything about objectDataSource of ADO.NET. Is it worthwhile
> or better for this kind of problem?
> Thanks for reading this ...
> Regds,
> Web learner
>

Year function in sql query

What's the error you're getting?

Error source:Microsoft Jet Database Engine

Error Message:Undefined function '[Year]' in expression

please note it adds [ ] Brakets to year

Than that's the problem: the adding of brackets to "Year" makes it a column identifier instead of a function name.

Are you passing that SQL statement to Access via a OleDbCommand object? And if so, can you please paste that code here?

The sql statement is as I stated earlier

Year([Created_Date]) in the query grid

But you said, "please note it adds [ ] Brakets to year"

That means it looks like this: [Year]([Created_Date])

Or like this: [Year([Created_Date])]

Correct? Or does it add the brackets ONLY to the error message and NOT to the query itself?

That means it looks like this: [Year]([Created_Date]

It adds it to query grid and error message as well.

Than that's the problem: the adding of brackets to "Year" makes it a column identifier instead of a function name.

So the question is why does it added and cannot evaluate

Year([Created_Date])

I just try it on the same table in access and it works well

Are you passing that SQL statement to Access via a OleDbCommand object? And if so, can you please paste that code here?
I have the same problem. Did you find a solution?
To work around this bug I'm going to make the query in the access mdb file that calculates and lists in columbs the Year, Month and Day. Then I'll set the SQLDataSource to use that view of the data instead of the table directly. However, there should be a way to use functions inside of the query designer build into the express.

Year portion of date is incorrect

Never mind...I figured it out. Their was a problem between my client code and my pl/sql code.

Yes I have no Money

Use String.Format() to format the output. If you are in a DataGrid, you can
use Format() as well to make sure it displays correctly.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Bob" wrote:
> Hello:
> VB.NET 2003
> Windows App
> SQL 2k Backend
> Typed DataSet
> I have a bound TextBox to a Money column in SQL Server 2k. When the dataset
> loads, it shows 4 decimals. The data type in the schema designer is Decimal.
> I realy don't care which type I use but I realy do need it to show 2 decimal
> places even if the value is 5.20. I created a user but the data coming from
> the DB is still giving me 4 decimals. I tried changing the data type but
> that gives me 5.2 for 5.20.
> Can someone point me to a good article. I've already seen soem on code
> project and a few other places but none of them are designed to work with
> databound. I can't believe .NET doesn't have that setting in the properties
> window anymore. more and more I use this, it seems always something lost
> from VB6
> TIA
> Bob
>
>
Cowboy:
It's progress.
I tired this
FormatCurrency(TextBox1.Text, 2, TriState.False, TriState.True,
TriState.True)
The error is Cast from string to type double is not valid
TIA
Bob

Yes/No Field Problem

Yes is -1 in Access, although True (without quotes etc) should also work. As indeed should Yes.

EDIT

Another thing - you should put the field names in [ ] brackets when performing a VBA function on them:

SELECT CAMERA_ID, RTRIM([MAKE]) + ' ' + RTRIM([MODEL]) AS CAMERA, ENABLED
FROM CAMERAS
WHERE (ENABLED = -1)
ORDER BY CAMERA

Thanks for your help.

After much messing I realised the error was caused by the ORDER BY statement. Got that sorted and it all worked.

Yes/No field in Stored Procedure (Query)

hi
look at the code below,In command.parameter.add parametername and datatype has to be specified,which is missing in your code.Also mention the direction.in store procedure side give properly the two input parameter.
OleDbCommand cmdGetPointer = new OleDbCommand("Storeprocedurename", cn);
OleDbParameter PointerOutParam = cmdGetPointer.Parameters.Add("@.Pointer", OleDbType.VarBinary, 100);
PointerOutParam.Direction = ParameterDirection.Output;

Thank you. I also had to make one other change. The Stored Procedure returned the values with the names Photos.AlbumID (instead of just AlbumID) and Photos.Caption (instead of just Caption). Now I am getting the data back. But I get another error.
Specified cast is not valid.
Line 126: Photo temp = new Photo(
Line 127: (int)reader["PhotoID"],
Line 128: (int)reader["Photos.AlbumID"],

--BEGIN CODE--
public static List<Photo> GetPhotos(int AlbumID) {
using (OleDbConnection connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
{
using (OleDbCommand command = new OleDbCommand("GetPhotos", connection))
{
command.CommandType = CommandType.StoredProcedure;

OleDbParameter paramAlbumID = command.Parameters.Add("@.AlbumID", OleDbType.SmallInt);
paramAlbumID.Direction = ParameterDirection.Input;
command.Parameters["@.AlbumID"].Value = AlbumID;

bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));

OleDbParameter paramIsPublic = command.Parameters.Add("@.IsPublic", OleDbType.Boolean);
paramIsPublic.Direction = ParameterDirection.Input;
command.Parameters["@.IsPublic"].Value = filter;

connection.Open();
List<Photo> list = new List<Photo>();
object obj = command.ExecuteNonQuery();
using (OleDbDataReader reader = command.ExecuteReader()) {
while (reader.Read()) {
Photo temp = new Photo(
(int)reader["PhotoID"],
(int)reader["Photos.AlbumID"],
(string)reader["Photos.Caption"]);
list.Add(temp);
}
}
return list;
}
}
}
--END CODE--
--BEGIN CLASS--
public class Photo {

private int _id;
private int _albumid;
private string _caption;

public int PhotoID { get { return _id; } }
public int AlbumID { get { return _albumid; } }
public string Caption { get { return _caption; } }

public Photo(int id, int albumid, string caption) {
_id = id;
_albumid = albumid;
_caption = caption;
}

}
--END CLASS--

Figured it out. The datatype coming back with Photos.AlbumID was not an int.

Try this linkhttp://forums.asp.net/1043012/ShowPost.aspx

YES/NO and asp.net

drw("someField")=myCheckBox.Checked

I tried that alredy and I get:
Exception Details:System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Are you letting the DataAdapter generate it's update statements foryou? If so, it's generating them incorrectly and you'll have towrite your Sql by hand.

Yes/No in Access Database, and the Radio Button List

Hello,

Take one boolean variable. Set it to True/False according to Selected value.

Like, for "Yes" selected set it to "True" other wise "False" (Using If/else)

And then pass this boolean variable to parameter.

Command.Parameters.AddWithValue("LikeJob", BooleanVariable)

Regards

Kuldeep Deokule

Thanks for the reply. I'm not sure I follow you. If I set a variable to Boolean, how can I determine it's selected value? Can you give me a code example?

Hello,

Post your non-working code here. So I'll incorporate boolean variable and related logic in it. This will be more helpful to you than any other example.

Regards
Kuldeep Deokule

I think I figured it out, but now I'm having problems updating the database. I'm trying to refer to the users email address that I have in the Email Label. Any ideas why that won't populate the field:

Protected

Sub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.ClickDim varBusAsBoolean
'varBus if thenIf rblBus.SelectedValue ="Yes"Then

varBus =

TrueElseIf rblBus.SelectedValue ="No"Then

varBus =

FalseEndIfDim ConnStringAsString ="Provider = Microsoft.Jet.OLEDB.4.0; Data Source=MyDataBase.mdb"Dim ConnAsNew Data.OleDb.OleDbConnection(ConnString)Dim CommStringAsString ="UPDATE tblPart SET BusPass = rblBusWHERE Email = lEmail.text"Dim CommandAsNew Data.OleDb.OleDbCommand(CommString, Conn)

Command.Parameters.AddWithValue(

"BusPass", varBus)
Command.Parameters.AddWithValue("Email", lEmail.Text)

Conn.open()

Command.ExecuteNonQuery()
Conn.Close()EndSub

barryman9000:
Dim CommStringAsString ="UPDATE tblPart SET BusPass = rblBusWHERE Email = lEmail.text"

Make change in Commstring as shown below:

Dim CommStringAsString ="UPDATE tblPart SET BusPass =?WHERE Email = ?"

I am assuming BusPass and Email fields names in table.

Regards

Kuldeep Deokule

Thanks again. I'm actually trying to update the fields that correspond to the users Email address, which is in a Label called lEmail.

I have a multiple page "sign up" form, and once they hit the submit button on the first page, the next page has my Yes/No radio button, and I pass their email address from the first page textbox, to a label on the next page. So I'm trying to update their info, to just add the BusPass yes/no data.

Any suggestions?

Hello,

I understood what you are trying. But...

Do you changed commstring as I suggested?

If yes, Are you facing any problems/errors. Just describe about that.

Regards
Kuldeep Deokule

I changed the CommString, but I just can't get the BusPass field to submit any data. I'm not getting any errors, after clicking submit it goes to my "thanks for registering" page, but nothing is entered into the DB.

Can't I update the BusPass field for this specific user, by using this:

Dim

varBusAsBoolean

If rblBus.SelectedValue ="Yes"Then
varBus =True
ElseIf rblBus.SelectedValue ="No"Then
varBus =False
EndIf

"UPDATE tblPart SET BusPass = ?,

WHERE Email = ?"

Command.Parameters.AddWithValue("BusPass",varBus)
Command.Parameters.AddWithValue("Email",lEmail.Text)

Where lEmail is the users email address?

barryman9000:
"UPDATE tblPart SET BusPass = ?,
WHERE Email = ?"

I've tested the code given by me and it's working fine with database. Also remove comma after "SET BusPass=?".(It is in your posted code that I quoted above)

For you, Here is tested code developed by me.:

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.ClickDim varBusAsBoolean

'varBus if thenIf rblBus.SelectedValue ="Yes"Then

varBus =

TrueElseIf rblBus.SelectedValue ="No"Then

varBus =

FalseEndIfDim ConnStringAsString = ConfigurationManager.ConnectionStrings _

(

"Conn").ConnectionStringDim ConnAsNew Data.OleDb.OleDbConnection(ConnString)Dim CommStringAsString ="UPDATE tblPart SET BusPass = ? WHERE Email = ?"Dim CommandAsNew Data.OleDb.OleDbCommand(CommString, Conn)

Command.Parameters.AddWithValue(

"BusPass", varBus)

Command.Parameters.AddWithValue(

"Email", lEmail.Text)

Conn.Open()

Command.ExecuteNonQuery()

Conn.Close()

EndSub

Regards

Kuldeep Deokule

Thanks for taking the time to help me out. I have the same thing, but it still doesn't work for me. It only works for me if I have the UPDATE statement written like this:

UPDATE tblPart SET [BusPass] = ? WHERE Email [Email] = Email

but if I have more than one record in the DB, the BusPass field updates every record, not just the field that corresponds to the lEmail.text control. Any ideas?

That is:

UPDATE tblPart SET [BusPass] = ? WHERE Email = Email

but the first Email is in [].

That is:

UPDATE tblPart SET [BusPass] = ? WHERE Email = Email

but the first Email is in [ ].

Hello,

Instead of paramaterized query try following code,

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.ClickDim varBusAsBoolean

'varBus if thenIf rblBus.SelectedValue ="Yes"Then

varBus =

TrueElseIf rblBus.SelectedValue ="No"Then

varBus =

FalseEndIfDim ConnStringAsString = ConfigurationManager.ConnectionStrings _

(

"Conn").ConnectionStringDim ConnAsNew Data.OleDb.OleDbConnection(ConnString)Dim CommStringAsString ="UPDATE tblPart SET BusPass =" & varBus & " WHERE Email ='" & lEmail.Text & "'"

Conn.Open()

Command.ExecuteNonQuery()

Conn.Close()

EndSub

Regards

Kuldeep Deokule
Thanks again. Without the

Dim

CommandAsNew Data.OleDb.OleDbCommand(CommString, Conn)

I get an error, because of the Command.ExecuteNonQuery. If I leave the Command Variable in, I get an error that I'm missing a parameter.

How do I get around that?

Yes/No field showing up as Yes/No in a datagrid.

you can use template column for that one column if you dont want to change all your code and write a helper function. google for "datagrid + helper function". theres an article on aspalliance.com that I can remember has some sample code too.

hth
The best of doing is in your Sql query .Use CASE stament in your query and change the field value to YES if it's true .
Or if you want to do it from Code see following example(NOT TESTED)


 Private Sub DgResult_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DgResult.ItemCommand
Select Case e.Item.ItemType
 Case ListItemType.Item, ListItemType.AlternatingItem
 if (e.Item.Cells(3).Text="True" Then
 e.Item.Cells(3).Text = "YES"
 Else
 e.Item.Cells(3).Text = "NO"
 End if
 End Select End Sub

Arvind Malik
I figured it out, thanks for the hint.

Sub dgOutput_ItemDataBound(sender As Object, e As DataGridItemEventArgs)
Select Case e.Item.ItemType
Case ListItemType.Item, ListItemType.AlternatingItem, ListItemType.SelectedItem
if (e.Item.Cells(16).Text="True") Then
e.Item.Cells(16).Text = "Yes"
Else
e.Item.Cells(16).Text = "No"
End if
End Select
End Sub

YesRe: Unable to authenticate on sql server 2005

Other than have incorrect credentials (such as a bad password), is the database configured for mixed mode security?

<ConnectionString>data source=(Local);packet size=4096;integrated security=SSPI;persist security info=False;initial catalog=PartsTrakUS</ConnectionString>

You should be admin on your own server, (if you installed with your current login).

connection.Connectionstring = " above string.

connection.Open();

We have had some problems with this when authenticating to a domain, and then unplugging the network.

We have told MS about the problem with no real resolution. Use sql authentication is a good idea, however you can also go into surface area configuration and configure for local only.

Thanks for the quick reply from everyone.

The database is however setup for mixed mode and I don't really see problems with the connection string other than the password you mentioned. Iv been working on this problem for a while now so reseting wont solve something here and I can't just use this for local as it will as soon as testing can be done upload the site its using.

I have thought the possibility that maybe my permissions are incorrect for the users on the database. Can anyone maybe direct me on the correct measures on creating one using the management studio?

Shouldn't the Initial Catalog (or Database) parameter value be your database name and the AttachDbFilename value be the path to your MDF?

Server=.\SQLExpress;AttachDbFilename=c:\..\..\mydbfile.mdf;Database=dbname;User ID=MyUsername;Password=MyPassword;Integrated Security=False;User Instance=False

I think there is an error in connection string

Data source=.//SQLEXPRESS

this is wrong

Data source=./SQLEXPRESS

Initial Catalog there is my database name, the AttachDbFilename is usually the path.
Im about to install SQL Server Dev edition to see if this may correct my issue, the "Data source = .//SQLEXPRESS" is correct here as // is seen as a single due to the fact that it sees as expecting a escape char to follow.

Anyway working on that install, will update in a little to let note of the progress. Thanks everyone for the replies, but i would like to make note once again that I dont think the connection string is at fault i still feel its the creating of a user or an error there of some kind. This due to the fact that it works with windows authentication. Anyway, if you got any more ideas ill love em.

Thanks

Hi,

To be sure if there is nothing wrong in your connection string do the following:

Create a connection to your database using Visual Studio Wizards (by adding a SQLConnection object in a form for example).

Test the conenction u created and if the test passes copy the connection string generated by Visual Studio.

HTH.

Hayder Marzouk

Hi again,

Well i checked out the wizard just to make double sure there wasn't a flaw there. This was now after installing SQL Server 2005 Dev Edition. Anyway I could now for the 1st time actually connect using a username and password so this is a milestone I have reached. How ever now since dev edition was installed im still unable to connect, heres the error I get:

An error has occurred while establishing a connection to the server. When

connecting to SQL Server 2005, this failure may be caused by the fact that under

the default settings SQL Server does not allow remote connections. (provider:

SQL Network Interfaces, error: 26 - Error Locating Server/Instance

Specified)

Also here is the new connection string im using to just as a test, this does work when testing using the facilities offered by the wizard and does return success:

string strConnection = "Data Source=RYAN; Initial Catalog = ASPNETDB.MDF; User ID=test; Password = test";

Any ideas here?

Thanks so far

The problem here is Initial Catalog refers to the database name and not the file name of the database.

So try:

string strConnection = "Data Source=RYAN; Initial Catalog = ASPNETDB; User ID=test; Password = test";

Or if you are not sure of the actual database name, then try connecting to the master database as integrated user and then run select name from sys.databases, like so:

' START SCRIPT
' This is a simple vbscript to test connecting and running a sql statement
' Save this as c:\test.vbs and then run cscript c:\test.vbs
Dim conn
set conn = createobject("adodb.connection")
conn.open "Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;"
set rs = conn.execute("select name from sys.databases")
wscript.echo "Here is list of database names ->"
while not rs.eof
wscript.echo rs.fields(0).value
rs.movenext
wend
' END SCRIPT

Hi,

I ran the script you mentioned above just to be double sure about the name. It was ASPNETDB.MDF, although with a file extention i just created like that originally, probably not the best way to go.

I read another forum that this error could be caused by 1 of 2 reasons. 1 being remote access on the current machine, how ever once again I have actually activated that so it doesnt seem to be a problem and the 2nd being about the machine.config trying to load sqlexpress when infact im running server 2005 dev edition.

Anyway, still clueless at the moment, but trying anyway

Ryan
Hi,

Finally I figured out, the problem was that SQL by default within the machine.config has a string defined which seems to want to use Express. The problem was corrected using the following code:

</connectionStrings>

Anyway, it works now although alot of that code seems redundant.
Yes I find that these .NET .config files are a horrible mess to deal with. You should not need to modify machine.config, this is my understanding. But if something is modified in the machine.config, it will affect everything.

Yet another "Concurrency violation: the UpdateCommand affected 0 records" exception

Are you modifying the PK?
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Santi" <santi.serrano@.gmail.com> wrote in message
news:1172251618.433602.144110@.k78g2000cwa.googlegroups.com...
> Hi friends,
> In an application with a single user - single computer, with
> InsertCommand and UpdateCommand created by hand, and a FoxPro database
> accessed with OleDB, when following this steps I get the exception:
> 1. Insert a row in the dataset OK
> 2. Call to Update, the row is inserted in the database OK
> 3. The inserted row is modified in the dataset OK
> 4. Call to Update, the row is not updated, concurrency exception
> appears KO
> Any help?
> Thank you.
>
I already get with the solution.
The problem was that I was not initializating a field in the moment of
inserting the row in the dataset. Then when this row was inserted in
the bd, the bd assigned a default value to that row. Finally when
updating the new changes both rows (dataset and bd) where different
and then the concurrency violation.
Thanks anyway!

yet another datalist findcontrol question

Clicking on an ImageButton in a DataList raises the DataLists ItemCommand event.
Try something like this:

Protected Sub DataList1_ItemCommand(ByVal sourceAs Object,ByVal eAs System.Web.UI.WebControls.DataListCommandEventArgs)Handles DataList1.ItemCommandIf TypeOf e.CommandSourceIs ImageButtonThen Dim thisImgButtonAs ImageButton =DirectCast(e.CommandSource, ImageButton)Dim altTextAs String = thisImgButton.AlternateTextEnd IfEnd Sub

Yet another Datagrid sorting question

Change
Me.DsContactDetails1.qryPOCReview.DefaultView.Sort() = e.SortExpression

To
Me.DsContactDetails1.Tables(0).DefaultView.Sort() = e.SortExpression

Thanks for your input, however, I get the same result.
This issue was resolved by setting the grid datasource property in Visual Studio to Unbound. Sort Logic worked fine after that. Apparently you can't have it both ways, you can either hardwire the grid datasource using VS. Or set it dynamically in the code, but the grid.databind() will use the VS setting even if you've changed the datasource dynamically for manipulation purposes. I recommend that you initially hardcode in VS during initial datagrid development then change the setting to Unbound when you start writing the support code. Maybe someone using VS 2005 can comment whether this is still an issue in the new version. Here is the final code (enhanced to use the VIEWSTATE for variable storage):

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

'Put user code to initialize the page here

If IsPostBack =FalseThen

viewstate("POCSort") = "Name"

viewstate("POCFilter") = "None"

viewstate("POCPgIndex") = 0

POCDataBind()

EndIf

EndSub

PrivateSub DataGrid1_PageIndexChanged(ByVal sourceAsObject, _

ByVal eAs System.Web.UI.WebControls.DataGridPageChangedEventArgs) _

Handles DataGrid1.PageIndexChanged

Me.Trace.Write("Contact Review.aspx", "Inside PageIndexChanged")

viewstate("POCPgIndex") = e.NewPageIndex

POCDataBind()

EndSub

PrivateSub DataGrid1_SortCommand(ByVal sourceAsObject, _

ByVal eAs System.Web.UI.WebControls.DataGridSortCommandEventArgs) _

Handles DataGrid1.SortCommand

Me.Trace.Write("Contact Review.aspx", "Inside DataGrid1_SortCommand")

viewstate("POCSort") = e.SortExpression

POCDataBind()

EndSub

PrivateSub POCDataBind()

Me.Trace.Write("Contact Review.aspx", "Inside POCDataBind")

Dim strPOCSortAsString =CStr(ViewState("POCSort"))

Dim strPOCFilterAsString =CStr(ViewState("POCFilter"))

Dim intPOCPgIndexAs Int32 =CInt(viewstate("POCPgIndex"))

Dim intCntRecAs Int32

'Dim ds As New System.Data.DataSet

'Dim da As New System.Data.SqlClient.SqlDataAdapter

Dim dvAsNew DataView

Me.Trace.Write("Contact Review.aspx", "POCSort=" & strPOCSort)

Me.Trace.Write("Contact Review.aspx", "POCFilter=" & strPOCFilter)

Me.Trace.Write("Contact Review.aspx", "POCPgIndex=" & Str(intPOCPgIndex))

'da = Me.SqlDataAdapter1

'ds = Me.DsContactDetails1

intCntRec =Me.SqlDataAdapter1.Fill(Me.DsContactDetails1, "qryPOCReview")

dv =Me.DsContactDetails1.Tables("qryPOCReview").DefaultView

If Len(strPOCSort) > 0Then

Me.Trace.Write("Contact Review.aspx", "Inside Sort")

dv.Sort = strPOCSort

EndIf

If (Len(strPOCFilter) > 0And (strPOCFilter <> "None"))Then

dv.RowFilter = strPOCFilter

EndIf

Me.DataGrid1.DataSource = dv

'Me.DataGrid1.DataMember = "qryPOCReview"

Me.DataGrid1.CurrentPageIndex = intPOCPgIndex

Me.DataGrid1.DataBind()

Me.LabelPages.Text = "Page " & Str(Me.DataGrid1.CurrentPageIndex + 1) & " of " & Str(Me.DataGrid1.PageCount)

Me.LabelRecords.Text = Str(intCntRec) & " Records Returned"

'Page.DataBind()

EndSub

Hi, try this
Private Sub DataGrid1_SortCommand(ByVal source As Object, _

ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) _

Handles DataGrid1.SortCommand

Me.SqlDataAdapter1.Fill(Me.DsContactDetails1, "qryPOCReview")

Me.DsContactDetails1.qryPOCReview.DefaultView.Sort() = e.SortExpression

DataGrid1.DataBind()

EndSub

Hope this works

Yet another DropDownList issue

u need to write <%(databinder.eval(container.dataitem("yourcolounmname")%>

Yet another designer issue

SAL,
What code are you using to add the parameters?
Kerry Moorman
"SAL" wrote:
> Bill,
> Thanks for helping with my last designer issue. Now, I execute the following
> query in the Query Builder and it returns one record:
> SELECT
> ReportedWeeds.RID, Weeds.Name, ReportedWeeds.Density, BioAgents.BName,
> ReportedWeeds.Assr_SN, ReportedWeeds.TownRange, ReportedWeeds.LotSize,
> ReportedWeeds.theDate, ROW.[Desc] AS RDesc, ReportedWeeds.Owner,
> ReportedWeeds.OwnStAddrs, ReportedWeeds.OwnCity, ReportedWeeds.OwnState,
> ReportedWeeds.OwnZip, ReportedWeeds.OwnPhone, MethodContacted.[Desc] AS
> MDesc, ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' ' +
> ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
> ReportedWeeds.SitusCity AS SITADDRS, ReportedWeeds.Source,
> ReportedWeeds.SourceCity, ReportedWeeds.SourceState,
> ReportedWeeds.SourceStDir,
> ReportedWeeds.SourceHsnbr, ReportedWeeds.SourceStName,
> ReportedWeeds.SourceZip, ReportedWeeds.SourcePhone,
> ReportedWeeds.Comments, ReportedWeeds.FollowUp, ReportedWeeds.FollowUpNotes,
> ReportedWeeds.Atlas
> FROM ((((ReportedWeeds LEFT OUTER JOIN
> ROW ON ReportedWeeds.ROWID = ROW.ROWID) LEFT OUTER JOIN
> Weeds ON ReportedWeeds.WID = Weeds.WID) LEFT OUTER JOIN
> MethodContacted ON ReportedWeeds.MCID = MethodContacted.MCID) LEFT OUTER
> JOIN
> BioAgents ON ReportedWeeds.BioAgent = BioAgents.BAID)
> WHERE (ReportedWeeds.Source LIKE '%' + ? + '%') AND (ReportedWeeds.theDate
> >= ?) AND (ReportedWeeds.theDate <= ?)
> I pass in the following as parameters:
> Palmer
> 1/1/2006
> 12/31/2006
> But, when I execute this same query through the TableAdapter (that was
> created via the designer), it returns an error:
> The provider could not determine the Double value. For example, the row was
> just created, the default for the Double column was not available, and the
> consumer had not yet set a new Double value.
> When I was getting this error before, it was because the SITADDRS column as
> type Double. But, that error went away when I set the type as a string as it
> should have been.
> Can anyone help with this maybe?
> S
>
>
Hi Kerry,
I have a business logic layer class that I'm using. Here's the code for this
querry:
Public Function GetWeedReportsBySourceDate(ByVal source As String, ByVal
year As Integer) As WeedRptds.ReportedWeedsDataTable
Dim startDate, endDate As Date
Dim rwdt As WeedRptds.ReportedWeedsDataTable = Nothing
startDate = Date.Parse("01/01/" & year.ToString())
endDate = Date.Parse("12/31/" & year.ToString())
Try
rwdt = Adapter.GetWeedReportsBySourceDate(source, startDate,
endDate)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Return rwdt
End Function
The class' Adapter property is:
Imports WeedRptdsTableAdapters ' imports the table adapters for the
class
Private mReportedWeeds As ReportedWeedsTableAdapter
Public ReadOnly Property Adapter() As ReportedWeedsTableAdapter
Get
If mReportedWeeds Is Nothing Then
mReportedWeeds = New ReportedWeedsTableAdapter
End If
Return mReportedWeeds
End Get
End Property
S
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:31560669-53BB-42FE-8308-40D131D09C40@.microsoft.com...
> SAL,
> What code are you using to add the parameters?
> Kerry Moorman
>
> "SAL" wrote:
>> Bill,
>> Thanks for helping with my last designer issue. Now, I execute the
>> following
>> query in the Query Builder and it returns one record:
>> SELECT
>> ReportedWeeds.RID, Weeds.Name, ReportedWeeds.Density,
>> BioAgents.BName,
>> ReportedWeeds.Assr_SN, ReportedWeeds.TownRange, ReportedWeeds.LotSize,
>> ReportedWeeds.theDate, ROW.[Desc] AS RDesc, ReportedWeeds.Owner,
>> ReportedWeeds.OwnStAddrs, ReportedWeeds.OwnCity, ReportedWeeds.OwnState,
>> ReportedWeeds.OwnZip, ReportedWeeds.OwnPhone, MethodContacted.[Desc] AS
>> MDesc, ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' ' +
>> ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
>> ReportedWeeds.SitusCity AS SITADDRS, ReportedWeeds.Source,
>> ReportedWeeds.SourceCity, ReportedWeeds.SourceState,
>> ReportedWeeds.SourceStDir,
>> ReportedWeeds.SourceHsnbr, ReportedWeeds.SourceStName,
>> ReportedWeeds.SourceZip, ReportedWeeds.SourcePhone,
>> ReportedWeeds.Comments, ReportedWeeds.FollowUp,
>> ReportedWeeds.FollowUpNotes,
>> ReportedWeeds.Atlas
>> FROM ((((ReportedWeeds LEFT OUTER JOIN
>> ROW ON ReportedWeeds.ROWID = ROW.ROWID) LEFT OUTER JOIN
>> Weeds ON ReportedWeeds.WID = Weeds.WID) LEFT OUTER JOIN
>> MethodContacted ON ReportedWeeds.MCID = MethodContacted.MCID) LEFT OUTER
>> JOIN
>> BioAgents ON ReportedWeeds.BioAgent = BioAgents.BAID)
>> WHERE (ReportedWeeds.Source LIKE '%' + ? + '%') AND
>> (ReportedWeeds.theDate
>> >= ?) AND (ReportedWeeds.theDate <= ?)
>> I pass in the following as parameters:
>> Palmer
>> 1/1/2006
>> 12/31/2006
>> But, when I execute this same query through the TableAdapter (that was
>> created via the designer), it returns an error:
>> The provider could not determine the Double value. For example, the row
>> was
>> just created, the default for the Double column was not available, and
>> the
>> consumer had not yet set a new Double value.
>> When I was getting this error before, it was because the SITADDRS column
>> as
>> type Double. But, that error went away when I set the type as a string as
>> it
>> should have been.
>> Can anyone help with this maybe?
>> S
>>
I've found the field that's causing the problem but seems goofy ntl:
if the concatenated field for SITADDRS as follows:
ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' ' +
ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
ReportedWeeds.SitusCity AS SITADDRS
If I remove this field from the query, it runs properly.
Anybody got any ideas on this?
S
"SAL" <SAL_@.NoNo.com> wrote in message
news:%23J%23mY7udHHA.2128@.TK2MSFTNGP04.phx.gbl...
> Bill,
> Thanks for helping with my last designer issue. Now, I execute the
> following query in the Query Builder and it returns one record:
> SELECT
> ReportedWeeds.RID, Weeds.Name, ReportedWeeds.Density, BioAgents.BName,
> ReportedWeeds.Assr_SN, ReportedWeeds.TownRange, ReportedWeeds.LotSize,
> ReportedWeeds.theDate, ROW.[Desc] AS RDesc, ReportedWeeds.Owner,
> ReportedWeeds.OwnStAddrs, ReportedWeeds.OwnCity, ReportedWeeds.OwnState,
> ReportedWeeds.OwnZip, ReportedWeeds.OwnPhone, MethodContacted.[Desc] AS
> MDesc, ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' ' +
> ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
> ReportedWeeds.SitusCity AS SITADDRS, ReportedWeeds.Source,
> ReportedWeeds.SourceCity, ReportedWeeds.SourceState,
> ReportedWeeds.SourceStDir,
> ReportedWeeds.SourceHsnbr, ReportedWeeds.SourceStName,
> ReportedWeeds.SourceZip, ReportedWeeds.SourcePhone,
> ReportedWeeds.Comments, ReportedWeeds.FollowUp,
> ReportedWeeds.FollowUpNotes, ReportedWeeds.Atlas
> FROM ((((ReportedWeeds LEFT OUTER JOIN
> ROW ON ReportedWeeds.ROWID = ROW.ROWID) LEFT OUTER JOIN
> Weeds ON ReportedWeeds.WID = Weeds.WID) LEFT OUTER JOIN
> MethodContacted ON ReportedWeeds.MCID = MethodContacted.MCID) LEFT OUTER
> JOIN
> BioAgents ON ReportedWeeds.BioAgent = BioAgents.BAID)
> WHERE (ReportedWeeds.Source LIKE '%' + ? + '%') AND (ReportedWeeds.theDate
> >= ?) AND (ReportedWeeds.theDate <= ?)
> I pass in the following as parameters:
> Palmer
> 1/1/2006
> 12/31/2006
> But, when I execute this same query through the TableAdapter (that was
> created via the designer), it returns an error:
> The provider could not determine the Double value. For example, the row
> was just created, the default for the Double column was not available, and
> the consumer had not yet set a new Double value.
> When I was getting this error before, it was because the SITADDRS column
> as type Double. But, that error went away when I set the type as a string
> as it should have been.
> Can anyone help with this maybe?
> S
>

Yet another issue with DataColumn inhertiance

On Tue, 12 Sep 2006 19:49:39 -0400, "Joe" <jbassking@.noemail.noemail> wrote:
>Since DataColumn is not sealed I would like to inherit from it and create my
>own. Unfortunately it seems that when serializing a DataTable with these
>columns, only DataColumn (the base) is serialized and my derived column is
>not.
>Is there anyway I can use and serialize a derived DataColumn?
>-Joe
>
Did you try marking your column with the Serializable attribute? It's worth a
try.
Good luck with your project,
Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
Yes, it has to attribute.
"Otis Mukinfus" <phony@.emailaddress.com> wrote in message
news:j0keg2d6mi4nh4p8283qshot8a3aq1s74l@.4ax.com...
> On Tue, 12 Sep 2006 19:49:39 -0400, "Joe" <jbassking@.noemail.noemail>
> wrote:
>>Since DataColumn is not sealed I would like to inherit from it and create
>>my
>>own. Unfortunately it seems that when serializing a DataTable with these
>>columns, only DataColumn (the base) is serialized and my derived column is
>>not.
>>Is there anyway I can use and serialize a derived DataColumn?
>>-Joe
> Did you try marking your column with the Serializable attribute? It's
> worth a
> try.
> Good luck with your project,
> Otis Mukinfus
> http://www.arltex.com
> http://www.tomchilders.com
Hi Joe,
The good news is that it is probably possible while the bad news is that it
will be not easy at all.
DataTable uses custom serialization via ISerializable and thus you have to
override GetObjectData and create a proper constructor or use new custom
serialization via attributes (.net 2.0).
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
"Joe" <jbassking@.noemail.noemail> wrote in message
news:uTp28%23u1GHA.3464@.TK2MSFTNGP03.phx.gbl...
> Yes, it has to attribute.
> "Otis Mukinfus" <phony@.emailaddress.com> wrote in message
> news:j0keg2d6mi4nh4p8283qshot8a3aq1s74l@.4ax.com...
>> On Tue, 12 Sep 2006 19:49:39 -0400, "Joe" <jbassking@.noemail.noemail>
>> wrote:
>>Since DataColumn is not sealed I would like to inherit from it and create
>>my
>>own. Unfortunately it seems that when serializing a DataTable with these
>>columns, only DataColumn (the base) is serialized and my derived column
>>is
>>not.
>>Is there anyway I can use and serialize a derived DataColumn?
>>-Joe
>> Did you try marking your column with the Serializable attribute? It's
>> worth a
>> try.
>> Good luck with your project,
>> Otis Mukinfus
>> http://www.arltex.com
>> http://www.tomchilders.com
>
Hi Miha,
Could you possibly give me an example of using the custom serialization
attributes?
-Joe
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eeew1Pw1GHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hi Joe,
> The good news is that it is probably possible while the bad news is that
> it will be not easy at all.
> DataTable uses custom serialization via ISerializable and thus you have to
> override GetObjectData and create a proper constructor or use new custom
> serialization via attributes (.net 2.0).
> --
> Miha Markic [MVP C#, INETA Country Leader for Slovenia]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
> "Joe" <jbassking@.noemail.noemail> wrote in message
> news:uTp28%23u1GHA.3464@.TK2MSFTNGP03.phx.gbl...
>> Yes, it has to attribute.
>> "Otis Mukinfus" <phony@.emailaddress.com> wrote in message
>> news:j0keg2d6mi4nh4p8283qshot8a3aq1s74l@.4ax.com...
>> On Tue, 12 Sep 2006 19:49:39 -0400, "Joe" <jbassking@.noemail.noemail>
>> wrote:
>>Since DataColumn is not sealed I would like to inherit from it and
>>create my
>>own. Unfortunately it seems that when serializing a DataTable with these
>>columns, only DataColumn (the base) is serialized and my derived column
>>is
>>not.
>>Is there anyway I can use and serialize a derived DataColumn?
>>-Joe
>> Did you try marking your column with the Serializable attribute? It's
>> worth a
>> try.
>> Good luck with your project,
>> Otis Mukinfus
>> http://www.arltex.com
>> http://www.tomchilders.com
>>
>
Hi Joe,
Here is an example of how to implement the ISerializable interface.
http://msdn2.microsoft.com/en-us/library/system.runtime.serialization.iseria
lizable.aspx
And here is an example of how to use the SerializableAttribute.
http://msdn2.microsoft.com/en-us/library/system.serializableattribute.aspx
HTH.
If anything is unclear, please feel free to let us know.
Kevin Yu
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Hi Joe,
I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.
Kevin Yu
Microsoft Online Community Support
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Yet another IsDBNull post. Stumped!


 If cmdSelectData.ExecuteScalar() is dbnull.value then
 strlogo = ""
 else
 strlogo = cmdSelectData.ExecuteScalar()
 end if

Not sure though this is the best way.
Whilst it will work it requires two trips to the database and performance will take a hit.

Although as a general rule it is bad practice to use the base type, sometimes it is the more efficent way. (always open to critisism and a better offer, pending .Net 2.0)

You could try this


 object logo = cmdSelectData.ExecuteScalar()
 strLogo = logo.ToString()

This works because DBNull.ToString returns an String.Empty

Hope this works
Apologies for mixing VB and C#

the first line needs a Dim statement


 Dim logo As Object = cmdSelectData.ExecuteScalar()

Thanks guys.
Should have mentioned this in my earlier post but a much better way of dealing with this situation, if it is under your control, is to ensure default values are set up for fields in the database. Once you do that you don't have to worry about Nulls

yet another nested repeater question

Perhaps you are not hooking into the ItemDataBound event on the outer repeater? You should have something like this in OnInit or InitializeComponent (may vary between 1.1 and 2.0):

PackageRepeater.ItemDataBound += new RepeaterItemEventHandler(PackageRepeater_ItemDataBound);

Also you might consider a little trick.. you could set the DataSource declaratively. I can't use the code insert feature at the moment so bare with me... substitute brackets for angle brackets:

[asp:Repeater id="CampaignRepeater" runat="server" DataSource="

Then you wouldnt need the databound event at all. One other thing.. I notice the parameter you are passing for the inner repeater doesnt have anything to do with the dataitem of the outer one. Did you mean to hard code the '2' there? You can get to the dataitem within that declaritive code to get a foreign key off it if thats what you meant...
YANRQ

:P
Argh sorry about that... heres the missing code

[asp:Repeater id="CampaignRepeater" runat="server" DataSource="[%# Campaign.FindAll(Expression.Eq("Package.Id", 2 )) %]"]
this is for 1.1 ... i should have mentioned that.

I tried doing it declaritively but that also gave me an error message (which i dont remember at the moment).

I do not have anything like the eventhandler code... i didnt see that mentioned in the msdn site or any other the online samples i found. I'll give that a try.

as for the hardcode value... thats just there temporaily. I want to make sure the datasource actually has data. Once i get the repeater working, my next question would probably be how to grab a value from the packagerepeater and use it in the data source call for the campaignrepeater ;) But i think i know how to do that.
is it possible the FindControl isnt finding anything? Because i put a break point just after that line. and i assume i should see the CampaignRepeater listed as a control of the PackageRepeater, but i dont. Its listed along side it, but with an undefined value. And I'm getting a null reference error when i try to do the set the datasource. so maybe the findcontrol isnt finding anything, which is where i am getting the null refernce.

If so, how can i test to see what FindControl is finding (if anything), and if its not finding something, what should i look at to see why its not finding it?

thanks for the help

Well if FindControl isn't finding it then thats definitely a clue... but it should, I dont see any issues with what you've got. If your CampaignRepeater variable reference is still null after you assign it to FindControl, then FindControl isn't finding it. Only way that could happen is if you have misspelled the ID, or if the repeater is actually within another control that implements INamingContainer. Is the source you posted complete or abbreviated?

the source was appreviated. I didnt want to clutter it up if people were going to try and read it and offer some advice.

i still dont know what was wrong, but i got it working by just starting the page over. So i guess there was some sort of syntax error somewhere.

thanks for the help

Have you tried

"CampaignRepeater = ((Repeater)e.Item.Controls[0].FindControl("CampaignRepeater"));"?

sophia:
Have you tried
"CampaignRepeater = ((Repeater)e.Item.Controls[0].FindControl("CampaignRepeater"));"?

no i didnt try that. Can you explain why i would need to do that?

thanks
Actually your code can work fine on my side.
Ok ... i know how to set an eventhandler for the repeater ... next question - how do i add an event handler for the nested repeater?

i tried doing the same thing i did for the outer repeater:

this.PackageRepeater.ItemDataBound +=new System.Web.UI.WebControls.RepeaterItemEventHandler(this.PackageRepeater_ItemDataBound);
this.CampaignRepeater.ItemDataBound +=new System.Web.UI.WebControls.RepeaterItemEventHandler(this.CampaignRepeater_ItemDataBound);
this.Load +=new System.EventHandler(this.Page_Load);

The first line is the outer level repeater and the 2nd one is the nested one. When i try to run the page i get the error:
System.NullReferenceException: Object reference not set to an instance of an object.

I assume i have to reference the control through the outer repeater somehow? not sure how though.

There's only one outer repeater, but there's N-number of inner repeaters, so you can't hook into it's event in the traditional sense. You'll have to hook into the ItemCreated event in the outer repeater, find the inner one, then hook into its event there. You'll be hooking into each of the inner repeaters.

One thing... why do you have a this.CampaignRepeater at all? That's only going to cause you confusion... you cant refer to the inner repeater that way. It isn't a control on the form like the outer repeater is, its just a control that is part of a TEMPLATE. And that template is instantiated once for every item in the outer repeater. It doesn't exist until the template is instantiated (when you databind), and even then, it's created multiple times. Having a single reference to it on the form doesn't make sense, so just get rid of that reference entirely, it's just going to make you think you can do things like this. That's also why its null by the way... it doesn't exist.

I hope that makes sense... :)

i was just using "this." because thats what was there previously and it worked. So i was guessing it would work again (i guessed wrong).

here's the layout of what the page looks like (and its working):
<package repeater>
<campaign repeater>
</campaign repeater>
</package repeater
<campaigns with no package repeater>
</campaigns with no package repeater
and here's is something like what i am trying to do:

<package repeater>
<campaign repeater>
<checkbox id="campaign#">
</campaign repeater>
</package repeater
<campaigns with no package repeater>
<checkbox id="campaign#">
</campaigns with no package repeater
Basically, for each row in the 2 campaign repeaters, i need to include a checkbox which is not going to have the same datasource as the repeaters since i need to use a different function to get their values. And i will then need to go through each checkbox when the page is submitted, gather the values and do my data processing.

Any suggestions on how to go about doing this?

Well you're going to just iterate over the repeater items, find the checkbox, and take appropriate action. For each item in the package repeater you will FindControl to get the campaign repeater, then for each item in that repeater, find the checkbox. Then the "NoPackageCampaignRepeater" you do the same.

Perhaps where you are getting messed up is that the "CampaignRepeater" control you have declared is referring to the one that is outside of the PackageRepeater. When you need to deal with one of the inner repeaters, use a new local variable, dont use the "this." one since thats referring to the one outside of the repeater...

You're totally on the right track with the ItemDatabound event, I just think you may have some issues with confused references or something. If you want to send me the complete source perhaps I can help in more detail. Either link through to my blog to get my email or send me a private message. I'll try to look at it when I have some time.

Yet another nested repeater newbie

Hi,Woppie:

ds.Relations.Add("myrelation", _
Line 17: ds.Tables("Links").Columns("ParentCategory"), _
Line 18: ds.Tables("tblRegions").Columns("ID"))"

I do not see the datatable Links and tblRegions in your ds above.

According to the following code you have, i suppose the name of the datatable is ParentCategory and ID.

cmd1.Fill(ds, "ParentCategory")

cmd2.Fill(ds, "ID")

Yet another ora-12154 error.

My guess is that in switching to the 10g client, Oracle still thinks you are looking for the 9i TNSNAMES.ORA file... in the Windows version of Oracle Client(s) it uses a registry setting to tell all Oracle products which "Oracle Home" to use for tnsnames entries. I would check which client is in use. It's been a while since I've looked for it, but I seem to remember it to be here:

\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

I think the Key you are looking for is something like CURRENT_HOME or ORACLE_HOME... it should point to your 10g directory, not the 9i one...

Hopefully, this will help...

Yikes - Repeater Row Color change needed

OK- I'm a little slow...
I'm using the so-called "Helper Function" for this and it works fine.
I was trying to embed the function logic in the designer page and it was causing me problems.

I really display the ItemDataBound event stuff. Far too tedious to cast into DbDatarecords and the like and I have to make each column it's own control in the designer. The "Helpers" make this really simple.

//In the CodeBehind page...
protected string FormatColorRow(string theData)
{
switch (theData)
{
case "F":
return "style='Font-weight:bold'";
case "I":
return "style='BACKGROUND-COLOR:Gainsboro'";
default: // case "M":
return null;
}
}
// In the designer page
<asp:Repeater ID="PerformanceRepeater" Runat="server" EnableViewState="False">
<ItemTemplate>
<tr <%# FormatColorRow((((DbDataRecord)Container.DataItem)["EntityTyp"]).ToString()) %<td nowrap width="20%">
<%# ((DbDataRecord)Container.DataItem)["EntityNme"] %>'>
</td

you inspiring me.


in Code Behind. VBpublic function SetBgColor (byval thedate as string) as string
 select case
 case "F"
 return "blue"
 end select
end function
in aspx
<asp:repeater bla..bla...>
 <itemtemplate>
 <tr bgcolor=<%# SetBgColor(Container.DataItem("EntityTyp").ToString)%>
 </tr>
 </itemtemplate>
</asp:repeater>

this work for me
tengkiu
FYI:http://aspalliance.com/31

Colt

Yikes! buttons in datagrid lose event handler after back button hit

Actually, it is the itemCommand of the cell in the datagrid that doesn't fire after the back, not the button itself in the cell.

so after the back button is hit this event handler assignment is ignored:

myDataGrid.ItemCommand += new DataGridCommandEventHandler(myDataGrid.ItemCommandHandler);

Any ideas?

You can have strongly-typed DataTables, or you can have nullable columns. Choose one.

If it's blowing up on the line where you do "Console.WriteLine" then that really isn't a problem. That doesn't mean that the column isn't allowed to be null, it just means you can't try to get it's value if it is null. Before utilizing the value in that column, you should check the "IsBirthDateNull" method to see if it is null or not-null, then act on that accordingly in your code (i.e. don't reference that column's value if it's null). If you don't see a "IsBirthDateNull" method on your datatable, then go back into the designer and set the "BirthDate" column's "AllowDBNull" property to "true", recompile, and check for that method again.

I think I know why (I've had the same problem):

Code Snippet

public decimal UnitValue {
get {
try {
return ((decimal)(this[this.tableDocumentLineKit.UnitValueColumn]));
}
catch (System.InvalidCastException e) {
throw new System.Data.StrongTypingException("The value for column \'UnitValue\' in table \'DocumentLineKit\' is DBNull.", e);
}
}
set {
this[this.tableDocumentLineKit.UnitValueColumn] = value;
}
}

The above is an example of how the strongly typed dataset works.
If you try to access a null value in the data set, it throws the exception. The reasoning is not clear, but if you change your code to the following it might help you:

Code Snippet

Console.WriteLine(r.IsBirthDateNull?new System.Date():r.BirthDate);

If you only want to display the value, you can cast the BirthDate as a string in your SQL query (yes, this is stupid too). I had a chance to work with a veteran of the .NET programming field. He mentioned wrapping the table adapter classes. I know... it should just work in the first place.

Another possibility is to write a simple utility class that converts all DbNull to null, i.e.

public object NC(object input)

{

if (null == input)

return null;

else if (input.Equals(DBNull.Value))

return null;

else

return input;

}

Wow, that's phenomenal. Thanks to ARK88 for what I guess is the right answer.

I mean, it's the the right explanation of the wrong answer. The right answer is that the properties for nullable columns should be of a datatype that can include null values. Why would you go to the trouble of defining DateTime? and int? and Decimal? types, use them in the parameter lists of the methods that wrap parameterized queries and stored procedure calls, and then, when the time comes to write the code generator for the strongly-typed DataTables, say "Hey, I know, let's generate a special utility property for every nullable column!" Why? Why would you choose two distinctly different and incompatible mechanisms for the same thing, make it a fundamental part of your architecture, and then not even highlight it with red flashing lights in your documentation?

The root cause is certain scalar types in CLR are not nullable. For example you cannot say:

int x = null;

You can say of course:

Int32 x = null;

But in .NET 1.1 the language did not support NullableT so they went with the IsField1Null method, which I agree is troublesome.

The history is discussed here:

http://blogs.msdn.com/adonet/archive/2007/02/05/type-safety-linq-to-datasets-part-2.aspx

"When the DataSet was first created, there was no concept of nullable value types in the CLR, so a new value type was defined: DBNull. This was used to represent null values for DataColumns that contain a value type, because you could not have have a null value type. The world has moved on, and we now have nullable types, and so it is now much more natural to write a query using null, as opposed to having to check for DBNull everywhere. The other feature offered by the Field<T> method is that it will convert a value type that has a value of DBNull.Value to a nullable type with a value of null. "

Well aren't you a breathe of fresh air? You are correct about me being incorrect... I did actually answer the wrong question. Sorry for setting myself up to be the butt of your sarcasm... I promise it won't happen again.
Sorry if I was unclear - I intended no sarcasm directed your way. Your answer is completely correct, full stop.

The answer that Microsoft provides is wrong. It's them I'm mad at.

The link Matt provides explains the half-assed reasoning behind the half-assed implementation of this feature. When the DataSet was created, NullableT didn't exist. Fine. Yet, strangely, in .NET 2.0 the ADO folks found the time to extend the DataSet to use NullableT. Only, just some of the DataSet's features use it.

As it stands, if I have a table with a nullable int column, and I create a TableAdapter and DataTable from it, the DataSet designer creates an InsertCommand that takes a parameter of int?, and a property accessor on the column of type int that throws an exception if I set it to null. How can that possibly be the right design?

Any why on earth go to all the trouble to define an entire library of SQL data types and then not support them? Why is the choice between using strongly-typed DataSets and SQL data types mutually exclusive? Where's the sense in that?

So much of the design of ADO.NET is deeply smart. This is a glaring exception.

Sorry I misinterpreted your previous post (and your original question). I'm completely on your side regarding this matter... it doesn't make a whole lot of sense to me either.

Sorry for the half-a**d response.

Essentially this is a impedence mismatch between database types and CLR types. Happens all the time, not just with ADO.NET. Look at other APIs like JDBC, you have ResultSet.getInt(columnIndex) and ResultSet.wasNull(columnIndex) for example. Programming languages typically have simple scalar types that are not nullable, like int. All database types are nullable, including simple scalar types like int.

So your choices are either have an API that returns some generic object type that you have to cast to the scalar type or you can return the true scalar type and have a IsFieldNull check. Now with .NET 2.0, there is NullableT support, this is the language bending to meet the database types, allowing scalar type that is nullable.

Microsoft is working very hard to solve this impedence mismatch problem. Look at LINQ, NullableT, etc... We know this is frustrating for programmers and we are working to solve these problems.

Sure. I understand the problem.

But .NET 2.0 has NullableT. ADO.NET in .NET 2.0 uses NullableT. Why doesn't the strongly-typed DataTable? That just doesn't make any sense. Was it a backwards-compatibility question? Or is there some good reason I'm not seeing why NullableT's not an appropriate solution?

Really, I wouldn't be so het up about this except that I have this application I built using generic DataTable objects, and in the middle of a big refactoring effort I thought "I know, I'll use strong typing, that'll make everything easier," and in fact it's made everything harder. Harder in the "Now I get to find out how SVN's revert works" sense.

I will go ask the DataTable PM to see what he says.
This is something I hit when I was using typed datasets in .Net 1.1, it caused a bunch of issues with data-binding amongst other things. It's also something I hit when creating my own objects in .Net 1.1, and it seemed that the MS approach with objects was to have an "IsBlahPopulated" boolean for each property. Obviously very similar to what they did with typed datasets. I was very glad to see nullable types in .Net 2.0, in fact it's one of my favourite improvements.

I suspect that typed datasets haven't been updated to make use of the new functionality because MS is moving us towards Linq-To-Sql, and for good reason IMHO. You still get to generate the code, but you get proper objects that you can extend. If it's true that MS has or will be deprecating typed datasets (and I have no information on this) it may be worth you investigating Linq-To-Sql.

An alternative to Linq-To-Sql is my own product, Foundation. It's similar, but is geared more towards making applications easier to write, rather than just loading and saving data. It only requires .Net 2.0.

Regards,

Sean

Note I talked to the DataSet PM and he indicated that they considered using NullableT in 2.0 but the Nullable types were not ready during the time that they were working on the DataSet code in 2.0 timeframe (NullableT came in late).

They are looking at it for next release but I got no hard promises.

Thanks, Matt.

you can see my program source please help for me about update in database

Please specify theExact error message. Absent that, it is impossible to help you.
thanks douglas.reilly

my error message is :

Syntax error in INSERT INTO statement.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

plz help me.

You can have strongly-typed DataTables, or you can have nullable columns. Choose one.

I think I know why (I've had the same problem):

Code Snippet

Console.WriteLine(r.IsBirthDateNull?new System.Date():r.BirthDate);

Another possibility is to write a simple utility class that converts all DbNull to null, i.e.

public object NC(object input)

{

if (null == input)

return null;

else if (input.Equals(DBNull.Value))

return null;

else

return input;

}

Wow, that's phenomenal. Thanks to ARK88 for what I guess is the right answer.

The root cause is certain scalar types in CLR are not nullable. For example you cannot say:

int x = null;

You can say of course:

Int32 x = null;

But in .NET 1.1 the language did not support NullableT so they went with the IsField1Null method, which I agree is troublesome.

The history is discussed here:

http://blogs.msdn.com/adonet/archive/2007/02/05/type-safety-linq-to-datasets-part-2.aspx

The answer that Microsoft provides is wrong. It's them I'm mad at.

Sorry for the half-a**d response.

Microsoft is working very hard to solve this impedence mismatch problem. Look at LINQ, NullableT, etc... We know this is frustrating for programmers and we are working to solve these problems.

Sure. I understand the problem.

Regards,

Sean

They are looking at it for next release but I got no hard promises.

Thanks, Matt.

you can't do this? @SortBy

You can only pass literal values in - not column names. If you wanted to do
something like that, you would have to create a string with the query and
execute it.
Also, you can try passing in @.SortBy as an integer, and sorting on the
column order, instead of using the column name to perform the sort. I
haven't tried it, but it might work - not sure if the order by can use any
parameters.
"djc" <noone@.nowhere.com> wrote in message
news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> I'm very new to stored procedures! I am using MSDE2000 and asp.net
WebMatrix
> to work on this page. Currently I
> have this in the stored procedure:
> CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> AS
> SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> Status
> FROM tblMain
> ORDER BY @.SortBy
> GO
> I get this error when trying to save it:
> "The select item identified by the ORDER BY number 1 contains a variable
as
> part of the expression identifying a column position. Variables are only
> allowed when ordering by an expression referencing a column name."
> any help would be very much appreciated. Thanks!
>
>
> CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> AS
> SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> Status
> FROM tblMain
> ORDER BY @.SortBy
No, but you can do this:
-- Note changes:
-- dbo should own everything
-- specify the size of your varchar in characters
-- this appends the content of @.sortBy onto the end of the sql string.
-- the sp_ExecuteSQL system stored procedure compiles it and runs it.
CREATE PROCEDURE dbo.GetAllIssues
(
@.sortBy VARCHAR(20)
)
AS
DECLARE @.sql VARCHAR(1000)
SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy
EXECUTE sp_ExecuteSql @.sql
GO
--
Peace & happy computing,
Mike Labosh, MCSD
"I have no choice but to believe in free will."
I guess that means the ORDER BY can't use a parameter for the ASC or DESC
options either then huh? So much for benefiting from stored procedures, in
my case at least. I'll go back to just putting the SQL directly in my code.
thanks for the reply.
"Marina" <someone@.nospam.com> wrote in message
news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> You can only pass literal values in - not column names. If you wanted to
do
> something like that, you would have to create a string with the query and
> execute it.
> Also, you can try passing in @.SortBy as an integer, and sorting on the
> column order, instead of using the column name to perform the sort. I
> haven't tried it, but it might work - not sure if the order by can use any
> parameters.
> "djc" <noone@.nowhere.com> wrote in message
> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
> WebMatrix
> > to work on this page. Currently I
> > have this in the stored procedure:
> >
> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> > AS
> >
> > SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> > Status
> > FROM tblMain
> > ORDER BY @.SortBy
> >
> > GO
> >
> > I get this error when trying to save it:
> > "The select item identified by the ORDER BY number 1 contains a variable
> as
> > part of the expression identifying a column position. Variables are only
> > allowed when ordering by an expression referencing a column name."
> >
> > any help would be very much appreciated. Thanks!
> >
> >
> >
>
ok. cool. I will do that. Currently I'm using something like this:
<snip>
IF (@.SortBy = 'MainID')
BEGIN
SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
Status
FROM tblMain
ORDER BY MainID
END
ELSE IF (@.SortBy = 'Title')
BEGIN
SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
Status
FROM tblMain
ORDER BY Title
END
</snip>
BUT then I realized I also need to dynamically assign the ASC or DESC
options. This is for an asp.net datagrid and I need the first click to sort
ASC and the second click on the same column to sort DESC etc... Using the
same method I have now would require a very long and likely unefficient
stored procedure. Any ideas for incorporating that?
thanks again!
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eERQItFwEHA.1988@.TK2MSFTNGP12.phx.gbl...
> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> > AS
> >
> > SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> > Status
> > FROM tblMain
> > ORDER BY @.SortBy
> No, but you can do this:
> -- Note changes:
> -- dbo should own everything
> -- specify the size of your varchar in characters
> -- this appends the content of @.sortBy onto the end of the sql string.
> -- the sp_ExecuteSQL system stored procedure compiles it and runs it.
> CREATE PROCEDURE dbo.GetAllIssues
> (
> @.sortBy VARCHAR(20)
> )
> AS
> DECLARE @.sql VARCHAR(1000)
> SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
> EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy
> EXECUTE sp_ExecuteSql @.sql
> GO
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."
>
> BUT then I realized I also need to dynamically assign the ASC or DESC
> options. This is for an asp.net datagrid and I need the first click to
> sort
> ASC and the second click on the same column to sort DESC etc... Using the
> same method I have now would require a very long and likely unefficient
> stored procedure. Any ideas for incorporating that?
Oh, c'mon, THINK! :)
CREATE PROCEDURE dbo.GetAllIssues
(
@.sortBy VARCHAR(20),
@.sortOrder VARCHAR(4) -- contains ' DESC' (space+DESC) or ''
)
AS
DECLARE @.sql VARCHAR(1000)
SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy + @.sortOrder
EXECUTE sp_ExecuteSql @.sql
GO
--
Peace & happy computing,
Mike Labosh, MCSD
"I have no choice but to believe in free will."
"djc" <noone@.nowhere.com> wrote in message
news:uxlPo2FwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> ok. cool. I will do that. Currently I'm using something like this:
> <snip>
> IF (@.SortBy = 'MainID')
> BEGIN
> SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> Status
> FROM tblMain
> ORDER BY MainID
> END
> ELSE IF (@.SortBy = 'Title')
> BEGIN
> SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> Status
> FROM tblMain
> ORDER BY Title
> END
> </snip>
>
> thanks again!
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:eERQItFwEHA.1988@.TK2MSFTNGP12.phx.gbl...
>> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
>> > AS
>> >
>> > SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
>> > Status
>> > FROM tblMain
>> > ORDER BY @.SortBy
>> No, but you can do this:
>> -- Note changes:
>> -- dbo should own everything
>> -- specify the size of your varchar in characters
>> -- this appends the content of @.sortBy onto the end of the sql string.
>> -- the sp_ExecuteSQL system stored procedure compiles it and runs it.
>> CREATE PROCEDURE dbo.GetAllIssues
>> (
>> @.sortBy VARCHAR(20)
>> )
>> AS
>> DECLARE @.sql VARCHAR(1000)
>> SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
>> EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy
>> EXECUTE sp_ExecuteSql @.sql
>> GO
>> --
>> Peace & happy computing,
>> Mike Labosh, MCSD
>> "I have no choice but to believe in free will."
>>
>
I am kind of posting frantically here and moving on before getting
replies... hehe.. I'm on a roll! I do see the simplicity of adding the
ASC/DESC into the EXECUTE sp_ExecuteSql method. I did not ask my question
clearly... I was looking for a way other than the sp_ExecuteSql method. I
had found further information in the meantime that suggested using EXEC or
EXECUTE sp_ExecuteSql has many disadvantages. I am now playing with this
setup:
CREATE PROCEDURE dbo.GetAllIssues
(
@.SortBy VarChar(50), @.Asc_Desc VarChar(8)
)
AS
SELECT MainID, Title, ShortDesc, EstimatedDeliveryDate, Status
FROM tblMain
ORDER BY
CASE @.Asc_Desc
WHEN 'desc' THEN
CASE @.SortBy
WHEN 'MainID' THEN MainID
END
END
Desc,
CASE @.Asc_Desc
WHEN 'desc' THEN
CASE @.SortBy
WHEN 'Title' THEN Title
END
END
DESC,
CASE @.Asc_Desc
WHEN 'desc' THEN
CASE @.SortBy
WHEN 'EstimatedDeliveryDate' THEN EstimatedDeliveryDate
END
END
DESC,
CASE @.Asc_Desc
WHEN 'desc' THEN
CASE @.SortBy
WHEN 'Status' THEN Status
END
END
DESC,
CASE @.Asc_Desc
WHEN 'asc' THEN
CASE @.SortBy
WHEN 'MainID' THEN MainID
END
END,
CASE @.Asc_Desc
WHEN 'asc' THEN
CASE @.SortBy
WHEN 'Title' THEN Title
END
END,
CASE @.Asc_Desc
WHEN 'asc' THEN
CASE @.SortBy
WHEN 'EstimatedDeliveryDate' THEN EstimatedDeliveryDate
END
END,
CASE @.Asc_Desc
WHEN 'asc' THEN
CASE @.SortBy
WHEN 'Status' THEN Status
END
END
GO
It works but again I AM new to TSQL aside from the basic SELECT, INSERT,
UPDATE commands. I'm trying to transition from putting my sql directly in my
code to using stored procedures as I see is recommended. The last part of my
task is how/where in my asp.net code do I communicate the asc or desc
parameter? I need to put something in place so that the page knows whether
its the first or second click etc... so it can pass the correct asc/desc
value. I'm not sure where/how that code will go. I'm new to asp.net as
well..hehe.
I appreciate your input. Any other comments are welcome... any ideas on the
last part of my task are certianly welcome as well.
-djc
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23l9hnSGwEHA.1452@.TK2MSFTNGP11.phx.gbl...
> > BUT then I realized I also need to dynamically assign the ASC or DESC
> > options. This is for an asp.net datagrid and I need the first click to
> > sort
> > ASC and the second click on the same column to sort DESC etc... Using
the
> > same method I have now would require a very long and likely unefficient
> > stored procedure. Any ideas for incorporating that?
> Oh, c'mon, THINK! :)
> CREATE PROCEDURE dbo.GetAllIssues
> (
> @.sortBy VARCHAR(20),
> @.sortOrder VARCHAR(4) -- contains ' DESC' (space+DESC) or ''
> )
> AS
> DECLARE @.sql VARCHAR(1000)
> SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
> EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy +
@.sortOrder
> EXECUTE sp_ExecuteSql @.sql
> GO
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."
> "djc" <noone@.nowhere.com> wrote in message
> news:uxlPo2FwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> > ok. cool. I will do that. Currently I'm using something like this:
> >
> > <snip>
> > IF (@.SortBy = 'MainID')
> >
> > BEGIN
> > SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate,
> > Status
> > FROM tblMain
> > ORDER BY MainID
> > END
> >
> > ELSE IF (@.SortBy = 'Title')
> >
> > BEGIN
> > SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate,
> > Status
> > FROM tblMain
> > ORDER BY Title
> > END
> > </snip>
> >
> >
> > thanks again!
> >
> > "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> > news:eERQItFwEHA.1988@.TK2MSFTNGP12.phx.gbl...
> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> >> > AS
> >> >
> >> > SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate,
> >> > Status
> >> > FROM tblMain
> >> > ORDER BY @.SortBy
> >>
> >> No, but you can do this:
> >>
> >> -- Note changes:
> >> -- dbo should own everything
> >> -- specify the size of your varchar in characters
> >> -- this appends the content of @.sortBy onto the end of the sql string.
> >> -- the sp_ExecuteSQL system stored procedure compiles it and runs it.
> >>
> >> CREATE PROCEDURE dbo.GetAllIssues
> >> (
> >> @.sortBy VARCHAR(20)
> >> )
> >> AS
> >> DECLARE @.sql VARCHAR(1000)
> >>
> >> SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
> >> EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy
> >>
> >> EXECUTE sp_ExecuteSql @.sql
> >> GO
> >>
> >> --
> >> Peace & happy computing,
> >>
> >> Mike Labosh, MCSD
> >> "I have no choice but to believe in free will."
> >>
> >>
> >
> >
>
>I am kind of posting frantically here and moving on before getting
> replies... hehe.. I'm on a roll! I do see the simplicity of adding the
> ASC/DESC into the EXECUTE sp_ExecuteSql method. I did not ask my question
> clearly... I was looking for a way other than the sp_ExecuteSql method. I
> had found further information in the meantime that suggested using EXEC or
> EXECUTE sp_ExecuteSql has many disadvantages. I am now playing with this
> setup:
The disadvantages that you refer to are that anytime you EXEC @.sql or EXEC
ExecuteSql @.sql, you are forcing SQL Server to parse, optimize and compile
your code, whereas a plain vanilla stored procedure that's not dynamic only
gets parsed optimized and compiled when it's created; not every time it's
run.
If you do a lot of dynamic stuff like this, then here are your options:
1. Build your SQL like this:
"EXECUTE sp_DexecuteSQL " & sql
and assign it to a SqlCommand.CommandText
2. Build your SQL in a string and pass it to a SqlParameter.Value wich you
Add() to a SqlCommand.Parameters collection where the SqlCommand.CommandText
is "sp_ExecuteSQL"
One thing you will want to consider is the subtle differences between these:
"EXECUTE @.sql" and "EXECUTE sp_ExecuteSql @.sql"
I personally am a bit weak on the details, but if you bump over to the
microsoft.public.sqlserver.programming group, you can ask them the
differences, and they are THE final SQL gods.
> SELECT MainID, Title, ShortDesc, EstimatedDeliveryDate, Status
> FROM tblMain
> ORDER BY
> CASE @.Asc_Desc
> WHEN 'desc' THEN
> CASE @.SortBy
> WHEN 'MainID' THEN MainID
> END
> END
> Desc,
You *definately* want to avoid this foolishness. Any DBA would slap you
with your own mouse cable :)
> The last part of my
> task is how/where in my asp.net code do I communicate the asc or desc
> parameter? I need to put something in place so that the page knows whether
> its the first or second click etc... so it can pass the correct asc/desc
> value. I'm not sure where/how that code will go.
First, assume ASC by default. It's easyier that way. Then you put a
checkbox on your WebForm:
[x] Descending
You set the checkbox to NOT do a PostBack.
Then you make your DataGrid or whatever you're using that the user clicks to
actually perform the sort do the PostBack. Then (I live in COM+ Enterprise
Services and I'm weak on asp) you do something like this in your CodeBehind
Class (aircode):
[VB]
Private Sub PageLoad(stuff)
Dim sql As String = "your stuff"
If Page.IsPostBack() Then
If chkDescending.Checked Then sql &= " DESC"
End If
Dim sortBy = sender.Name '?
Dim cm As New SqlCommand(sql, conn)
With cm ' WAY faster
.CommandText = sql
With .Parameters 'WAY faster
.Add("@.sortBy", SqlDbType.VarChar)
.Item(0).Value = sortBy
etc.
[CS]
private void pageLoad(stuff) {
string sql = "your stuff";
if (Page.IsPostBack)
if (chkDescending.Checked) sql += " DESC";
string sortBy = sender.Name;
SqlCommand cm = new SqlCommand(sql, conn);
cm.CommandText = sql;
cm.Parameters.Add("@.sortBy", SqlDbType.VarChar);
cm.Parameters(0).Value = sortBy
etc.
--
Peace & happy computing,
Mike Labosh, MCSD
"I have no choice but to believe in free will."
All very cool... except for one thing. This approach opens your program up
to a SQL injection attack. These are very common and there are lots of
people out there that pound web sites and internal programs looking for this
gateway to your server.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:ebDfBSHwEHA.1260@.TK2MSFTNGP12.phx.gbl...
> >I am kind of posting frantically here and moving on before getting
>> replies... hehe.. I'm on a roll! I do see the simplicity of adding the
>> ASC/DESC into the EXECUTE sp_ExecuteSql method. I did not ask my question
>> clearly... I was looking for a way other than the sp_ExecuteSql method. I
>> had found further information in the meantime that suggested using EXEC
>> or
>> EXECUTE sp_ExecuteSql has many disadvantages. I am now playing with this
>> setup:
> The disadvantages that you refer to are that anytime you EXEC @.sql or EXEC
> ExecuteSql @.sql, you are forcing SQL Server to parse, optimize and compile
> your code, whereas a plain vanilla stored procedure that's not dynamic
> only gets parsed optimized and compiled when it's created; not every time
> it's run.
> If you do a lot of dynamic stuff like this, then here are your options:
> 1. Build your SQL like this:
> "EXECUTE sp_DexecuteSQL " & sql
> and assign it to a SqlCommand.CommandText
> 2. Build your SQL in a string and pass it to a SqlParameter.Value wich you
> Add() to a SqlCommand.Parameters collection where the
> SqlCommand.CommandText is "sp_ExecuteSQL"
> One thing you will want to consider is the subtle differences between
> these:
> "EXECUTE @.sql" and "EXECUTE sp_ExecuteSql @.sql"
> I personally am a bit weak on the details, but if you bump over to the
> microsoft.public.sqlserver.programming group, you can ask them the
> differences, and they are THE final SQL gods.
>> SELECT MainID, Title, ShortDesc, EstimatedDeliveryDate, Status
>> FROM tblMain
>> ORDER BY
>> CASE @.Asc_Desc
>> WHEN 'desc' THEN
>> CASE @.SortBy
>> WHEN 'MainID' THEN MainID
>> END
>> END
>> Desc,
> You *definately* want to avoid this foolishness. Any DBA would slap you
> with your own mouse cable :)
>> The last part of my
>> task is how/where in my asp.net code do I communicate the asc or desc
>> parameter? I need to put something in place so that the page knows
>> whether
>> its the first or second click etc... so it can pass the correct asc/desc
>> value. I'm not sure where/how that code will go.
> First, assume ASC by default. It's easyier that way. Then you put a
> checkbox on your WebForm:
> [x] Descending
> You set the checkbox to NOT do a PostBack.
> Then you make your DataGrid or whatever you're using that the user clicks
> to actually perform the sort do the PostBack. Then (I live in COM+
> Enterprise Services and I'm weak on asp) you do something like this in
> your CodeBehind Class (aircode):
> [VB]
> Private Sub PageLoad(stuff)
> Dim sql As String = "your stuff"
> If Page.IsPostBack() Then
> If chkDescending.Checked Then sql &= " DESC"
> End If
> Dim sortBy = sender.Name '?
> Dim cm As New SqlCommand(sql, conn)
> With cm ' WAY faster
> .CommandText = sql
> With .Parameters 'WAY faster
> .Add("@.sortBy", SqlDbType.VarChar)
> .Item(0).Value = sortBy
> etc.
> [CS]
> private void pageLoad(stuff) {
> string sql = "your stuff";
> if (Page.IsPostBack)
> if (chkDescending.Checked) sql += " DESC";
> string sortBy = sender.Name;
> SqlCommand cm = new SqlCommand(sql, conn);
> cm.CommandText = sql;
> cm.Parameters.Add("@.sortBy", SqlDbType.VarChar);
> cm.Parameters(0).Value = sortBy
> etc.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."
>
What am I missing?
SQL Server is primarily optimized for storing and retrieving data.
If the datasets are small enough (and they should be!), isn't using a
Dataview then appropriate?
Do your CRUD with stored procs.
Do your display with a DataView.
With the dataview you can then use the RowFilter and Sort properties to
control the display of the data.
You are opening yourself up for SQL injection attacks and missing/extra
quotes when you use string concatentation in code.
"djc" <noone@.nowhere.com> wrote in message
news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
>I guess that means the ORDER BY can't use a parameter for the ASC or DESC
> options either then huh? So much for benefiting from stored procedures, in
> my case at least. I'll go back to just putting the SQL directly in my
> code.
> thanks for the reply.
> "Marina" <someone@.nospam.com> wrote in message
> news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> You can only pass literal values in - not column names. If you wanted to
> do
>> something like that, you would have to create a string with the query and
>> execute it.
>> Also, you can try passing in @.SortBy as an integer, and sorting on the
>> column order, instead of using the column name to perform the sort. I
>> haven't tried it, but it might work - not sure if the order by can use
>> any
>> parameters.
>> "djc" <noone@.nowhere.com> wrote in message
>> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
>> WebMatrix
>> > to work on this page. Currently I
>> > have this in the stored procedure:
>> >
>> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
>> > AS
>> >
>> > SELECT MainID, Title, ShortDesc, DateCreated,
>> > EstimatedDeliveryDate,
>> > Status
>> > FROM tblMain
>> > ORDER BY @.SortBy
>> >
>> > GO
>> >
>> > I get this error when trying to save it:
>> > "The select item identified by the ORDER BY number 1 contains a
>> > variable
>> as
>> > part of the expression identifying a column position. Variables are
>> > only
>> > allowed when ordering by an expression referencing a column name."
>> >
>> > any help would be very much appreciated. Thanks!
>> >
>> >
>> >
>>
>
I thought I would get that response from someone! hehe. No fear... I'm
commited to learning stored procedures... I was just frustrated. Programming
is more hobby than job for me right now. I'm actually a network
administrator. I just find this stuff fun for some wierd reason!
Please correct me if I'm wrong but I don't think I can use a DataView in my
case. The data I'm retrieving is for the datasource of a DataGrid that needs
to be both sortable and pagable. I'm currently using a DataTable.. can I use
a DataView for that?
any input is appreciated. Thanks.
"Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> What am I missing?
> SQL Server is primarily optimized for storing and retrieving data.
> If the datasets are small enough (and they should be!), isn't using a
> Dataview then appropriate?
> Do your CRUD with stored procs.
> Do your display with a DataView.
> With the dataview you can then use the RowFilter and Sort properties to
> control the display of the data.
> You are opening yourself up for SQL injection attacks and missing/extra
> quotes when you use string concatentation in code.
> "djc" <noone@.nowhere.com> wrote in message
> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> >I guess that means the ORDER BY can't use a parameter for the ASC or DESC
> > options either then huh? So much for benefiting from stored procedures,
in
> > my case at least. I'll go back to just putting the SQL directly in my
> > code.
> >
> > thanks for the reply.
> >
> > "Marina" <someone@.nospam.com> wrote in message
> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> >> You can only pass literal values in - not column names. If you wanted
to
> > do
> >> something like that, you would have to create a string with the query
and
> >> execute it.
> >>
> >> Also, you can try passing in @.SortBy as an integer, and sorting on the
> >> column order, instead of using the column name to perform the sort. I
> >> haven't tried it, but it might work - not sure if the order by can use
> >> any
> >> parameters.
> >>
> >> "djc" <noone@.nowhere.com> wrote in message
> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
> >> WebMatrix
> >> > to work on this page. Currently I
> >> > have this in the stored procedure:
> >> >
> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> >> > AS
> >> >
> >> > SELECT MainID, Title, ShortDesc, DateCreated,
> >> > EstimatedDeliveryDate,
> >> > Status
> >> > FROM tblMain
> >> > ORDER BY @.SortBy
> >> >
> >> > GO
> >> >
> >> > I get this error when trying to save it:
> >> > "The select item identified by the ORDER BY number 1 contains a
> >> > variable
> >> as
> >> > part of the expression identifying a column position. Variables are
> >> > only
> >> > allowed when ordering by an expression referencing a column name."
> >> >
> >> > any help would be very much appreciated. Thanks!
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
A DataView is generally associated with a DataTable. You fill the DataTable
and use the DefaultView to get the DataView.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"djc" <noone@.nowhere.com> wrote in message
news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
>I thought I would get that response from someone! hehe. No fear... I'm
> commited to learning stored procedures... I was just frustrated.
> Programming
> is more hobby than job for me right now. I'm actually a network
> administrator. I just find this stuff fun for some wierd reason!
> Please correct me if I'm wrong but I don't think I can use a DataView in
> my
> case. The data I'm retrieving is for the datasource of a DataGrid that
> needs
> to be both sortable and pagable. I'm currently using a DataTable.. can I
> use
> a DataView for that?
> any input is appreciated. Thanks.
>
> "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
> news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
>> What am I missing?
>> SQL Server is primarily optimized for storing and retrieving data.
>> If the datasets are small enough (and they should be!), isn't using a
>> Dataview then appropriate?
>> Do your CRUD with stored procs.
>> Do your display with a DataView.
>> With the dataview you can then use the RowFilter and Sort properties to
>> control the display of the data.
>> You are opening yourself up for SQL injection attacks and missing/extra
>> quotes when you use string concatentation in code.
>> "djc" <noone@.nowhere.com> wrote in message
>> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
>> >I guess that means the ORDER BY can't use a parameter for the ASC or
>> >DESC
>> > options either then huh? So much for benefiting from stored procedures,
> in
>> > my case at least. I'll go back to just putting the SQL directly in my
>> > code.
>> >
>> > thanks for the reply.
>> >
>> > "Marina" <someone@.nospam.com> wrote in message
>> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> >> You can only pass literal values in - not column names. If you wanted
> to
>> > do
>> >> something like that, you would have to create a string with the query
> and
>> >> execute it.
>> >>
>> >> Also, you can try passing in @.SortBy as an integer, and sorting on the
>> >> column order, instead of using the column name to perform the sort. I
>> >> haven't tried it, but it might work - not sure if the order by can use
>> >> any
>> >> parameters.
>> >>
>> >> "djc" <noone@.nowhere.com> wrote in message
>> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> >> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
>> >> WebMatrix
>> >> > to work on this page. Currently I
>> >> > have this in the stored procedure:
>> >> >
>> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
>> >> > AS
>> >> >
>> >> > SELECT MainID, Title, ShortDesc, DateCreated,
>> >> > EstimatedDeliveryDate,
>> >> > Status
>> >> > FROM tblMain
>> >> > ORDER BY @.SortBy
>> >> >
>> >> > GO
>> >> >
>> >> > I get this error when trying to save it:
>> >> > "The select item identified by the ORDER BY number 1 contains a
>> >> > variable
>> >> as
>> >> > part of the expression identifying a column position. Variables are
>> >> > only
>> >> > allowed when ordering by an expression referencing a column name."
>> >> >
>> >> > any help would be very much appreciated. Thanks!
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>
ok. I'm still unclear as to whether I could assign a dataView to the
dataSource property of a dataGrid though? (a sortable, pagable, datagrid)
I'll look more into it.
thanks for the reply.
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:OKrTGFQwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> A DataView is generally associated with a DataTable. You fill the
DataTable
> and use the DefaultView to get the DataView.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "djc" <noone@.nowhere.com> wrote in message
> news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> >I thought I would get that response from someone! hehe. No fear... I'm
> > commited to learning stored procedures... I was just frustrated.
> > Programming
> > is more hobby than job for me right now. I'm actually a network
> > administrator. I just find this stuff fun for some wierd reason!
> >
> > Please correct me if I'm wrong but I don't think I can use a DataView in
> > my
> > case. The data I'm retrieving is for the datasource of a DataGrid that
> > needs
> > to be both sortable and pagable. I'm currently using a DataTable.. can I
> > use
> > a DataView for that?
> >
> > any input is appreciated. Thanks.
> >
> >
> >
> > "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
> > news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> What am I missing?
> >>
> >> SQL Server is primarily optimized for storing and retrieving data.
> >>
> >> If the datasets are small enough (and they should be!), isn't using a
> >> Dataview then appropriate?
> >>
> >> Do your CRUD with stored procs.
> >> Do your display with a DataView.
> >>
> >> With the dataview you can then use the RowFilter and Sort properties to
> >> control the display of the data.
> >>
> >> You are opening yourself up for SQL injection attacks and missing/extra
> >> quotes when you use string concatentation in code.
> >>
> >> "djc" <noone@.nowhere.com> wrote in message
> >> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> >> >I guess that means the ORDER BY can't use a parameter for the ASC or
> >> >DESC
> >> > options either then huh? So much for benefiting from stored
procedures,
> > in
> >> > my case at least. I'll go back to just putting the SQL directly in my
> >> > code.
> >> >
> >> > thanks for the reply.
> >> >
> >> > "Marina" <someone@.nospam.com> wrote in message
> >> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> >> >> You can only pass literal values in - not column names. If you
wanted
> > to
> >> > do
> >> >> something like that, you would have to create a string with the
query
> > and
> >> >> execute it.
> >> >>
> >> >> Also, you can try passing in @.SortBy as an integer, and sorting on
the
> >> >> column order, instead of using the column name to perform the sort.
I
> >> >> haven't tried it, but it might work - not sure if the order by can
use
> >> >> any
> >> >> parameters.
> >> >>
> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> >> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
> >> >> WebMatrix
> >> >> > to work on this page. Currently I
> >> >> > have this in the stored procedure:
> >> >> >
> >> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> >> >> > AS
> >> >> >
> >> >> > SELECT MainID, Title, ShortDesc, DateCreated,
> >> >> > EstimatedDeliveryDate,
> >> >> > Status
> >> >> > FROM tblMain
> >> >> > ORDER BY @.SortBy
> >> >> >
> >> >> > GO
> >> >> >
> >> >> > I get this error when trying to save it:
> >> >> > "The select item identified by the ORDER BY number 1 contains a
> >> >> > variable
> >> >> as
> >> >> > part of the expression identifying a column position. Variables
are
> >> >> > only
> >> >> > allowed when ordering by an expression referencing a column name."
> >> >> >
> >> >> > any help would be very much appreciated. Thanks!
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
Sure. That's a great way to add sort, seek, find, filter to what you see in
a DataGrid.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"djc" <noone@.nowhere.com> wrote in message
news:OQikmKQwEHA.1564@.TK2MSFTNGP09.phx.gbl...
> ok. I'm still unclear as to whether I could assign a dataView to the
> dataSource property of a dataGrid though? (a sortable, pagable, datagrid)
> I'll look more into it.
> thanks for the reply.
> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> news:OKrTGFQwEHA.2016@.TK2MSFTNGP15.phx.gbl...
>> A DataView is generally associated with a DataTable. You fill the
> DataTable
>> and use the DefaultView to get the DataView.
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> __________________________________
>> "djc" <noone@.nowhere.com> wrote in message
>> news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
>> >I thought I would get that response from someone! hehe. No fear... I'm
>> > commited to learning stored procedures... I was just frustrated.
>> > Programming
>> > is more hobby than job for me right now. I'm actually a network
>> > administrator. I just find this stuff fun for some wierd reason!
>> >
>> > Please correct me if I'm wrong but I don't think I can use a DataView
>> > in
>> > my
>> > case. The data I'm retrieving is for the datasource of a DataGrid that
>> > needs
>> > to be both sortable and pagable. I'm currently using a DataTable.. can
>> > I
>> > use
>> > a DataView for that?
>> >
>> > any input is appreciated. Thanks.
>> >
>> >
>> >
>> > "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
>> > news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
>> >> What am I missing?
>> >>
>> >> SQL Server is primarily optimized for storing and retrieving data.
>> >>
>> >> If the datasets are small enough (and they should be!), isn't using a
>> >> Dataview then appropriate?
>> >>
>> >> Do your CRUD with stored procs.
>> >> Do your display with a DataView.
>> >>
>> >> With the dataview you can then use the RowFilter and Sort properties
>> >> to
>> >> control the display of the data.
>> >>
>> >> You are opening yourself up for SQL injection attacks and
>> >> missing/extra
>> >> quotes when you use string concatentation in code.
>> >>
>> >> "djc" <noone@.nowhere.com> wrote in message
>> >> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
>> >> >I guess that means the ORDER BY can't use a parameter for the ASC or
>> >> >DESC
>> >> > options either then huh? So much for benefiting from stored
> procedures,
>> > in
>> >> > my case at least. I'll go back to just putting the SQL directly in
>> >> > my
>> >> > code.
>> >> >
>> >> > thanks for the reply.
>> >> >
>> >> > "Marina" <someone@.nospam.com> wrote in message
>> >> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> >> >> You can only pass literal values in - not column names. If you
> wanted
>> > to
>> >> > do
>> >> >> something like that, you would have to create a string with the
> query
>> > and
>> >> >> execute it.
>> >> >>
>> >> >> Also, you can try passing in @.SortBy as an integer, and sorting on
> the
>> >> >> column order, instead of using the column name to perform the sort.
> I
>> >> >> haven't tried it, but it might work - not sure if the order by can
> use
>> >> >> any
>> >> >> parameters.
>> >> >>
>> >> >> "djc" <noone@.nowhere.com> wrote in message
>> >> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> >> >> > I'm very new to stored procedures! I am using MSDE2000 and
>> >> >> > asp.net
>> >> >> WebMatrix
>> >> >> > to work on this page. Currently I
>> >> >> > have this in the stored procedure:
>> >> >> >
>> >> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
>> >> >> > AS
>> >> >> >
>> >> >> > SELECT MainID, Title, ShortDesc, DateCreated,
>> >> >> > EstimatedDeliveryDate,
>> >> >> > Status
>> >> >> > FROM tblMain
>> >> >> > ORDER BY @.SortBy
>> >> >> >
>> >> >> > GO
>> >> >> >
>> >> >> > I get this error when trying to save it:
>> >> >> > "The select item identified by the ORDER BY number 1 contains a
>> >> >> > variable
>> >> >> as
>> >> >> > part of the expression identifying a column position. Variables
> are
>> >> >> > only
>> >> >> > allowed when ordering by an expression referencing a column
>> >> >> > name."
>> >> >> >
>> >> >> > any help would be very much appreciated. Thanks!
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>
ok. cool. So then this suggested approach is to use a dataView to bind to
the datagrid. I assume the dataView is *not* forward only then, since I need
the datagrid to be pageable. One last question with regard to this approach
compared to calling a stored procedure then... and not the stored procedure
you see below but one without any dynamic sql. Will there be significant
performance differences? In other words would it be faster to sort or filter
a dataview than to call another stored procedure to do it?
thanks again!
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:OavWl0RwEHA.1292@.TK2MSFTNGP10.phx.gbl...
> Sure. That's a great way to add sort, seek, find, filter to what you see
in
> a DataGrid.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "djc" <noone@.nowhere.com> wrote in message
> news:OQikmKQwEHA.1564@.TK2MSFTNGP09.phx.gbl...
> > ok. I'm still unclear as to whether I could assign a dataView to the
> > dataSource property of a dataGrid though? (a sortable, pagable,
datagrid)
> > I'll look more into it.
> >
> > thanks for the reply.
> >
> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> > news:OKrTGFQwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> >> A DataView is generally associated with a DataTable. You fill the
> > DataTable
> >> and use the DefaultView to get the DataView.
> >>
> >> --
> >> ____________________________________
> >> William (Bill) Vaughn
> >> Author, Mentor, Consultant
> >> Microsoft MVP
> >> www.betav.com
> >> Please reply only to the newsgroup so that others can benefit.
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >> __________________________________
> >>
> >> "djc" <noone@.nowhere.com> wrote in message
> >> news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> >> >I thought I would get that response from someone! hehe. No fear... I'm
> >> > commited to learning stored procedures... I was just frustrated.
> >> > Programming
> >> > is more hobby than job for me right now. I'm actually a network
> >> > administrator. I just find this stuff fun for some wierd reason!
> >> >
> >> > Please correct me if I'm wrong but I don't think I can use a DataView
> >> > in
> >> > my
> >> > case. The data I'm retrieving is for the datasource of a DataGrid
that
> >> > needs
> >> > to be both sortable and pagable. I'm currently using a DataTable..
can
> >> > I
> >> > use
> >> > a DataView for that?
> >> >
> >> > any input is appreciated. Thanks.
> >> >
> >> >
> >> >
> >> > "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
> >> > news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> >> What am I missing?
> >> >>
> >> >> SQL Server is primarily optimized for storing and retrieving data.
> >> >>
> >> >> If the datasets are small enough (and they should be!), isn't using
a
> >> >> Dataview then appropriate?
> >> >>
> >> >> Do your CRUD with stored procs.
> >> >> Do your display with a DataView.
> >> >>
> >> >> With the dataview you can then use the RowFilter and Sort properties
> >> >> to
> >> >> control the display of the data.
> >> >>
> >> >> You are opening yourself up for SQL injection attacks and
> >> >> missing/extra
> >> >> quotes when you use string concatentation in code.
> >> >>
> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> >> >> >I guess that means the ORDER BY can't use a parameter for the ASC
or
> >> >> >DESC
> >> >> > options either then huh? So much for benefiting from stored
> > procedures,
> >> > in
> >> >> > my case at least. I'll go back to just putting the SQL directly in
> >> >> > my
> >> >> > code.
> >> >> >
> >> >> > thanks for the reply.
> >> >> >
> >> >> > "Marina" <someone@.nospam.com> wrote in message
> >> >> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> >> >> >> You can only pass literal values in - not column names. If you
> > wanted
> >> > to
> >> >> > do
> >> >> >> something like that, you would have to create a string with the
> > query
> >> > and
> >> >> >> execute it.
> >> >> >>
> >> >> >> Also, you can try passing in @.SortBy as an integer, and sorting
on
> > the
> >> >> >> column order, instead of using the column name to perform the
sort.
> > I
> >> >> >> haven't tried it, but it might work - not sure if the order by
can
> > use
> >> >> >> any
> >> >> >> parameters.
> >> >> >>
> >> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> >> >> > I'm very new to stored procedures! I am using MSDE2000 and
> >> >> >> > asp.net
> >> >> >> WebMatrix
> >> >> >> > to work on this page. Currently I
> >> >> >> > have this in the stored procedure:
> >> >> >> >
> >> >> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> >> >> >> > AS
> >> >> >> >
> >> >> >> > SELECT MainID, Title, ShortDesc, DateCreated,
> >> >> >> > EstimatedDeliveryDate,
> >> >> >> > Status
> >> >> >> > FROM tblMain
> >> >> >> > ORDER BY @.SortBy
> >> >> >> >
> >> >> >> > GO
> >> >> >> >
> >> >> >> > I get this error when trying to save it:
> >> >> >> > "The select item identified by the ORDER BY number 1 contains a
> >> >> >> > variable
> >> >> >> as
> >> >> >> > part of the expression identifying a column position. Variables
> > are
> >> >> >> > only
> >> >> >> > allowed when ordering by an expression referencing a column
> >> >> >> > name."
> >> >> >> >
> >> >> >> > any help would be very much appreciated. Thanks!
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
The DataView is just that--a "view" on a DataTable. DataTables are static
rowsets and not "pageable". That is, they are filled in a single operation
(via the Fill method) and the connection is closed. They do not work like a
server-side cursor that permits you to scroll around in a selected rowset.
Is it smarter to requery to resort? Usually not. Consider that a good app
will keep only as many rows as the user can deal with in memory (in the
DataTable) so it's often necessary to requery to get more data. To resort?
Nope, this can be done by the DataView.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"djc" <noone@.nowhere.com> wrote in message
news:uNh8GIawEHA.1408@.TK2MSFTNGP10.phx.gbl...
> ok. cool. So then this suggested approach is to use a dataView to bind to
> the datagrid. I assume the dataView is *not* forward only then, since I
> need
> the datagrid to be pageable. One last question with regard to this
> approach
> compared to calling a stored procedure then... and not the stored
> procedure
> you see below but one without any dynamic sql. Will there be significant
> performance differences? In other words would it be faster to sort or
> filter
> a dataview than to call another stored procedure to do it?
> thanks again!
> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> news:OavWl0RwEHA.1292@.TK2MSFTNGP10.phx.gbl...
>> Sure. That's a great way to add sort, seek, find, filter to what you see
> in
>> a DataGrid.
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> __________________________________
>> "djc" <noone@.nowhere.com> wrote in message
>> news:OQikmKQwEHA.1564@.TK2MSFTNGP09.phx.gbl...
>> > ok. I'm still unclear as to whether I could assign a dataView to the
>> > dataSource property of a dataGrid though? (a sortable, pagable,
> datagrid)
>> > I'll look more into it.
>> >
>> > thanks for the reply.
>> >
>> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
>> > news:OKrTGFQwEHA.2016@.TK2MSFTNGP15.phx.gbl...
>> >> A DataView is generally associated with a DataTable. You fill the
>> > DataTable
>> >> and use the DefaultView to get the DataView.
>> >>
>> >> --
>> >> ____________________________________
>> >> William (Bill) Vaughn
>> >> Author, Mentor, Consultant
>> >> Microsoft MVP
>> >> www.betav.com
>> >> Please reply only to the newsgroup so that others can benefit.
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >> __________________________________
>> >>
>> >> "djc" <noone@.nowhere.com> wrote in message
>> >> news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
>> >> >I thought I would get that response from someone! hehe. No fear...
>> >> >I'm
>> >> > commited to learning stored procedures... I was just frustrated.
>> >> > Programming
>> >> > is more hobby than job for me right now. I'm actually a network
>> >> > administrator. I just find this stuff fun for some wierd reason!
>> >> >
>> >> > Please correct me if I'm wrong but I don't think I can use a
>> >> > DataView
>> >> > in
>> >> > my
>> >> > case. The data I'm retrieving is for the datasource of a DataGrid
> that
>> >> > needs
>> >> > to be both sortable and pagable. I'm currently using a DataTable..
> can
>> >> > I
>> >> > use
>> >> > a DataView for that?
>> >> >
>> >> > any input is appreciated. Thanks.
>> >> >
>> >> >
>> >> >
>> >> > "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
>> >> > news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
>> >> >> What am I missing?
>> >> >>
>> >> >> SQL Server is primarily optimized for storing and retrieving data.
>> >> >>
>> >> >> If the datasets are small enough (and they should be!), isn't using
> a
>> >> >> Dataview then appropriate?
>> >> >>
>> >> >> Do your CRUD with stored procs.
>> >> >> Do your display with a DataView.
>> >> >>
>> >> >> With the dataview you can then use the RowFilter and Sort
>> >> >> properties
>> >> >> to
>> >> >> control the display of the data.
>> >> >>
>> >> >> You are opening yourself up for SQL injection attacks and
>> >> >> missing/extra
>> >> >> quotes when you use string concatentation in code.
>> >> >>
>> >> >> "djc" <noone@.nowhere.com> wrote in message
>> >> >> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
>> >> >> >I guess that means the ORDER BY can't use a parameter for the ASC
> or
>> >> >> >DESC
>> >> >> > options either then huh? So much for benefiting from stored
>> > procedures,
>> >> > in
>> >> >> > my case at least. I'll go back to just putting the SQL directly
>> >> >> > in
>> >> >> > my
>> >> >> > code.
>> >> >> >
>> >> >> > thanks for the reply.
>> >> >> >
>> >> >> > "Marina" <someone@.nospam.com> wrote in message
>> >> >> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> >> >> >> You can only pass literal values in - not column names. If you
>> > wanted
>> >> > to
>> >> >> > do
>> >> >> >> something like that, you would have to create a string with the
>> > query
>> >> > and
>> >> >> >> execute it.
>> >> >> >>
>> >> >> >> Also, you can try passing in @.SortBy as an integer, and sorting
> on
>> > the
>> >> >> >> column order, instead of using the column name to perform the
> sort.
>> > I
>> >> >> >> haven't tried it, but it might work - not sure if the order by
> can
>> > use
>> >> >> >> any
>> >> >> >> parameters.
>> >> >> >>
>> >> >> >> "djc" <noone@.nowhere.com> wrote in message
>> >> >> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> >> >> >> > I'm very new to stored procedures! I am using MSDE2000 and
>> >> >> >> > asp.net
>> >> >> >> WebMatrix
>> >> >> >> > to work on this page. Currently I
>> >> >> >> > have this in the stored procedure:
>> >> >> >> >
>> >> >> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
>> >> >> >> > AS
>> >> >> >> >
>> >> >> >> > SELECT MainID, Title, ShortDesc, DateCreated,
>> >> >> >> > EstimatedDeliveryDate,
>> >> >> >> > Status
>> >> >> >> > FROM tblMain
>> >> >> >> > ORDER BY @.SortBy
>> >> >> >> >
>> >> >> >> > GO
>> >> >> >> >
>> >> >> >> > I get this error when trying to save it:
>> >> >> >> > "The select item identified by the ORDER BY number 1 contains
>> >> >> >> > a
>> >> >> >> > variable
>> >> >> >> as
>> >> >> >> > part of the expression identifying a column position.
>> >> >> >> > Variables
>> > are
>> >> >> >> > only
>> >> >> >> > allowed when ordering by an expression referencing a column
>> >> >> >> > name."
>> >> >> >> >
>> >> >> >> > any help would be very much appreciated. Thanks!
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>
Thanks again. You have been very helpful!
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:ugPl5PpwEHA.3584@.TK2MSFTNGP10.phx.gbl...
> The DataView is just that--a "view" on a DataTable. DataTables are static
> rowsets and not "pageable". That is, they are filled in a single operation
> (via the Fill method) and the connection is closed. They do not work like
a
> server-side cursor that permits you to scroll around in a selected rowset.
> Is it smarter to requery to resort? Usually not. Consider that a good app
> will keep only as many rows as the user can deal with in memory (in the
> DataTable) so it's often necessary to requery to get more data. To resort?
> Nope, this can be done by the DataView.
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "djc" <noone@.nowhere.com> wrote in message
> news:uNh8GIawEHA.1408@.TK2MSFTNGP10.phx.gbl...
> > ok. cool. So then this suggested approach is to use a dataView to bind
to
> > the datagrid. I assume the dataView is *not* forward only then, since I
> > need
> > the datagrid to be pageable. One last question with regard to this
> > approach
> > compared to calling a stored procedure then... and not the stored
> > procedure
> > you see below but one without any dynamic sql. Will there be significant
> > performance differences? In other words would it be faster to sort or
> > filter
> > a dataview than to call another stored procedure to do it?
> >
> > thanks again!
> >
> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> > news:OavWl0RwEHA.1292@.TK2MSFTNGP10.phx.gbl...
> >> Sure. That's a great way to add sort, seek, find, filter to what you
see
> > in
> >> a DataGrid.
> >>
> >> --
> >> ____________________________________
> >> William (Bill) Vaughn
> >> Author, Mentor, Consultant
> >> Microsoft MVP
> >> www.betav.com
> >> Please reply only to the newsgroup so that others can benefit.
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >> __________________________________
> >>
> >> "djc" <noone@.nowhere.com> wrote in message
> >> news:OQikmKQwEHA.1564@.TK2MSFTNGP09.phx.gbl...
> >> > ok. I'm still unclear as to whether I could assign a dataView to the
> >> > dataSource property of a dataGrid though? (a sortable, pagable,
> > datagrid)
> >> > I'll look more into it.
> >> >
> >> > thanks for the reply.
> >> >
> >> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in
message
> >> > news:OKrTGFQwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> >> >> A DataView is generally associated with a DataTable. You fill the
> >> > DataTable
> >> >> and use the DefaultView to get the DataView.
> >> >>
> >> >> --
> >> >> ____________________________________
> >> >> William (Bill) Vaughn
> >> >> Author, Mentor, Consultant
> >> >> Microsoft MVP
> >> >> www.betav.com
> >> >> Please reply only to the newsgroup so that others can benefit.
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >> >> __________________________________
> >> >>
> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> >> >> >I thought I would get that response from someone! hehe. No fear...
> >> >> >I'm
> >> >> > commited to learning stored procedures... I was just frustrated.
> >> >> > Programming
> >> >> > is more hobby than job for me right now. I'm actually a network
> >> >> > administrator. I just find this stuff fun for some wierd reason!
> >> >> >
> >> >> > Please correct me if I'm wrong but I don't think I can use a
> >> >> > DataView
> >> >> > in
> >> >> > my
> >> >> > case. The data I'm retrieving is for the datasource of a DataGrid
> > that
> >> >> > needs
> >> >> > to be both sortable and pagable. I'm currently using a DataTable..
> > can
> >> >> > I
> >> >> > use
> >> >> > a DataView for that?
> >> >> >
> >> >> > any input is appreciated. Thanks.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
> >> >> > news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> >> >> What am I missing?
> >> >> >>
> >> >> >> SQL Server is primarily optimized for storing and retrieving
data.
> >> >> >>
> >> >> >> If the datasets are small enough (and they should be!), isn't
using
> > a
> >> >> >> Dataview then appropriate?
> >> >> >>
> >> >> >> Do your CRUD with stored procs.
> >> >> >> Do your display with a DataView.
> >> >> >>
> >> >> >> With the dataview you can then use the RowFilter and Sort
> >> >> >> properties
> >> >> >> to
> >> >> >> control the display of the data.
> >> >> >>
> >> >> >> You are opening yourself up for SQL injection attacks and
> >> >> >> missing/extra
> >> >> >> quotes when you use string concatentation in code.
> >> >> >>
> >> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> >> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> >> >> >> >I guess that means the ORDER BY can't use a parameter for the
ASC
> > or
> >> >> >> >DESC
> >> >> >> > options either then huh? So much for benefiting from stored
> >> > procedures,
> >> >> > in
> >> >> >> > my case at least. I'll go back to just putting the SQL directly
> >> >> >> > in
> >> >> >> > my
> >> >> >> > code.
> >> >> >> >
> >> >> >> > thanks for the reply.
> >> >> >> >
> >> >> >> > "Marina" <someone@.nospam.com> wrote in message
> >> >> >> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> >> >> >> >> You can only pass literal values in - not column names. If
you
> >> > wanted
> >> >> > to
> >> >> >> > do
> >> >> >> >> something like that, you would have to create a string with
the
> >> > query
> >> >> > and
> >> >> >> >> execute it.
> >> >> >> >>
> >> >> >> >> Also, you can try passing in @.SortBy as an integer, and
sorting
> > on
> >> > the
> >> >> >> >> column order, instead of using the column name to perform the
> > sort.
> >> > I
> >> >> >> >> haven't tried it, but it might work - not sure if the order by
> > can
> >> > use
> >> >> >> >> any
> >> >> >> >> parameters.
> >> >> >> >>
> >> >> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> >> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> >> >> >> > I'm very new to stored procedures! I am using MSDE2000 and
> >> >> >> >> > asp.net
> >> >> >> >> WebMatrix
> >> >> >> >> > to work on this page. Currently I
> >> >> >> >> > have this in the stored procedure:
> >> >> >> >> >
> >> >> >> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> >> >> >> >> > AS
> >> >> >> >> >
> >> >> >> >> > SELECT MainID, Title, ShortDesc, DateCreated,
> >> >> >> >> > EstimatedDeliveryDate,
> >> >> >> >> > Status
> >> >> >> >> > FROM tblMain
> >> >> >> >> > ORDER BY @.SortBy
> >> >> >> >> >
> >> >> >> >> > GO
> >> >> >> >> >
> >> >> >> >> > I get this error when trying to save it:
> >> >> >> >> > "The select item identified by the ORDER BY number 1
contains
> >> >> >> >> > a
> >> >> >> >> > variable
> >> >> >> >> as
> >> >> >> >> > part of the expression identifying a column position.
> >> >> >> >> > Variables
> >> > are
> >> >> >> >> > only
> >> >> >> >> > allowed when ordering by an expression referencing a column
> >> >> >> >> > name."
> >> >> >> >> >
> >> >> >> >> > any help would be very much appreciated. Thanks!
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >>
> >> >> >> >>
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
On other database platforms I have done things similar to what you are trying
to do with your strored procedure. Although it is not elegant it works and
the logic is on the Database:
create procedure mysql @.sortby varchar(12) As
if @.sortby = 'CustomerId'
Begin
select CustomerId, CompanyName, Address
from Customers
order by CustomerId
End
else
if @.sortby = 'CompanyName'
begin
select CustomerId, CompanyName, Address
from Customers
order by CompanyName
end
else
begin
select CustomerId, CompanyName, Address
from Customers
end
"djc" wrote:
> I'm very new to stored procedures! I am using MSDE2000 and asp.net WebMatrix
> to work on this page. Currently I
> have this in the stored procedure:
> CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> AS
> SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> Status
> FROM tblMain
> ORDER BY @.SortBy
> GO
> I get this error when trying to save it:
> "The select item identified by the ORDER BY number 1 contains a variable as
> part of the expression identifying a column position. Variables are only
> allowed when ordering by an expression referencing a column name."
> any help would be very much appreciated. Thanks!
>
>
thanks for the input.
"fom" <fom@.discussions.microsoft.com> wrote in message
news:FD631212-3D7C-4C58-AF90-961FA566770A@.microsoft.com...
> On other database platforms I have done things similar to what you are
trying
> to do with your strored procedure. Although it is not elegant it works and
> the logic is on the Database:
> create procedure mysql @.sortby varchar(12) As
> if @.sortby = 'CustomerId'
> Begin
> select CustomerId, CompanyName, Address
> from Customers
> order by CustomerId
> End
> else
> if @.sortby = 'CompanyName'
> begin
> select CustomerId, CompanyName, Address
> from Customers
> order by CompanyName
> end
> else
> begin
> select CustomerId, CompanyName, Address
> from Customers
> end
>
> "djc" wrote:
> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
WebMatrix
> > to work on this page. Currently I
> > have this in the stored procedure:
> >
> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> > AS
> >
> > SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> > Status
> > FROM tblMain
> > ORDER BY @.SortBy
> >
> > GO
> >
> > I get this error when trying to save it:
> > "The select item identified by the ORDER BY number 1 contains a variable
as
> > part of the expression identifying a column position. Variables are only
> > allowed when ordering by an expression referencing a column name."
> >
> > any help would be very much appreciated. Thanks!
> >
> >
> >
> >
Just as an aside you can do a conditional order by clause like this
order by Case When @.SortBy1 = '1' Then ContactFirstForename
When @.SortBy1 = '2' Then ContactSurname
When @.SortBy1 = '3' Then ContactTelephoneSurname
End
Only thing you may need to do is make sure that the typing is consistant;
you cant mix numeric and character fields; but you can have as many
parameters as you like so you can do @.SortBY1 = 4 then 'A' and add a sortby2
as neccessary
"djc" wrote:
> thanks for the input.
> "fom" <fom@.discussions.microsoft.com> wrote in message
> news:FD631212-3D7C-4C58-AF90-961FA566770A@.microsoft.com...
> > On other database platforms I have done things similar to what you are
> trying
> > to do with your strored procedure. Although it is not elegant it works and
> > the logic is on the Database:
> >
> > create procedure mysql @.sortby varchar(12) As
> > if @.sortby = 'CustomerId'
> > Begin
> > select CustomerId, CompanyName, Address
> > from Customers
> > order by CustomerId
> > End
> > else
> > if @.sortby = 'CompanyName'
> > begin
> > select CustomerId, CompanyName, Address
> > from Customers
> > order by CompanyName
> > end
> > else
> > begin
> > select CustomerId, CompanyName, Address
> > from Customers
> > end
> >
> >
> >
> > "djc" wrote:
> >
> > > I'm very new to stored procedures! I am using MSDE2000 and asp.net
> WebMatrix
> > > to work on this page. Currently I
> > > have this in the stored procedure:
> > >
> > > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> > > AS
> > >
> > > SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> > > Status
> > > FROM tblMain
> > > ORDER BY @.SortBy
> > >
> > > GO
> > >
> > > I get this error when trying to save it:
> > > "The select item identified by the ORDER BY number 1 contains a variable
> as
> > > part of the expression identifying a column position. Variables are only
> > > allowed when ordering by an expression referencing a column name."
> > >
> > > any help would be very much appreciated. Thanks!
> > >
> > >
> > >
> > >
>
>
Thanks for the info.
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:D6580D37-CC7C-4C04-8381-91BFAD177D14@.microsoft.com...
> Just as an aside you can do a conditional order by clause like this
> order by Case When @.SortBy1 = '1' Then ContactFirstForename
> When @.SortBy1 = '2' Then ContactSurname
> When @.SortBy1 = '3' Then ContactTelephoneSurname
> End
> Only thing you may need to do is make sure that the typing is consistant;
> you cant mix numeric and character fields; but you can have as many
> parameters as you like so you can do @.SortBY1 = 4 then 'A' and add a
sortby2
> as neccessary
> "djc" wrote:
> > thanks for the input.
> >
> > "fom" <fom@.discussions.microsoft.com> wrote in message
> > news:FD631212-3D7C-4C58-AF90-961FA566770A@.microsoft.com...
> > > On other database platforms I have done things similar to what you are
> > trying
> > > to do with your strored procedure. Although it is not elegant it works
and
> > > the logic is on the Database:
> > >
> > > create procedure mysql @.sortby varchar(12) As
> > > if @.sortby = 'CustomerId'
> > > Begin
> > > select CustomerId, CompanyName, Address
> > > from Customers
> > > order by CustomerId
> > > End
> > > else
> > > if @.sortby = 'CompanyName'
> > > begin
> > > select CustomerId, CompanyName, Address
> > > from Customers
> > > order by CompanyName
> > > end
> > > else
> > > begin
> > > select CustomerId, CompanyName, Address
> > > from Customers
> > > end
> > >
> > >
> > >
> > > "djc" wrote:
> > >
> > > > I'm very new to stored procedures! I am using MSDE2000 and asp.net
> > WebMatrix
> > > > to work on this page. Currently I
> > > > have this in the stored procedure:
> > > >
> > > > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> > > > AS
> > > >
> > > > SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate,
> > > > Status
> > > > FROM tblMain
> > > > ORDER BY @.SortBy
> > > >
> > > > GO
> > > >
> > > > I get this error when trying to save it:
> > > > "The select item identified by the ORDER BY number 1 contains a
variable
> > as
> > > > part of the expression identifying a column position. Variables are
only
> > > > allowed when ordering by an expression referencing a column name."
> > > >
> > > > any help would be very much appreciated. Thanks!
> > > >
> > > >
> > > >
> > > >
> >
> >
> >

Sunday, March 11, 2012

Year only data from daily dates

Year function in sql query

Year portion of date is incorrect

Yes I have no Money

Yes/No Field Problem

Yes/No field in Stored Procedure (Query)

YES/NO and asp.net

Yes/No in Access Database, and the Radio Button List

Yes/No field showing up as Yes/No in a datagrid.

YesRe: Unable to authenticate on sql server 2005

Yet another "Concurrency violation: the UpdateCommand affected 0 records" exception

yet another datalist findcontrol question

Yet another Datagrid sorting question

Yet another DropDownList issue

Yet another designer issue

Yet another issue with DataColumn inhertiance

Yet another IsDBNull post. Stumped!

yet another nested repeater question

Yet another nested repeater newbie

Yet another ora-12154 error.

Yikes - Repeater Row Color change needed

Yikes! buttons in datagrid lose event handler after back button hit

You can have strongly-typed DataTables, or you can have nullable columns. Choose one.

you can see my program source please help for me about update in database

You can have strongly-typed DataTables, or you can have nullable columns. Choose one.

you can't do this? @SortBy

ZombieCheck

Blog Archive

About Me