Clue - Who Dun It? The Data Adapter, DataSet, SqlSelect Etc...
Feb 23, 2007
Ok, I know my connection string and config file are good. Once the rest of the code is added I receive this error:
"SQL Server does not exist or access denied. "
I'm using the 1.1 framework and my hosting company provides MSSql 2005.
Thanks for your time,
Charlie
Here's the code in my cond behind file.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace blog
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlConnection sqlConnect;
protected System.Web.UI.WebControls.DataList dList;
protected System.Data.SqlClient.SqlDataAdapter DA;
protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
protected System.Web.UI.WebControls.LinkButton linkAdmin;
protected System.Web.UI.WebControls.Image header;
protected blog.DS ds1;
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.sqlConnect = new System.Data.SqlClient.SqlConnection();
this.DA = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.ds1 = new blog.DS();
((System.ComponentModel.ISupportInitialize)(this.ds1)).BeginInit();
this.linkAdmin.Click += new System.EventHandler(this.linkAdmin_Click);
//
// sqlConnection String
//////////////////webserver////////////////
this.sqlConnection1.ConnectionString = "Server=xxx.xxx.xxx.x;Database=myDataBase;User ID=myID;Password=myPass";
//////////////////local///////////////////
//this.sqlConnect.ConnectionString = "workstation id=HAL;packet size=4096;integrated security=SSPI;data source=HAL;pers" +
//"ist security info=False;initial catalog=blog";
//
// DA
//
this.DA.SelectCommand = this.sqlSelectCommand1;
this.DA.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "blogEntry", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("bodyID", "bodyID"),
new System.Data.Common.DataColumnMapping("linkID", "linkID")})});
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT id, wxID, dateID, titleID, bodyID, linkID FROM blogEntry ORDER BY id DESC";
this.sqlSelectCommand1.Connection = this.sqlConnect;
//
// ds1
//
this.ds1.DataSetName = "DS";
this.ds1.Locale = new System.Globalization.CultureInfo("en-US");
this.Load += new System.EventHandler(this.Page_Load);
((System.ComponentModel.ISupportInitialize)(this.ds1)).EndInit();
I'm trying to figure this out I have a store procedure that return the userId if a user exists in my table, return 0 otherwise ------------------------------------------------------------------------ Create Procedure spUpdatePasswordByUserId @userName varchar(20), @password varchar(20) AS Begin Declare @userId int Select @userId = (Select userId from userInfo Where userName = @userName and password = @password) if (@userId > 0) return @userId else return 0 ------------------------------------------------------------------ I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value. But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0 passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters(); Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) ); Do you guys have any idea why?
I have a table that is returning rows from a table query. It seems I have done it before but I cannot seem to get the right procedure to obtain the values. I will paste in the code below in which you will see my bad attempts at accomplishing what I need.
Dim uid As String Dim pw As String Dim em As String, fn, ln, mi As String Dim par As String Dim Field, n, j As Integer Dim JJ As Integer
I hope someone has seen this one...I've got a very simple web method that returns a DataTable from my database. It worked fine until I changed this line of code:
string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? ORDER BY Timestamp desc";
To this line of code:
string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? AND CategoryID = ? ORDER BY Timestamp desc";
Here is the complete webmethod:
[WebMethod] public DataTable GetConfession (int ConfessionID, int CategoryID) {
DataTable dt = new DataTable(); dt.TableName = "XMLConfession";
string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? AND CategoryID = ? ORDER BY Timestamp desc";
using (OleDbConnection cn = new OleDbConnection (ConnectionString)) { using (OleDbCommand cmd = new OleDbCommand (SqlString, cn)) {
return dt; } To cause the error, all I did was add the AND statement into the query. Now I get this .NET error message every time I try to run this program:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at System.Data.Common.UnsafeNativeMethods.ICommandWithParameters.SetParameterInfo(IntPtr cParams, IntPtr[] rgParamOrdinals, tagDBPARAMBINDINFO[] rgParamBindInfo) at System.Data.OleDb.OleDbCommand.ApplyParameterBindings(ICommandWithParameters commandWithParameters, tagDBPARAMBINDINFO[] bindInfo) at System.Data.OleDb.OleDbCommand.CreateAccessor() at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior behavior, Boolean throwifnotsupported) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
I am using SQL Server Compact Edition 3.5 and C# .NET 2.0. It's worth noting that the web service does this outside of the program as well, so I don't think the problem is in the code that calls it. Also worth noting is that the query itself works fine when I substitute values in and run it in .NET Server Explorer.
I am a business user trying to build an incremental ETL package with SSIS. I have a working prototype on SQL Server 2005 where I select the max(ID) from the last successful run and pass that value into a variable. Then, in my Data Flow step, I select an OLE DB source adapter and use this variable in a custom select statement.
Here's my challenge....the live data is actually in a Postgres DB so I have to use a Data Reader Source adapter. When I try to pass my variable to this adapter the job bombs out. Does anyone know how to dynamically update the query text inside a Data Reader source adapter using variables or otherwise?
Hi, Quick question on how SSIS handles queries from Data Source in a Data Flow. I noticed that when I run a particular query from Query Analyzer it takes forever. But, when I run the same query in SSIS data source in a data flow. The query results are immediate.
The query plan is already cached in SQL.
Is this just something which I am seeing incorrect or is there some bit of optimization in there in SSIS. As per my understanding SSIS does not optimize the source query.
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
I have used both data readers and data adapters(with datasets) in the projects that I have worked on. I am trying to get some clarification on when I should be using which one. I think I am doing this correctly but I want to be sure I am developing good habits.
As the name might suggest, it seems like a datareader is for only reading data. I have read that the data adapter and dataset are for a disconnected architecture. Or, that they can be used for this type of set up. I have been using the data adapter and datasets when writing to a database and the datareader when reading from a database.
Is this how these should be used? Is the data reader the best choice for reading data? Am I doing this the optimal way from a performance stand point?
......................................................thanks in advance
i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer. but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
No clue whats causing this error,please help Server Error in '/learn' Application.
Unable to open the physical file "g:inetpubwwwrootlearnApp_DataPersonal.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".An attempt to attach an auto-named database for file g:inetpubwwwrootlearnApp_DataPersonal.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I have no idea what I'm doing in SQL Server 2000... honestly no DBMS. I have a book at home but am required to do a backup of about 20 databases today (updating software tonight). Can anyone help me out? I found this (http://www.databasejournal.com/img/backupdatabase.sql) script but do not know how to run it (query analyzer?).
hi i am having a hard time with two kinds of text files that have kind of 'repeating groups' in them...i want to loop it, but dont know how. one is a text file with a record length of 1200 bytes, but all 95601records are all on one row with no lf, cr or anything else between them, so i cannot feature how to get the forEach container to chop of a Right of claimchunk of 1200 bytes at a time, then go get the next 1200 bytes, because the items aren't stacked, they are adjacent to each other, if you see what i mean. the other text file has a record lenght of 52 bytes with 28 bytes filler, but this file also goes 'down and across', meaning that here, there are fourteen 'rows' in the file, and they have thousands of lines too, so this one also has to consume all the columns on the row before it moves to the next row. am i making this harder than it needs to be? thanks for any light
I need to get back the actual characters (without trailing blanks) contained in a char(43) field. So i tried: - substring(fieldname,1,len(fieldname)) which yields a 43 char column - using a variable that contains the actual length in substring(fieldname,1,@nchar)) the same. - tricking by concatenating the resulting string with a dummy like "" didn't work either. So apparently I am at a dead end. Anybody a clue, hint or solution???? Thanks to all contributors
Any help would be really appreciated.... My stored procedure...
CREATE PROCEDURE business3rd7 @Fromdate DATETIME, @ToDate DATETIME AS
select distinct CONVERT(VARCHAR(10),Receipts.Companynumber1)+CONVE RT(VARCHAR(10),Receipts.Companynumber2) as co , Receipts.Premium1+Receipts.Premium2 as Premium, "CAN"=case when Receipts.transactiontype='CAN' then (receipts.premium1+receipts.premium2) else 0 end, "NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN' then (receipts.premium1+receipts.premium2) else 0 end,
"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else 0 end,
------- "$NEW"= case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2) else 0 end, "#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else 0 end, "$RENEW"= case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2) else 0 end, "#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0 end, "$AP"= case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else 0 end, "#SENT"=case when policy.Renewalofferdate between @Fromdate AND @ToDate then count(policy.policynumber) end,
"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0 end =0 then 0 when case when policy.Renewalofferdate between @Fromdate AND @ToDate then count(policy.policynumber) else 0 end=0 then 0 else case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0 end /case when policy.Renewalofferdate between @Fromdate AND @ToDate then count(policy.policynumber) else 0 end end,
"Current Year"= case when policy.Renewalofferdate between @Fromdate AND @ToDate then count(clubamount) end, "Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @Fromdate) AND DateAdd(year, -1, @ToDate) then count(clubamount) end, "#AA"=count(receipts.clubamount), "$AA"=sum(receipts.clubamount)
FROM Receipts,Policy where Receipts.Agencyid=Policy.Agentid group by Receipts.CompanyNumber1,Receipts.CompanyNumber2, Receipts.Premium1,Receipts.Premium2, Receipts.TransactionType,policy.Renewalofferdate, Receipts.Agencyid
I am wondering is there any way to select only a portion of a data set to train the mining model? In this case, I mean we dont need to split the dataset in advance, what I want to do is being able to select any random portion of a selected dataset to train a mining model. Any advices?
I am looking forward to hearing from you and thanks a lot in advance for your advices and help.
Originally got this error as the reason some of my .rptproj files could not be converted from SRS 2000 to 2005. Now I€™m getting this error when checking out a report from Visual Source Safe 6.0€¦any clue as to what it means?
Project item '4294967294' does not represent a file.
This is for a solution created and stored in VSS using VS2003 and SRS 2000. Now trying to open with VS 2005.
Created a maintenance plan to backup my sharepoint databases. When I execute it the following error occurs: Execution failed. See the maintenance plan and SQL Server Agent job history logs for details: Additional Information: Job 'SharePointBackUp.Backup_SharePoint faild. (SqlManagerUI) - Execute maintenance plan. SharePointBackUP (Error) Messages * Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
When checking the Maintenance PlansharePointBackUP log it is empty! Under Job History I thinks this: Date 4/18/2008 12:55:35 PM Log Job History (SharePointBackUP.Backup_SharePoint) Step ID 1 Server DESD7 Job Name SharePointBackUP.Backup_SharePoint Step Name Backup_SharePoint Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed. line 1? Is that line one of the T-SQL statement? Because if that's the case then it's not because I copied and pasted the line in to a query and it executed without error. Under SQL Agent there are no entries! So what bloody log am I suppose to be checking?! This is very frustrating. I then copied and pasted every sql statement in to a query and THEY all ran just fine.
What's going wrong here, and how can I correct it?
I upsized some database tables for use in Visual Studio. When I add these data sources I see the data in all the tables except one. Any suggestion on why I can see data in all the other tables except tis one?
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.
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, I am trying to pull run this sql statement but it's bombing out at the comm.ExecuteNonQuery();. ..
Could someone help me figure this out.. Ex.System.Int32 bum = System.Convert.ToInt32(Request.QueryString["dum"]); SqlConnection conn = new SqlConnection("Data Source=**********************");SqlCommand comm = new SqlCommand(); comm.Connection = conn;SqlDataAdapter myadapter = new SqlDataAdapter(comm);DataSet myset = new DataSet(); conn.Open(); comm.CommandText = "Select * from Order_Forms where (Order_Num = " + Session["dum1"] + " ) ";
Hi,SQL Server 2000 SP3Has anyone ever successfully loaded data into SQL Server from a SASdataset. I have tried using DTS and SAS OLE DB drivers but get thefollowing errorError Description:A provider specific error occurred (%1:%2)Context:Error calling GetRowSet to get DBSCHEMA_TABLES schema info. Yourprovider does not support all the schema rowsets required by DTS.It does seem to me to be a problem with the OLE DB providers but ifanyone has seen this issue with DTS previously , ler me know......Any help is appreciated....Thanks in advanceReg
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.
I'm new at this so I apologize in advance for my ignorance.
I'm creating a website that collects dates in a calendar control (from Peter Blum). When the page containing the control loads it populates the calendar with dates from the database (that have previously been selected by the user). The user then can delete existing dates and/or add new dates.
I create a dataset when the page loads and use it to populate the calendar. When the user finishes adding and deleting dates in the calendar control I delete the original dates from the dataset and then write the new dates to the dataset. I then give execute the data adapter update command to load the contents of the dataset back into the database. This command involves using parameterized queries. For example the Insert command is:
Dim cmdInsert As SqlCommand = New SqlCommand("INSERT INTO Requests VALUES(@fkPlayerIDNumber, @RequestDate, @PostDate, @fkGroupID)", conn)
I am trying to combine like data from two different data sources into a single data set. Is there anyway I can do this? It seems like I can only add one data set, but is there some sort of workaround I could use?