I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.
I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:
set @FullQuery = 'Select ' + @FieldsinOrder + ' from tblExample'
exec (@FullQuery)
This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.
If I change the query to be a normal select it works. For example:
select * from tblEmample
That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?
Hi, I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!! What's going on? Any thoughts highly appreciated. t.i.a.,ratjetoes.
I need to display something like "Results x-y of z." The problem is, I can't figure out how to get the right value for z.I am using SqlDataAdapter and Fill()ing a DataSet with subset x through y of the result set. How can I get the right value for z?
I'm trying without success to do a select command against the dataadapter and then fill a dataset. I'm using VS 2008, and I've set my project to the 2.0 framework. What is wrong is I get an empty dataset (The count on the dataset = 0. )with the code below. The database is connecting fine, so I assume it's something to do with the commandtext. The database is SQL CE 3.5.
Any ideas would be appreciated.
Code Snippet
Public Shared Function Init() As DataSet 'Todo: need to encrypt the password Dim instance As New SqlCeConnection("Data Source=|DataDirectory|PBdata.sdf") Dim selectcmd As SqlCeCommand = instance.CreateCommand selectcmd.CommandText = "Select * From [dusers]" Dim adp As New SqlCeDataAdapter(selectcmd) Dim ds As New DataSet("dusers") adp.Fill(ds)
A - Must Declare variable @ZipEntry - error generates when I run the code below. If I hard code the "WHERE ZipCode = 72364" it works (display messed up but it works - don't know how to code in the line breaks yet). But when I try to pass in the value of a TextBox called txtZipEntry on the page I get this error. Am I not delcaring @ZipCode in the correct spot ? Thank you, public partial class ContractorResultsDisplay : System.Web.UI.Page{string ZipEntry;protected void Page_Load(object sender, EventArgs e){ ZipEntry = txtZipEntry.Text;SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);string sql = "Select [City], [StateCode], [AreaCode], [Latitude], [Longitude], [ZipCode] From [ZipCensus11202006] Where ZipCode > @ZipEntry";SqlCommand cmd = new SqlCommand(sql, con);cmd.Parameters.AddWithValue("@ZipEntry", ZipEntry);SqlDataAdapter da = new SqlDataAdapter(sql, con); DataSet ds = new DataSet();da.Fill(ds, "ZipCodeRefs");StringBuilder htmlStr = new StringBuilder(""); foreach (DataRow dr in ds.Tables["ZipCodeRefs"].Rows){htmlStr.Append(dr["City"].ToString());} ZipContent.Text = htmlStr.ToString(); } }
The following is NOT filling the dataset or rather, 0 rows returned.....The sp.....CREATE PROCEDURE dbo.Comms @email NVARCHAR ,@num INT OUTPUT ,@userEmail NVARCHAR OUTPUT ASBEGIN DECLARE @errCode INT SELECT fldNum, fldUserEmailFROM tblCommsWHERE fldUserEmail = @email SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeENDGOAnd the code to connect to the sp - some parameters have been removed for easier read..... Dim errCode As Integer Dim conn = dbconn.GetConnection() Dim sqlAdpt As New SqlDataAdapter Dim DS As New DataSet Dim command As SqlCommand = New SqlCommand("Comms", conn) command.Parameters.Add("@email", Trim(sEmail)) command.CommandType = CommandType.StoredProcedure command.Connection = conn sqlAdpt.SelectCommand = command Dim pNum As SqlParameter = command.Parameters.Add("@num", SqlDbType.Int) pNum.Direction = ParameterDirection.Output Dim pUserEmail As SqlParameter = command.Parameters.Add("@userEmail", SqlDbType.NVarChar) pUserEmail.Size = 256 pUserEmail.Direction = ParameterDirection.Output sqlAdpt.Fill(DS) Return DS Like I said, a lot of parameters have been removed for easier read.And it is not filling the dataset or rather I get a count of 1 back and that's not right.I am binding the DS to a datagrid this way.... Dim DScomm As New DataSet DScomm = getPts.getBabComm(sEmail) dgBabComm.DataSource = DScomm.Tables(0) And tried to count the rows DScomm.Tables(0).Rows.Count and it = 0Suggestions?Thanks all,Zath
Hi,I am unable to load data in dataset via sqlconncetion object. Always i get error at ds.fill.Thanks in advance. PLS Help.Sudipta Datta www.geocities.com/dattasudipta
i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...
One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.
But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.
this may seem like a real newbie question, but I got no clue how to solve this problem. When I was working in Visual Studio 2003, whatever I wanted to fill a dataset with data, I was using a SqlDataAdapter that I created like this adapTest.Fill(dsTest); But now, I cant seem to find the sqldataadapter anywhere, I can just create (on the visual interface of one of my page of my aspx project) dataset, but cant find anywhere a adapter to put an SQL instruction into...maybe theres a new way to do this in Visual Studio 2005 that I dont know about. Im not sure if I was clear enough, but what I want in the end is to be able to use my dataset like that dsTest.Tables.Rows[0]["a column"].ToString()by filling the dataset with data like I was able to do in VS2003. Thanks for taking the time to read this.
Hello, I have been reading about how you shouldn't build dynamic SQL statements (see TextBox1.Text in line 3) and should use parameters instead, but I haven't yet found how to create a SELECT statement with a parameter that fills a dataset. If anyone can show me the correct way of doing this I would appreciate it so I can add it to my code snippets for proper coding practices. Thanks in advance for any assistance. string strConnectionString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnectionString); -> string sqlSelect = "select * from customers where city = " + "'" + TextBox1.Text + "'"; SqlDataAdapter da = new SqlDataAdapter(sqlSelect, myConnection); DataSet ds = new DataSet(); myConnection.Open(); da.Fill(ds, "myDataset"); myConnection.Close();
I have an off the wall situation that occurs when I try to fill a dataset that has already been filled with data. The issue with the DataSet in and of itself was a mistake I made and is not relevant to this forum.
What would appear to be relevant though, is the fact that after the exception was caught, I could not longer query data from the SQL Express database that was included in my project. In other words, when I tried to view the data in the IDE by selecting "Show Table Data," I received a message telling me that the query was running and eventually it timed out.
To fix the problem in my application, I obviously eliminated the bad code. But to be able to query the database again, I had to physically restart my computer (yes, I probably could have restarted the service, I know).
So my question is, has anyone run into this phenomenon before? If so, is there a way to prevent it other than the obvious, which is to clear the DataTables in my DataSet before filling them?
It's not that big a deal, but it is kind of a pain in the rear to have to restart everything because of a stupid coding mistake on the client side while I'm developing an app.
It's well known issue, that one can't use any dataset fields in a report header/footer directly. One of the approach is to create query-based parameter that basically equals =First(Fields!@FieldName@.Value, "@DataSetName@") and use that parameter value instead. But it doesn't work in my case!
My report displays some entity description and is parametrized with EntityID param. Its header contains entity name that, according to the approach, is queried from the data source through the EntityName report parameter. There's important issue: the report is displayed in ReportViewer control, that is embedded into my application and entity ID parameter isn't ser by user in ReportViewer parameters area. Its default value is changed by the application with SetReportParameters() web method every time a user wants to view the report according to the entity the user is exploring in the application. But after the report has been rendered, its header always contains not actual (outdated) entity name. Nevertheless, the report body contains actual data (including entity name). If I alter entity ID parameter in ReportViewer or in web-based Report Manager and refresh report, header displays correct entity name.
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL , [DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode 0 and SQLState 08S01, to a StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
I 've done a store procedure(SP), one for 7 report's. The problem that i have is that the dataset doesn't refresh cols from the SP, how can i do ..i need all the columnsi n order to put them in the report.
create table data_set (id int primary key, col1 varchar(10)) go insert into data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a') select * from data_set
I tried this below
Declare @child_ids int @col_val varchar @count int
select @child_ids, @col_val, @count, count(col1) as records from data_set group by col1 order by col1
I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet. For example: 'oConn is defined elsewhere... Dim sErr as String = "" Dim dt As New DataTable If Not oConn Is Nothing Then Try Dim sSQL as String = "select 1" Dim oCommand As New OdbcDataAdapter(sSQL, oConn) oCommand.Fill(dt) Catch ex As Exception sErr = "Database Error: " & ex.Message Finally sqlCloseConnection(oConn) End Try End Ifthis works fine and my dt DataTable object gets one row. However using this as the SQL: Dim sSQL as String = "declare @foo table(mycol integer);insert @foo select 1;select mycol from @foo;"does not work. It executes with no errors, but the DataTable has no rows. Finally, if I replace the DataTable with: Dim ds as DataSetI can then get the data in ds.Tables(0) no problem.So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?When a sql is a simple select statement it always works directly to a DataTable. Only when it's a SP or sql with some logic does it require the DataSet approach. This is a reporting utility so I need to standardize the code though the sql will be dynamic. Any ideas?
Ok, I've spent a good amount on time on debugging an unupdating scenario in my application. Finally I knew the reason which is very annoying. Either I'm missing something really obvious (I hope so,) or this is a bug.
to reproduce what I'm talking about: 1- create a new win forms application using VS2005 sp1 2- add some SQL Compact Edition data file that have some records from the data menu, you'll get the designer to generate the dataset and everything.. 3- drag a table from the data sources window, you'll get the data grid and the navigator on the form 4- add a button and have this in the click event handler: datasetname.Clear(); TableAdapterName.Update(datasetname);
launch the program, click the button, you'll see the grid get wiped out as it supposed to do. close the program and relaunch. the data is there again (this has nothing to do with the copy always, copy if newer infamous stupidity)
now: 5- edit the click event handler and change it so something like this: foreach (datasetname.TableRow row in datasetname.Table) { row.Delete(); } TableAdapterName.Update(datasetname);
Launch the program, hit the button, grid wiped out. exit and relaunch. You'll see no data (i.e. the update on table adapter worked alright)
You can also try the GetChanges method on the dataset rightafter you use the clear method and you'd get no deleted records at all.
So, in 100,000+ records dataset, if i need to wipe the thing out and add some new records do i have to loop over every record and call delete (which will take LOTS of time). I do hope that I'm missing something obvious.
I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report. However, I only want to select specific data from the dataset to fill textboxes and others. I cannot change the stored procedure, but the sample of the data is shown below:-
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories. In my first table, I tried putting the cells as follows:- (expressions are highlighted in italics and bold)
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value. Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
and it give me an error The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
the Dataset in my report doesn't update the fields' values from the database. I refresh the dataset manualy in the SQL Server Business Intelligence Development Studio to see the new values in the report.
I am currently doing a mapping from text file to sql table. there is one column in the text file that contain more than 15,000 characters in it. it causes me an error because i was using varchar data type, in which i believe can only store 8000 char. can anybody tell me which data type to use? and also, will i be able to change the data type in the input column also? if yes, which data type should i change it to?
I want to read a String - character by character.I mean If the string is 'SAMPLE' then I want to go to each and every character in it 'S','A','M','P','L','E' to compare with another string for equality. I hope I am clear.Is there a way that I can read it?
I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length.
Here are the function and the stored procedure wher eit occurs:
CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
RETURNS @Results TABLE (Item nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
Basically I need to insert underscore in between a string when there is a space in the content of the string. For Example say we have string 'ABC XYZ', I've to convert it into something like this 'ABC_XYZ'. Some strings do not have space in between and I should not add underscore in such cases. I'm working with MSSQL Server 2008 version.
I simply need to separate the string for each row by the & character and then I'm assuming i ll be able to COUNT and GROUP BY the occurrences of each separate value in order to find the most commonly used inputs.I have a column Variables in the table Functions, that contains a string of values separated by the & character that shows the inputs each student inserted into a function.
How would I go about splitting that string without the use of a function or stored procedure and the find the most commonly used variables? (I was thinking the latter part could be easily solved with a COUNT(*) and appropriate GROUP BY.)
