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.

[techgnome]

Would a Mod please help this lost thread find its way to the C/C++ forums? It zigged when it should have zagged.

But, since I'm here anyways... two things stand out. 1) I only see one parameter being created and appended. 2) The parameter definitions in the SP aren't complete... you've specified the data type as varchar, but w/o a size. That may be enough to prevent it from executing properly.

-tg

[eranga262154]

First of all, why I put this thread it is mainly related to DB, I think. Anyway...

Actually my SP not gives any error at all when I'm executing it. Most important this I found on debuging mode is that, adVarChar is not the correct variable type I used to add a string to a DB. Do you have any suggestion for me.

[techgnome]

Sorry... I still to this day don't know why Db dev is under VB... but any ways....

The varchar I mentioned was in the SP itself:
@FileName varchar, @State varchar <-- there should be size limits on it....
Operative being "should"....

You got me.... No errors, eh? it just simply silently fails to insert the data?

Ok, this is going to sound weird, but jsut for shifts and giggles.... look in the master database.... make sure there isn't a copy of this sp in there. When you execute a stored procedure that starts with "sp_" SQL Server will actually FIRST look into the master database.... turns out that "sp_" to SQL Server means "system procedure" .... only when it fails to find the sp in the Master does it revert back to the connected db..... We spent weeks once tracking down a problem that really wasn't there. All because some one ran the sp scripts in the master database.

Wait..... Um... you are executing it, right? It's not in the code you posted... so either you aren't executing it, or it's executed somewhere else....

-tg

[eranga262154]

The varchar I mentioned was in the SP itself:
@FileName varchar, @State varchar <-- there should be size limits on it....
Operative being "should"....

Yep, actually when I define the size it gives an error.

Ok, this is going to sound weird, but jsut for shifts and giggles.... look in the master database.... make sure there isn't a copy of this sp in there. When you execute a stored procedure that starts with "sp_" SQL Server will actually FIRST look into the master database.... turns out that "sp_" to SQL Server means "system procedure" .... only when it fails to find the sp in the Master does it revert back to the connected db..... We spent weeks once tracking down a problem that really wasn't there. All because some one ran the sp scripts in the master database.

Wait..... Um... you are executing it, right? It's not in the code you posted... so either you aren't executing it, or it's executed somewhere else....

-tg

Yes, I'm really wired with this.

Sorry I've missing that execution line of the pCom object. It is ok, I already put it there.

To test that my SP and all other stuff I write extra data there, and its work. Once again pal, I think my error is with that wrong selection of variable type. I want to find it. Work on Google and still no luck to me

[techgnome]

What error does it give, when you do define the size?

-tg

[eranga262154]

Following is the error I got when I define the size into 50.

Msg 102, Level 15, State 1, Procedure sp_GroupState, Line 3
Incorrect syntax near '50'.
Msg 137, Level 15, State 2, Procedure sp_GroupState, Line 6
Must declare the scalar variable "@FileName".

[techgnome]

That's a strange error.... you defined it like this?

@FileName varchar(50), @State varchar(2)

-tg

[eranga262154]

Oops, I try it as @FileName varchar[50]. It gives an error.

Like this, @FileName varchar, not gives any error. It added data to the DataBase. Only thing I can't add there is the filename. That is something wrong with variable type(adVarChar) enum I used in my code.

That mean there is some default variable size define by the sql sever, isn't it?

[techgnome]

No, adVarChar tells it the data type.... which is correct.... On the SQL Side.... I don't know that there is a default size when you don't specify a size for varchars.... I've always defined the size explicitly, so I was thinking that was the problem..... but now..... No clue...

Just for fun, try also including the @ with the parameter name when creating the parameters.... it's not supposed to matter, but... at this point, anything is possible.

-tg

[eranga262154]

Ok, I'll try it. At the same time I have one thing to know, do it for testing.

Create a SQL table with two columns and data type make as text. Column names are DiskFileName and FileState. Table name is tblGroupState.

Write a sql query to update as follows.

UPDATE tblGroupState SET FileState = 'R' WHERE DiskFileName = 'F_001.txt'

Is that right. As far as I know it is ok, but didn't update it my database.

[eranga262154]

I've modify my table, change column data types into varchar and it works. Now where I'm wired is, since text is used in large variables why I can't use it here.