Error With Databound Datagrid
May 12, 2005
Help,
I have a simple app that only has on datagrid that is bound by the typical sqlconnection,sqldataadapter and dataset. But I keep getting this error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
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.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.Source Error:
Line 55: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Line 56: 'Put user code to initialize the page here
Line 57: SqlDataAdapter1.Fill(DataSet11)
Line 58: DataGrid1.DataBind()
Line 59:
WHY :)
View 2 Replies
ADVERTISEMENT
Jun 29, 2007
Hello, I have a datagrid which is populated with data from an MS SQL server database. When I run an update query it always throws an exception - what is the most likely cause for this given that I am using the code below: 1 public void DataGrid_Update(Object sender, DataGridCommandEventArgs e)
2 {
3 String update = "UPDATE Fruit SET Product = @ID, Quantity = @Q, Price = @P, Total = @T where Product = @Id";
4
5 SqlCommand command = new SqlCommand(update, conn);
6
7 command.Parameters.Add(new SqlParameter("@ID", SqlDbType.NVarChar, 50));
8 command.Parameters.Add(new SqlParameter("@Q", SqlDbType.NVarChar, 50));
9 command.Parameters.Add(new SqlParameter("@P", SqlDbType.NVarChar, 50));
10 command.Parameters.Add(new SqlParameter("@T", SqlDbType.NVarChar, 50));
11 command.Parameters["@ID"].Value = DataGrid.DataKeys[(int)e.Item.ItemIndex];
12 command.Connection.Open();
13
14 try
15 {
16 command.ExecuteNonQuery();
17 Message.InnerHtml = "Update complete!" + update;
18 DataGrid.EditItemIndex = -1;
19 }
20 catch (SqlException exc)
21 {
22 Message.InnerHtml = "Update error.";
23 }
24
25 command.Connection.Close();
26
27 BindGrid();
28 }
All of the row types in MS SQL server are set to nvarchar(50) - as I thought this would eliminate any inconsistencies in types. Thanks anyone
View 5 Replies
View Related
Jul 23, 2005
VB.NET 2003 / SQLS2KThe Stored Procedure returns records within Query Analyzer.But when the Stored Procedure is called by ADO.NET ~ it produced thefollowing error message.---------------------------Exception Message: Failed to enable constraints. One or more rowscontain values violating non-null, unique, or foreign-key constraints.------------------------------------------------------Exception Source: System.Data---------------------------If I click OK past the error messages I will get data filling thedatagrid. However not as I would like to see it.Even though it returns the proper data rows and includes all thecolumns I asked for, it also returns plenty of columns I didn't ask for(all the columns of the main table) and all those columns are filledwith "null"In addition each row header contains a red exclaimation mark whch whenhovered over reads;"Column 'cmEditedBy' does not allow DBNull.Values."An interesting thing about this column 'cmEditedBy' is that there isnoting wrong with it and all rows for that column contain data.I believe this error is a mistake! But it probably indicates some otherproblem. How should I track its cause?M O R E ...Below is the code in the data layer, the stored procedure, and the datareturned within query analyzer.\'DataAdapterFriend daView041CmptCyln As New SqlDataAdapter'SqlCommandPrivate daView041CmptCyln_CmdSel As New SqlCommand'Add the commanddaView041CmptCyln.SelectCommand = daView041CmptCyln_CmdSel'SelectWith daView041CmptCyln_CmdSel.CommandType = CommandType.StoredProcedure.CommandText = "usp_View_041Cmpt_ByJobCyln".Connection = sqlConnWith daView041CmptCyln_CmdSel.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, _4, ParameterDirection.ReturnValue, False, CType(0,Byte), _CType(0, Byte), "", DataRowVersion.Current, Nothing))'Criteria.Add("@fkJob", SqlDbType.Text).Value = _"48c64a55-874d-40d0-addc-7245f5d9c118"'.Add("@fkJob", SqlDbType.Text).Value = f050View.jobIDEnd WithEnd With//\ALTER PROCEDURE usp_View_041Cmpt_ByJobCyln(@fkJob char(36))AS SET NOCOUNT ON;SELECTJobNumber,DeviceName,ComponentName,Description,Quan,Bore,Stroke,Rod,Seconds,CylPSI,PosA,PosB,PosC,PosD,PosE,HomeIsRet,RetIsRetrac,POChecks,Regulated,FlowControl,PortSize,LoadMassFROM tbl040cmptINNER JOIN tbl030Devi ON fkDevice = pkDeviceIdINNER JOIN tbl020Proc ON fkProcess = pkProcessIdINNER JOIN tbl010Job ON fkJob = pkjobIdINNER JOIN lkp202ComponentType ON fkComponenttype = pkComponentTypeIdINNER JOIN lkp201DeviceType ON fkDeviceType = pkDeviceTypeIdINNER JOIN lkp101PortSize on cmSmallint05 = pkPortSizeIdWHERE(fkJob = @fkJob)--fkJob = '48c64a55-874d-40d0-addc-7245f5d9c118'AND fkComponentType = 2GO//(note - columns are wrapped)\F1111Clip DriverCylinderClip Driver_2 - Top -Cylinder91.2502.250.8752.250NULL01101110011/8 NPTNULLF1111Punch MechCylinderPunch Mech_1 -Cylinder_222.1002.0001.0001.234NULL11000110011/8NPTNULLF1111ClipDriverCylinderBottom92.1002.0001.0001.000NULL11010110011/4NPTNULLF1111Punch MechCylinderPunch Mech_1 -Cylinder_122.1002.0001.0001.000NULL01000110011/8NPTNULLF1111DegateCylinderDegate 1 -Cylinder21.1882.500.8751.000NULL11000110011/8 NPTNULLF1111Clip DriverCylinderClip Driver 1 -Bottom11.1801.250.8751.000NULL00011110011/4 NPTNULL//
View 1 Replies
View Related
Oct 5, 2006
I have a datagrid script where I modify data in an sql dbase in asp.net, when i hit the "update" button, I get a Specified cast is not valid error on my 'descript' declaration, whereas 'descript' is a multiline text box and a varchar datatype (everything else is either a char or a datetime datatype). Am I assigning 'descript' a wrong datatype? Tried making it a nvarchar, still get same result'''''''''''''''''''''''''''''''Sub MyDataGrid_UpdateCommand(s As Object, e As DataGridCommandEventArgs ) Dim conn As SQLConnection Dim MyCommand As SQLCommand Dim strConn as string = "Server=sql.mydomain.com;Initial Catalog=mydb;User ID=DBxxx;Password=xxxxx;" Dim company As textbox = E.Item.cells(2).Controls(0) Dim address As textbox = E.Item.cells(3).Controls(0) Dim city As textbox = E.Item.Cells(4).Controls(0) Dim state As textbox = E.Item.cells(5).Controls(0) Dim county As textbox = E.Item.cells(6).Controls(0) Dim zip As textbox = E.Item.cells(7).Controls(0) Dim phone As textbox = E.Item.cells(8).Controls(0)'''''' the following line declaring the descript var is the line of the error Dim descript As textbox = E.Item.cells(9).Controls(0) Dim web As textbox = E.Item.cells(10).Controls(0) Dim email As textbox = E.Item.cells(11).Controls(0) Dim datesold As textbox = E.Item.cells(12).Controls(0) Dim dateexpire As textbox = E.Item.cells(13).Controls(0) Dim strUpdateStmt As String strUpdateStmt =" UPDATE CPAs SET" & _ " company = @company, address = @address, city = @city, state = @state, " & _ "county = @county, zip = @zip, phone = @phone, descript =@ descript, " & _ "web = @web, email = @email, datesold = @datesold, dateexpire = @dateexpire" & _ " WHERE cpaID = @cpaID" conn = New SqlConnection(strConn) MyCommand = New SqlCommand(strUpdateStmt, conn) MyCommand.Parameters.Add(New SqlParameter("@company", company.text)) MyCommand.Parameters.Add(New SqlParameter("@address", address.text)) MyCommand.Parameters.Add(New SqlParameter("@city", city.text)) MyCommand.Parameters.Add(New SqlParameter("@state", state.text)) MyCommand.Parameters.Add(New SqlParameter("@county", county.text)) MyCommand.Parameters.Add(New SqlParameter("@zip", zip.text)) MyCommand.Parameters.Add(New SqlParameter("@phone", phone.text)) MyCommand.Parameters.Add(New SqlParameter("@descript", descript.text)) MyCommand.Parameters.Add(New SqlParameter("@web", web.text)) MyCommand.Parameters.Add(New SqlParameter("@email", email.text)) MyCommand.Parameters.Add(New SqlParameter("@datesold", datesold.text)) '', dateexpire =@dateexpire MyCommand.Parameters.Add(New SqlParameter("@dateexpire", dateexpire.text)) MyCommand.Parameters.Add(New SqlParameter("@cpaID", e.Item.Cells(1).Text )) conn.Open() MyCommand.ExecuteNonQuery() MyDataGrid.EditItemIndex = -1 conn.close BindDataEnd Sub'''''''''''''''''''''''''''''netsports
View 1 Replies
View Related
Jun 14, 2006
After finding out that SSRS doesn't directly support databound items in the header or footer, I found the workaround from Microsoft yesterday at http://msdn2.microsoft.com/en-us/library/ms159677.aspx. The problem is that their solution only works for the first page of the report, so it's basically useless! Because this is an image, it also means the workaround of using a parameter doesn't work, either.
Does anybody have any advice? Is there a solution to this problem?
View 10 Replies
View Related
Jan 3, 2007
im trying to display all of the results from a SQL database where the username is the same as the current user.currently, i have a long way around way of implementing this. i have a dropdownbox with a static entry (set at the page load) of the users name. i then tried to databind the gridview to the database and use the dropbox as the variable. however, for some reason, it doesnt acknowledge the username field and it doesnt return anything. i think this is because the dropbox is a static entry, but why does that matter? here is my current code: <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource3" EmptyDataText="There are no data records to display."> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:pas_db_connectionstring %>" ProviderName="<%$ ConnectionStrings:pas_db_connectionstring.ProviderName %>" SelectCommand="SELECT * FROM [files] WHERE ([submitter] = @submitter)"> <SelectParameters> <asp:ControlParameter ControlID="usernameDropBox" DefaultValue="none" Name="submitter" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> please understand that i am trying to set the @submitter variable equal to the current user's username. thx
View 4 Replies
View Related
Oct 31, 2006
I followed the instructions from this post...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384621&SiteID=1
on how to add databound images into a report header.
The issue I am encountering is exporting to PDF. If the report spans multiple pages, in PDF the image only shows up on the first page..and broken on all other pages.
I have tied the hidden image to the table that spans multiple pages. Shows ok in HTML but breaks when exported.
Thanks for any help.
View 3 Replies
View Related
Jan 12, 2006
Hi all,
I have a problem in displaying databound images in page header.
I have done as is stated in the below article.
----X----
http://msdn2.microsoft.com/en-us/library/ms159677(en-us,VS.90).aspx
Adding a Databound Image to a Header or Footer
You can use image data stored in a database in a header or footer. However, you cannot reference database fields from the Image control directly. Instead, you must add a text box in the body of the report and then set the text box to the data field that contains the image (note that the value must be base64 encoded). You can hide the text box in the body of the report to avoid showing the base64 encoded image. Then, you can reference the value of the hidden text box from the Image control in the page header or footer.
For example, suppose you have a report that consists of product information pages. In the header of each page, you want to display a photograph of the product. To print a stored image in the report header, define a hidden text box named TXT_Photo in the body of the report that retrieves the image from the database and use an expression to give it a value:
=System.Convert.ToBase64String(Fields!Photo.Value)
In the header, add an Image control which uses the TXT_Photo text box, decoded to show the image:
=System.Convert.FromBase64String(ReportItems!TXT_Photo.Value)
----X----
but I am not getting the image.
The error message displayed is like this
[rsInvalidExpressionDataType] The Value expression used in image €˜image2€™ returned a data type that is not valid.
Can any one help me in this regard.
Thanks in advance.
Ramesh
View 5 Replies
View Related
Oct 26, 2007
Ive run into a situation where some code im using will not function the same when between these two cases. So, this has me wondering what is going on behind the scenes. What is the sequence of events that are occurring that would possibly be messing things up.Here is the sample codeIts a gridview that is using a sqldatasource control. The code works fine, but if you want to bind the grid to a dataset and call databind yourself, things dont work as expected and the other features that the code performs just isnt happening, at least not for me.
View 1 Replies
View Related
Feb 14, 2008
I have seen some forums on SSRS page footer which is data bound.
The procedure is as follow:
a hidden text box is put into the body of the page...which can be data bound..
than a textbox is placed in the footer, which is linked to the hidden text box in
the body. This is a good solution to this problem.. but the is 1 major problem I am
facing. I render the report to PDF. But the result is that the databound footer
shows up only on the last page. I need it to be seen on all the pages..
Can someone tell me how to solve this problem?
View 13 Replies
View Related
Aug 10, 2007
I need to take 8 indiviual parts of a table and combine then into 1 Column of a Datagrid. Is this even possible, if so how?
example:
The DB contains:
Comp1 Comp2 Comp3 ... Comp8
The DG should say
header --> Comp
Data --> Comp1, Comp2, Comp3, ..., Comp8
View 4 Replies
View Related
May 6, 2004
hey all
i have 2 tables
1. Location (locid, name)
2. Product (pid, locid, productname, ...etc)
right now i do a select * from product where locid ='locid', and a datagrid with colums
ProductId | ProductName | AvailableIn
how do i do a select * from Location where locid ='locid' and show Location Name in the datagrid?
ProductId | ProductName | AvailableIn
123456 | testing | Somewhere (instead of the locid)
thanks
View 2 Replies
View Related
Aug 29, 2006
Here is my code : string connstring = System.Configuration.ConfigurationSettings.AppSettings["myconn"]; string selectquery = "Select * from nhacungcap"; protected System.Web.UI.WebControls.DataGrid DataGrid1; string insertquery = "Insert into nhacungcap(mancc,tenncc,diachi,dienthoai) values(@mancc1,@tenncc1,@diachi1,@dienthoai1)";string updatequery = "Update nhacungcap set mancc=@mancc, tenncc=@tenncc, diachi=@diachi, dienthoai=@dienthoai where (mancc=@mancc)"; myconnection.Open(); SqlCommand updatecommand = new SqlCommand(updatequery,myconnection);// sua truong mancc updatecommand.Parameters.Add(new SqlParameter("@mancc",SqlDbType.VarChar,10)); updatecommand.Parameters["@mancc"].Value = DataGrid1.DataKeys[e.Item.ItemIndex];// sua truong tenncc updatecommand.Parameters.Add(new SqlParameter("@tenncc",SqlDbType.NVarChar,50)); updatecommand.Parameters["@tenncc"].Value = ((TextBox) e.Item.Cells[3].Controls[0]).Text;// sua truong diachi updatecommand.Parameters.Add(new SqlParameter("@diachi",SqlDbType.NVarChar,200)); updatecommand.Parameters["@diachi"].Value = ((TextBox) e.Item.Cells[4].Controls[0]).Text;// sua truong dienthoai updatecommand.Parameters.Add(new SqlParameter("@dienthoai",SqlDbType.Char,10)); updatecommand.Parameters["@dienthoai"].Value = ((TextBox) e.Item.Cells[5].Controls[0]).Text;// kiem tra lenh thuc thi int result1 = updatecommand.ExecuteNonQuery(); myconnection.Close();// dieu kien kiem tra if (result1 > 0 ) { lbcheck.Text = "Cáºp Nháºt Thành công !"; }// hien thi du lieu hienthidulieu(); And my error appear, when I edit value in datagrid, I only update all value fields, without value @mancc . Hu hu hu, I don't know what i must do with it.
View 1 Replies
View Related
Jul 24, 2007
Hi I'm having a problem deleting rows from my datagrid. Basically I hit delete and a message box pops up and asks if Im sure I want to delete so I hit yes and then I get the following error --> Could not find stored procedure 'delete from SECTION_TBL where SECT_ID = @SECT_ID'.
Is it my code thats wrong or is our test sql server that is the problem?1 <%@ Page Language="VB" EnableEventValidation="True" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
2 <%@ import namespace="System" %>
3 <%@ import namespace="System.Data" %>
4 <%@ import namespace="System.Data.SqlClient" %>
5
6 <script language="VB" runat="server">
7
8 Dim section As String
9 Dim myconnection As SqlConnection
10 Dim myda As SqlDataAdapter
11 Dim ds As DataSet
12
13 Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)
14 BindData()
15 End Sub
16
17 Sub BindData()
18
19 Dim strConn As String = "server=fileserver; uid=xxx; pwd=xxx; database=NEW_CMS"
20 Dim sql As String = "Select * from SECTION_TBL"
21 myconnection = New SqlConnection(strConn)
22 myda = New SqlDataAdapter(sql, myconnection)
23 ds = New DataSet
24 myda.Fill(ds, "SECTION_TBL")
25 sectList.DataSource = ds
26 sectList.DataBind()
27
28 End Sub
29
30 Private Sub sectList_ItemDataBound(ByVal sender As Object, ByVal e As DataGridItemEventArgs) Handles sectList.ItemDataBound
31
32 Dim l As LinkButton
33
34 If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
35 l = CType(e.Item.Cells(0).FindControl("cmdDel"), LinkButton)
36 l.Attributes.Add("onclick", "return getconfirm();")
37 End If
38
39 End Sub
40
41 Sub sectList_DeleteCommand(ByVal s As Object, ByVal e As DataGridCommandEventArgs)
42
43 Dim ConnectionStr As String = ConfigurationManager.AppSettings("ConnStr")
44 Dim conn As SqlConnection
45 Dim cmd As SqlCommand
46 Dim Id As Integer
47
48 Id = CInt(e.Item.Cells(0).Text)
49 conn = New SqlConnection("server=fileserver; uid=xxx; pwd=xxx; database=NEW_CMS")
50 cmd = New SqlCommand("delete from SECTION_TBL where SECT_ID = @SECT_ID", conn)
51 cmd.CommandType = CommandType.StoredProcedure
52 cmd.Parameters.Add("@SECT_ID", SqlDbType.Int).Value = Id
53
54 cmd.Connection.Open()
55 cmd.ExecuteNonQuery()
56 cmd.Connection.Close()
57
58 DataBind()
59
60 End Sub
61
62
63
64 </script>
65
66 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
67
68 <script language="javascript">
69
70 function getconfirm()
71 {
72 if (confirm("Do you want to delete record?")==true)
73 return true;
74 else
75 return false;
76 }
77
78 </script>
79
80 <table cellpadding="2" cellspacing="2" width="760">
81 <tr>
82 <td>Sections</td>
83 </tr>
84 <tr>
85 <td>
86
87 <asp:DataGrid OnDeleteCommand="sectList_DeleteCommand" ID="sectList" runat="server" DataKeyField="SECT_ID" AutoGenerateColumns="False">
88
89 <Columns>
90
91 <asp:BoundColumn DataField="SECT_ID" Visible="False" />
92
93 <asp:HyperLinkColumn HeaderText="SECTION NAME" DataTextField="SECT_NAME" DataNavigateUrlField="SECT_ID" DataNavigateUrlFormatString="manageSection.aspx?SECT_ID={0}" />
94
95 <asp:TemplateColumn>
96 <ItemTemplate>
97 <asp:LinkButton id="cmdDel" runat="server" Text="Delete" CommandName="Delete" CausesValidation="false" />
98 </ItemTemplate>
99 </asp:TemplateColumn>
100
101 </Columns>
102
103 </asp:DataGrid>
104
105 </td>
106 </tr>
107 <tr>
108 <td></td>
109 </tr>
110 </table>
111
112 </asp:Content> Thanks in advance.
View 2 Replies
View Related
Mar 10, 2008
Hi,
I'm trying to use VS2005 to create an ASP.NET 2.0 application. As part of this application I need to be able to read value from a SQL2005 database. I used the connection string builder to create the connection string, but am unable to run a simple SELECT statement. In .NET 1.1, I was able to do this pretty easily, but am unable to even find the same namespaces in .NET 2.0.
For example (VS2003):
'Construct new SQL statement sqlDBDAHulls.SelectCommand.CommandText = "SELECT COUNT('HullID') FROM Hulls" iNoRows = sqlDBDAHulls.SelectCommand.ExecuteScalar
ReDim arrHullClass(iNoRows - 1) ReDim arrHullDesign(iNoRows - 1)
ddlHull.Items.Clear()
For iArrLoop = LBound(arrHullClass) To UBound(arrHullClass) 'Redefine SQL statement sqlDBDAHulls.SelectCommand.CommandText = "SELECT Class FROM Hulls WHERE (HullId = " + Trim(Str((iArrLoop + 1))) + ")" 'Populate array arrHullClass(iArrLoop) = sqlDBDAHulls.SelectCommand.ExecuteScalar 'Redefine SQL Statement sqlDBDAHulls.SelectCommand.CommandText = "SELECT Design FROM Hulls WHERE (HullId = " + Trim(Str((iArrLoop + 1))) + ")" 'Populate array arrHullDesign(iArrLoop) = sqlDBDAHulls.SelectCommand.ExecuteScalar 'Populate combobox ddlHull.Items.Insert(iArrLoop, arrHullClass(iArrLoop) + ":" + arrHullDesign(iArrLoop)) Next iArrLoop
'Close the database sqlConnBC.Close()
This all works absolutely fine.
In VS2005 there does not appear to be the same data adapter and sql client controls and I am starting to pull my hair out. this is what I have:
ASP:
<asp:SqlDataSource ID="connSQL" runat="server" CancelSelectOnNullParameter="False" ConnectionString="Data Source=STREETROD;Initial Catalog=DVD;Persist Security Info=True;User ID=sa;Password=xj600f" DataSourceMode="DataReader" ProviderName="System.Data.SqlClient" FilterExpression="ID" SortParameterName="ID"></asp:SqlDataSource>
VB:
'Connect to database and read values
connSQL.ConnectionString = sConnStr
connSQL.SelectCommandType = SqlDataSourceCommandType.Text
connSQL.SelectCommand = "SELECT COUNT('ID') FROM Users"
iCount = connSQL.Select()ReDim sDbUN(iCount - 1)
ReDim sDbPW(iCount - 1)For iLoop = LBound(sDbUN) To UBound(sDbUN)
connSQL.SelectCommand = "SELECT 'UN' FROM Users WHERE 'ID'='" & Str(iLoop + 1) & "'"
sDbUN(iLoop) = connSQL.Select()
connSQL.SelectCommand = "SELECT 'PW' FROM Users WHERE 'ID'='" & Str(iLoop + 1) & "'"
sDbPW(iLoop) = connSQL.Select()
Next
and all this keeps telling me is that I have not specified any.arguements under System.Web.UI.DataSourceSelectArguments. I have even tried entering System.Web.UI.DataSourceSelectArguments.Empty to no avail
Can someone please give me a code example that will help me understand this, or at least point me in the right direction? Or is that I simply HAVE to use a datagrid?
Many thanks.
ProudFoots
View 3 Replies
View Related
Mar 21, 2008
Hello,
I ran into a little problem. My problem is: i need to substract 2 variabeles from 2 different tables in the database
TitleTimes left todayTimes left
My first excercise!15
My second excercise!19
The fields times left are a calculation... the number of times that the admin entered minus a count in the table scores.
Has anyone an idea how i can solve this?
An example excercise would be great!
Thanks in advance
View 5 Replies
View Related
Mar 22, 2005
please help me to take element in datagrid?
View 1 Replies
View Related
Jul 13, 2005
Hi,I am attempting to achieve some form of report that needs to make use of sql rollup and display it as follows:Category Subject Matter1 Subject Matter2 Subject Matter3 No of CasesClubs Facilities Sport Facilities Swimming Pool 3 SubTotal 3Events SBR/AHM NULL NULL 1 SubTotal 1 GrandTotal 4However, with my sql query, using roll up, it will look like the following which is not correct.Category Subject Matter1 Subject Matter2 Subject Matter3 No of CasesClubs Facilities Sport Facilities Swimming Pool 3Clubs Facilities Sport Facilities NULL 3Clubs Facilities NULL NULL 3Clubs Sub Total NULL NULL 3Events SBR/AHM NULL NULL 1Events SBR/AHM NULL NULL 1Events SBR/AHM NULL NULL 1Events Sub Total NULL NULL 1This is the query I am using:<code>select casewhen (grouping(Cat.Description)=1) then 'Grand Total'else Cat.Descriptionend as Category,casewhen (grouping(sub.description)=1) then 'Sub Total'else Sub.descriptionend as SM1,SubSub.Description as SM2, SM.Description as SM3, count(sub.description)from tb_feedbackcase FB left join tb_category Cat on FB.Category_ID = Cat.Category_ID left join tb_subcategory Sub on FB.SubCategory_ID = Sub.SubCategory_IDleft join tb_subsubcategory SubSub on FB.SubSubCategory_ID = SubSub.SubSubCategory_IDleft join tb_SubjectMatterLV3 SM on FB.SM3_ID = SM.SM3_IDwhere fb.commenttype_id in (select commenttypes_id from tb_comment_types where description = @feedback_type)and convert(char(10),feedback_datetime,102) >= convert(char(10),@date_from, 102)and convert(char(10), feedback_datetime, 102) <= convert(char(10),@date_to, 102)group by Cat.Description, Sub.Description, SubSub.Description, SM.Description with rollup</code>How can I change it to reflect more accurately? Please help. Thanks.
View 1 Replies
View Related
Feb 4, 2006
Hi,
As a relative newbie to SQL Server/ASP.NET I'm hoping someone here can help with my problem. I'm developing a timesheet application in ASP.NET C# using Visual Studio 2003 with a database built in MSDE. One of my forms needs to return a simple list of resources from my database. I have followed the guide on the MSDN libraries, but for some reason I continuously get the same error message.
What I've done so far is Create the database, tables, and populate with some sample data using using Server Explorer in Visual Studio. I have connected to the database (using integrated security) and I am trying to get the contents of the Resource table to appear on my form. I have then created a DataAdapter (tested the connection, set the SQL as a simple SELECT * from Resource, etc), which also generates an sqlConnection for me. To test this I have previewed the generated data, and it returns what I want, so I have chosen to generate a DataSet of this. I am then trying to get this data into a simple DataGrid. On the properties of the DataGrid I have changed the DataSource to point at my Dataset. As I understand it, I then have to add the following to my Page Load section of my code.
sqlConnection1.Open();this.sqlDataAdapter1.Fill(this.dsResource);DataGrid1.DataBind();sqlConnection1.Close();
The form builds fine, but when I browse to the particular form I get the following error for the sqlConnection1.Open(); line. If I remove this line the error simply moves to the line below.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'SCMS'. Login fails. Login failed for user 'AL-NOTEPADASPNET'.
To me this is an error with my connection string. My database instance is actually 'AL-NOTEPADVSDOTNET'. However the properties for sqlConnection1 are pointing to the correct datasource. I do not know why the application is looking for user 'AL-NOTEPADASPNET'. It does not exist, to my knowledge. Do I need to grant access to this user? If so, how would I do it? Bearing in mind I am using MSDE, and do not have Enterprise Manager.
Any help with this would be greatly appreciated, as I get the same error with my code for forms-based login...
Thanks in advance..
View 2 Replies
View Related
Mar 13, 2006
I'm working on a website where we're using .Net web services to feed data to a Flash front-end. The site will have a comments section and we want to display 15 or so comments per page with 'back' and 'next' functionalitiy. We'd also like to show the number of pages of comments and highlight the page they're on. The standard stuff that datagrids do so well. How can this be accomplished without a datagrid? There's a good page that explains a number of ways to do this using classic ASP. Some of the solutions they implement I can most likely use with .Net.http://www.aspfaq.com/show.asp?id=2120But I wanted to ask the community. How to paginate without a recordset? Sql Server 2000 back-end, ASP.NET 1.xThanks.
View 3 Replies
View Related
Jul 23, 2005
Hi,No I don't want to loop through my dataset ;) I want to loop through mydatagrid to retrieve the values in each cells, but I don't know how todo that ?Can someone help ?thx
View 1 Replies
View Related
Sep 14, 2006
OK I am a newbie when it comes to lots of .Net so if you answer go slowwww. I have an auto incrementing ID field in a SQL database.This is all displayed to the user in a datagrid control. I add two or three new rows and AcceptChanges.At this point I my ID field displayed on the grid bound tot he SQL ID field is out of Sync. What the #@^%#$&* In fact it looks like the data grid placed in its own numbers by doing some sort of Maxvalue on the column. HELP! Bob
View 3 Replies
View Related
Dec 4, 2006
I am using C#.Net, Visual web Developer, SQL server 2000. I have a SQL query which I am binding it to a DataGrid. SQL : "SELECT ord_number, ord_ID, ord_split, ord_Name, ETD_Date, OSP_FSD FROM ORDERS" In My DataGrid I have a dynamic databound column. I am able to bind one column to this databound column using following code. BoundColumn ETDDate = new BoundColumn(); ETDDate.HeaderText = "ETD Date"; ETDDate.DataField = "OSP_FSD"; mygrid2.Columns.AddAt(ETDDate); but now I want to bind this databound column based on the following criteria to two different database columns. if(ord_split = 1) { ETDDate.DataField = "OSP_FSD"; } else { ETDDate.DataField = "ETD_Date"; } How to get value of ord_split before binding SQL to teh DataGrid? i.e I just want to take value of ord_split and not all the values of SQL. Please Help!
View 1 Replies
View Related
Jan 19, 2004
Hello all
i am trying to update the database with the DataSet (Updated from the DataGrid) but it is giving the error as
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
the code is as follows
public DataSet bindControlToGrid(DataSet dataSet)
{
SqlConnection objConn= null;
SqlCommand objComm = null;
try
{
objConn = new SqlConnection(@"User ID=sa;Password=sa;database=ExtraNet;server=EAGLESEAGLES");
SqlDataAdapter objAdapter=new SqlDataAdapter();
objAdapter.SelectCommand= new SqlCommand("SELECT sUserID,sUserName FROM tblUser WHERE dUserDeActivated IS NULL ORDER BY sUserName,nUsrSerialNo",objConn);
SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(objAdapter);
objConn.Open();
DataSet objdataSet= new DataSet("ko");
string d = (dataSet.Tables[0].Rows[0].ItemArray.GetValue(0).ToString());
objAdapter.Fill(objdataSet);
objAdapter.Update(dataSet);
objAdapter.Dispose();
}
catch(Exception ex)
{
string s= ex.Message;
}
finally
{
//objComm.Dispose();
objConn.Close();
}
return dataSet;
}
kindly help
View 1 Replies
View Related
Jul 19, 2005
For a datagrid dataset, I'm trying to take the amount of all the sales by a salesperson in two days and create an alias for sum(saleamount) (which would become allsales) to put the grand total in.
select thedate, sum(saleamount) as allsales , salesperson, orderID from transactions WHERE (thedate IN ('6/1/2005', '6/2/2005')) GROUP BY Salesperson HAVING SUM(allsales > 0)
I'm getting "invalid syntax near SUM (on the Having clause)" when trying to run this query. And I know there is data in there meeting this criteria.Thanks chumley
View 1 Replies
View Related
Nov 10, 2005
I have been trying to formulate a fully editabe datagrid for a couple of days with no luck. I have used code from 4guys and some other sites and am at the point where I can render the datagrid correctly (as a bouncolumn and template column-textbox) but when I try to update the database it all falls apart. I am getting "input string was not in a correct format" and the error references ...Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex))I suspect the problem lies in the area of primary indexes and such. The table I am using is a simple two-column table with usernames, passwords. Username is the primary field. Here is the actual code I am using...<code><%@ Page Explicit="True" Language="VB" Debug="True" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.OleDb" %><html>
<script runat="server">Dim Connect As OleDbConnection = New OleDbConnectionDim Adapter As OleDbDataAdapter = New OleDbDataAdapterDim DS As DataSet = New DataSetDim ConnectString, SelectStatement As String
Sub Page_Load(Sender As Object, E As EventArgs)
If Not IsPostBack Then SelectStatement = "Select * From Table"ConnectString = "Provider=SQLOLEDB;UID=;PWD=;" & "Data Source=;Initial Catalog=;"
Connect.ConnectionString = ConnectStringAdapter.SelectCommand = New oleDbCommand(SelectStatement, Connect)Adapter.SelectCommand.Connection.OpenAdapter.Fill(DS, "Items")sls.Datasource = DS.Tables("Items")Page.DatabindConnect.Close()End IfEnd Sub
Sub Click(sender As Object, e As EventArgs) Dim myConnection as New OleDbConnection(ConnectString)Dim updateSQL as String = "UPDATE Table SET password = @Password WHERE Username = @ID"Dim myCommand as New oleDbCommand(updateSQL, myConnection)
Dim dgi as DataGridItemFor Each dgi in sls.Items 'Read in the Primary Key Field Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex)) Dim password as String = CType(dgi.FindControl("txtPass"), TextBox).Text
'Issue an UPDATE statement... myCommand.Parameters.Clear() myCommand.Parameters.Add("@ID", id) myCommand.Parameters.Add("@Password", password)
myCommand.ExecuteNonQuery()Next
End Sub
</script><body><form runat="Server">
<asp:datagrid id="sls" runat="server" AutoGenerateColumns="False" datakeyfield="Username"> <Columns> <asp:BoundColumn HeaderText="UserName" datafield="Username"/>
<asp:TemplateColumn HeaderText="Password"> <ItemTemplate> <asp:TextBox runat="server" id="txtPass" Columns="10" Text='<%# DataBinder.Eval(Container, "DataItem.Password") %>' /> </ItemTemplate> </asp:TemplateColumn>
</Columns> </asp:datagrid><asp:button id="Update" text="Update All" runat="Server" onclick="Click"/></form></body></html></code>Anyone have any idea as to why the id variable (error message above) is causing problems?
View 2 Replies
View Related
Jul 20, 2005
I'm trying to add functionality to a VB 6 application allowingcustomer service to add a customer number to a new customer.Customers are added to the database by sales personnel, and aprospective customer may have multiple rows due to projected ordersfor multiple products. Customer numbers are assigned when a newcustomer makes their first order.I'm using a DataGrid connected to an ADO Data Control. The datacontrol is connected to a view in SQL Server using the 'distinct'directive (I know it's not updatable) to show only one line per newcustomer. What I wish to do is capture the update event (probablythrough the FieldChangeComplete routine of the data control), manuallyassign the newly entered customer number to all appropriate rows inthe database, and cancel the update event with no notifications.I'm having two problems:1. I can't capture the newly entered customer number. The Textproperty of the DataGrid returns the old value of the cell instead ofthe newly entered value. How do I get the edited value?2. Even though I set adStatus = adStatusCancel in theFieldChangeComplete routine, I get a Microsoft DataGrid Control dialogstating 'Operation was Canceled'. How do I avoid this notification?
View 1 Replies
View Related
Oct 11, 2006
Hi,I have a DataGrid whose DataSource is an SQLDataSource. This SQLDataSource references a SQL Server table with an Identity column, named Id. The SQLDataSource has generated an Update statement for me, but this only works if the Id column is selected for display in the DataGrid, and has Visible = True. If not, then the @original_Id parameter to the generated Update statement has a value of Nothing, and the Update has no effect. So is there a way to enable the Update statement to work, without the user having to see the Id column in the DataGrid? Many thanks,Keith.
View 3 Replies
View Related
Apr 20, 2007
Ok here goes. I have 3 tables, one holds case info, the 2nd holds possible outcome on the charges, and they're joined on a 3rd table (CaseOutComes). With me so far? Easy stuff, now for the hard part.
Since there's a very common possiblitly that the Case has multiple charges, we need to track those, and therefore, display them on a datagrid or some other control. I want the user to be able to edit the info and have X number of dropdowns pertaining to how many ever charges are on the case. I can get the query to return the rows no sweat, but ...merging them into 1 record (1 row) with mutiple drops is seeming impossible -- I thought about using a placeholder and added the controls that way, but it was not in agreement with what I was trying to tell it .
Any ideas on how to attack this?
View 3 Replies
View Related
Jan 13, 2004
Hi, I am trying to create a create for two table A and table B which have no relationship each time. For TableA, there are 3 columns like ID, APoints1, APoint2. For Table B, there are also 3 columns as ID, Qty, BPoints. There is no internal relationship for these two tables. But there may be same ID inside A and B for some records. Now I want to create a datagrid for displaying the information as :
ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID
Please Notice that I can't use directly SQL script as following from table A and table B because there is no relationship for Table A and Table B, otherwise the recult set would be wrong:
Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID group by A.ID
May I know is there solution for it?
Thank you very much!
View 4 Replies
View Related
Apr 1, 2004
I am attempting to bind a smalldatetime field to a datagrid and want to truncate the time off the end of the field so only the date shows up.
Here is the query that I am using:
Select
UserId,
Lastname + ',' + ' ' + Firstname as MembName,
Convert( datetime, Convert( int, MembExpiryDate))
FROM Users
In the datagrid, I have referenced a data element called MembExpiryDate, but with the above query, it no longer recognizes this filed.
If I use
Select
UserId,
Lastname + ',' + ' ' + Firstname as MembName,
MembExpiryDate
It works fine, the date is bound to the grid, but the time is still included obviously.
If I use
Select
UserId,
Lastname + ',' + ' ' + Firstname as MembName,
Convert( datetime, Convert( int, MembExpiryDate)) as ExpiryDate
and reference Expiry date in the datagrid instead, the time is still included.
Any ideas how to write this query to remove the damn time?
Thanks.
Dave
View 4 Replies
View Related
Apr 15, 2004
Hi there :)
I am developing a system for my uni course and I am stuck a little problem...
Basically its all about lecturers, students modules etc - A student has many modules, a module has manu students, a lecturer has many modules and a module has many lecturers.
I am trying to get a list of lecturers that run modules associated with a particular student. I am able to get a list of the appropriate lecturers, but some lecturers are repeated because they teach more than one module that the student is associated with.
How can I stop the repeats?
Heres my sql select code in my cs file:
string sqlDisplayLec = "SELECT * FROM student_module sm, lecturer_module lm, users u WHERE sm.user_id=" + myUserid + "" + " AND lm.module_id = sm.module_id " + " AND u.user_id = lm.user_id ";
SqlCommand sqlc2 = new SqlCommand(sqlDisplayLec,sqlConnection);
sqlConnection.Open();
lecturersDG.DataSource = sqlc2.ExecuteReader(CommandBehavior.CloseConnection);
lecturersDG.DataBind();
And here is a pic of my Data Model:
Data Model Screenshot
Any ideas? Many thanks :) !
View 1 Replies
View Related
Apr 24, 2004
I want to select 2 fields from two tables inorder to bind my Datagrid...
How can I do that using SQL statement?
Possible to do that with one statement? or i need to create stored procedure?
Can anyone show me sample codes?
View 2 Replies
View Related
