I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value. This is the code for the Method to return a datareader
publicSqlDataReader GetOrgID() { Singleton s1 = Singleton.Instance(); Guid uuid; uuid = new Guid(s1.User_id); SqlConnection con = new SqlConnection(conString); string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID"; SqlCommand cmd = new SqlCommand(selectString, con); cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd;
adapter.Fill(dataset); return dataset;
}
Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well. So assume that the Guid is a valid entry..I should return a valid dataset but its null. Additionally if I change the sql query to just be Select * From aspnet_OrgNames I still get a null value...I am assuming I am doing something wrong trying to fill the dataset.
i have 50 tables. M trying to fill the table in dataset using a loop. no roblem for first 25 tables , but at 26th table it gives the error "Input string was not in a correct format.Couldn't store <value> in "column_name" Column. Expected type is UInt32.
column type is varchar(50) changed it to TEXT ....but nothing happened.
its taking numericals but no characters.
Debugged one by one... at 26th table.... adapter.fill(dataset) throws exception
I always get a "ConstraintException" error when trying, at beginning of application, to run following statement (within "Form1_Load" routine called by "this->Load" EventHandler):
Also curiously if, while application is still running, I invoke again that same statement by means of a pushbutton clickevent, everything is running smootly without error... Looks to me that it is bugging only when running the first time...
Within Dataset, I tried to see what constraint could give me such trouble. Here is the only constraint I could find:
I then tried to find within "TauxTaxe" table if there could be any trace of records where "Pays" and "Province_Etat" columns would show any null value as well as any duplicate key values but there wasn't any...
Any place I should start to look for? BTW, I'm using a SQL Express database.
Finally, SQL Statement for "TA_TauxTaxe::FillByPays" is the following:
SELECT Pays, Province_Etat, Taxe1_Appl, Taxe1_Dsc, Taxe1_Taux, Taxe2_Appl, Taxe2_Dsc, Taxe2_Taux FROM TauxTaxe WHERE (Pays = @Pays)
In debug mode, I double-checked and could verify that @Pays didn't have any null value but a valid string value at time "Fill" routine was invoked. Any clue?
Hi, What is the difference b/w sqldatareader and sqldataadapter? For what purpose are they used in a database connection & how do they differ from each other? Pls explain me in detail.Regards Vijay.
Why DataAdapter does NOT load relations from DB ?the relations are made using SQL SERVER MANAGEMENTbut when a fill a datatable using dataadapter the relations are not loaded .why ? what is the solution to this problem ?
Hi i'm a new to ASP.NET and for some reason when i click the Next button in the code below, the pageIndex does not change. Please assist, Basically what i'm trying to do is to use DataAdapter.fill but passing in the start index and the number of records to pull from the dataset table. using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OleDb; public partial class Home : System.Web.UI.Page { //ConnectionOleDbConnection dbConn; //discount that can be change by user using a gui interface //CurrentPageint pageIndex = 0;double discount = 0.15 ; protected void Page_Load(object sender, EventArgs e) { // homeGridView.Visible = true;
BindList();
}protected string getSpecial(string price,object sale) {String special = "";if (sale.ToString().CompareTo("True") == 0) {special = String.Format("{0:C}",double.Parse(price) * (1-discount)); }return special; } protected void BindList() { //Creating an object for the 'PagedDataSource' for holding the data.
//PagedDataSource objPage = new PagedDataSource(); try { //open connection openConnection(); //sql commandstring columns = "*"; string SqlCommand = "Select " + columns + " from Books"; //create adapters and DataSetOleDbDataAdapter myAdapter = new OleDbDataAdapter(SqlCommand, dbConn);DataSet ds = new DataSet("bSet");
//create tableDataTable dt = new DataTable("Books");myAdapter.Fill(ds, pageIndex, 9, "Books");
Response.Write("Page Index: "+pageIndex); //create table data viewDataView dv = new DataView(ds.Tables["bTable"]); booksDataList.DataSource = ds; booksDataList.DataBind();
I have done some reading on the dataadapter class, but do not have a good handle on how to work with dataadapter after it has been filled by a dataset. For instance, should I use an if statement such as this to update the value in the dataadapter? if (dsSunbelt.Tables[0].Columns[39].Equals("")) { DataRow drUpdate = dsSunbelt.Tables[0].Columns[39].Equals(DateTime.Now); } I am sure it is not hard to see what I am doing, and I hope that I am missing something small, but how do I isolate a dataadapter value to update in the corresponding database?
Hi, I am populating a datagrid with data from one sql table, I add additional columns and then wish to write it back out to another table. I have written the following code to acheive this, however it keeps throwing the follwing exception - "Update requites a valid UpdateComman when passed a DataRow collection with modified rows" I've looked and I can see what the problem is, can anybody help please? Private Sub FillData() 'SQL connection Me.sqlconnection = New System.Data.SqlClient.SqlConnection Me.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")
Dim sql As String If Request("action") = "gen" Then sql = "select item from tblPickList where GroupName = 'xxx'AND Category = '" & Category2 & "' ORDER BY item ASC" ElseIf Request("action") = "fav" Then sql = "select identifiertext as item from tblfavourites where HRID = " & Session("hrid") & " and type = 3 ORDER BY identifiertext ASC" ElseIf Request("action") = "spec" Then sql = "select item from tblPickList where GroupName = '" & GroupID & "' AND Category = '" & Category2 & "' ORDER BY item ASC" End If
ds = New DataSet da = New SqlClient.SqlDataAdapter(sql, sqlconnection)
Try sqlconnection.Open() da.Fill(ds, "tblPickListTemp") sqlconnection.Close() Catch ex As Exception sqlconnection.Close() End Try
ds.Tables(0).Columns.Add("HRID") ds.Tables(0).Columns.Add("Flag") 'may possibly add a favourites column here at a later date to ensure no duplication
Dim dr As DataRow For Each dr In ds.Tables("tblPickListTemp").Rows dr("HRID") = Session("hrid") dr("Flag") = 0 Next
DgrdFavourites.DataSource = ds DgrdFavourites.DataBind() End Sub
Public Sub CreateTempRecords()
'SQL connection Me.sqlconnection = New System.Data.SqlClient.SqlConnection Me.sqlcommand = New System.Data.SqlClient.SqlCommand Me.da = New SqlClient.SqlDataAdapter Me.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")
Dim sql As String sql = "Insert into tblPickListTemp (Item, HRID, Flag) values (@Item, @HRID, @Flag)" sqlcommand.CommandText = sql sqlcommand.Connection = sqlconnection
sqlcommand.Parameters.Add("@Item", "Item") sqlcommand.Parameters.Add("@HRID", "HRID") sqlcommand.Parameters.Add("@Flag", "Flag") da.InsertCommand = sqlcommand da.Update(ds.Tables(0)) End Sub
I'm reading XML data into a DataTable that is populated into a datagrid. From here I need to update database and wanted to know how to Fill a DATA ADAPTER with a DATA TABLE? I'm familiar with updating using a SQL command, but in this case, i have the dataTable already created from XML. Please help. Thanks,
SQL Server does not exist or access denied. 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: SQL Server does not exist or access denied.Source Error:
Line 41: DataSet dataset = new DataSet();Line 42: Line 43: dataAdapter.Fill(dataset, "Products");Line 44: SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);Line 45: Source File: e:asp.net egisteration egister.aspx.cs Line: 43 Stack Trace:
SqlDataAdapter dataAdapter = new SqlDataAdapter(strCommand, strConnection);
DataSet dataset = new DataSet();
dataAdapter.Fill(dataset, "Products");
SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);
DataTable dataTable = dataset.Tables[0];
dgCustomer.DataSource = dataTable;
dgCustomer.DataBind();
} }
when i run this code, error like this appear :
Server Error in '/Registeration' Application.
SQL Server does not exist or access denied. 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: SQL Server does not exist or access denied.Source Error:
Line 41: DataSet dataset = new DataSet();Line 42: Line 43: dataAdapter.Fill(dataset, "Products");Line 44: SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);Line 45: Source File: e:asp.net egisteration egister.aspx.cs Line: 43 Stack Trace:
this does not work, it does work however if i replace the Me.textboxhello.Text with a value in quotation marks or with another text box not linked to that feild in the table... it does not work with the relevant text box!!!!!!!!
Hy, again! I am at the begining of an application. I have some modules that insert, delete and update only one row at one table. My question is should I use dataadapter.Update() or ExecuteNonQuery(). I prefer ExecuteNonQuery because I want to build a class : DataLayer to implement my own InsertProcedure(), UpdateProcedure(),DeleteProcedure(). I want speed in my application, so which is the best: dataadapter.Update() OR ExecuteNonQuery(). Thank you!
Good day, I just like to ask if anybody has experienced getting empty rows from SQL data adapter? I'm using SQL Server 2005. Problem is when the sql is run on Query Analyzer it retrieves a number of rows but when used in my application it returns 0 or empty rows. I thought the connection is not the problem since I got my columns right. Below is my code snippet. Thanks! const string COMMAND_TEXT = @"select distinct somefield as matchcode, count(somefield) " + "as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1"; SqlDataAdapter adapter = new SqlDataAdapter(COMMAND_TEXT, connection); DataTable dt = new DataTable(sometablename); adapter.Fill(dt);
For some reason I've had trouble with this today... can anyone provide me with a generic code snippet that programatically allows me to select data from a database limited by a parameter and then fills a datatable?
I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?
I'm using DataAdapter.Update() to update data in a table. My question is; how do I create a trigger that works after the update has completely finished?
For example if my update adds 50 new rows to a table the trigger I've currently got fires after each new row that is added ie 50 times in total. Is it possible to get it to trigger after the last (ie 50th) row is added???
I am trying to insert data into 2 different tables. I am using dataadapter and dataset.
Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click Call ConnectionString()
Dim insertSQL As New SqlCommand() insertSQL.Connection = sqlConn insertSQL.CommandText = "SELECT location.CountryName, location.CityName, location.BuildingName, location.FloorID, rooms.name, rooms.FloorID AS Expr1 FROM location INNER JOIN floors ON location.FloorID = floors.id INNER JOIN rooms ON floors.id = rooms.FloorID"
Dim ds As New DataSet() Dim da As New SqlDataAdapter()
da.SelectCommand = insertSQL Dim scb As New SqlCommandBuilder(da)
Try da.Fill(ds) Dim ndr = ds.Tables("location").NewRow Dim ndr2 = ds.Tables("rooms").NewRow
I am reading multiple databases into a single dataset, putting each read into a new datatable in the set. Ultimately, I want to merge the multiple tables (they are all from different DB tables in different locations using the same schema). I am also combining this with data from an Oracle database which is in yet another table in the same dataset.
Here is my question:
I will not be propagating any changes to this data back to its original source, but I will be populating it out to Active Directory. (Don't worry; I'm not asking any AD questions here.) But I do want to run SELECT, DELETE, and UPDATE queries against the data in the dataset in memory without writing it out to a separate file. Based on that type of usage, would I be better to use a DataReader or a DataAdapter to do the read from the original database? If I use a DataReader, can I still update the data in memory? In either case, what's the correct methodology to update data in a dataset in memory?
My DataAdapter.Update() Method is running this query against my database and all the parameters and the formatting looks correct to me. I was wondering if anyone could identify obvious errors.... Thanks!
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1: Small table without CLOB, 10 rows. Table 2: Large table with CLOB, 10,000,000 rows
select CLOB from table2 where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
My task is to bind and show 3 different values coming from three different queries into three different columns of GridView. I had done this as mention in below. Program was successful. But I want to excute these three queries in same Stored Procedure. I can do that and stored in seperated variables. I need help how to call these three different values in data adapters and store each value in three different columns of grid view.
Simply I want to below statement in stored procedures and call from program. Can any one help me plz.
con = DataBaseConnection.GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con); da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da.Fill(ds,"Dep");
SqlDataAdapter da1 = new SqlDataAdapter("select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID", con); da1.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da1.Fill(ds,"Exp");
SqlDataAdapter da2 = new SqlDataAdapter("select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID", con); da2.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da2.Fill(ds,"Inc");
// This is another way I had tried. But failed. I am getting Index out of bound error. Can any solve this if possible to u. SqlCommand cmd = new SqlCommand("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con); cmd.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"]; GridView1.Rows[0].Cells[1].Text = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID"; GridView1.Rows[0].Cells[2].Text = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID"; GridView1.Rows[0].Cells[3].Text = cmd.ExecuteScalar().ToString();
ASPX Code for Grid View <h2>Account Summary</h2><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" emptydatatext="There are no data records to display." Width="238px" > <Columns> <asp:TemplateField >
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
Hello All I am wanting to fill a drop down list in ASP.NET using C# from a SQL database table using a stored procedure. I have my Sproc. But using ASP.NET C# I have no idea how to do this. Can someone give me a good example, and if not too much trouble, place comments in the code, and give an explanation. I am just learning ASP.NET after moving from Classic. Things are alot different.
I have a ton of data to load into a SQL 2005 database. I just loaded a bunch of data for a number of tables using bcp, and the last table that my script loaded was an 8 million row table. The next table was a 12 million row table, and about 1 million rows into the bcp'ing a log full error was incurred. I have the batch size set to 10000 for all bcp commnads. Here is the bcp command that failed:
Here is the last part of the output from the bcp command:
... 10000 rows sent to SQL Server. Total sent: 970000 10000 rows sent to SQL Server. Total sent: 980000 10000 rows sent to SQL Server. Total sent: 990000 SQLState = 37000, NativeError = 9002 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'billing_data_repository' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
BCP copy in failed
I thought that a commit was issued after every 10000 rows and that this would keep the log from filling up.
The log_reuse_wait_desc column in sys.databases is set to 'LOG_BACKUP' for the database being used.
Does a checkpoint need to be done more often?
Besides breaking up the 12 million row data file into something more manageable, does anyone have a solution?
How can I continue to use my same loading script, and keep the log from filling up?
MS SQL Enterprise Server, SP5, running under version 6.5.
I have recently been having a problem with the TempDb database filling up. I originally started the database at 250 Mb but recently expanded it to 500 Mb.
My last check of the activity on the server during an event such as this produced the following information.
- Approx. 300 connections to primarily 2 databases.
- 4 active connections:
Connection 1 -SELECT on database 1 with 13,000 records and a record size of approx. 300 bytes.
Connection 2 -SELECT on database 1 with 13,000 records and a record size of approx. 300 bytes.
Connection 3 -SELECT on database 2 with 550 records and a record size of approx. 100 bytes.
Connection 4 -Replication subscriber set at 100 transactions.
My questions are:
1. What processes may cause the TempDb database to fill up?
2. What processes prevent the database from purging?
We are having continual problems with our transaction log filling up on one of our major applications. Does anyone know of a way or tool to read the transaction log? We want to determine what is causing this problem.
