Table Adapter Help
Jun 9, 2008
Can someone tell me why I an getting the following error message for my table adapter: "incorrect syntax near '?'"
The selecte statement is below.
SELECT Questions.QuestionID, Questions.QuestionNumber, Questions.QuestionText,
Questions.SampleAnswer,
SAFSection.SectionNumber + ' - ' + SAFSection.SectionName as [Section],
SAFSubSection.SubSectionNumber + ' - ' + SAFSubSection.SubSectionName as [SubSection],
SAFSubSection.SubSectionDefinition, SAFSubSection.SubSectionQuestion,
SAFSubSection.SubSectionInstruction, Answers.Answer
FROM (SAFSubSection INNER JOIN (SAFSection INNER JOIN Questions ON SAFSection.[SectionID] = Questions.[QuestionSectionID]) ON SAFSubSection.[SubSectionID] = Questions.[QuestionSubSectionID]) LEFT JOIN Answers ON Questions.QuestionID = Answers.QuestionID
WHERE Answers.SystemID = ?
Order BY SAFSection.SectionID, SAFSubSection.SubSectionID,Questions.QuestionID
View 2 Replies
ADVERTISEMENT
Jan 21, 2008
I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
View 9 Replies
View Related
Apr 24, 2008
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.
main.ds.MailsSignature.Clear();
main.ds.MailsSignature.AcceptChanges();
string[] signFiles = Directory.GetFiles(Settings.signDirectory);
foreach (string signFile in signFiles)
{
mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();
mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.
main.ds.MailsSignature.Rows.Add(mailsSignatureRow);
}
mailsSignatureTableAdapter.Update(main.ds.MailsSignature);
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Is there any limitation in CF?
Regards,
Professor Corrie.
View 3 Replies
View Related
Dec 20, 2007
Hello,
I have a table adapter which uses a Query string to get the select view and a stored procedure for its delete portion. Not all of the columns in the select statement are required for the stored procedure. When I try to do the delete, it attempts at passing in more variables than it needs and ends up resulting in an error saying it can't find a procedure that is valid. Is there any way to restrict which columns are being passed?
Thanks,
Chris
View 1 Replies
View Related
Nov 26, 2007
Now, I don't know if what I want to do is possible, but here goes. In the table I want to query, there is an "approval status" column, of type Int32. There are four approval levels, 1, 2, 3 and 4. What I want to set up is a query in the table adapter that can return all entries of one or more approval levels. In "raw" sql, I would do something like: SELECT * FROM facility_table WHERE (approved IN (1,2,3));What I want to do though, is to have the list of approval codes to be a parameter that I can pass to the table adapter query, so the where clause becomes "WHERE (approved IN (@approval))", and I pass a string with the list of approval codes. But the query designer doesn't want to cooperate with me, as it insists that "@approval" should be an int32.Any suggestions?
View 1 Replies
View Related
Feb 9, 2007
I have an application (ASP.NET 2.0/SQL Server 2005) which makes heavy use of table adapters for pulling records from SQL. Under heavy load, we get a lot of SQL Server Timeout errors. We have run a trace on SQL Server and it shows that several of the SQL statements being passed into SQL Server, from the Table Adapters, have bad SQL.
For example, here is the SQL in one of the table adapters
SELECT HomeMsgID, messageName, messageHTML, messageText, populationID
FROM MyUCR_HomeMessages
WHERE (populationID IN
(SELECT populationID
FROM MyUCR_Population_CPID AS
MyUCR_Population_CPID_1
WHERE (CPID = @CPID))) AND (isVisible = 1)
AND (showDate <= @showDate) AND (removeDate >= @removeDate)
I call it with the following:
DateTime showDate = DateTime.Today;
DateTime removeDate = DateTime.Today;
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter ta = new
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter();
myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable dt = new
myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable();
ta.FillByCPID(dt, showDate, removeDate, CPID);
What the SQL trace shows, when it fails, is this (notice the extra single
quotes around the showDate, removeDate parameters):
E000
exec sp_executesql N'SELECT HomeMsgID, messageName, messageHTML,
messageText, populationID
FROM MyUCR_HomeMessages
WHERE (populationID IN
(SELECT populationID
FROM MyUCR_Population_CPID AS
MyUCR_Population_CPID_1
WHERE (CPID = @CPID))) AND (isVisible = 1)
AND (showDate <= @showDate) AND (removeDate >= @removeDate)',N'@showDate
datetime,@removeDate datetime,@CPID int',@showDate=''2007-02-05
00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3071225
1[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
I recreated the SQL to use a stored procedure, and got a similar error:
E000exec dbo.spFillHomeMsgByCPID @showDate=''2007-02-05
00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3008195
5[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
However, if I create dynamic SQL and use the following, there are no errors.
string mySql = string.Empty;
mySql = "SELECT HomeMsgID, messageName, messageHTML, messageText,
populationID FROM MyUCR_HomeMessages WHERE (populationID IN (SELECT
populationID FROM MyUCR_Population_CPID AS MyUCR_Population_CPID_1 WHERE
(CPID = " + CPID + "))) AND (isVisible = 1) AND (showDate <= '" + showDate +
"') AND (removeDate >= '" + removeDate + "')"; SqlDataAdapter adapter = new
SqlDataAdapter(mySql, ConfigurationManager.ConnectionStrings["MyUCR2007ConnectionString"].ToString());
DataSet RecordCount = new DataSet();
adapter.Fill(RecordCount);
DataTable testDT = RecordCount.Tables[0];
I am using VSTS with the Service Pack installed. SQL 2005 is running on W2K3 Enterprise, fully patchedThanks,James
View 1 Replies
View Related
Apr 15, 2008
Hi All,
I would like to send a query via Table adapter using parameter that is not the whole field. (Its only a part of the filed).
I will try to illustrate my needs:
We have a list of items in a data grid.
I would like to get the list of items who are answering my item name search query.
(I need to change the list every time the user enter another key letter for the search).
I would like to do so in the data adapter, but I'm not able to use the LIKE statement.
Please please try to help me
P.S.
Do I need to use stored procedure, if so, can anyone give me an idea how to do so ?
View 1 Replies
View Related
May 23, 2007
I'm looking for a way to pass an array of values as a parameter to a query in a table adapter. For example I want to run a query something like:SELECT * FROM menu WHERE menu_role IN (@roles)And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @roles parameter.For some reason I can't figure out a way to do this. Any help would be greatly appericated.Thanks,Ryan.
View 6 Replies
View Related
Sep 27, 2007
I have an SP that I call via table adapter. If I do not pass a value in the string field ('eventRef'), the SP never gets called - it appears that the table adapter is filtering it...? I need to be able to count records that have an 'eventRef' = to the one I pass, including null. This works fine if I pass an 'eventRef', but never runs if that parm is empty.
Here is the TA definition
<asp:ObjectDataSource ConvertNullToDBNull="true" ID="ObjectDataSource1" runat="server" SelectMethod="GetData" TypeName="ImagesByEventTableAdapters.PagedImagesByEvent" OnSelecting="ObjectDataSource1_Selecting" OnSelected="ObjectDataSource1_Selected">
<SelectParameters>
<asp:QueryStringParameter Name="CategoryID" QueryStringField="CategoryID" Type="Int32" DefaultValue="24"/>
<asp:QueryStringParameter Name="EventID" QueryStringField="EventID" Type="Int32"/>
<asp:QueryStringParameter Name="EventRef" QueryStringField="erf" Type="String"/>
<asp:QueryStringParameter Name="PageIndex" QueryStringField="PageIndex" Type="Int32" DefaultValue="0"/>
<asp:Parameter Name="NumRows" Type="Int32" DefaultValue="16"/>
<asp:Parameter Name="ImageCount" Direction="Output" Type="Int32" DefaultValue="0" />
</SelectParameters>
</asp:ObjectDataSource>
And the SP:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PagingGetProductsByEvent]
@CategoryID INT,
@EventID INT,
@EventRef CHAR(10)=NULL,
@PageIndex INT,
@NumRows INT,
@ImageCount INT OUTPUT
AS
BEGINdo my stuff....
View 1 Replies
View Related
Apr 14, 2008
Hi All,
I'm trying to use a very simple SQL String that check if a specific string exist in the DB.
When I do the SQL and use a regular Connection I get the result.
When I use a data set and on the table adapter I need to use the "LIKE %" method I get an error.
What should I write on the table adapter in order to make this search?
ItemID is a type string.
This is my SQL:
SELECT itemID, itemName
FROM Items
WHERE (itemID LIKE @ItemIDTemp)
This sample give no result
or
SELECT itemID, itemName
FROM Items
WHERE (itemID LIKE '%' + @ItemIDTemp + '%')
This sample return an error message
"Data conversion failed. [OLEDB status value (if known)=2]"
What is the right way to do so?
View 3 Replies
View Related
Apr 15, 2008
hi I have this asp sql statement which works fine but I want to use an spl adapter instead as I am enabling paging in details view. I am stuck in one place. How do I declare @listing as I have done in the select parameter in the asp sql statement? Where in ths sql adapter do i declare it? listing value is derived from a dropdown box when the user makes a selection.
This is the sql statement i want to declare in the adapter.<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" SelectCommand="SELECT UserDetails.UserId, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, aspnet_Membership.Email FROM UserDetails INNER JOIN
aspnet_Membership ON UserDetails.UserId = aspnet_Membership.UserId WHERE Category = @Listing">
<selectparameters>
<asp:controlparameter name="Listing" controlid="SearchListingDropDownList" propertyname="SelectedValue"/></selectparameters>
This is the adapter so far which gives me an error to declare scalable value @listing. How do i incoporate the select parameters below?
Function getTheData() As DataTable Dim DS As New DataSet()
Dim strConnect As New SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ConnectionString)Dim objSQLAdapter As New SqlDataAdapter("SELECT aspnet_Membership.UserId,UserDetails.FirstName + ' ' + UserDetails.LastName AS 'Customer',UserDetails.City, aspnet_Membership.Email FROM aspnet_Membership INNER JOIN UserDetails ON aspnet_Membership.UserId = UserDetails.UserId WHERE Category = @Listing", strConnect)objSQLAdapter.Fill(DS, "aspnet_Membership,UserDetails")
Return DS.Tables("aspnet_Membership,UserDetails").Copy
End Function
www.mylookmeup.co.uk
View 2 Replies
View Related
Nov 16, 2007
I am running Windows XP and have SQL Server 2005 Enterprise Edition that I use for testing on my notebook. I want to install Oralce 11g on it as well and use VS 2005 as a front end programming. For me to install Oracle I need to install a loopback adapter. If I do this will it mess up SQL Server 2005 or will it not affect SQL Server 2005? Please advise
View 1 Replies
View Related
Nov 12, 2007
In VS2003 I have a user defaults table that I would call in the OnLoad - No Postback section to get the user defaults and then used some of the fields from that recordset as Parameters for other recordsets that would also load in that same section and might even issue a different Sql statement based on the field. In VS2005 (VB.NET), I can no longer figure out how to accomplish this. I can use the SQLDatasource Control which seems much improved but I cannot even find the generated code which I would use as a quick way to create my own datasources in the OnLoad event. Where is this code now stored and how can I accomplish my objectives in VB.NET 2005 with the current framework or programatically create data sources as necessary as I did before?
View 3 Replies
View Related
Jun 22, 2006
I am currently writing a custom source adapter that extracts data from a JD Edwards OneWorld system. In the custom user interface of the source component I need to allow the user to set a query (a custom property), and then refresh a list of output columns that will be extracted based upon that query (similar to the list shown in the advanced editor).
My question is, can I apply the custom property change to the component and build my output column list without closing and restarting my custom UI form? I understand that the IDTSComponentUI interface being implemented allows for transactional editing of the component, in that the changes to the component are not applied until I have returned a result in the implemented Edit() method. However is there a way to apply changes without returning this result (and closing my UI)?
Essentially I am looking to have similar behaviour to that of the 'Refresh' button in the advanced component editor form.
Thanks
View 3 Replies
View Related
Jan 31, 2006
Hi All,
I have built a custom flat file destination adapter but it appears that the code is not working. When I debug the process I notice that the ProcessInput section is called multiple times. The first time it looks like everything is working, then it call it again and there is no inpout from the DTSInput90.
Why would it do this?
Thanks
Mike
View 4 Replies
View Related
Feb 7, 2008
I have been struggling with the Adapter Fill issue for days.
I got an eeror "Invalid object name 'Weather'." Why? How to modify it?
Thanks
Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
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 My.Name.Space;
public partial class GetStrings : System.Web.UI.Page
{
getStrings g = new getStrings();
protected void Page_Load(object sender, EventArgs e)
{
string strConnectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
SqlConnection myConnection = new SqlConnection(strConnectionString);
DataSet myDataSet = new DataSet();
try
{
myConnection.Open();
// create a new DataSet
// create the data
GenerateDataSet(myDataSet, myConnection);
}
finally
{
myConnection.Close();
// bind each to table to a grid
GridView1.DataSource = myDataSet.Tables["Weather"];
}
// databind the page
GridView1.DataBind();}
void GenerateDataSet(DataSet dset, SqlConnection conn)
{
AddTable(dset);
FillTable(dset, conn);
}
void AddTable(DataSet dset)
{
// create the table
DataTable WeatherTable = new DataTable("Weather");
DataColumn WeatherID = WeatherTable.Columns.Add("State", typeof(string));
WeatherTable.Columns.Add("Division", typeof(String));
WeatherTable.Columns.Add("TEMP", typeof(string));
WeatherTable.Columns.Add("PCPN", typeof(string));
WeatherTable.Columns.Add("UL", typeof(string));
WeatherTable.Columns.Add("LL", typeof(string));
WeatherTable.Columns.Add("PCT", typeof(string));
WeatherTable.Columns.Add("POT", typeof(string));
WeatherTable.Columns.Add("RO", typeof(string));
WeatherTable.Columns.Add("MOIST_INDEX", typeof(string));
WeatherTable.Columns.Add("CF", typeof(string));
WeatherTable.Columns.Add("MNA", typeof(string));
WeatherTable.Columns.Add("DROUGHT_INDEX", typeof(string));
WeatherTable.Columns.Add("DROUGHT", typeof(string));
// set the column properties
WeatherTable.Columns["State"].MaxLength = 50;
WeatherTable.Columns["Division"].MaxLength = 50;
WeatherTable.Columns["TEMP"].MaxLength = 50;
WeatherTable.Columns["PCPN"].MaxLength = 50;
WeatherTable.Columns["UL"].MaxLength = 50;
WeatherTable.Columns["LL"].MaxLength = 50;
WeatherTable.Columns["PCT"].MaxLength = 50;
WeatherTable.Columns["POT"].MaxLength = 50;
WeatherTable.Columns["RO"].MaxLength = 50;
WeatherTable.Columns["MOIST_INDEX"].MaxLength = 50;
WeatherTable.Columns["CF"].MaxLength = 50;
WeatherTable.Columns["MNA"].MaxLength = 50;
WeatherTable.Columns["DROUGHT_INDEX"].MaxLength = 50;
WeatherTable.Columns["DROUGHT"].MaxLength = 50;
GetDataRow(WeatherTable, g.gets());
// add the table
dset.Tables.Add(WeatherTable);
}
void FillTable(DataSet dset, SqlConnection conn)
{
// create the Command and DataAdapter
SqlDataAdapter WeatherAdapter = new SqlDataAdapter();
SqlCommand WeatherCommand = new SqlCommand("SELECT State, Division,MOIST_INDEX,DROUGHT_INDEX FROM Weather", conn);
WeatherAdapter.SelectCommand = WeatherCommand;
// fill the DataTable
WeatherAdapter.Fill(dset,2,0,"Weather");//wrong here,
//WeatherAdapter.Fill(dset.Tables["Weather"]);
}
//assuming the first dimention is collumn and the second is row
public void GetDataRow(DataTable datatable, string[][] source)
{
DataRow dr;
int rows = source.GetLength(0);
int collumns = source[0].Length;
for (int i = 0; i < rows; i++)
{
dr = datatable.NewRow();
if (source[i] != null)
{
dr.ItemArray = (object[])source[i];
datatable.Rows.Add(dr);
}
}
datatable.AcceptChanges();
}
}
View 1 Replies
View Related
Jan 4, 2007
Hi All,
Using a data access mode of SQL Command, I have a stmt that returns one date ( e.g select max(LastChangedDate) from TblA ).
I want to store the result in a variable. How do I do this?
Cheers,
Tamim.
View 13 Replies
View Related
May 14, 2008
I downloaded and installed the Informix Client SDK 3.0 and was able to extract data from an Informix 10 database. All of a sudden, it stopped working. The error message I received is "... error in initializing provider. (-25580) System error occurred in network function.". Has anyone encountered this error before and know of a solution?
Thanks.
View 3 Replies
View Related
Oct 30, 2007
I am using an "XML Source Adapter" in an SSIS Package to try and load an XML file into a database table. But having trouble with a date field.
The XSD defines the field as:-
<xs:element minOccurs="1" name="DateTime" type="xs: string" />
The XML looks like this:-
<DateTime>12/31/2007 10:12:14.123</DateTime>
And the database table column is defined:-
[DateTime] [datetime] NOT NULL,
So in the Data Flow I have a "Data Conversion Transformation" converting [DT_WSTR] to [DT_DBTIMESTAMP].
However, when I run the package I get:-
Error: 0xC02020C5 at Load XXX File, Data Conversion 1 [13550]: Data conversion failed while converting column "DateTime" (15100) to column "DateTimeDT" (15422). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
So what how do I fix it?
I don't mind losing some miliseconds of precision, so I have tried configuring the error output of the Data Conversion for this field to "ignore failure" for "Truncation". But that seems to have no effect.
I'd be happy to change the XSD to:
<xs:element minOccurs="1" name="DateTime" type="xs:dateTime" />
But the "XML Data Flow Source" doesn't seem to support the dateTime data type.
Any suggestions appreciated. Thanks.
View 1 Replies
View Related
Oct 22, 2007
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
View 2 Replies
View Related
Apr 3, 2006
I'm having alot of trouble figuring out the proper way to log from inside my custom source adapter.
I couldn't find my useful information in MSDN, it mostly applies to logging from inside a script task.
I'd like to log my messages along with all the other SSIS package log entries, which I have going to the dts log table.
I'm assuming I should be using some functionality from Microsoft.SqlServer.Dts.RunTime, probably the LogProvider.
Can anyone advise?
View 3 Replies
View Related
Oct 22, 2007
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
View 4 Replies
View Related
May 23, 2006
Quick question...
I have a conditional split transform in a dataflow...
One of the outputs of the conditional split will take records that are just not needed...
Do I have to send that output into the Trash Destination adapter? Or can I just let those records hang?
Will there be a memory leak if I don't use a trash adapter?
I'm just thinking about if I move the package I'd have to go reinstalling the trash adapter every time...
Thanks
View 10 Replies
View Related
Jun 6, 2006
I would Like to know whether anyone tried to SSIS from SQL to AS400.
I am getting Exception when I set destination adapter ( for As400).
Whats the acceptable value .
View 1 Replies
View Related
Oct 22, 2007
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
View 3 Replies
View Related
Feb 7, 2007
Hi,
I have conditional split on a boolean variable, when the value is true, case1 output goes to Oledb Destination, defaukt Output goes to Sql Server Destination.
When I run the package with value "true" the package runs, but when i run the package with bool value "false", the data is inserted through oledb but Sql Server destination fails.
Can any one tell me Why.
[SQL Server Destination [292]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
I don't have any linked server.
thanks
Dharmbir
View 3 Replies
View Related
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;
private void Page_Load(object sender, System.EventArgs e)
{
DA.Fill(ds1, "blogEntry");
dList.DataBind();
}
#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();
}
#endregion
private void linkAdmin_Click(object sender, System.EventArgs e)
{
Server.Transfer("logOn.aspx",true);
}
}
}
View 5 Replies
View Related
Apr 22, 2007
Hi,
I'm getting some unexpected behaviour from my SSIS packages when targeting tables that are being referenced by Indexed Views. There's two separate issues:
1. When writing into a pair of tables with a SuperType / SubType relationship concurrently with a pair of SS destinations I'm getting deadlocks between the two. Removing the index on the view that references both of these fixes the problem.
2. Much odder, I'm getting some extremely long waits (10 times longer than the whole package should take to run!) from an SS Destination adapter even when there's no data in the flow for it to bulk insert. Again, removing the indexed views that reference the destination table fixes the problem.
The views aren't mine, and (apparently) are required by the reporting app (BO), so removing them isn't really an option. I realise that there's quite a lot of overhead to maintaining indexed views, but unfortunately, the project is on a very tight timeline, so I can't look into it in as much detail as I'd like.
I was wondering if anyone's experienced any similar issues, or would have any ideas as to where to start investigating?
Thanks a lot
Mark
View 2 Replies
View Related
Mar 6, 2007
I didn't want to deal with any truncation issues so I edited the SQL created by the Import Wizard. I made the fields VARCHAR instead of NVARCHAR and changed the size from the default 50 to 250.
Now I have a triangle warning on the OLE DB Destination control. So I am wondering, is it limited to only handling 50 characters?
IanO
View 2 Replies
View Related
Jul 3, 2007
I'm working on a new project using SSIS in SQL Server 2005 and have an issue that I need resolved.
I'm loading an XML file into SSIS using the XML Source Adapter. This maps to an OLE DB destination which reads the data into a SQL table (please see below for the table structure). In between this stage is a data conversion to convert the Unicode characters from XML into non-Unicode characters.
CREATE TABLE [dbo].[XMLTest](
[Forename] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Surname] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[PostCode] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL
)
The problem I have is with blank strings in xml are being inserted into the SQL table as NULL values and not blanks (see the xml below).
<Root>
<People>
<Forename>Duncan3</Forename>
<Surname></Surname>
<PostCode>ME16 0WH</PostCode>
</People>
</Root>
Correct me if I'm wrong but a null value in xml means the node does not exist (See the xml below)?
<Root>
<People>
<Forename>Duncan3</Forename>
<PostCode>ME16 0WH</PostCode>
</People>
</Root>
Please also find the schema attached for reference.
<?xml version="1.0"?>
<xschema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Root">
<xs:complexType>
<xsequence>
<xs:element minOccurs="0" name="People">
<xs:complexType>
<xsequence>
<xs:element minOccurs="1" name="Forename" type="xstring" />
<xs:element minOccurs="0" name="Surname" type="xstring" />
<xs:element minOccurs="1" name="PostCode" type="xstring" />
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xschema>
View 5 Replies
View Related
Jul 25, 2007
I am writing a custom task to import data from delimited files into SQL tables. I use the standard Flat File Source adapter, a custom transformation to add a URN column and a filename column to the data and the standard OLEDB Destination Adapter.
Most of my test data files work fine except for ones with a lot of columns (around 350 columns). I get an error when I call the ReinitializeMetaData() method for the destination adapter.
Q1) Is there a restriction on the number of columns (or data row size) that can be imported into an OLEDB Destination Adapter?
Q2) The reason I use this adapter rather than the SQL Server Destination Adapter is that I need to set the destination table name using a variable. I don't believe I can do this with the SQL Server Destination Adapter. Is this the case?
Q3) Anyone know of a better/alternative way of acheiving the above? One way I have thought of is to create a custom destination adapter using the SQL Server Destination adapter as the base but I'm not sure whether this is a) possible and b) worth the hassle.
Hope someone can help...
Cheers
Nick
View 2 Replies
View Related
Aug 24, 2007
Hi,
Will someone please tell me what is the difference between Destination adapter and bcp in Sql server 2005 ?
Thanks and Regards
Altaf Nizamuddin
View 1 Replies
View Related
Oct 14, 2007
It€™s always said that using the SQL Server Destination adapter is much faster when loading data to a SQL Server database. My question is, if I have a SQL 2000 database, can I still use the SQL Server Destination adapter, or is it only for SQL 2005 db? Is OLE DB Destination adapter my best bet? My data files(flat files), SSIS package and destination SQL 2000 db are all in the same physical server.
View 1 Replies
View Related
