Data Access and ObjectDataSource Control

Conversion from type 'DBNull' to type 'Integer' is not valid

keyvan1 — Sat, 19 Dec 2009 12:57:22 GMT

Hi,

i have some searh fields that display the result in a gridview

and the gridview which binds to an objectdatasource programmatically

there are some fields that the user may not enter and leave them empty like city

i should fill out all the fields otherwise i get conversion error

when i bind it declaratively and set the ConvertEmptyStringToNull="False" for each parameter i get the result

but when i do it programmaticlly in the code i get conversion from type error for the userID integer field

i have tried to set the ConvertEmptyStringToNull="False" in the code and also set a default ='' value for the sp

but it does not work

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim ds As New ObjectDataSource
        ds.ID = "sqlDataSource1"

        With ds

            .SelectMethod = "getSearchResult"
            .TypeName = "myproject.user"
            ' .SelectParameters

            .SelectParameters.Add(("Fname"), TypeCode.String, TxtFirstname.Text.Trim)
            .SelectParameters("Fname").ConvertEmptyStringToNull = "False"
            .SelectParameters.Add(("Lname"), TypeCode.String, common.RemoveHTML(TxtLastname.Text.Trim))
            .SelectParameters.Add(("Gender"), TypeCode.String, ddlGender.SelectedValue)
            .SelectParameters.Add(("Country"), TypeCode.String, ddlCountry.SelectedValue)
            .SelectParameters.Add(("City"), TypeCode.String, common.RemoveHTML(TxtCity.Text.Trim))

            If hasPicture.Checked = True Then
                .SelectParameters.Add(("userPic"), TypeCode.Boolean, "True")
            Else
                .SelectParameters.Add(("userPic"), TypeCode.Boolean, "False")
            End If

            If isOnline.Checked = True Then
                .SelectParameters.Add(("isOnline"), TypeCode.Boolean, "True")
            Else
                .SelectParameters.Add(("isOnline"), TypeCode.Boolean, "False")
            End If
        End With

        Dim strKeys() As String = {"userID"}
        userGridview.DataKeyNames = strKeys

        userGridview.DataSource = ds
        userGridview.DataBind()
    End Sub
End Class

and here is the sp:

create PROCEDURE [dbo].[CmmSPNet_searchUser]
@Fname nVarchar(50) ='',
@Lname nVarchar(50) ='',
@Country nVarchar(50) ='',
@City nVarchar(50) ='',
@Gender Varchar(10) ='',
@userPic BIT,
@isOnline BIT
AS
SELECT ................................

Many to Many relationship - Update in LINQ-to-SQL

Angad Bhat — Fri, 18 Dec 2009 11:26:31 GMT

I have a juction table with ony two fields that come from other two tables. I want to update one field's value.

E.g.
Table 1 : customer
Table 2 : order
Junction table : custorder (customerid, orderid)

I want to update a record in custorder
From

customerid	orderid
26	24

customerid	orderid
26	23

When I try this is linq i get the following error message
ex = {"Value of member 'PersonRoleTypeID' of an object of type 'PersonRole' changed.\r\nA member defining the identity of the object cannot be changed.\r\nConsider adding a new object with new identity and deleting the existing one instead."}

I tried the solution of deleting the record and then inserting a new one with new value and it works but is there any way so that I can straight away update the record instead of deleting and inserting it

Thanks for you help

unable to use LINQ in a .netTiers generated project.

prakashav — Thu, 17 Dec 2009 20:49:32 GMT

hi ,

I have a website project which was generated by .netTiers im not able to use linq.

When i add a using System.Data.Linq to a code behind file the project fails to build and i get this error.

Error 1 The type or namespace name 'Linq' does not exist in the namespace 'System' (are you missing an assembly reference?) E:\NetTiers\CityGrub\CityGrub.Website\test.aspx.cs 3 14 E:\...\CityGrub.Website\

Error 1 The type or namespace name 'Linq' does not exist in the namespace 'System' (are you missing an assembly reference?)

I tried adding the reference in the web.config but i still get that error.

Should i add the reference at any other place in the project.

Please help

Prakash

Linq DeleteOnSubmit followed by InsertOnSubmit fails with duplicate key

steddyman — Thu, 17 Dec 2009 13:14:36 GMT

Apologies if this is in the wrong forum. This is a DD application, and I can not find a LINQ specific forum in the list so posted here.

I have an application where I have a table called Guests that has a unique column called Name. The Guest has a number of other objects linked to it, such as DiskFiles, CPUResource, etc so is quite complex. I want to be able to realllocate the resources a guest object represents by deleting the Guest object and create a new Entry for it with the changes. I am trying to do that with code similar to the following ( this is simplified ):

