USE Parameter.Value to load VARCHAR

[beanNut]

Environment: Windows XP
Using MS Visual Studio 2005
SQL Server 2000

My Question: I decided to change an integer column in my SQL server table to a VARCHAR(32). I changed my SQL server stored procedure to accept a varchar(32) input parameter and I changed the VB.net application to correctly Add the varchar parameter to my insert command.
I'll include snippet in a moment.

Problem: The error message tells me that it recognizes my variable input as VARCHAR but tries to convert it to INT.

I dimension the variables as string in my VB.net code.
Populate it with the text data and then pass it to the stored procedure call from VB. When this didn't work I tried a test and actually sent integer values instead of text. And this worked fine. The SQL insert correctly inserted integer values into my VARCHAR fields.

REQUEST: Your help is appreciated. I hope you can spot something that explains why it won't take the TEXT values.

SNIPPET:
From VB.net

Dim txtChar1 as String
Dim cmd As New SqlClient.SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "pr_test"
cmd.Parameters.Add("@CharTEXT", SqlDbType.VarChar, 32)
Dim SQlconn As New SqlClient.SqlConnection
cmd.Connection = SQlconn
SQlconn.Open()
cmd4.Parameters("@CharTEXT").Value = String.Format(txtChar1)
cmd.ExecuteNonQuery()

From: SQL Server
ALTER PROCEDURE dbo.pr_test
(
@CharTEXT varchar(32),
)
AS
SET NOCOUNT OFF;
BEGIN
INSERT INTO TestTable (CharacteristicID)
VALUES (@CharTEXT)
END

RETURN

ERROR MESSAGE:
Syntax Error converting the VARCHAR VALUE 'MYINPUT' to a column of datatype int


By the way, the Table has a varchar(32) data item.
So I tried some various formatting attempts to try to get my Visual Basic. NET code to accept the varchar text. But most reasonable solutions don't seem to work. You'll will notice I'm using String.Format(txtChar1). I tried txt.Char1.TEXT but intellisense wouldn't allow it. Any ideas appreciated.

 

[Ruffnekk]

INSERT INTO TestTable (CharacteristicID)
VALUES (@CharTEXT)

---

Are you sure the INSERT command is properly defined this way? It seems to me you are trying to write a VARCHAR value into the CharacteristicID field, which I presume to be of type INT. But that might of course be the field you altered from INT to VARCHAR.

Also, I notice this line:

cmd4.Parameters("@CharTEXT").Value = String.Format(txtChar1)

which uses 'cmd4' instead of 'cmd'. I hope that's just a typo though.

I also wonder why you are using 'String.Format(txtChar1)'. Simply 'txtChar1' should suffice in this case. You are confusing yourself ('txtChar1.Text' doesn't work) by naming the variable 'txtChar1', which indicates it to be of type TextBox, according to various naming conventions. You declared it to be a string and it is wise to use 'sChar1' or 'strChar1'.

Regards, Ruffnekk
---
Is it my imagination or do buffalo wings taste just like chicken?

 

[chrissie1]

where do you say what txtchar1 is?

txtchar1 is empty, no value whatsoever.

Christiaan Baes
Belgium

"My new site" - Me

 

[beanNut]

Response to Ruffnekk
thanks for reviewing.

Yes, I think the insert is correct.
CharacteristicID - is a Varchar(32)
I probably should have call it txtCharacteristic
cmd4 should have been cmd in my post.
I agre I think I should be able to use txtChar1 w/o formatting or casting.

Remember: This is all working when I change the CharacteristicID to an integer. And your right I think the stored procedure is expecting an integer so I checked to make sure it was defined as a input variable type varchar.

Chrissie1(Programmer)
Thanks for response. Yes, I'm setting textchar1 to "text"
Just forgot to show that in posting.
Do you think I need to format txtchar1 in some way to get the store procedure to understand it instead of converting it to int?? Remember I loaded txtChar1 with and integer and it workd without any other changes. this means the vb program actually worked using the stored procedure "as is".
My thought is that either the stored procedure is wrong or the parameters.Add isn't setting it to type varchar...

 

[Ruffnekk]

You say when you use it with an integer it works. Have you verified that it writes the correct value? Or does it always write 0?

Shouldn't you ommit the '@' in the Parameters.Add and Paramters.Value statements?

Regards, Ruffnekk
---
Is it my imagination or do buffalo wings taste just like chicken?

 

[beanNut]

Response to Ruffnekk
With my integer test, I verified that it writes to the database the correct value.
I have used the debugger to verify that the varchar contains a string value of whatever I put into it. So when it sends it to SQL server this is when it definitely gets the error. I have verified that SQL server parameter is defined as a varchar (32) variable.

You questioned the @ symbol. Here is a snippet of code from the internet which shows how to setup "parameters.add construct". It uses and @ symbol

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _
"Initial Catalog=northwind")

Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn)

catDA.InsertCommand = New SqlCommand("InsertCategory" , nwindConn)
catDA.InsertCommand.CommandType = CommandType.StoredProcedure

Dim myParm As SqlParameter = catDA.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int)
myParm.Direction = ParameterDirection.ReturnValue

catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName")

myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID")
myParm.Direction = ParameterDirection.Output

Dim catDS As DataSet = New DataSet()
catDA.Fill(catDS, "Categories")

Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
catDS.Tables("Categories").Rows.Add(newRow)

catDA.Update(catDS, "Categories")

Dim rowCount As Int32 = CInt(catDA.InsertCommand.Parameters("@RowCount").Value)

 

[beanNut]

I did some further testing with this problem today.
2-22-2006. Instead of passing in TEXT values I passed
an integer to the VARCHAR(32) variable. The VB program worked fine passing the value to the store procedure.

However, if I pass text into the varchar variable "sometext" it tries to implicitly convert it to an int.

Does anyone have an idea to correct this??

 

[beanNut]

Here is the code again.

Dim txtChar1 as String
Dim cmd As New SqlClient.SqlCommand
txtChar1 = "sometext"
' set txtChar1 to an integer and it works
' set txtChar1 to a stringvalue like "sometext" if fails
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "pr_test"
cmd.Parameters.Add("@CharTEXT", SqlDbType.VarChar, 32)
Dim Sqlconn As New SqlClient.SqlConnection
cmd.Connection = Sqlconn
Sqlconn.Open()
cmd4.Parameters("@CharTEXT").Value = txtChar1
cmd.ExecuteNonQuery()

SQL Server Procedure contains:
ALTER PROCEDURE dbo.pr_test
(
@CharTEXT varchar(32)
)
AS
SET NOCOUNT OFF;
BEGIN
INSERT INTO TestTable (CharacteristicID)
VALUES (@CharTEXT)
END
RETURN

 

[beanNut]

Here is the code again. Corrected....

Dim txtChar1 as String
Dim cmd As New SqlClient.SqlCommand
txtChar1 = "sometext"
' set txtChar1 to an integer and it works
' set txtChar1 to a stringvalue like "sometext" if fails
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "pr_test"
cmd.Parameters.Add("@CharTEXT", SqlDbType.VarChar, 32)
Dim Sqlconn As New SqlClient.SqlConnection
cmd.Connection = Sqlconn
Sqlconn.Open()
cmd.Parameters("@CharTEXT").Value = txtChar1
cmd.ExecuteNonQuery()

SQL Server Procedure contains:
ALTER PROCEDURE dbo.pr_test
(
@CharTEXT varchar(32)
)
AS
SET NOCOUNT OFF;
BEGIN
INSERT INTO TestTable (CharacteristicID)
VALUES (@CharTEXT)
END
RETURN