Vladut Vladut - 4 months ago 18
SQL Question

Why my procedure insert just first character from string?

Why my procedure insert just first character from string?
I have dat query:

USE [TCO_Orders_SS]
GO
/****** Object: StoredProcedure [dbo].[InsertIroBOMParts] Script Date: 07/15/2016 08:28:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertIroBOMParts]
@OrderID int,
@OrderSelection int,
@Idx varchar,
@Component varchar,
@DrawingNo varchar,
@PartNo varchar,
@BatchNoParts varchar,
@Amount varchar,
@Comments varchar

AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Orders_IroBOMParts(OrderID,OrderSelection, idx,componenent,drawingno,partno,batchno,amount,comments)

VALUES (@OrderID,@OrderSelection, @Idx,@Component,@DrawingNo,@PartNo,@BatchNoParts,@Amount,@Comments)
END


I try to insert into for more line but insert just first caracter from my string, if i have String "Modern" put into table just "M". BUT WHY?

enter image description here

My code for insertion in vb.net are:

For Each gvr As TableRow In TabelVerificari.Rows

For i = 1 To TabelVerificari.Rows.Count - 1

Idx = "P" & i
Dim Result1 As TextBox = gvr.FindControl("BOX_Component" & "_" & Col & i)
Dim Result2 As TextBox = gvr.FindControl("BOX_Drowing" & "_" & Col & i)
Dim Result3 As TextBox = gvr.FindControl("BOX_PartNo" & "_" & Col & i)
Dim Result4 As TextBox = gvr.FindControl("BOX_BatchNo" & "_" & Col & i)
Dim Result5 As TextBox = gvr.FindControl("BOX_Amount" & "_" & Col & i)
Dim Result6 As TextBox = gvr.FindControl("BOX_Comments" & "_" & Col & i)

Component = Result1.Text
Drawing = Result2.Text
PartNo = Result3.Text
batchNo = Result4.Text
Amount = Result5.Text
Comments = Result6.Text

Dim InsertBOM As SqlConnection = Nothing
InsertBOM = FunctionConnection()
Dim cmdBOM As SqlCommand = New SqlCommand()

Try
'Insert Function
cmdBOM.Connection = InsertBOM
cmdBOM.CommandTimeout = 50
cmdBOM.CommandType = System.Data.CommandType.StoredProcedure
cmdBOM.CommandText = "InsertIroBOMParts"

cmdBOM.Parameters.AddWithValue("@OrderID", OrderID)
cmdBOM.Parameters.AddWithValue("@OrderSelection", i)
cmdBOM.Parameters.AddWithValue("@Idx", Idx)
cmdBOM.Parameters.AddWithValue("@Component", Component)
cmdBOM.Parameters.AddWithValue("@DrawingNo", Drawing)
cmdBOM.Parameters.AddWithValue("@PartNo", PartNo)
cmdBOM.Parameters.AddWithValue("@BatchNoParts", batchNo)
cmdBOM.Parameters.AddWithValue("@Amount", Amount)
cmdBOM.Parameters.AddWithValue("@Comments", Comments)

InsertBOM.Open()
cmdBOM.ExecuteNonQuery()

Catch ex As Exception
LBL_Error.Text = "Please complete correctly boxes!"
Exit Sub
Finally

If InsertBOM.State = ConnectionState.Open Then
InsertBOM.Close()
End If
'Session("OrderNo") = OrderID
'Response.Redirect("MV_UpdateSample.aspx")
End Try

Next
Exit For
Next

Answer

You need to specify the width of your VARCHAR variables. Currently you have:

@Idx varchar,
@Component varchar,
@DrawingNo varchar,
@PartNo varchar,
@BatchNoParts varchar,
@Amount varchar,
@Comments varchar

This specifies no numerical width, which defaults to a width of one character. Instead, specify a width in number of characters which you expect to reasonably fit all your data, e.g.:

@Idx varchar(55),
@Component varchar(55),
@DrawingNo varchar(55),
@PartNo varchar(55),
@BatchNoParts varchar(55),
@Amount varchar(55),
@Comments varchar(55)

This would give each VARCHAR a width of 55 characters.

Comments