// Now create the Guest object
Guest guest = new Guest();
guest.Name = r.ServerName;
// Get any existing guest object
Guest oldguest = db.Guests.SingleOrDefault(p => p.RequestID == r.id);

db.Guests.DeleteOnSubmit(oldguest);
//db.SubmitChanges();
db.Guests.InsertOnSubmit(guest);
db.SubmitChanges();

The problem I am having is that the call to InsertOnSubmit fails with a Duplicate Key error on the Name field for the Guests table, because it seems the DeleteOnSubmit has not done its job.

It is crucial that the DeleteOnSubmit command only occurs if the InsertOnSubmit is successful, hence only one call to SubmitChanges. If I delete the object and the Insert failed then all the resources already allocated to this Guest would become unreserved and possible used by other Guest objects.

There are Try / Catch blocks around this code, the above is a simple example of the code causing the issue. I also tried putting a SubmitChanges immediately after the DeleteOnSubmit then wrapping the entire block in a TransactionScope. This fails on the call to InsertOnSubmit stating that the scope is already closed!!

How can I delete an old entry and add a new one all in one transaction?

Thanks

Linq question: query with 2 different datacontents

tompy_nation — Thu, 17 Dec 2009 11:27:03 GMT

System.InvalidOperationException: The query contains references to items defined on a different data context

private static DataContext dcInfogebruiker = new DataContext(ConfigurationAdapter.InfogebruikerConnectionString);
private static DataContext dcCevips = new DataContext(ConfigurationAdapter.CevipsConnectionString);

public static List<Gebruiker> GetDienstHoofd(string dienstcode)
      {
        var query = from o in dcCevips.GetTable<DienstVerantwoordelijke>()
                    join pers in dcCevips.GetTable<Persoon>() on o.Persoonsleutel equals pers.Persoonsleutel
                    join huisst in dcInfogebruiker.GetTable<Huisstijl>() on pers.Rijksregisternr equals huisst.Rijksregisternr
                    where o.Dienstcode == dienstcode
                    select new Gebruiker { Firstname = pers.Voornaam, Lastname = pers.Naam, Email = huisst.Email, Login = huisst.Login };

        return query.ToList();

      }

Each datacontext refers to a db that is on the same SQL Server.

    <add name="Infogebruiker" connectionString="Data Source=ocmwsqlcl04in04\in04,10001;Initial Catalog=Infogebruiker;User Id=xxx;Password=xxx" providerName="System.Data.SqlClient"/>
    <add name="Cevips" connectionString="Data Source=ocmwsqlcl04in04\in04,10001;Initial Catalog=Cevips;User Id=xxx;Password=xxx" providerName="System.Data.SqlClient"/>

So why is this join impossible?

How to insert multiple value

Furq — Thu, 17 Dec 2009 11:12:07 GMT

how to insert multiple value through checkbox in a single column through asp form :S

can any one recommend me some good tutorial... as i am beginner

LINQ - Getresult Exception

aravindkumarp — Thu, 17 Dec 2009 00:01:40 GMT

Hi All,

I faced one odd exception, when i am trying to retrieve the datas from SP using Entity Set. But am getting this exception error:

Data = Property evaluation failed.

There are two result set in Sequence from stored procs. But I can able to retieve the first set only, when i use the Geresult() function
to retieve the secondits throwing this error. Not sure why it was? Give me some idea to free from this issue, and correct me if am wrong.

Thanks.

3 tier.

BlurGirl — Mon, 21 Dec 2009 04:32:19 GMT

