Hi,
Currently, I have a table containing Unicode values for a particular column and I am trying to retrieve them using the SQLBindCol and SQLFetch functions in C/C++. However, I realised that the binding converts the fetched values into C data types(none of them can handle Unicode values). Any idea how I can overcome this problem?
Thanks in advance.
Regards
Melvin
Hi,usually, I'm not using MS servers, but I have a big problem with aAccess table.I should create a web application for a Historical Dipartment.They have create a populated a Access database using unicodecompression field (for ancient language).I would like to export this table into MySQL o Postgres, but it'simpossible because when I export this table in a .txt o cvs format theunicode charaters have been "destroyed" for memory allocation problems(cause Access use a compression tool for unicode fields).Also with professional tools for dump Access to another DBMS.I would to know if using a MS SQL server I can skip this problem causeboth MSQLserver both Access are Microsoft product.Thank you ;)J
View 1 Replies View RelatedIn my package , I am used CDC Source transformation and received the Net changes then insert into Destination. But whatever Data coming from CDC source data type Varchar value needs to Converting Non Unicode string to Unicode string SSIS. So used Data conversion transformation to achieved this.  I need to achieve this without data conversion.
View 3 Replies View RelatedI am following the SSIS overview video- URL...I have a flat file that i want to import the contents onto a SQL database.I created a Dataflow task, source file and oledb destination.I am getting the folliwung error -"column "A" cannot convert between unicode and non-unicode string data types".in the origin file the data type is coming as string[DT_STR] and in the destination object it is coming as "Unicode string [DT_WSTR]"I used a data conversion object in between, dosent works very well
View 5 Replies View RelatedGood afternoon
View 32 Replies View RelatedI'm connecting to a SQL Server 2005 database using the latest (beta) sql server driver (Microsoft SQL Server 2005 JDBC Driver 1.1 CTP June 2006) from within Java (Rational Application Developer).
The table in SQL Server database has collation Latin1_General_CI_AS and one of the columns is a NVARCHAR with collation Indic_General_90_CI_AS. This should be a Unicode only collation. However when storing for instance the following String:
‚¬_£_ÙÚÜÛùúüû_ÅÆØåæøߣÇçÑñ_¼½¾_ЎўЄєÒ?Ò‘_прÑ?туф_ЂЉЊЋ
... it is saved with ? for all unicode characters as follows (when looking in the database):
‚¬_£_ÙÚÜÛùúüû_ÅÆØåæøߣÇçÑñ_¼½¾_??????_??????_????
The above is not correct, since all unicode characters should still be visible. When inserting the same string directly into the sql server database (without using Java) the result is ok.
Also when trying to retrieve the results again it complains about the following error within Java:
Codepage 0 is not supported by the Java environment.
Hopefully somebody has an answer for this problem. When I alter the collation of the NVARCHAR column to be Latin1_General_CI_AS as well, the data can be stored and retrieved however then of course the unicode specific characters are lost and results into ? So in that case the output is as described above (ie ‚¬_£_ÙÚÜÛùúüû_ÅÆØåæøߣÇçÑñ_¼½¾_??????_??????_????)
We would like to be able to persist and retrieve unicode characters in a SQL Server database using the correct JDBC Driver. We achieved this result already with an Oracle UTF8 database. But we need to be compliant with a SQL Server database as well. Please help.
Thanks in advance for your help.
I have an SSIS package that pulls data from a MYSQL DB (Using RSSBus for Salesforce in SSIS to accomplish this). Most of the columns are loading properly, but I have many columns that I need to convert.
I have been using the Data Conversion dataflow task in SSIS to convert the rows.
I have 2 data conversions that work on most of the columns, but the DESCRIPTION column continues to return an error saying "Cannot convert between unicode and non-unicode types", regardless of what I choose on the Data Conversion task. So, basically I want to dump this column data into a SQL table with NVARCHAR datatypes. Here is what I am doing in my SSIS package...
1) Grab subset of data from SOURCE
2) Converts to TEXTSTREAM. (Data Conversion)
3) Converts to STRING. (Data Conversion)
4) Load Destination table. (OLE DB Destination)
I have also tried to simply convert the values to STRING, but that doesn't work either.
So, I have 2 Data Conversions working here that process most of the data correctly. What I can do to load the DESCRIPTION column?
Hi guys and gals,
I've had some great headaches with SSIS this morning, which I have managed to get a workarounds for, but I'm not happy with them so I've come to ask for advice.
Basically, I am exporting data from an SQL Server database into an Excel spreadsheet and hitting issues with unicode and non-unicode data types.
For example, I have a column that is char(6) and have added a data conversion step to the data flow, which converts it to type DT_WSTR and then everything works!
However, this seems like a completely un-neccessary step as I should be able to do the conversion in T-SQL - but no matter what I try I keep getting the same problem.
SELECT Cast(employee_number As nvarchar(255)) As [employee_number]
FROM employee
WHERE forename = 'george'
ErrorValidation error. details: 1 [1123]: Column "employee_number" cannot convert between unicode and non-unicode string data types.
I know I have a solution (read: workaround) but I really don't want to do this everytime!
Any suggestions for what else to try?
I have an Excel Source component hooked to an OLE DB Destination component in my SSIS 2005 Data Flow Task. After I mapped the excel columns to the OLE DB table columns i get these errors below. I noticed that for the first error, the Excel Field format (when you mouse over the column name in the mappings section in OLE DB component) is of type [DTWSTR] and the corresponding SQL field from my SQL table that it's mapping to is of type [DT_STR] when mousing over that field in the mappings in the properties of my OLE DB component. All table fields in SQL Server for the table I'm inserting into are of type varchar.
print screens here:
http://www.webfound.net/excel_ssis.jpg
http://www.webfound.net/excel_to_oledb_mappings.jpg
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Commission Agency" and "CommissionAgency" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Column "Product" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Officer Code" and "OfficerCode" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Agency Name" and "AgencyName" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Agency Id" and "AgencyID" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Tran Code" and "TranCode" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "User Id" and "UserID" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [27]]: Columns "Acct Number" and "AccountNumber" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB Destination" (27)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
HI.
I'm having this problem.
I use Visual Studio's, integration project to load XML file into SQL Server. In the XML file, i have defined collumns as string. When i try to load XML file with parts defined in scheme as string, i get an error "cannot convert between unicode and non-unicode string data type.
Destinated collumns in SQL are defined as varchar and char.
Thanks for help
For packages that I have created to read Oracle 10g tables, that work fine with debugging in 32-bit mode, I get an error message on all string fields when I try to run in 64-bit mode. An example error message is:[OLE DB Source [1]] Error: Column "ACCT_UNIT" cannot convert between unicode and non-unicode string data types.Another interesting warning included is:[OLE DB Source [1]] Warning: The external columns for component "OLE DB Source" (1) are out of synchronization with the data source columns. The external column "ACCT_UNIT" needs to be updated.I cannot even try to convert this data with a Data Conversion item because the (red) error is on the OLE DB Source item and stops there. It doesn't matter what the destination is or even if there is a destination in the package yet.I'm using Oracle Provider for OLE DB, Oracle Client version 10.203 for 32-bit and Oracle Client 10.204 for 64-bit.Oracle is 10g on a UNIX 64-bit server and the data is not unicode.I'm using SQL Server Enterprise 2008 (10.0.1600) on Windows Server 2008 Standard SP1 on a 64-bit server.The packages work fine in 32-bit mode and the data is not unicode data. When I change Run64BitRuntime to True in the Debugging Property Page, I get the error on the OLE DB Source item. I also get the error when I schedule a package to run using the SQL Server Agent.
View 4 Replies View RelatedHi,
I have spent countless number of hours trying to solve the issue, but to no vail. My problem is SSIS throws "cannot convert between unicode and non-unicode string data types" when i am try to transform data from DB2 to SQL Server 2005. And please note, i tried all possibilities like changing the destination field which is in SQL Server 2005 to nvarchar and also text. But so far no help. And i also looked at previous posts which did not help me either.
Thank You in advance.
Any one know the process of transfering the database from non-unicode to unicode. Coz I like to transfer the data from english to hebrew.
View 1 Replies View RelatedHi all, we are now planning to upgrade our application from anon-unicode version to a unicode version. The application's backend isa SQL Server 2000 SP3.The concern is, existing business data are stored using collation"Chinese_PRC_CI_AS", i.e. Simplified Chinese. So I thought we need toextract these data out to the new SQL Server which is using Unicode (Iassume it means converting them to nchar, nvarchar type of fields for Idon't enough information from the application side, or is there ageneral unicode collation that will make even char and varchar types tostore data as Unicode?).The problem is what's the best and most efficient way to do this dataconversion?bcp? DTS? or others?thanks a lot
View 6 Replies View RelatedI have built a large package and due to database changes (varchar to nvarchar) I need to do a data conversion of all the flat file columns I am bringing in, to a unicode data type. The way I know how to do this is via the data conversion component/task. My question is, I am looking for an easy way to "Do All Columns" and "Map all Columns" without doing every column by hand in both spots.
I need to change all the columns, can I do this in mass? More importantly once I convert all these and connect it to my data source it fails to map converted fields by name. Is there a way when using the data conversion task to still get it to map by name when connecting it to the OLE destination?
I know I can use the wizard to create the base package, but I have already built all the other components, renamed and set the data type and size on all the columns (over 300) and so I don't want to have to re-do all that work. What is the best solution?
In general I would be happy if I could get the post data conversion to map automatically to the source. But because its DataConversion.CustomerID it will not map to CustomerID field on destination. Any suggestions on the best way to do this would save me hours of work...
Thanks.
In SQL 2000 DTS, I was able to append data from an ODBC source to a SQL 2000 destination table. The destination table was created by copying an attached source table in Access to a new table, then upsizing it to SQL. The character fields come over as varchar, and that seemed to be fine with the DTS job.
Now using the same source table and the same SQL destination, only in SQL 2005 with Integration Services instead of DTS, I get an error because the connection manager interprets the source text fields as Unicode and the destination fields are varchar.
I could script the table and change the text fields in the destination table to nvarchar, but this could have adverse affect on the application that uses the destination table. Is there a way to make the connection manager see the source text fields as varchar, or have the integration package allow the append even though the destination is varchar and the source is nvarchar?
My ssis package exports .xls file data into a sql server table.
The fields in the sql server table have to be nvarchar instead of varchar.
If I use varchar then the ssis package gives an error about converting unicode to non-unicode...
Another ssis package exports .csv data into the sqme sql server table as above.
This time the sql server table fields have to be of type varchar because otherwise the ssis package gives an error about converting non-uniciode to unicode...
I basically would like to have a sql server table with varchar fields and let these packages import into it.
These packages may retrieve data from .csv or .xls.
How can I resolve this?
Thanks
I keep getting the error message below when attempting to import a text file (flat file) to an SQL Server Destination using SSIS. This database has recently been migrated from SQL Server 2000 (where I used a DTS Package that worked fine). I plan on doing further manipulation to the file after importing but have so far tried to keep one step at a time. I have tried using a smaller files and different tables but still get a similar response. I believe that the encoding of the text file is ANSI. If it is relevant the database collation is Latin1_General_CI_AS (the same as it was when 2000). Any help anyone can provide will be greatly appreciated!!!
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 0" and "AccountNumber" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 1" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 2" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 3" and "Name" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 4" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 5" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 6" and "ExpiryDate" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 7" and "RateType" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 8" can't be inserted because the conversion between types DT_STR and DT_BOOL is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 9" and "FullName" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 10" and "Address" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 11" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 12" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 13" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 14" and "Occupancy" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 15" and "LoanPurpose" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 16" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 17" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 18" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 19" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 20" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 21" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 22" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 23" and "DocumentLocation" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 24" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 25" and "SecurityType" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 26" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 27" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 28" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 29" and "MortgageInsurancePolicyNumber" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 30" and "SecurityAddress" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 31" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 32" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 33" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 34" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 35" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 36" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 37" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 38" and "SecuritySuburb" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 39" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 40" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 41" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 42" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 43" and "MortgageNumber" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 44" and "TitleParticulars" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 45" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 46" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 47" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 48" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 49" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 50" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 51" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [DTS.Pipeline]: "component "SQL Server Destination" (174)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
My ssis package exports .xls file data into a sql server table.
The fields in the sql server table have to be nvarchar instead of varchar.
If I use varchar then the ssis package gives an error about converting unicode to non-unicode...
Another ssis package exports .csv data into the sqme sql server table as above.
This time the sql server table fields have to be of type varchar because otherwise the ssis package gives an error about converting non-uniciode to unicode...
I basically would like to have a sql server table with varchar fields and let these packages import into it.
These packages may retrieve data from .csv or .xls.
How can I resolve this?Thanks
I have a DTS package that is connected to a table in an Access97 database and to a duplicate(by schema) table in SQL2000. If there is a default value in the Access97 field properties the DTS package will import that value regardless of any other value in the Access field. I can not see anything in the DTS designer that handles this choice.
Even when I look at the Access connection in preview it shows only the default values.
Any thoughts on getting the actual values?
Thanks
I may be looking too hard for this but I can't find a way around it.
I have an Expression and in that expression, I want to access a property on the same object (it would be great to get the properties of other objects as well).
Example: I have a flat file connection where I defined the name of the flat file in my ConnectionString. I also have a variable that I have it linked to my dtsConfig which points to the proper folder name at run time.
How can I create an expression similar to this:
@[User::strFolder] + @[Connectionstring]
where @[User::strFolder] is my variable and points to the correct folder for the given server the package is running on and @[Conenctionstring] is my made up name to access the VALUE of the Connectionstring that I have for this flat file.
So if I have the following:
in my connectionstring property: flatfile.txt
in my strFolder derived from dtsConfig at runtime: E:etl_data
I would like my final connectionstring to look as follow:
E:etl_dataflatfile.txt
So far I know I can do it with two variables but it would be great if I could reuse the property values of the current object for my expressions or any other object.
Perhaps this value is available thru the script where I can access "any" property in my dtsx and store it into another variable and then use it. This option at least allows me to reuse code instead of hardcoding table name (connectionstring) into my variables.
Did I make this too difficult and there is a simple way to access an object's property inside the expression builder?
Thanks
Anatole
How do I access sqldatasource data values in code behind and bind them to strings or texboxes etc. as oposed to using Eval in the markup?
I can create a new database connection, but I would like to use the data values from the autogenerated sqldatasource control
Many thanks,
HiI've got a quandry - I have a detailed database that handles advertorders between a design agency and printers / magazines etc.I want to add up the total spent by the client and put the results to afield.I've actually done that using a query table in access - it should bequite simple as i can bind the 'total amount' to my table - the onlything it does not currently do is filter the total based on the monthselected.For example if you look athttp://www.daneverton.com/dg2data/months/2006-12.aspThe data here is filtered by the issue equaling Dec-2006The actual order total is £13,622 but the column is showing the totalfor all entries to date (a years worth = £ 422,048)I'm sure that there is only a basic tweak required but i'm banging myhead over what to doThe sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE[Issue / Edition] LIKE ? ORDER BY Publication ASC"Any help gladly received.
View 1 Replies View RelatedQuestion: Why would I not be able to import an Access 97 table inwhich some records have null values in fields that allow null values?Wouldn't the table's design be imported first, bringing the columns'"allow nulls" attribute with it?I'm dealing with both text and numeric columns. Not all columnscontaining nulls cause an error.Thanks,Bob C.
View 2 Replies View RelatedHi there.We have an application that can run on a non-unicode or a unicode sqlserverdatabase.Currently the application is running in a unicode database, as anon-unicode database is less than half the size, I would prefer tohave a non-unicode database for demo purposes to be on my laptop, etcetcIs it possible to change a unicode sql server 2000 database into anon-unicode database?And if so, how would I go about doing this?Any help would be greatly appreciated.ThanksRodger
View 2 Replies View RelatedI'm executing a stored procedure but got error :
Msg 213, Level 16, State 1, Procedure ExtSales, Line 182
Column name or number of supplied values does not match table definition.
Goodday.
I have finally been able to create a connection from Access to the SQL 2005 Server and was able to call a stored proc (in Server) in the following way
Code Block
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
cnn.ConnectionString = "Provider='sqloledb'; Data Source='Private';" & _
"Initial Catalog='DBName';Integrated Security='SSPI';"
cnn.Open
cmd.ActiveConnection = cnn
cmd.CommandText = "sp_DefaultEntityData"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute
rst.MoveFirst
Do While Not rst.EOF
lstEntity.AddItem (rst.Fields(0)), 0
'lstEntity.AddItem (rst.Fields(1)), 1
rst.MoveNext
Loop
The Stored Proc is as follow:
Code Block
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_DefaultEntityData]
AS
BEGIN
SET NOCOUNT ON;
SELECT tblEntities.[Name], tblEntities.PrimaryKey
FROM tblEntities
ORDER BY tblEntities.PrimaryKey;
END
The table contains 24 entries
As you can see in the VB code, I am trying to read the returned "table" into an Access ListBox. The listbox should display the entities Name but not the Primary key, but the primary key should still be "stored" in the to so that it can be used to access other data.
I have moved the tables from Access to SQL Server 2005, and would also like to port all the sql queries to sp's in SQL Server. The old way for populating the listbox was a direct SQL query in the RowSource property field. I have tried to set the lstEntity.RowSource = rst but it did not work.
Here are my Q's:
1) As what does the SP return when it is called and is there a better way to catch it than I am doing at the moment?
2) How do I read the values into the listbox, without displaying the primary key in die box?
Thank you in advance!
Any help is very much appreciated.
Hi all,
In my SQL Server Management Studio Express (SSMSE), pubs Database has a Stored Procedure "byroyalty":
ALTER PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
And Table "titleauthor" is:
au_id title_id au_ord royaltyper
172-32-1176
PS3333
1
100
213-46-8915
BU1032
2
40
213-46-8915
BU2075
1
100
238-95-7766
PC1035
1
100
267-41-2394
BU1111
2
40
267-41-2394
TC7777
2
30
274-80-9391
BU7832
1
100
409-56-7008
BU1032
1
60
427-17-2319
PC8888
1
50
472-27-2349
TC7777
3
30
486-29-1786
PC9999
1
100
486-29-1786
PS7777
1
100
648-92-1872
TC4203
1
100
672-71-3249
TC7777
1
40
712-45-1867
MC2222
1
100
722-51-5454
MC3021
1
75
724-80-9391
BU1111
1
60
724-80-9391
PS1372
2
25
756-30-7391
PS1372
1
75
807-91-6654
TC3218
1
100
846-92-7186
PC8888
2
50
899-46-2035
MC3021
2
25
899-46-2035
PS2091
2
50
998-72-3567
PS2091
1
50
998-72-3567
PS2106
1
100
NULL
NULL
NULL
NULL
////////////////////////////////////////////////////////////////////////////////////////////
I try to do an ADO.NET 2.0-VB 2005 programming in my VB 2005 Express to get @percentage printed out in the VB Form1. I read some articles in the websites and MSDN about this task and I am very confused about "How to Work with Output Parameters & Report their Values in VB Forms": (1) Do I need the Form.vb [Design] and specify its properties of the object and classes I want to printout? (2) After the SqlConnectionString and the connection.Open(), how can I bring the value of @percentage to the Form.vb? (3) The following is my imcomplete, crude draft code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI;"
Dim connection As SqlConnection = New
SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("byroyalty", connection)
command.CommandType = CommandType.StoredProcedure
...................................................................
..................................................................
etc.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From the above-mentioned (1), (2) and (3), you can see how much I am lost/confused in attempting to do this task. Please help and give me some guidances and good key instructions for getting the output parameter printed out in the FORM.vb in my VB 2005 Express project.
Thanks in advance,
Scott Chang
Public Function WriteUniCode(ByVal InQ As String) As String
Dim tmpStr1 As String, i As Integer
For i = 1 To Len(InQ)
tmpStr1 &= AscW(Mid(InQ, i, 1))
If i < Len(InQ) Then tmpStr1 &= ","
Next
WriteUniCode = tmpStr1
End Function
Public Function ReadUniCode(ByVal InQ As String) As String
Dim tmpStr1(), tmpStr2 As String, i As Integer
tmpStr1 = Split(InQ, ",")
For i = 0 To UBound(tmpStr1)
tmpStr2 &= ChrW(tmpStr1(i))
Next
ReadUniCode = tmpStr2
End Function
Write :
SqlStr = "Insert Into Table1(Col1) Values('" & WriteUniCode("YourText As UniCode") & "')"
Read :
SqlStr = "Select Col1 From Table1"
Textbox1.Text = ReadUniCode(DataRead.item("Col1"))
Hi. I have a hard choice on my hands. I'm not actually sure if I should use n
data types or normal ones.
I need to be able to use several different languages inside a single string
(long articles written in French, English, partially German and Swedish).
I'm developing my ASP.NET application using UTF-8 for all data to ensure I
can display all languages. I'm in doubt what types to use on the SQL server
when I store data... n-something like nchar or normal types like char.
Also, that N prefixing all inputs sounds messy :-)(http://support.microsoft.com/?kbid=239530)Also should I use "UTF-8" or "Unicode" (UTF-16, right?)?
Please adwise. Thank you.
Hi everybody
My database contain both chinese and english datas. When working with select command i am able to retrieve only english datas eventhough i have made all the datatypes nvarchar.
I am using a stored procedure to retrieve the data
declare @chinese nvarchar
select field_name from Table_name
where name like @chinese+'%'
The qurey is working if we give
select * from table name where name like N'政府机关'
pls help me to put this query in a procedure
pls reply
thanks
i've am trying to move a database from SQL6.5 to SQL2005.
I tried to move one of the tables which was a number of char fields to nvarchar fields. Errors came up saying it had Unicode characters ?
Does anyone know how i can remove this ?
smart writes "hi
please help me to insert unicode characters in sql
more information:
* SQL server 2000
* filed data type: varchar or nchar or nvarchar
but it does not work properly and all characters converted to Question Mark(?)
please help me
with many thanks
Regards,
SMART"