|
Glen Harvy wrote: The datatable must be replaced ie the row ID numbers must remain the same.
Is the RowID an identity column? If so, disable Identity by setting SET IDENTITY INSERT [Table Name] ON and import the data from the older database.
Glen Harvy wrote: I thought that writing the old datatable to to an xml file and then reading that data back in would do the trick but I can't get it to work.
That statement is devoid of any useful information. Is there any particular error you face?
|
|
|
|
|
Your suggestion would seem to resolve my problem however I am not aware of a way to achieve my end result without using datasets and datatables as in the following code:
string tempDBLocation = MainForm.dataDirectory + @"Temp\";
string tempXMLFile = tempDBLocation + @"tempData.xml";
File.Copy(MainForm.dataDirectory + "my.sdf", MainForm.dataDirectory + "my_org.sdf", true);
File.Copy(tempDBLocation + "my.sdf", MainForm.dataDirectory + "my.sdf", true);
this.configTableAdapter1.Fill(this.adminDataSet1.Config);
this.adminDataSet1.Config.WriteXml(tempXMLFile, XmlWriteMode.WriteSchema);
File.Copy(MainForm.dataDirectory + "my_org.sdf", MainForm.dataDirectory + "my.sdf", true);
File.Delete(tempDBLocation + "my.sdf");
File.Delete(MainForm.dataDirectory + "my_org.sdf");
this.configTableAdapter1.Fill(this.adminDataSet1.Config);
for (int i = 0; i < this.adminDataSet1.Config.Rows.Count; i++)
{
this.adminDataSet1.Config.Rows[i].Delete();
}
this.configTableAdapter1.Update(this.adminDataSet1.Config);
this.adminDataSet1.AcceptChanges();
this.adminDataSet1.Config.ReadXmlSchema(tempXMLFile);
this.adminDataSet1.Config.ReadXml(tempXMLFile);
this.configTableAdapter1.Update(this.adminDataSet1.Config);
this.adminDataSet1.AcceptChanges();
I would prefer not to use ado.net but am unable to locate how to achieve what I want without it.
It seems I have a lot more research to do.
Thanks for your assistance.
Glen Harvy
|
|
|
|
|
string tempDBLocation = MainForm.dataDirectory + @"Temp\";
string tempXMLFile = tempDBLocation + @"tempData.xml";
string conString = "Data Source=" + tempDBLocation + "my.sdf;Persist Security Info=False";
SqlCeConnection conn = new SqlCeConnection(conString);
SqlCeCommand command = conn.CreateCommand();
conn.Open();
string tableName = "Config";
SqlCeDataAdapter mySqlCeDataAdapter = new SqlCeDataAdapter("select * from " + tableName, conn);
DataTable myDT = new DataTable(tableName);
mySqlCeDataAdapter.Fill(myDT);
myDT.WriteXml(tempXMLFile,XmlWriteMode.WriteSchema);
conn.Close();
conString = Properties.Settings.Default.mycourtsConnectionString.ToString();
SqlCeConnection conn2 = new SqlCeConnection(conString);
SqlCeCommand command2 = conn2.CreateCommand();
conn2.Open();
SqlCeDataAdapter mySqlCeDataAdapter2 = new SqlCeDataAdapter("select * from " + tableName, conn2);
DataTable myDT2 = new DataTable(tableName);
mySqlCeDataAdapter2.Fill(myDT2);
SqlCeCommandBuilder ccmdBuilder = new SqlCeCommandBuilder(mySqlCeDataAdapter2);
for (int i = 0; i < myDT2.Rows.Count; i++)
{
myDT2.Rows[i].Delete();
}
myDT2.ReadXmlSchema(tempXMLFile);
myDT2.ReadXml(tempXMLFile);
command2.CommandText = "SET IDENTITY_INSERT " +tableName + " ON";
command2.ExecuteScalar();
mySqlCeDataAdapter2.Update(myDT2);
conn2.Close();
And of course the Update command now generates this error:
The column cannot contain null values. [ Column name = ID,Table name = Config ]
It makes sense of course but how do I get around this - or must I generate an insert command and do it that way.
Glen Harvy
|
|
|
|
|
Check this this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi guys
I got a weird error which iam struggling to fix for soo many days. I need u guys help. please help me. I got a web service in win 2003 server which access the database to do insert operation (dataadapter.update(dataset)). This webservice is called by 2 applications. one is client server application(winform poped up in browser at client side takes input file and processes to a dataset and calls webservice to insert into database using (dataadapter.update(dataset)). the second application(asp.net app) rests in same server calls webservice and does same operation inserting into database using same statement works fine). The problem arose when i migrated from .net 1.1 to .net 2.0 framework.
The error message iam getting is:
"System.data.odbc.odbcexception ERROR [07002] [IBM][iSeries Access ODBC Driver]Wrong number of parameters".
Please help me. iam inserting it into db2 database(as400) . The server has v5r4 client access software. If u need further info please let me know.
Note:
I caught the dataset just before the insert operation and wrote it to a xml to check the condition of dataset(to verify any damage in network). It looks fine and i used same dataset to build insert statements to enter manually into database. So i believe there is some security or access provider issue. 
|
|
|
|
|
I have a SQL table that I am importing that has 2 columns title and description of type text that need to be converted to varchar(max). none of the 2 have more than 4000 chars and its English only.
I created a data flow task with a transformation to convert both columns to string [DT_STR] 4000. My destination has both columns as varchar(MAX).
I get a very informative error of Cannot create an OLE DB accessor. Verify that the column metadata is valid.
When I GIS most suggest the conversion which I am already doing.
Anyone been through this?
modified on Monday, August 4, 2008 2:30 PM
|
|
|
|
|
If your destination column is Varchar(MAX), you've to convert it to DT_WSTR in your transformation.
|
|
|
|
|
I tried this but then I get an error "Error at Insert Articles [Data Conversion [3463]]: Conversion from "DT_TEXT" to "DT_WSTR" is not supported".
|
|
|
|
|
I figured it out I had to convert to text stream and then use derived column add as new column (DT_STR,4000,1252)(DT_TEXT,1252)[Description]
What a pain!
|
|
|
|
|
Hi guys,
Is there any way to have SQL Server return the table name as part of the column please? In my example, I would like to do something akin to...
SELECT * FROM [Address]
and have SQL Server return a result set containing columns with "Address." prepended to the column name.
Address.ID, Address.Line1, Address.Line2
Many Thanks for any help you can provide!
|
|
|
|
|
Try this
select ID as 'Address.ID', Line1 as 'Address.Line1' ....
But why do you need it? Column names are unique in a select.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi!
Thank you for your reply.
Unfortunately explicitly naming the columns is the only way I've found to do this so far. I would however like to be able to do something like SELECT * FROM Address and retrieve the columns as [Address].[name].
Do you know of a way of doing this without explicitly aliasing the column names please?
|
|
|
|
|
Sorry, no. I assume your query sometimes joins several tables, otherwise it would be a trivial thing to auto-generate your query.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
That is correct. In some instances I will be selecting fields from a view and would like to simply have
SELECT * FROM vAddress AS Address
which would output
Address.ID, Address.Line1, Address.Line2
Thank you for your help. Looks like I'm gonna have to explicitly state the aliases in my procs.
Cheers
|
|
|
|
|
I dont know why you need to do this but you can just
SELECT 'Address.' + ID AS ID, 'Address.' + Line1 AS Line1
FROM [Address]
If your trying to use this to make it relational on the client side this is a bad way to do it.
|
|
|
|
|
I'm attempting to build a DAL that will automatically create and populate objects from the results of a database call.
As such, I need the data to be returned in column names that represent the object they are populating such as object.property e.g. Address.ID, Address.Line1
Sometimes I need just the property names e.g. ID, Line1, Line2 and other times I need Address.ID, Address.Line1, Address.Line2. It would be nice to have a view that I can call which returns the relevent fields and then I can simply prepend the object name to each of the fields when required.
|
|
|
|
|
I dont know if this is helpful but here is an example of my customer DAL. I use Microsoft patterns and practices for SQL data access but you can just use SQL data adapter.
I just dim cust as new DAL.Customer()
cust.CustomerName = textbox1.text
....
cust.save
or
cust.FetchCustomer(22)
Imports Microsoft.VisualBasic
Namespace DAL
Public NotInheritable Class Customer
#Region " Local Variables "
Private _CustomerId As Integer
Private _CustomerName As String
Private _Address1 As String
Private _Address2 As String
Private _City As String
Private _StateProvince As String
Private _ZipPostal As String
Private _CountryCode As String
Private _DateCreated As Date
Private _DateUpdated As Date
Private _IsDirty As Boolean = False
#End Region
#Region " Public Properties "
Public Property CustomerId() As Integer
Get
Return _CustomerId
End Get
Set(ByVal value As Integer)
If value <> _CustomerId Then
_IsDirty = True
End If
_CustomerId = value
End Set
End Property
Public Property CustomerName() As String
Get
Return _CustomerName
End Get
Set(ByVal value As String)
If value <> _CustomerName Then
_IsDirty = True
End If
_CustomerName = value
End Set
End Property
Public Property Address1() As String
Get
Return _Address1
End Get
Set(ByVal value As String)
If value <> _Address1 Then
_IsDirty = True
End If
_Address1 = value
End Set
End Property
Public Property Address2() As String
Get
Return _Address2
End Get
Set(ByVal value As String)
If value <> _Address2 Then
_IsDirty = True
End If
_Address2 = value
End Set
End Property
Public Property City() As String
Get
Return _City
End Get
Set(ByVal value As String)
If value <> _City Then
_IsDirty = True
End If
_City = value
End Set
End Property
Public Property StateProvince() As String
Get
Return _StateProvince
End Get
Set(ByVal value As String)
If value <> _StateProvince Then
_IsDirty = True
End If
_StateProvince = value
End Set
End Property
Public Property ZipPostal() As String
Get
Return _ZipPostal
End Get
Set(ByVal value As String)
If value <> _ZipPostal Then
_IsDirty = True
End If
_ZipPostal = value
End Set
End Property
Public Property CountryCode() As String
Get
Return _CountryCode
End Get
Set(ByVal value As String)
If value <> _CountryCode Then
_IsDirty = True
End If
_CountryCode = value
End Set
End Property
Public ReadOnly Property DateCreated() As Date
Get
Return _DateCreated
End Get
End Property
Public ReadOnly Property DateUpdated() As Date
Get
Return _DateUpdated
End Get
End Property
#End Region
Public Sub New()
'blank
End Sub
Public Function Save() As Boolean
If _IsDirty Then
Dim cmd As DbCommand
Dim db As Database = DatabaseFactory.CreateDatabase("CustomerDb")
cmd = db.GetStoredProcCommand("sp_Customer_iu")
Try
' add input parameters
db.AddInParameter(cmd, "CustomerID", DbType.Int32, _CustomerId)
db.AddInParameter(cmd, "CustomerName", DbType.String, _CustomerName)
db.AddInParameter(cmd, "CompanyName", DbType.String, _CompanyName)
db.AddInParameter(cmd, "Address1", DbType.String, _Address1)
db.AddInParameter(cmd, "Address2", DbType.String, _Address2)
db.AddInParameter(cmd, "CityName", DbType.String, _City)
db.AddInParameter(cmd, "StateProvince", DbType.String, _StateProvince)
db.AddInParameter(cmd, "ZipPostal", DbType.String, _ZipPostal)
db.AddInParameter(cmd, "CountryCode", DbType.String, _CountryCode)
' Add output paramaters
db.AddOutParameter(cmd, "ReturnCustomerId", DbType.Int32, 4)
db.ExecuteNonQuery(cmd)
_CustomerId = db.GetParameterValue(cmd, "ReturnAccountID")
Return True
Catch ex As Exception
Throw New Exception(ex.Message)
Finally
cmd.Dispose()
End Try
Else
Return True
End If
End Function
Public Function FetchCustomer(ByVal CustomerId As Integer) As Customer
Dim db As Database = DatabaseFactory.CreateDatabase("CustomerDb")
Dim cmd As DbCommand = db.GetStoredProcCommand("sp_Customer_s")
Dim Output As New Customer
db.AddInParameter(cmd, "CustomerId", DbType.Int32, CustomerId)
Dim dr As IDataReader = db.ExecuteReader(cmd)
Try
If dr.Read Then
Output.LoadData(dr)
End If
Finally
dr.Close()
cmd.Connection.Close()
End Try
Return Output
End Function
Friend Sub LoadData(ByVal reader As IDataReader)
Dim i As Integer
With reader
For i = 0 To .FieldCount - 1
If Not .IsDBNull(i) Then
Select Case .GetName(i).ToLower
Case "customerid"
_CustomerId = .GetInt32(i)
Case "accountname"
_CustomerName = .GetString(i)
Case "address1"
_Address1 = .GetString(i)
Case "address2"
_Address2 = .GetString(i)
Case "cityname"
_City = .GetString(i)
Case "stateprovince"
_StateProvince = .GetString(i)
Case "zippostal"
_ZipPostal = .GetString(i)
Case "countrycode"
_CountryCode = .GetString(i)
Case "datecreated"
_DateCreated = .GetDateTime(i)
Case "dateupdated"
_DateUpdated = .GetDateTime(i)
End Select
End If
Next
End With
End Sub
End Class
End Namespace
|
|
|
|
|
Hi,
Thank you for your posting. This is essentially what I am doing with my DAL however am using reflection to make the DAL more generic across objects. As such, the column names in the returned data set must match the property names on the object.
Additionally, my DAL is able to perform an eager-loading of sub-structures. It is for this eager loading that I need to have the table name returned as part of the column name e.g.
Address
-- ID
-- Line1
-- Line2
-- Country
-- -- ID
-- -- Name
-- Postcode
By performing a join between the Address table and Country table, I can eagerly load the country information as a structure into the Address object. I just need to be able to identify the ID and Name fields as belonging the Country structure by prepending 'Country.' to the names.
In the above example, it's fine to explicity alias the field names however I would like a solution which could perform something like...
SELECT
Address.ID AS ID,
Address.Line1 AS Line1,
Address.Line2 AS Line2,
Country.*,
Address.Postcode AS Postcode
FROM
Address LEFT JOIN Country etc...
and which would rename the fields in Country to Country.ID and Country.Name. Obviously I could use a view for this however there may be times when I need the ID and Name property without the 'Country.' prefix. In such instances, it would be nice to reuse the same view instead of having a view that prepends the prefix and a view which does not.
|
|
|
|
|
Hi,
Currently we are shifted on Oracle from MsSQL 2005. I want to convert a stored procedure which will take dynamic query in string format and execute it. Here is MsSQL equivalant:
<code>CREATE Proc [dbo].[GetCars]
@YearOfManf Int,
@PriceFrom Decimal,
@PriceTo Decimal
As
Begin
Declare @sql nVarchar(2000)
set @sql=''
If(@YearOfManf is not null)
set @sql=@sql+' And ManufactureYear='+Convert(Varchar(4),@YearOfManf)+''
If(@PriceFrom Is Not Null and @PriceTo Is Not Null)
set @sql=@sql+' And ExpectedPrice Between '+Convert(Varchar(10),@PriceFrom)+' And '+Convert(Varchar(10),@PriceTo)+''
IF(@PriceFrom Is Not Null and @PriceTo Is Null)
SET @sql=@sql+' And ExpectedPrice &gt;= '+Convert(Varchar(10),@PriceFrom)+''
set @sql='SELECT
CarID,
ManufactureYear
ExpectedPrice
FROM
CarMaster
WHERE Visible = 1 AND Deleted = 0 '
+ @sql +'
GROUP BY
ID, ManufactureYear, ExpectedPrice'
exec sp_executesql @sql
End</code>
Is anybody knw how to convert this line: <b>exec sp_executesql @sql</b>
Thanx in advance...
-Abhijit
|
|
|
|
|
Hi,
In Oracle use either EXECUTE IMMEDIATE statement or DBMS_SQL-package for dynamic sql.
Mika
|
|
|
|
|
hi all,
i use following statement to Get all SQL server instance on my local network
Dim instance As System.Data.Sql.SqlDataSourceEnumerator = System.Data.Sql.SqlDataSourceEnumerator.Instance()<br />
Dim table1 As System.Data.DataTable = instance.GetDataSources()<br />
For Each row As DataRow In table1.Rows<br />
cboServer.Items.Add(row.Item(0).ToString)<br />
Next
Above code Add all the Server in combo box which is in local network. Now i select a Server and try to connect to server as following
Dim MainServer As Server = New Server(cboServer.Text.Trim & "\SQLEXPRESS")<br />
With MainServer.ConnectionContext<br />
.LoginSecure = True<br />
.Connect()<br />
End with
but above line give error "Failed to connect to server MACHINENAME\SQLEXPRESS."
Detail of error(Inner Exception) show "{"Login failed for user 'MACHINENAME\Guest'."}"
MACHINENAME is name of server
I try lot of googling. But nothing is helpful.
Can any body help me that how can i resolve this error or How can i enable the Guest account on Server so it is connected.
|
|
|
|
|
It's obvious to the point where it glares right at you.
The windows account MACHINENAME\Guest does NOT have login privileges for the SQL Server instance. You either have to run the application as a different user or have to give login rights to MACHINENAME\Guest in SQL Server Security permissions.
If you have a SQL Server username and password, use that to connect.
And.
If you're not in a domain, Windows Authentication would not work if you're trying to connect to a SQL Server instance in another machine in the network.
sunil goyalG wrote: How can i enable the Guest account on Server so it is connected.
Ask your administrator to do it. Or if you have access right to that machine and you're an admin, the Guest account can be enabled using the System Management MMC. You'd also need to add Guest as a login in SQL Server. For that, use Management Studio --> Security --> Logins to do that.
|
|
|
|
|
SimulationofSai wrote: You either have to run the application as a different user or have to give login rights to MACHINENAME\Guest in SQL Server Security permissions.
How can i give right to MACHINENAME\Guest in SQL Server Security permissions ?
What action i performed after (Management Studio --> Security --> Logins) ? .WIndows XP is running on both computer(Local and Remote)
|
|
|
|
|
sunil goyalG wrote: What action i performed after
In my opinion, the first action you've got to perform is buy a good book on SQL Server and read it.
|
|
|
|
|
Yaa. I feel this thing . I have no idea, that what is the role of Security (Logins, Server Roles and Credentials). Presently i simply want to test that how to connect to a server so i ask this stupid question.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