anyone, someone teach me how to retrieve datas from the database (i'm using sql server 2005) into the dropdown list? using 3 tier architecture. i have DAL and BLL forms.

this is the rough UI i had for user to update info.

<%@ Page Language="VB" MasterPageFile="~/ProjMaster.Master" AutoEventWireup="false" CodeFile="UpdateSchoolAppt.aspx.vb" Inherits="SchoolAppointment_UpdateSchoolAppt" title="Untitled Page" %>

<%@ Register assembly="EclipseWebSolutions.DatePicker" namespace="EclipseWebSolutions.DatePicker" tagprefix="cc1" %>

<%-- Add content controls here --%><asp:Content ID="Content1" runat="server" 
    contentplaceholderid="ContentPlaceHolder1">

                <p>
                    Welcome,
                <asp:Label ID="lb_user" runat="server"></asp:Label>
        
<asp:LoginStatus ID="LoginStatus1" runat="server" 
    LogoutAction="RedirectToLoginPage" LogoutPageUrl="~/Login.aspx" />
                </p>
    <p>
                <asp:Label ID="Label1" runat="server" Font-Names="Aharoni" Font-Size="X-Large" 
                    Text="Update School Talk Appointment"></asp:Label>
                </p>
                <table class="style20">
                    <tr>
                        <td class="style21">
                <asp:Label ID="Label14" runat="server" Font-Names="Arial" Font-Size="Small" 
                    Text="Appt No.:"></asp:Label>
                        </td>
                        <td>
                            <asp:DropDownList ID="apptNoDDL" runat="server" Width="80px">
                            </asp:DropDownList>
                        </td>
                    </tr>
                    <tr>
                        <td class="style21">
                <asp:Label ID="Label15" runat="server" Font-Names="Arial" Font-Size="Small" 
                    Text="First Name:"></asp:Label>
                        </td>
                        <td>
                            <asp:TextBox ID="tb_firstName" runat="server" ReadOnly="True"></asp:TextBox>
                        </td>
                    </tr>
                </table>
                <table class="style20">
                    <tr>
                        <td class="style21">
                <asp:Label ID="Label16" runat="server" Font-Names="Arial" Font-Size="Small" 
                    Text="Last Name:"></asp:Label>
                        </td>
                        <td>
                            <asp:TextBox ID="tb_lastName" runat="server" ReadOnly="True"></asp:TextBox>
                        </td>
                    </tr>
                </table>
    <table class="style20">
        <tr>
            <td class="style21">
                <asp:Label ID="Label9" runat="server" Font-Names="Arial" Font-Size="Small" 
                    Text="School Name:"></asp:Label>
                        </td>
            <td>
                <asp:DropDownList ID="schNameDDL" runat="server" Height="22px" Width="195px">
                    <asp:ListItem>Evergreen Sec School</asp:ListItem>
                    <asp:ListItem>Fuchun Sec Sch</asp:ListItem>
                    <asp:ListItem>Riverside Sec School</asp:ListItem>
                    <asp:ListItem>Woodlands Sec School</asp:ListItem>
                    <asp:ListItem>Sembawang Sec School</asp:ListItem>
                </asp:DropDownList>
                        </td>
        </tr>
        <tr>
            <td class="style21">
                <asp:Label ID="Label10" runat="server" Font-Names="Arial" Font-Size="Small" 
                    Text="School Talk:"></asp:Label>
                        </td>
            <td>
                <asp:DropDownList ID="schTalkDDL" runat="server" Height="21px" Width="195px">
                    <asp:ListItem>SIT Talk</asp:ListItem>
                    <asp:ListItem>ScholarShip Talk</asp:ListItem>
                </asp:DropDownList>
                        </td>
        </tr>
        <tr>
            <td class="style21">
                <asp:Label ID="Label11" runat="server" Font-Names="Arial" Font-Size="Small" 
                    Text="Date Of School Talk:"></asp:Label>
                        </td>
            <td>
                            <cc1:DatePicker ID="DatePicker1" runat="server" />
                        </td>
        </tr>
        <tr>
            <td class="style21">
                <asp:Label ID="Label12" runat="server" Font-Names="Arial" Font-Size="Small" 
                    Text="Time of School Talk: "></asp:Label>
                        </td>
            <td>
                            <asp:DropDownList ID="timeDDL" runat="server">
                    <asp:ListItem>9 am - 10 am</asp:ListItem>
                    <asp:ListItem>10 am - 11 am</asp:ListItem>
                    <asp:ListItem>11 am - 12 pm</asp:ListItem>
                    <asp:ListItem>12 pm - 1 pm</asp:ListItem>
                    <asp:ListItem>1 pm - 2 pm</asp:ListItem>
                    <asp:ListItem>2 pm - 3 pm</asp:ListItem>
                    <asp:ListItem>3 pm - 4 pm</asp:ListItem>
                    <asp:ListItem>4 pm - 5 pm</asp:ListItem>
                    <asp:ListItem>5 pm - 6 pm</asp:ListItem>
                    <asp:ListItem></asp:ListItem>
                </asp:DropDownList>
                        </td>
        </tr>
        <tr>
            <td class="style21">
                <asp:Label ID="Label13" runat="server" Font-Names="Arial" Font-Size="Small" 
                    Text="Status:"></asp:Label>
                        </td>
            <td>
                <asp:TextBox ID="status_tb" runat="server" ReadOnly="True">Pending</asp:TextBox>
                        </td>
        </tr>
        <tr>
            <td class="style21">
                 </td>
            <td>
                 </td>
        </tr>
        <tr>
            <td class="style21">
                 </td>
            <td>
                                                
                <asp:Button ID="Button3" runat="server" Text="Update" style="height: 26px" />
                         </td>
        </tr>
    </table>
    <p>
    </p>
    <p>
                <asp:TextBox ID="date_tb" runat="server" Width="155px" Visible="False"></asp:TextBox>
                </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>
    <p>
    </p>

</asp:Content>
<asp:Content ID="Content2" runat="server" contentplaceholderid="head">

    <style type="text/css">
        .style20
        {
            width: 100%;
        }
        .style21
        {
            width: 143px;
        }
    </style>

</asp:Content>

The AppNo is in the dropdown list. when user selects the particular ApptNo accordingly, the textboxes below would change accordingly. the schooltalk and schoolname would be in dropdown list mode because user can just select the schoolstalk/name easily when it's in dropdown. so when using dropdown, be it for AppNo or schooltalk/schoolname, everything has to be retrieved from the database (that is the retrieval of data has to be dynamic and not static > just typing the names into the dropdown manually)

this is the UserDAL.vb

the update code:

Public Function UpdateSchTalkAppt(ByVal ApptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String, ByVal Status As String) As Integer
        Dim connectionString As String = My.Settings.dbConnection
        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
        Dim queryString As String = "UPDATE SchoolTalkAppt SET FirstName='" & FirstName & "',LastName='" & LastName & "',SchoolName='" & SchoolName & "',SchoolTalk='" & SchoolTalk & "',Dates='" & Dates & "',Time='" & Time & "',Status='" & Status & "'where ApptNo='" & ApptNo & "'"
        Dim dbCommand As SqlCommand = New SqlCommand(queryString, dbConnection)
        Dim rowsAffected As Integer = 0
        dbConnection.Open()
        Try
            rowsAffected = dbCommand.ExecuteNonQuery
        Finally
            dbConnection.Close()
        End Try
        Return rowsAffected
    End Function

Public Function UpdateSchTalkAppt(ByVal ApptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String, ByVal Status As String) As Integer
        Dim connectionString As String = My.Settings.dbConnection
        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

        Dim queryString As String = "UPDATE SchoolTalkAppt SET FirstName='" & FirstName & "',LastName='" & LastName & "',SchoolName='" & SchoolName & "',SchoolTalk='" & SchoolTalk & "',Dates='" & Dates & "',Time='" & Time & "',Status='" & Status & "'where ApptNo='" & ApptNo & "'"the retrieve code: 
        Dim dbCommand As SqlCommand = New SqlCommand(queryString, dbConnection)
        Dim rowsAffected As Integer = 0
        dbConnection.Open()
Public Function RetrieveApptss(ByVal ApptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String, ByVal Status As String) As System.Data.DataSet

        Dim connectionString As String = My.Settings.dbConnection

        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

        Dim queryString As String = "SELECT ApptNo, FirstName, LastName, SchoolName, SchoolTalk, Dates, Time, Status FROM SchoolTalkAppt"

        Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand

        dbCommand.CommandText = queryString

        dbCommand.Connection = dbConnection

        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter

        dataAdapter.SelectCommand = dbCommand

        Dim dataSet As System.Data.DataSet = New System.Data.DataSet
        dataAdapter.Fill(dataSet)
        Return dataSet
    End Function

 
        Try
            rowsAffected = dbCommand.ExecuteNonQuery
        Finally
            dbConnection.Close()
        End Try
        Return rowsAffected
    End Function

this is the UserBLL.vb

for the retrieve and update code:

Public Function getUpdateSchTalkAppt(ByVal ApptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String, ByVal Status As String) As Integer

        Dim ds As Integer = UserDA.UpdateSchTalkAppt(ApptNo, FirstName, LastName, SchoolName, SchoolTalk, Dates, Time, Status)

        Return ds

    End Function

    'get retrieve presenters.

    Public Function getRetrieveApptss(ByVal ApptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String, ByVal Status As String) As System.Data.DataSet

        Dim ds As Data.DataSet = UserDA.RetrieveApptss(ApptNo, FirstName, LastName, SchoolName, SchoolTalk, Dates, Time, Status)

        Return ds

    End Function

someone teach me pls? :(

i have both retrieve and update codes because first i would need to retrieve and after that have to update...im getting confused..my codes aint working! it doesnt even able to retrieve...

Strongly typed Dataset - need help

lusty_learner — Tue, 15 Dec 2009 15:45:37 GMT

Hi,

I right clicked on my project and added a dataset called 'PlntAccuracySWDst.xsd' and dragged the table that I want to work with from the server Exploerer and the table is 'PlantInventoryAccuracy'.

I am trying to access this dataset in my code behind

PlntAccuracySWDst mySWdst = new PlntAccuracySWDst();

For some reason I can't see 'PlantInventoryAccuracyTableAdapter' and 'PlantInventoryAccuracy' datatable objects when I do the 'mySWdst.' in my codebehind file. Am I missing some thing here? I have Select, Insert, and Update stored procs define in the database and I want to be able use those here. But as a first step I am unable to access the DataTable and Table adapters.

I am following this link http://www.4guysfromrolla.com/articles/020806-1.aspx but no luck. Can some one help please me with this?

Thanks,

How to Get Login Form UserID into SQL For an Audit Table ?

mondo21 — Mon, 24 Jul 2006 15:58:08 GMT

Hiya all.

I have used Nigels great Generic audit trail trigger to audit a table in my SQL DB.

http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html

However the Username in the audit table is Pulled from "system_user" in SQL.

I have created a Login.aspx page using forms Authentication that references another table in SQL called users..When user is verified then they are passed onto the InsertPage.

However the Username used in the connection string is what is Picked up by the trigger.

My Question is , How do I transfer the userID from the Login.aspx to System_user in SQL.

Is there a way to setup some sort of Dynamic Connection string in Web.config that Sends the user ID to SQL.

Nigel sugested using Integrated Security to do this but this is not an Option as My users are not part of the Domain..

Any help please..

Ray..

Put data from .cs file to .aspx file

nvleeuwen88 — Sat, 19 Dec 2009 20:08:20 GMT

Hi asp.net builders,

I'm new to asp.net and I am trying to learn it so I was making a web application which needed to get some data from the database and show it in the website.

So I thought let's make a database class to make it in the whole application a little bit easier to get data from the database. So I made a function called getRow which is getting one row from the database by the query.

 public DataSet getRow( String query, String table )
        {
            try
            {
                this.open();

                SqlDataAdapter da = new SqlDataAdapter(query, this.conn);
                //create dataset to insert data in it
                DataSet ds = new DataSet();
                da.Fill(ds, table);
                
                this.close();
            }
            catch(SqlException ex)
            {
                throw ex;
            }

            return ds;

        }

But I get every time a error under the word Dataset so I was looking on the internet but could not find it. I think it's because I'm not have all the using

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

The type or namespace name 'DataSet' could not be found (are you missing a using directive or an assembly reference?) Almeerse\Almeerse\classes\database.cs

So maby someone can help me to fix this problem. And can someone tell me how I can show the data which I get from the database. Because in the .aspx file I need to show the data.

Thnx's for all the tips/advices.

Nick

Excute Scallar Questions plz help

abu_qusy — Sun, 20 Dec 2009 11:41:30 GMT

hi all , Can any body help me , im new in use asp.net , i want to understand the following

1- cmd.CommandText = "select passward from Admin where username='"+this.txtUserName .Text +"'";

cmd.Connection =con ;

string pwd=Convert.ToString (cmd.ExecuteScalar ());

What ExcuteScaller work here , and why use Convert to String ?

When We use ExcuteScaller ?

2- int reviewid = Convert.ToInt32(Request.QueryString["ReviewID"]

Before We convert to Int32 what was the type and why we convert to ?

Request.QueryString :: Recived ReviewID as an int or string

Parameterized Select

alex2go — Sun, 20 Dec 2009 11:50:58 GMT

I've been using parameters like this for sometime, and they work fine unless they are in a select statement:

SqlParameter idval = new SqlParameter("@idval", SqlDbType.VarChar);

idval.Value = Request.QueryString["id"];

System.Data.SqlClient.SqlDataAdapter ada = new System.Data.SqlClient.SqlDataAdapter("SELECT [itemid], [title], [description], [price], [weight], [pages], [discount], [featured], [amtsold] FROM [items] WHERE [itemid] = @idval",

This gets me the error: "must declare scalar variable "@idval"

Can one pass back a SqlTransaction object?

rmdw — Sat, 19 Dec 2009 07:41:24 GMT

I've long had a static method in one of my libraries that I called to open a SqlConnection to SQL Server. It works fine.

Now I would like to introduce Transactions. I made a copy of the method and then modified it like this:

public static SqlConnection GetConnectionWithTransaction(string timeout, ref SqlTransaction transaction)

Then within the method a BeginTransaction() call is made. This seems to work fine.

What I'm having problems with is how to set things up in the calling method so I can obtain the passed back transaction. Here's my first attempt:

SqlTransaction transact;
SqlConnection conn = ConnectionManager.GetConnectionWithTransaction(ref transact);

But Visual Studio is balking at the referenced "transact". So in the first line I tried changing things to this:

SqlTransaction transact = new SqlTransaction();

But it wouldn't accept that.

Is what I'm trying to do even possible or can I only instantiate a Transaction in the calling routine?

Robert

Retrieve field names from Object Data Source

dpreznik — Fri, 18 Dec 2009 19:06:05 GMT

Is there any way to retrieve a list of column names from ObjectDataSource?

Thanks,

Dmitriy

Where to put general queries when using a DAL

rtcary — Sat, 19 Dec 2009 16:15:49 GMT

I am new to 3-Tier design and implementation of ASP.net, so forgive my use of terms that may be incorrect. So far I have created a DAL and have implemented an ObjectDataSource for a Markup page. The DAL is part of a Business Logic Layer with everything working as expected.

Where I am lost is where/how to put queries that perform tasks not directly related to the presentation of data on the Markup page. For instance, I have a query that contains a sub query moving a record from one table into a temporary table that is called in the beginning of the application. All Markup pages get their data from the temporary table (the DAL is based on the temporary table). At the end of the application, the data is moved back to the permanent table; again a query that is not part of the display.

Along the way, there are other queries that get or put data into the tables(s) and are not part of the display (ObjectDataSource). Are there any examples of how this can be done using the 3-Tier structure or should these specialized queries be put into a separate class?

Thank you for any help/suggestions...

Todd

Linq and Gridview SelectedIndex

rds1 — Sat, 19 Dec 2009 17:22:46 GMT

I databind a shaped some shaped data to a gridview.

My intention if to use this gridview to have the user select then display the complete record to update.

It seems that there must be a more efficient approach then what I used to accomplish this task.

In particular, is there a more appropriate approach then filling the ids list with the for each loop?

Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        ShowMaintReqInGrid();
    }

    protected void ShowMaintReqInGrid()
    {
        MaintReqDataContext db = new MaintReqDataContext();
        var q = from mr in db.MaintRequests select new { mr.ID, mr.MaintMachine.Name, mr.ShortDescript, mr.Requestor, mr.ReqDate, mr.MaintPriority.Description };
        GridView1.DataSource = q;
        GridView1.DataBind();
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        MaintReqDataContext db = new MaintReqDataContext();
        List<short> ids = new List<short>();
        var q = from mr in db.MaintRequests select new { mr.ID };

        foreach (var id in q)
        {
            ids.Add(id.ID);
        }
        int TargetID = ids.ElementAt(GridView1.SelectedIndex);
        MaintRequest MR = db.MaintRequests.Single(mr=>mr.ID.Equals(TargetID));

}

Filtering a dataset

XD40_Man — Sat, 19 Dec 2009 16:05:56 GMT

Any idea how I can filter a dataset according to what the string starts with? When I use LIKE it only filters by what my textbox contains.

Here's what I have so far:

If Common.IsAlpha(txtLastName.Text) = True Then
      Me.rdoLastName.Checked = True
      Me.GetClientsBindingSource.Filter = String.Format("[Name Last] LIKE '%" + Me.txtLastName.Text + "%'")
    Else
      Me.rdoHIPAAID.Checked = True
      Me.GetClientsBindingSource.Filter = String.Format("[HIPPA_IDD] LIKE '%" + Me.txtLastName.Text + "%'")
    End If

    Try
      Me.GetClientsTableAdapter.Fill(Me.SDSDataSet.GetClients)

    Catch ex As Exception
      Me.lblError.Text = ex.Message()
    End Try

What is the best way to store a DataSet to be used throughout my page?

jaden404 — Fri, 18 Dec 2009 23:03:20 GMT

I have a dataset that is being filled on Page_Load. I want to use this DataSet (with about 80,000 rows) several places within a single .aspx page. How is the best way to go about doing this? Should I cache it, use a session, etc?

Get players ordered by rank in Linq (order by subselect)

AceCorban — Fri, 18 Dec 2009 23:41:31 GMT

Let's say I have the following tables:

Guild

Player

GuildPlayer (many to many association table with playerId and guildId foreign keys)

And a guild can have games that some players play in, and some do not. So I have these additional tables:

Game (has a foreign key to the guild)

PlayerGame (association table of players to a game by use of playerId and gameId foreign keys. Also has points that player got for that game)

So I want to implement a linq query that will list the players in order based on the sum of points in each PlayerGame. I've already gotten this to work in SQL:

SELECT guildPlayer.playerId, 
(SELECT SUM(points) FROM PlayerGame WHERE playerId=guildPlayer.userId) AS totalPoints
FROM GuildPlayer guildPlayer 
WHERE guildPlayer.guildId=@guildId 
ORDER BY totalPoints DESC

Just not really sure how to translate it to linq to do the same thing...

I'm trying this:

return from guildPlayer in db.GuildPlayers
where (guildPlayer.Guild.id == this.id)
orderby 
     (from playerGame in db.PlayerGames 
      where (playerGame .playerId== guildPlayer.playerId) 
      && (playerGame .Game.guildId== guildPlayer.guildId) 
      select guildPlayer.points).Sum() descending
select guildPlayer;

Doesn't seem to be working. (BTW, "this.id" is because this is the definition of a GetRankedPlayers() method that is part of the Guild class)

How do I access SQL table data programatically?

Maurice Osborn — Wed, 16 Dec 2009 20:18:20 GMT

I need to access table column data with C# in a row selected with a LinqDataSource control in a button-click event handler. I stored a row of data in a table within a database that I created for the Developer Edition of SQL Server by using the SqlDataSource.Insert control in a web page created with the Web Developer. Now, I want to access the column data individually in a button-click event handler of a web page with a LinqDataSource control. I would be happy to use plain LINQ in code, if I knew how. I created the control and the data classes with O/R Designer. Now, I need something in an event handler to query a specific row and access column data. How do I do that?

Maurice

Insert New Record With LINQ

WildButNice — Tue, 15 Dec 2009 18:13:39 GMT

Dear All

This is the first entry for me in this forums, in fact i decied to ask after i did everything i can to solve my problem .

My problem is i used LINQ to update one table in database, everything working fine except INSERT !!

when i press insert i got nothing & even there is no error message !!

Please Help

Kindly be informed that i m new in everything, i never touch Visual Studio Beofre.

Here is my code which i created by GUI

@ Page Title="" Language="VB" MasterPageFile="~/EmployeePage.master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">asp:Content>asp:Content ID="Content2" ContentPlaceHolderID="MainContentPlaceHolder" Runat="Server">

<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">

</asp:ToolkitScriptManager>

<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" DataKeyNames="Hotel_Code" DataSourceID="LinqDataSource2" HorizontalAlign="Center">

<asp:CommandField HeaderText="Commands" ShowEditButton="True" ShowInsertButton="True" />

<asp:BoundField DataField="Hotel_Name" HeaderText="Hotel_Name" SortExpression="Hotel_Name" />

<asp:BoundField DataField="Hotel_Code" HeaderText="Hotel_Code" InsertVisible="False" ReadOnly="True" SortExpression="Hotel_Code" />

<asp:BoundField DataField="AvailableRooms" HeaderText="AvailableRooms" SortExpression="AvailableRooms" />

</Columns>

</asp:GridView>

<asp:LinqDataSource ID="LinqDataSource2" runat="server" ContextTypeName="ZamzamDB" EnableInsert="True" EnableUpdate="True" TableName="Hotels">

</asp:LinqDataSource>

<br />

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="ID" DataSourceID="LinqDataSource1" GridLines="Vertical" ForeColor="Black">

<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" ShowSelectButton="True" />

<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" />

<asp:TemplateField HeaderText="G_Date" SortExpression="G_Date">

<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("G_Date", "{0:d}") %>'></asp:TextBox>

<asp:CalendarExtender ID="TextBox1_CalendarExtender" runat="server" Enabled="True" TargetControlID="TextBox1" FirstDayOfWeek="Saturday">

</asp:CalendarExtender>

</EditItemTemplate>

</InsertItemTemplate>

<asp:Label ID="Label1" runat="server" Text='<%# Bind("G_Date", "{0:d}") %>' ></asp:Label> </ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="H_Date" SortExpression="H_Date">

<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("H_Date", "{0:d}") %>'></asp:TextBox>

<asp:CalendarExtender ID="TextBox2_CalendarExtender" runat="server" Enabled="True" TargetControlID="TextBox2" FirstDayOfWeek="Saturday">

</asp:CalendarExtender>

</EditItemTemplate>

</InsertItemTemplate>

<asp:Label ID="Label2" runat="server" Text='<%# Bind("H_Date", "{0:d}") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField="H1Occ" HeaderText="H1Occ" SortExpression="H1Occ" />

<asp:BoundField DataField="H1Rev" HeaderText="H1Rev" SortExpression="H1Rev" />

<asp:BoundField DataField="H2Occ" HeaderText="H2Occ" SortExpression="H2Occ" />

<asp:BoundField DataField="H2Rev" HeaderText="H2Rev" SortExpression="H2Rev" />

<asp:BoundField DataField="H3Occ" HeaderText="H3Occ" SortExpression="H3Occ" />

<asp:BoundField DataField="H3Rev" HeaderText="H3Rev" SortExpression="H3Rev" />

<asp:BoundField DataField="H4Occ" HeaderText="H4Occ" SortExpression="H4Occ" />

<asp:BoundField DataField="H4Rev" HeaderText="H4Rev" SortExpression="H4Rev" />

<asp:BoundField DataField="H5Occ" HeaderText="H5Occ" SortExpression="H5Occ" />

<asp:BoundField DataField="H5Rev" HeaderText="H5Rev" SortExpression="H5Rev" />

<asp:BoundField DataField="H6Occ" HeaderText="H6Occ" SortExpression="H6Occ" />

<asp:BoundField DataField="H6Rev" HeaderText="H6Rev" SortExpression="H6Rev" />

<asp:BoundField DataField="H7Occ" HeaderText="H7Occ" SortExpression="H7Occ" />

<asp:BoundField DataField="H7Rev" HeaderText="H7Rev" SortExpression="H7Rev" />

<asp:BoundField DataField="H8Occ" HeaderText="H8Occ" SortExpression="H8Occ" />

<asp:BoundField DataField="H8Rev" HeaderText="H8Rev" SortExpression="H8Rev" />

<asp:BoundField DataField="H9Occ" HeaderText="H9Occ" SortExpression="H9Occ" />

<asp:BoundField DataField="H9Rev" HeaderText="H9Rev" SortExpression="H9Rev" />

<asp:BoundField DataField="H10Occ" HeaderText="H10Occ" SortExpression="H10Occ" />

<asp:BoundField DataField="H10Rev" HeaderText="H10Rev" SortExpression="H10Rev" />

</Columns>

savsVS

</EmptyDataTemplate>

</asp:GridView>

<asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="ZamzamDB" EnableDelete="True" EnableInsert="True" EnableUpdate="True" TableName="MarketingCompetition" >

</asp:LinqDataSource>asp:Content>

Retrieve Values from DataTable columns

RSquared — Fri, 18 Dec 2009 17:59:33 GMT

Please help me here. I want to obtain the individual data in columns from a data table.... I think I need some attributes of the data table, so as to obtain data from the rows and columns? Any suggestions or advice would be greatly appreciated.

DataTable.RemoveAt); not work with me !

greaso — Wed, 16 Dec 2009 12:25:18 GMT

Hi all how are u?

i have method as follow:

DataTable dt=new DataTable();

Students st=new Students();

dt=st.getallstudent();

foreach(DataRow row in dt.Rows)

{

if(row["studentname"]=="greaso")

{

st.deletestudent("greaso") ; <=========== this method worked successfuly and delete record from database without any error

dt.Rows.RemoveAt(dt.Rows.IndexOf(row)); <----- but this not executed i dont know why !

}

i havre tried another solution but give me the same problem:

DataTable dt=new DataTable();

Students st=new Students();

dt=st.getallstudent();

foreach(DataRow row in dt.Rows)

{

if(row["studentname"]=="greaso")

{

st.deletestudent("greaso") ; <=========== this method worked successfuly and delete record from database without any error

dt.Rows.Removet(row); <----- but this not executed i dont know why !

}

Insert using object data source

joaoluizbt — Fri, 18 Dec 2009 10:27:14 GMT

Hi, Friends.

I have a webform with a gridview that uses object datasource and works fine.

when i click in a hyperlink to add a new item, another webform with a detailsview is opened. Until here, thats all ok.

Then I fill the textboxes with the information I want, BUT, when i click "insert" I got an error message:

Erro de Servidor no Aplicativo '/HeraSupportSite'.

Connection must be valid and open.

Descrição: Ocorreu uma exceção não tratada durante a execução da atual solicitação da Web. Examine o rastreamento de pilha para obter mais informações sobre o erro e onde foi originado no código.

Detalhes da Exceção: System.InvalidOperationException: Connection must be valid and open.

Erro de Origem:

Linha 38:             comandomysql.Parameters.AddWithValue("@situacao", situacao);
Linha 39:             stringconexaomysql.Open();
Linha 40:             comandomysql.ExecuteNonQuery();
Linha 41:             stringconexaomysql.Close();
Linha 42:         }

Arquivo de Origem: C:\Desenvolvimento\Projetos Web\SHI\DAL\Solicitacao.cs Linha: 40

Here is my code:

        private String ConStr = @"server=127.0.0.1;user id=root;database=shi";

        [DataObjectMethod(DataObjectMethodType.Insert, true)] 
        public void Insert(int cod_usuario, string data_solicitacao, string hora_solicitacao, string solicitacao, string situacao, string avaliacao)
        {
            MySqlConnection stringconexaomysql = new MySqlConnection(ConStr);
            MySqlCommand comandomysql = new MySqlCommand("INSERT INTO solicitacaoatendimento(cod_usuario, data_solicitacao, hora_solicitacao, solicitacao, situacao) VALUES (@cod_usuario, @data_solicitacao, @hora_solicitacao, @solicitacao, @situacao)");
            comandomysql.Parameters.AddWithValue("@cod_usuario", cod_usuario);
            comandomysql.Parameters.AddWithValue("@data_solicitacao", data_solicitacao);
            comandomysql.Parameters.AddWithValue("@hora_solicitacao", hora_solicitacao);
            comandomysql.Parameters.AddWithValue("@solicitacao", solicitacao);
            comandomysql.Parameters.AddWithValue("@situacao", situacao);
            stringconexaomysql.Open();
            comandomysql.ExecuteNonQuery();
            stringconexaomysql.Close();
        }

Thanks