ORDER BY conflicting with WHERE? 1

[CoolMama]

I'm creating an UPDATE database page. The database is MS Access. The page works fine. The user selects which entry to update by selecting the title of the entry from a drop down menu. I want the titles to be in alphabetical order. But when I use a ORDER BY, it doesn't put them in alphabetical order. Is there a conflict with the WHERE statement? I've tried setting up the SELECT with both "*" and complete field names, but neither makes a difference. How do I get the titles to be ordered?

...
ddlPolicies.DataValueField = "PolicyID";
ddlPolicies.DataTextField = "Title";
ddlPolicies.DataBind();
objRdr.Close();
dbConn.Close();
}}

void SelectPolicy(Object s, EventArgs e)  {
objCmd = new OleDbCommand("SELECT [PFFPolicies].[PolicyID], [PFFPolicies].[Manual], [PFFPolicies].[PageNumber], [PFFPolicies].[Title], [PFFPolicies].[Notes], [PFFPolicies].[Link], [PFFPolicies].[LastUpdated] FROM [PFFPolicies] WHERE [PFFPolicies].[PolicyID]=@PolicyID  ORDER BY [PFFPolicies].[Title]", dbConn);

objCmd.Parameters.Add("@PolicyID", ddlPolicies.SelectedItem.Value);

dbConn.Open();
objRdr = objCmd.ExecuteReader();
...

Dawn

http://www.dawningdesigns.net

 

[jbenson001]

The syntax looks correct. What I would do to check is to run the query in Access and see if you get the desired results.

Jim

 

[CoolMama]

The query doesn't seem to be a problem in Access. I wonder if it's a server thing.

I also have a couple more tweeks to do on this project...

1) When the LastUpdated field displays the date to be updated, it is in 5/3/06 12:00:00AM format. I want it to show without the time. So I amended the textbox with the following code, but it still shows the time. I wonder if it's related to the sorting thing.

<asp:TextBox id="txtLastUpdated" runat="server" DataFormatString="{0:d}"></asp:TextBox>

2) When I add a policy or select one to update, if a data field is empty, then it throws an error. I can't remember or find the code that handles NULL data fields.

Dawn

http://www.dawningdesigns.net

 

[jbenson001]

It sounds like it could be a framework issue, possibley being corrupted. Uninstall and reinstall the framework and see if that helps...

Jim

 

[adamroof]

As for the db stuff, im not so sure about access and asp....

but...

1. DataFormatString is not a member of an asp:Textbox unless its being databound. if your code is populating the box, you should be able to do this...

lblCreate.Text = String.Format("{0:d}", ds.Tables(0).Rows(0).Item("DateTimeCreated"))

if your html is populating, you might be able to do this...
<asp:TextBox id=textbox runat=server text='<%# Databinder.Eval(Container.DataItem,"DateTimeCreated","{0:d}") %>' />

2. heres a couple of vb functions you can convert to c#

ex..
objCmd.Parameters.Add("@PolicyID", checkDDLInsert(ddlPolicies));

Function CheckTbInsert(ByVal tb As TextBox) As Object
If Not Trim(tb.Text) = "" Then
CheckTbInsert = tb.Text
Else
CheckTbInsert = System.DBNull.Value
End If
Return CheckTbInsert
End Function

Function checkDDLInsert(ByVal ddl As DropDownList) As Object
If ddl.SelectedIndex > 0 Then
checkDDLInsert = ddl.SelectedItem.Value
Else
checkDDLInsert = System.DBNull.Value
End If
Return checkDDLInsert
End Function



use this link to convert my code to yours!

http://www.developerfusion.co.uk/utilities/convertvbtocsharp.aspx

 

[CoolMama]

The code is populating the textbox. I tried doing the date format thing 10 different ways. It doesn't seem to like anything.

string LstDate=objRdr["LastUpdated"].ToString();
txtLastUpdated.Text = LstDate;
txtLastUpdated.Text = string.Format("{0:d}")

Dawn

http://www.dawningdesigns.net

 

[jbenson001]

How about:
string LstDate = objRdr("LastUpdated").ToString.Format("{0:d}");

 

[adamroof]

not so sure on the porting here, but checkDate as a function too??

txtLastUpdated.Text = checkDate(objRdr["LastUpdated"]);

string checkDate(object txt)
{
if (txt is System.DBNull.Value){
checkDate = "";
}
else {
checkDate = string.Format("{0:d}",txt);
}
}

the whole .ToString u are using in case its null im guessing, but the function can do it all for you. (kinda reversing the insert functions)

 

[CoolMama]

I figured another method was needed. just hoped it wasn't true.

As for the date format, it didn't like/recognize Trim(). So I tried it this way

if(tb.Text.Length() == 0)
{CheckTbInsert = tb.Text;}

and that creates an error as well.

I'm about ready to call it a day...or maybe a week.


Dawn

http://www.dawningdesigns.net

 

[ca8msm]

If you are just trying to format the date, try using the ToString method e.g

        Dim dte As Date = System.DateTime.Now
        TextBox1.Text = dte.ToString("dd/mm/yyyy")

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[CoolMama]

