Click here to Skip to main content
15,920,438 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
CREATE Procedure [dbo].[InsertUpdate_DashBoardMst]  
(     
 @nDashBoardNo numeric(18,0),  
 @nCompanyNo numeric (18, 0) ,  
 @nPositionNo numeric (18,0) ,  
 @vDashBoardTitle nvarchar (255) ,  
 @iWidth Integer ,  
 @iHeight Integer ,  
 @iLayoutNo Integer ,   
 @bIsActive Bit ,  
 @nUserNo Numeric(18,0),   
 @DataOpMode TinyInt,  
 @nDashBoardNo_OutPut Int Output  
)   
  
As  
  
Begin  
if (@DataOpMode = 1)  
 begin  
    
  insert into DashBoardMst(nCompanyNo, nPositionNo, vDashBoardTitle, iWidth, iHeight, iLayoutNo, bIsActive,  
         nCreatedUserNo, nUserNo, dCreatedOn, dModifyOn)           
  values (@nCompanyNo, @nPositionNo, @vDashBoardTitle, @iWidth, @iHeight, @iLayoutNo, @bIsActive,  
         @nUserNo, @nUserNo, GETDATE(), GETDATE())  
  
  set @nDashBoardNo_OutPut = @@IDENTITY  
 end  


i only get problem when i try to save the data into Dashboardmst with DataOpMode=1
my data some sort like
1,1,1,'ask',0,0,1,1,14,3,1

if add this manually then saved succesfully but using vb code
VB
For Each dr_Save As Data.DataRow In tbl4Save.Rows

               sqlCmd = New SqlCommand(ProcedureName_1, _objDtLogic.Connection, _objDtLogic.Transaction)
               sqlCmd.CommandType = CommandType.StoredProcedure
               ParaRowArr = New ArrayList

               For Each dr_P As Data.DataRow In tbl_ProcDtl.Rows

                   Proc_ColName = dr_P("COLNAME").ToString.Substring(1).ToUpper
                   ColIndex_1 = CType(dr_P("COLINDEX"), System.Int32) - 1

                   SqlPara = New Data.SqlClient.SqlParameter()
                   SqlPara.ParameterName = dr_P("COLNAME").ToString
                   SqlPara.Direction = ParameterDirection.Input

                   If Proc_ColName = "DATAOPMODE" Then

                       '-- Change by Gunjan Gandhi as on 14/04/08
                       '
                       SqlPara.Value = dr_Save("DATAOPMODE") 'Convert.ToInt32(Choice_1)

                   ElseIf Convert.ToInt32(dr_P("ISOUTPUT")) = 0 Then ' when column is not output parameter

                       SqlPara.Value = dr_Save(Proc_ColName)

                   ElseIf Convert.ToInt32(dr_P("ISOUTPUT")) = 1 Then ' when column is output parameter

                       SqlPara.Direction = ParameterDirection.Output
                       SqlPara.Size = dr_P("LENGTH")

                       ParaRetuVal = New ParameterReturnValue
                       ParaRetuVal.ParameterName = dr_P("COLNAME")
                       ParaRetuVal.ParameterIndex = ColIndex_1
                       ParaRowArr.Add(ParaRetuVal)

                   End If

                   sqlCmd.Parameters.Add(SqlPara)

               Next dr_P

               sqlCmd.ExecuteNonQuery()

here tbl4Save is target table which has data and tbl_ProcDtl has data with datatype information
and it's give the error "Error while converting in to nvarchar"

does i missing something?
Posted
Comments
PrissySC 23-Jun-13 11:30am    
I don't have your answer but you should be aware of the deprication for 2012 which includes nvarchar ... http://msdn.microsoft.com/en-us/library/ms186302.aspx
7045Jeegnesh 24-Jun-13 7:44am    
thank u. i am aware about that and i tied everything on the web.
PrissySC 24-Jun-13 9:56am    
I think it is your conversion. I am linking a table that tells you when to use explicit and implicit conversion. This should help you. I consult it when I need to know.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

@vDashBoardTitle nvarchar (255) - As you mention the web, I have to ask why nvarchar then. It is twice the cost to use over varchar. There are quite a few articles on it ... memory and performance have been discussed at length. Test thoroughly. How large is the data that your are converting? You have a "255" limit. Try outputing the test to as a string to view (shortest way to check).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900