Implicit conversion from data type sql_variant to varchar is not allowed

[alemio]

I'm working with SQL server.
If I try to do an INSERT like this:

TUniSQL *UniSQL = new TUniSQL(NULL);
UniSQL->Connection = UniConnection1;
UniSQL->Debug = true;
UniSQL->ParamCheck = false;
AnsiString text = "INSERT INTO AgeCategories VALUES (:AgeCategoryId, :AgeCategoryName, :LocalizedAgeCategoryName)";
UniSQL->SQL->Clear();
UniSQL->SQL->Add(text);
UniSQL->ParamCheck = true;
UniSQL->Prepared = true;
UniSQL->Params->ParamByName("AgeCategoryId")->AsString = "AA";
UniSQL->Params->ParamByName("AgeCategoryName")->AsString = "TEST";
UniSQL->Params->ParamByName("LocalizedAgeCategoryName")->AsString = AnsiString("a");

UniConnection1->StartTransaction();
try
{
UniSQL->Execute();
UniConnection1->Commit();
}
catch (Exception *E)
{
UniConnection1->Rollback();
throw E;
}

I get an exeption:

Project TestSQLServer.exe raised exception class EUniError with message 'Statement(s) could not be prepared.
Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.'. Process stopped. Use Step or Run to continue.

The table schema is the following:

CREATE TABLE [dbo].[AgeCategories](
[AgeCategoryId] [varchar](2) NOT NULL,
[AgeCategoryName] [nvarchar](50) NOT NULL,
[LocalizedAgeCategoryName] [nvarchar](50) NULL,
CONSTRAINT [PK_AgeCategories] PRIMARY KEY CLUSTERED
(
[AgeCategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

It works if I execute visually on the form.

Regards

[azyk]

To avoid this error message, you should set the TUniSQL->Prepared property in your code sample to true after you assign values to all the parameters. Taking into account this edit, your code will look like the following:

Code: Select all

...
// UniSQL->Prepared = true; // remove this line
UniSQL->Params->ParamByName("AgeCategoryId")->AsString = "AA";
UniSQL->Params->ParamByName("AgeCategoryName")->AsString = "TEST";
UniSQL->Params->ParamByName("LocalizedAgeCategoryName")->AsString = AnsiString("a");
UniSQL->Prepared = true; // insert code here
...

[alemio]

Thank you, works fine.
Another question: should I use one connection for each thread or just one for all?

[azyk]

A separate TUniConnection instance must be used for each thread in a multi-threaded application.

[alemio]

Thank you.

Just one question

I have a TUniConnection in my c++ form

object UniConnection1: TUniConnection
ProviderName = 'SQL Server'
Database = 'RaceManagerDevelopment'
SpecificOptions.Strings = (
'SQL Server.Authentication=auWindows')
Pooling = True
Server = 'NB-ALESSANDROM\SQLEXPRESS'
Connected = True
LoginPrompt = False
Left = 424
Top = 168
end

And I can connect this component to the sql server database.
But if I do that in the code

TUniConnection* _uniConnection = new TUniConnection(NULL);
_uniConnection->ConnectString= "Provider Name=SQL Server;Login Prompt=False;Pooling=True;Data Source=NB-ALESSANDROM\SQLEXPRESS;Initial Catalog=RaceManagerDevelopment;Port=0;Integrated Security=SSPI";
_uniConnection->SpecificOptions->Add("SQL Server.Authentication=auWindows");
_uniConnection->Connect();

I receive this error

'Named Pipes Provider: Could not open a connection to SQL Server [53]. '

What is wrong?

[alemio]

Thank you, I see now ... should be "NB-ALESSANDROM\\SQLEXPRESS" instead of "NB-ALESSANDROM\SQLEXPRESS"

I took this string from inspector.

Now it works

Thank you again for your support

[azyk]

Glad to see that the issue was resolved. If any other questions come up, please contact us.