Thanks... however, "LastUpdated" is the date the policy was updated, not the date it was updated in the database. So I don't think this will work exactly.

Dawn

http://www.dawningdesigns.net

 

[ca8msm]

I don't understand. I simply showed you how to format a date - you will have to apply this logic to your scenario.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[adamroof]

maybe your imports in your code file...

if it cant find trim, then your missing Microsoft.VisualBasic (dont know if that applies to c#)

heres mine...

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HTMLControls
Imports Microsoft.VisualBasic


i guess yours would be

using system
...
i.e.

that could be the String.Format issue too, i guess i need to port to C#, seems more are using that language

if your comfortable posting more code, that might help us get this done today! dont give up yet!

 

[CoolMama]

I'm using Web Matrix at work since it's free, but I'm tempted to install the education version of Visual Studio just to get the Debugging and HELP features. And Web Matrix, unlike Visual Studio, doesn't seem to require that I import anything accept System.Data.

Anyway, below is my code. I've temporarily given up on the date and am focusing on the NULL field issue. I tried what should have been a simple solution...instantiate the textboxes with NULL so that they don't have to be checked. But it gives me an error that it is expecting a ";". Why! And in the CheckTbInsert method (should I choose to use it), it's expecting parentheses. Why! I still need to talk to IS about reinstalling .NET Framework 1.5.

<%@ Page Language="C#" %>
<%@ Register TagPrefix="uc0" TagName="top" Src="top.ascx" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Configuration" %>
<%@ import Namespace="System.Web" %>
<%@ import Namespace="System.Web.UI" %>
<%@ import Namespace="System.Web.UI.WebControls" %>
<%@ import Namespace="System.Web.UI.HTMLControls" %>
<script runat="server">

    OleDbConnection dbConn = new OleDbConnection(ConfigurationSettings.AppSettings["policiesDNS"]);
    OleDbCommand objCmd;
    OleDbDataReader objRdr;
    
    TextBox txtManualNo.Text = System.DBNull.Value;
    TextBox txtPageNo.Text = System.DBNull.Value;
    TextBox txtTitle.Text = System.DBNull.Value;
    TextBox txtURL.Text = System.DBNull.Value;
    TextBox txtLastUpdated.Text = System.DBNull.Value;
    TextBox txtNotes.Text = System.DBNull.Value;
    
    void Page_Load() {
    if (!IsPostBack){
    BindData();
    }//postback
    }//page load
    
    void SelectPolicy(Object s, EventArgs e)  {
    objCmd = new OleDbCommand("SELECT [PFFPolicies].[PolicyID], [PFFPolicies].[Manual], [PFFPolicies].[PageNumber], [PFFPolicies].[Title], [PFFPolicies].[Notes], [PFFPolicies].[Link], [PFFPolicies].[LastUpdated] FROM [PFFPolicies] WHERE [PFFPolicies].[PolicyID]=@PolicyID  ORDER BY [PFFPolicies].[Title]", dbConn);
    
    objCmd.Parameters.Add("@PolicyID", ddlPolicies.SelectedItem.Value);
    
    dbConn.Open();
    objRdr = objCmd.ExecuteReader();
    while (objRdr.Read()){
    txtManualNo.Text = (string) objRdr["Manual"];
    string pgNo=objRdr["PageNumber"].ToString();
    txtPageNo.Text = pgNo;
    txtTitle.Text = (string) objRdr["Title"];
    txtURL.Text = (string) objRdr["Link"];
    string LstDate=objRdr["LastUpdated"].ToString();
    txtLastUpdated.Text = LstDate;
    txtNotes.Text = (string) objRdr["Notes"];
    }
    objRdr.Close();
    dbConn.Close();
    btnUpdate.Enabled = true;
    btnDelete.Enabled = true;
    }//select policy
    
    void UpdatePolicy(Object s, EventArgs e){
    objCmd = new OleDbCommand("UPDATE PFFPolicies SET Manual=@Manual, PageNumber=@PageNumber, Title=@Title, Notes=@Notes, Link=@Link, LastUpdated=@LastUpdated WHERE PolicyID=@PolicyID", dbConn);
    
    objCmd.Parameters.Add("@Manual", txtManualNo.Text);
    objCmd.Parameters.Add("@PageNumber", txtPageNo.Text);
    objCmd.Parameters.Add("@Title", txtTitle.Text);
    objCmd.Parameters.Add("@Notes", txtNotes.Text);
    objCmd.Parameters.Add("@Link", txtURL.Text);
    objCmd.Parameters.Add("@LastUpdated", txtLastUpdated.Text);
    objCmd.Parameters.Add("@PolicyID", ddlPolicies.SelectedItem.Value);
    
    dbConn.Open();
    objCmd.ExecuteNonQuery();
    dbConn.Close();
    BindData();
    }//update policy
    
    void DeletePolicy(Object s, EventArgs e){
    objCmd = new OleDbCommand("DELETE FROM PFFPolicies WHERE PolicyID=@PolicyID", dbConn);
    
    objCmd.Parameters.Add("@PolicyID", ddlPolicies.SelectedItem.Value);
    
    dbConn.Open();
    objCmd.ExecuteNonQuery();
    dbConn.Close();
    BindData();
    }//delete policy
    
    void BindData() {
    objCmd = new OleDbCommand("SELECT [PFFPolicies].[PolicyID], [PFFPolicies].[Manual], [PFFPolicies].[PageNumber], [PFFPolicies].[Title], [PFFPolicies].[Notes], [PFFPolicies].[Link], [PFFPolicies].[LastUpdated] FROM [PFFPolicies]", dbConn);
    dbConn.Open();
    objRdr = objCmd.ExecuteReader();
    ddlPolicies.DataSource = objRdr;
    ddlPolicies.DataValueField = "PolicyID";
    ddlPolicies.DataTextField = "Title";
    ddlPolicies.DataBind();
    objRdr.Close();
    dbConn.Close();
    }//bind data
    
    object CheckTbInsert(TextBox tb)
    {
      String outText = tb.Text;
      if((outText == null) || (outText.Length == 0))
        CheckTbInsert = System.DBNull.Value;
      else
        CheckTbInsert = outText;
      return CheckTbInsert;
    } // check Textbox insert
    
    object CheckDDLInsert(DropDownList ddl){
    if(ddl.SelectedIndex > 0)
    {CheckDDLInsert = ddl.SelectedItem.Value;}
    else{CheckDLLInsert = System.DBNull.Value;}
    return CheckDLLInsert;
    }//check drop down insert

Dawn

http://www.dawningdesigns.net

 

[adamroof]

1. Get Visual Web Developer 2005 - its out of BETA mode, and its FREE! - you cant get web matrix anymore (became vs web dev 2005), and gives you a "VisualStudio" feel and functionality

2. Add <%@ import Namespace="Microsoft.VisualBasic" %> to see if you can trim and string.format

3. I dont think you can
TextBox txtManualNo.Text = System.DBNull.Value;
outside of a void, try it under page_load. acutuall you dont need to put TextBox in front, as its not a variable...so try

txtManualNo.Text = "";

instead

4. your missing some if else {'s (bolded below)

    object CheckTbInsert(TextBox tb)
    {
      String outText = tb.Text;
      if((outText == null) || (outText.Length == 0))
        CheckTbInsert = System.DBNull.Value;
      [b]} else {[/b]
        CheckTbInsert = outText;
      return CheckTbInsert;
     [b]} // extra thingy here too[/b]
    } // check Textbox insert

this could be my fault for the vb example...sorry!


5. we're getting there!

 

[adamroof]

if this is like javascript one more { after your last end parenthesis in the if statement too...
if (bla || blabla) { this } else { that }

 

[adamroof]

and to use the functions in your void UpdatePolicy statement...

...
objCmd.Parameters.Add("@LastUpdated", CheckTBInsert(txtLastUpdated));
objCmd.Parameters.Add("@PolicyID", CheckDDLInsert(ddlPolicies));
...
so you would just reconsume the function for each textbox and ddl by passing in the different textbox names.

sorry if im confusing you, or going to far here.

 

[CoolMama]

OK, I added the

<%@ import Namespace="Microsoft.VisualBasic" %>

and it still didn't recognize "Trim".

Then I fixed up the CheckTbInsert with all the proper syntax and it says:

Compiler Error Message: CS0654: Method 'ASP.PFFpolicies_update_aspx.CheckTbInsert(System.Web.UI.WebControls.TextBox)' referenced without parentheses

Source Error: 

Line 98:           String outText = tb.Text;
Line 99:           if((outText == null) || (outText.Length == 0))
[red]Line 100:            {CheckTbInsert = System.DBNull.Value;}[/red]
Line 101:            else {CheckTbInsert = outText;
Line 102:          return CheckTbInsert;}

Why does it keep asking for parentheses on this line?!

As for installing Visual Web Developer, I think that involves a lot of uninstalling and installing of Framework, SDK, etc...which, of course, will create more problems. So if I can get the code to work on this system, then I'll tackle the new system.



Dawn

http://www.dawningdesigns.net

 

[adamroof]

close..

if((outText == null) || (outText.Length == 0))
{CheckTbInsert = System.DBNull.Value;}
else
{CheckTbInsert = outText;} //forgot one } here
return CheckTbInsert;}

 

[CoolMama]

That's fixed. But things are getting wierder and wierder...we've approached the HIDDEN CODE errors! which I can't get to in Web Matrix. I don't think I was meant to succeed in what should be a simple project. Fortunately my day has now ended.

Compilation Error 
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: CS1518: Expected class, delegate, enum, interface, or struct

Source Error:

Line 264:        #line hidden
Line 265:        
[red]Line 266:        public PFFpolicies_update_aspx() {[/red]
Line 267:            System.Collections.ArrayList dependencies;
Line 268:            if ((ASP.PFFpolicies_update_aspx.__initialized == false)) {

Dawn

http://www.dawningdesigns.net