SqlDataSource: How To Find If Nothing Was Fetched?
Dec 10, 2007
I use a SqlDataSource control in the markup as such. I am querying something like this...
select top 5 name, count(*) as Replies from repliesTable inner join userTable on id=replyById where replyToReplyID != 0 and month(getdate()) = MONTH(replyDate) group by name order by Replies desc
This query list the top 5 forum repliers for a particular month. During the start of the month there might not be any new replies. In that case I would have an empty result set returned. How can I programatically determine this and then display a friendly text msg that says "post some replies...add your name here"
I have an Oracle table which contains a LONG column and I need to migrate the data from this table using SSIS to a table in SQLServer. The column in SQLServer is defined as nvarchar(MAX). This works until I changed the Oracle table to a view. The columns in the view are defined exactly the same as the source table except that it uses a database link to point to the table in a different schema. When executing the data flow task using the view, it gives me the following error message -
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01406: fetched column value was truncated ".
The error happens only on views with LONG column. Views with only varchar or numeric data types work fine.
Why does it work with a table but not a view which contains a LONG column?
I have a STORED PROC for dynamic T-SQL that returns a OUTPUT varaible of typevarchar . This works fine in query analyzer. But when I try to get values in asp.net page it does not return anything. No execeptions or errors also. Below is the code snippet HDConn = CommonMethods.BuildConnection();SqlCommand cmd1 = new SqlCommand("GenerateRowids",HDConn);cmd1.CommandType = CommandType.StoredProcedure;// add parameters for Proccmd1.Parameters.Add("@TableName",SqlDbType.VarChar,50).Value= "MetricsMaster";cmd1.Parameters.Add("@ColumnName",SqlDbType.VarChar,50).Value= "MetricId";cmd1.Parameters.Add("@rowidval",SqlDbType.VarChar,30);cmd1.Parameters["@rowidval"].Direction = ParameterDirection.Output;try{cmd1.ExecuteNonQuery();//the below line does not print the valueResponse.Write(cmd1.Parameters["@rowidval"].Value.ToString());}My stored proc looks like :CREATE PROCEDURE dbo.GenerateRowids@TableName varchar(50),@ColumnName varchar(50),@rowidval varchar(30) outputASBegindeclare @sql nvarchar(4000), @Idval varchar(50) set @sql = N'select left(MAX(' + @ColumnName +') ,1)+ convert(varchar(5),Cast(SUBSTRING((MAX(' +@ColumnName+ ')),2,(len(MAX(' +@ColumnName+ '))))as int)+1) from ' + @TableNameexec sp_executesql @query = @sql,@params = N'@rowidval varchar OUTPUT', @rowidval = @rowidval OUTPUT ENDGO
Hello,I need a clarification on the mechanism of sql server fetches recordsto memory (buffer)Example: A Table ( Table1 ) having 10 Columns(column1,column2,column3,column4 etc)When SELECT column1,column2 FROM Table1 is executedWhether all columns are trasnferred to the server memory from the diskOR only column1 and column2 are read from disk to memory .My assumption is that, entire page (8K) containg the rows with all thecolunmns are transferred to the memory and from the memory the selectcolumns are displayed .Please confirmThanksM A Srinivas
Hi Friends, I have created the procedure in sql server 2005 for retriving email addresses from table based on date_expiry and concatinating all email addresses in to @tolist as shown below Declare @tolist varchar(8000) set @tolist = ''SELECT @tolist = @tolist + ';' + COALESCE(email, '') FROM awc_register WHERE DATEDIFF(day, date_expiry, GETDATE())='3'print @tolist set @tolist = substring(@tolist, 2, len(@tolist)) and I passed this @tolist to another procedure which should send mails for email addresses present in @tolist variable. Problem is I need to send the mail to each email address separatley.( not bulkly)
Code : protected void Page_Load(object sender, EventArgs e) { Session["ID"] = "2"; string strConnString = ConfigurationManager.ConnectionStrings["BSDConnectionString"].ConnectionString; var con = new SqlConnection(strConnString); using (var sda = new SqlDataAdapter())
[Code] ....
That was my code , now lets see what my problem is :
I am getting only two dates in a single row from sql to my asp.net webform and then bindng those dates in jQuery UI Datepicker. Now if i set session to 1 then date picker works well but if i put session = 2 , it shows the end date till 2020 which is wrong.
Below are the dates which are fetched from database and i have copied here for your ease.
When Session["ID"] = "1"; Start Date : 01/07/2014 00:00:00 End Date : 05/02/2015 00:00:00
When Session["ID"] = "2";
Start Date : 07/04/2015 00:00:00 End Date : 27/08/2016 00:00:00
I have set my mindate to startdate and maxdate to end date. please check and see where the error is happening.
Also point of interest is that if i don't fetch values from database and use only List<string> in my web method then every thing works well. like this :
[WebMethod] public static List<string> GetDates() { List<string> arr = new List<string>(); arr.Add("2014-07-01"); arr.Add("2015-02-05"); return arr; }
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
What is the C# code I use to do this? I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
i am using visual web developer 2005 with SQL Express 2005 with VB as the code behindi have one database and three tables in itfor manipulating each table i am using separate SqlDataSource() is it sufficient to use one SqlDataSource() for manipulating all the three tables ? i am manipulating all the tables in the same page only please help me
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
I have an asp:sqldatasource which is bound to a gridviewIn addition to this I would like it to a) see if there is a specif row/ item in it (ie item_id = 10 for any of the rows it has received) as I conditionally want to show another item outside of the gridview subject to if it is in the gridview or notb) show the sum of all the values within a certain column of returned rowsMany thanks
How do I get the result of this select into a variableDim sqldsFindUserId As SqlDataSource = New SqlDataSource sqldsFindUserId.ConnectionString = ConfigurationManager.ConnectionStrings("bluConnectionString2").ToString sqldsFindUserId.SelectCommandType = SqlDataSourceCommandType.Text Dim myUserIdCmd As String = "select pkUser from tblUsers where strDisplayName='" + myDisplayname + "" sqldsFindUserId.SelectCommand = myUserIdCmd ' The result of this select statement is to be stored in a variable, how do I do it?
hi all, in all my 2.0 learnings and books i keep coming across the page element <asp:sqldatasource>. I have always (in 1.1) used server side connections and adapters to bind my Sql datasets to any control needed. Now that im learning 2.0 im finding it difficult to understand using control on the page to bind my data. Can someone explain the benefits of using this data source? Ideally i would like to keep my data access layer separate from my presentation layer but i'd really like to understand why this method seems so popular. thanks in advance, mcm
Hi, I am new to ASP.NET 2.0 and I am trying to use VWB to bind my web site to a SQL Express edition. I used SQLDataSource to specify the .mdf file so I can connect to my tables but when I click on the advanced button to generate the Insert, Update, Delete SQL I find it grayed out and it cannot be clicked. I looked into several tutorials online and I couldn't find the problem, can anyone explain what I am missing or doing wrong? Any suggestion is very appreciated. Thanks
SELECT * FROM [CONTACTS] WHERE @ddl_value LIKE '%@txt_value%'
Why doesn't this not working I am using SQLdatasource control to bind a gridview If my query is wrong then what might be the correct one to work with like operator in the sqldatasource Can any ony help me!
Hello All, I have quick question .. In my aspx page i have gridview and Sql DataSource object as you can see <asp:sqldatasource id="SqlDataSource1" runat="server" ></asp:sqldatasource>
<asp:gridview id="GridView1" runat="server" allowpaging="True" allowsorting="True" autogeneratecolumns="False" datasourceid="SqlDataSource1"> <columns> <asp:boundfield datafield="breakdownid" headertext="breakdownid" insertvisible="False" readonly="True" sortexpression="breakdownid" /> <asp:boundfield datafield="ticketno" headertext="ticketno" sortexpression="ticketno" /> <asp:boundfield datafield="systemtype" headertext="systemtype" sortexpression="systemtype" /> <asp:boundfield datafield="break_date" headertext="break_date" readonly="True" sortexpression="break_date" /> <asp:boundfield datafield="subject" headertext="subject" sortexpression="subject" /> <asp:boundfield datafield="status" headertext="status" sortexpression="status" /> <asp:boundfield datafield="prioritylevel" headertext="prioritylevel" sortexpression="prioritylevel" /> <asp:boundfield datafield="mobiletype" headertext="mobiletype" sortexpression="mobiletype" /> </columns> </asp:gridview> In codebehind file i call a function to get data from database. what i want is to bind the result to the sqlDataSourse not the gridview. I need to have the SqlDataSourse thier.. Any help please
Sub Page_load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load If Not Page.IsPostBack Then Dim objReader As New Dynamic.Reportsdemo SqlDataSource1 = .objReader.Get_Tickets(1, 0, "all")
I am trying to get record from a table and verify it with a textbox i have a sqldatasource. i have a text box called txtEmail and this is my Select command. how can i get this working if its possible ? something like txtEmail.text = SqlDataSource1.Secect then ... my code. (i dont know if this a correct way to do this) <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>" SelectCommand="SELECT FROM [t_CustomerAcct]"
hi all,i am using sqldatasource for gridviewso that i can edit and update any row at the same place ..... and not to write any code for that....now later on if i wanna change the selectcommand of that sqldatasource how can i ? so that the edit and update will be same as it was....
Hi, I'm trying to go through a checkbox list and inserting them into a database using a sqldatasource with the following code:For Each li As ListItem In Locations_Checkbox.Items If li.Selected = True Then
Dim DataSource2 As SqlDataSource = CType(InsertProgrammeLocations, SqlDataSource) DataSource2.InsertParameters.Add("ProgrammeID", li.Value) DataSource2.Insert()
End If Next When more than one checkbox is ticked, I'm getting the error 'The variable name '@ProgrammeID' has already been declared'. How do I close or reset my Datasource before I try and use it again? Thanks for your help
Hi, I want to know when to use an sqlDataSource object because I used to populate for example a listBox control in visual studio net 2003 from AQLK server database by using a connection object, command object, dataAdapter object and a DataSet object. I discovered if I use sqlDataSource object (using visual studio net 2005) ,all I need is to write the connection string and a Select statement as properties for that sqlDataSource, so why to use the above mentioned objects(connection,command,adapter,dataset) while I can access any database in sql server through the use of sqlDataSource? I appreciate if you can explain to me the difference and when to use an sqlDataSource thx
Hi, I have an application w/ n-tier design so I've never used the SqlDataSource up to this point but having to do my own sorting for GridViews is not something I want to keep dealing with. I'd like to take advantage of some of the packaged features too. My question is: are there any purists out there who opted to use SqlDataSource. If so, what do you think about it? It's nice that SqlDataSource makes things simple but having data classes and dealing w/ exceptions in those classes certainly make an application pretty robust. Should I entertain the idea of using SqlDataSource or stay as a purist and keep doing things the old fashioned way?
Hey all,I've just started to use asp.net and so far have found it fairly easy. The only problem I've come across is with putting variable clauses into the SqlDataSources.This is one example of what I've done trying to get this to work. <asp:SqlDataSource ID="ClaimsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT TOP 20 dbo_ClaimStatus.ClaimStatus, dbo_CLAIMS.PACKET, dbo_INSURED.NAME, dbo_FEES.INSURER, dbo_FEES.CLAIM_NUMBER, dbo_CLAIMS.LOSS_DATE, dbo_CLAIMS.RECEIVED, dbo_CLAIMS.LOSS_LOCATION, dbo_CLAIM_TYPES.CODE FROM ( ( ( ( dbo_CLAIMS INNER JOIN dbo_CLAIM_TYPES ON dbo_CLAIMS.CLAIM_TYPE = dbo_CLAIM_TYPES.CODE ) INNER JOIN dbo_ClaimStatus ON dbo_CLAIMS.StatusID = dbo_ClaimStatus.ClaimStatusID ) INNER JOIN dbo_FEES ON dbo_CLAIMS.PACKET = dbo_FEES.PACKET ) INNER JOIN dbo_INSURED ON dbo_CLAIMS.PACKET = dbo_INSURED.PACKET ) WHERE dbo_ClaimStatus.ClaimStatus = @claimStatus"> <FilterParameters> <asp:QueryStringParameter Name="claimStatus" QueryStringField="ctl00$MainContent$claimStatus" /> </FilterParameters></asp:SqlDataSource>I'm posting claimStatus from a previous page using PostBackUrl on an <asp:Button /> tag and claimStatus is an <asp:DropDownList /> tag.Any idea's as to why this doesn't work?Nalum
Dear Friends in my project , i' ve used two SqlDataSource, one button and one gridview control; the process that i want to do is when i click the button control ,according the if structure in this button control ; one of the SqlDataSource control assign to the gridview control DataSource property (but i assign the DataSourceID property for the gridview , it runs); but i couldnt do it, what can i do for it? (my code doesnt give any errors but doesnt do anything too) my code is here; protected void btnSearch_Click(object sender, EventArgs e) { if (txtSearch.Text != null){ SqlDataSource3.SelectParameter["key"].DefaultValue = txtsearch.Text; Gridview1.DataSource = SqlDataSource3; GridView1.DataBind(); } else { SqlDataSource1.SelectParameters["keySession"].DefaultValue = cbSession.SelectedItem.ToString(); SqlDataSource1.SelectParameters["keyClass"].DefaultValue = cbClass.SelectedItem.ToString(); SqlDataSource1.SelectParameters["keyUnit"].DefaultValue = cbUnit.SelectedItem.ToString(); Gridview2.DataSource = SqlDataSource1; GridView2.DataBind(); }
Dear Friends in my project , i' ve used two SqlDataSource, one button and one gridview control; the process that i want to do is when i click the button control ,according the if structure in this button control ; one of the SqlDataSource control assign to the gridview control DataSource property (but i assign the DataSourceID property for the gridview , it runs); but i couldnt do it, what can i do for it? (my code doesnt give any errors but doesnt do anything too) my code is here; protected void btnSearch_Click(object sender, EventArgs e) { if (txtSearch.Text != null){ SqlDataSource3.SelectParameter["key"].DefaultValue = txtsearch.Text; Gridview1.DataSource = SqlDataSource3; GridView1.DataBind(); } else { SqlDataSource1.SelectParameters["keySession"].DefaultValue = cbSession.SelectedItem.ToString(); SqlDataSource1.SelectParameters["keyClass"].DefaultValue = cbClass.SelectedItem.ToString(); SqlDataSource1.SelectParameters["keyUnit"].DefaultValue = cbUnit.SelectedItem.ToString(); Gridview1.DataSource = SqlDataSource1; GridView1.DataBind(); } }
Could someone help me with syntax? Everything works until line 23. At that point, I get a NullReferenceException. When I examine the SqlDataSource object, everything inside reads "Object cannot be null" or "The pointer for this method was null." The SQLDataSource looks ok, just before executing line 23. Does anyone have a complete example of how to do this ADO operation? Thank you. 1 Dim fv As FormView = Nothing 2 Dim sds As New SqlDataSource 3 4 ' ===== Lookup connection string ===== 5 sds.ConnectionString = ConfigurationManager.ConnectionStrings("LocalSQLServer").ToString 6 7 ' ===== Define command statements ===== 8 cmdSelect = _ 9 "SELECT rowid, FRScrnLev, FRPgmLev, " + _ 10 "FRFName, FRLName, FRIDate, OverRide, OverRideDesc " + _ 11 "FROM PSB_Pre WHERE PSBid = @PSBid" 12 13 ' ===== Get handle to Formview ===== 14 fv = CType(MultiView1.Views(0).FindControl(ViewState(VS_VIEW).ToString), FormView) 15 16 ' ===== Complete definition of data source ===== 17 sds.SelectCommand = cmdSelect 18 sds.SelectCommandType = SqlDataSourceCommandType.Text 19 20 sds.UpdateCommand = cmdUpdate 21 sds.UpdateCommandType = SqlDataSourceCommandType.Text 22 23 sds.SelectParameters("PSBid").DefaultValue = CP.PsbId 24 25 ' ===== Bind data source to FormView. ===== 26 fv.DataSource = sds 27 fv.DataBind()
I have an sqldatasource that has a select statement and i just wanna pull a value out of it a single value row 0 colum 0 and assign it to a string the coloum is an interger in the database
Hi, i am trying to use a sqldatasource to update a table and when i go and update the table it puts a null value in for all of my fields. I am not sure what is happening and i have never used on of these before. Is there some code that i need to write to accompany the sqldatasource or am i just missing something. I have posted the code below. Thanks.<asp:GridView ID="grdSize" runat="server" AutoGenerateColumns="False" DataSourceID="sqlbk" Width="295px" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical"> <FooterStyle BackColor="#CCCCCC" ForeColor="Black" /> <RowStyle CssClass="grdsizerow" BackColor="#EEEEEE" ForeColor="Black" /> <EditRowStyle CssClass="grdsizeedit" /> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" /> <asp:TemplateField HeaderText="Diameter" SortExpression="Diameter"> <EditItemTemplate> <asp:TextBox ID="TextBox1" Width="60px" Height="16px" runat="server" Text='<%# Eval("Diameter") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("Diameter") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="HighLimit" SortExpression="HighLimit"> <EditItemTemplate> <asp:TextBox ID="TextBox2" Width="60px" Height="16px" runat="server" Text='<%# Eval("HighLimit") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("HighLimit") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="LowLimit" SortExpression="LowLimit"> <EditItemTemplate> <asp:TextBox ID="TextBox3" Width="60px" Height="16px" runat="server" Text='<%# Eval("LowLimit") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("LowLimit") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" /> <HeaderStyle CssClass="grdsizeheader" BackColor="#000084" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="#DCDCDC" /> </asp:GridView><asp:SqlDataSource ID="sqlbk" runat="server" ConnectionString="<%$ ConnectionStrings:qdbConnectionString %>" SelectCommand="Select ID, screwdia as Diameter , HighLimit, LowLimit from ScrewSize"
