Command object use to add data to a database

[eranga262154]

Hi all,

I want to add a string and a character to two columns of a sql database table. To do that I use a Command object rather to use the same connection on different functions. At the same time used a stored procedure to add data.

Hear are some code segments.

// VC++ code use to add data 
void CDbService::AddGData(string& fileName)
{
_CommandPtr pCom;

pCom.CreateInstance(__uuidof(Command));

pCom->ActiveConnection = m_pConn;
pCom->CommandType = adCmdStoredProc;
pCom->CommandText = _bstr_t("sp_GroupState");

// Add file name
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("FileName"), adVarChar, adParamInput, 50,&fileName));
}

Here is the stored procedure I used

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[sp_GroupState] 
@FileName varchar, @State varchar
As
Insert into tblGroupState(DiskFileName, FileState)
Values (@FileName, @State);

But this code not added any data to my table. Here is the table create query I used.

USE [RFDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblGroupState](
	[DiskFileName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FileState] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Can you guys see any mistake I have made. It's really appreciate.

[eranga262154]

By debuging my code I found that, use the wrong data type for the filename adding, that is adVarChar. Do you guys know what should I use for it.

[nav33n]

In mysql, we dont have adVarChar! Its either char, varchar or text.

[eranga262154]

Yep, adVarChar is not a mysql data type. Its ADODB enum data type.

[nav33n]

So, what are you looking for ? An error in your code or the datatype ? :s

[eranga262154]

Yep, on the line where I write the file name is the error. I can write any other value to the DB. Like int, text etc...

But I can't write a string(file name is a string there) to the database using that variable. Actually using adVarChar I can add a character successfully. I can't use it to add a stream of characters.

[nav33n]

If you are inserting a string to a table, you have to use single quotes around it. Eg. insert into tablename (varchar_field,int_field) VALUES ('this is a test',3); That might be causing the problem at this line.

Insert into tblGroupState(DiskFileName, FileState)
Values (@FileName, @State);

[eranga262154]

Actually I use stored procedure for this, to add data to the database. Can you please look at my code on the first post.

[nav33n]

umm.. I am not much sure about the procedure you are using. But I use something like this.

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_addcategory`(IN categoryid INT,IN published TINYINT,IN categoryname VARCHAR(60),IN contents TEXT)
BEGIN
 insert into category (ct_name,ct_published,ct_description) values (categoryname,published,contents);
 END

[eranga262154]

Ok, but I want to do it using a Stored Procedure. Actually the issue is about EnumDataType, which should be used in inserting a line of text(a string actually).

Do you know that data type pal?

[nav33n]

Sorry dude! I don't know :(

[Sulley's Boo]

To create the usp_InsertGroup stored procedure, right-click the Stored Procedures node and choose New Stored Procedure or click the Database menu and choose New Stored Procedure. Enter the following code:

SQL Server

CREATE PROCEDURE usp_InsertGroup
(
   @GroupID            UNIQUEIDENTIFIER,
   @GroupName          VARCHAR(50),
   @GroupDescription   TEXT
)
AS
INSERT INTO Groups 
   (GroupID, GroupName, GroupDescription, LastUpdateDate)
   VALUES(@GroupID, @GroupName, @GroupDescription, GETDATE())

Source: http://www.wrox.com/WileyCDA/Section/id-107160.html

:?:

what do the square-brackets in your procedure name do?

[nav33n]

To create the usp_InsertGroup stored procedure, right-click the Stored Procedures node and choose New Stored Procedure or click the Database menu and choose New Stored Procedure. Enter the following code:

SQL Server

CREATE PROCEDURE usp_InsertGroup
(
   @GroupID            UNIQUEIDENTIFIER,
   @GroupName          VARCHAR(50),
   @GroupDescription   TEXT
)
AS
INSERT INTO Groups 
   (GroupID, GroupName, GroupDescription, LastUpdateDate)
   VALUES(@GroupID, @GroupName, @GroupDescription, GETDATE())

Source: http://www.wrox.com/WileyCDA/Section/id-107160.html

:?:

what do the square-brackets in your procedure name do?

Its an optional field which specifies the owner of that procedure, followed by procedure name.

Source: http://msdn2.microsoft.com/en-us/library/aa258259(SQL.80).aspx

[Sulley's Boo]

t-hintu jee :D

[eranga262154]

Anyway thanks for all your guys replay. I've correct my mistake. Used a null-terminated string, and on that I can use adVarChar variable type without any issue.