passing a string to an int??

D

DaveF

I am not sure how to pass the variable. It seems to need an int, but if I
try to pass an in, it drops the commas

Dim dr As SqlDataReader = appGen.AppInfoBuild("1,2,3,4")

Public Function AppInfoBuild(ByVal appVals As String)
' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
Dim myCommand As New SqlCommand("AppInfoBuild", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure



Dim parameterappVals As New SqlParameter("@appVals", SqlDbType.Int)
parameterappVals.Value = appVals
myCommand.Parameters.Add(parameterappVals)

' Open the database connection and execute the command
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Function


CREATE Procedure AppInfoBuild
(
@appVals int
)
AS
SELECT App.f_name, App.m_initial, App.l_name, App.b_address, App.b_City,
stateTable.state, App.b_Zip,
App.b_dayarea + '-' + App.b_dayphone1 + '-' +
App.b_dayphone2 + '-' + App.b_dayext AS B_BusPhone,
App.b_evearea + '-' + App.b_evephone1 + '-' +
App.b_evephone2 AS B_HomePhone,
App.b_cellarea + '-' + App.b_cellphone1 + '-' +
App.b_cellphone2 AS B_CellPhone, App.b_faxphone2,
App.b_SSN1 + '-' + App.b_SSN2 + '-' + App.b_SSN3 AS
b_SSN, App.b_Age + '-' + App.b_Age1 + '-' + App.b_Age2 AS b_Age,
App.b_School,
App.b_MaritalStatusID, App.b_d_No, App.b_d_Ages,
App.cb_FirstName, App.cb_MiddleInitial, App.cb_LastName,
App.cb_SSN1 + '-' + App.cb_SSN2 + '-' + App.cb_SSN3 AS
cb_SSN, App.cb_Age + '-' + App.cb_Age1 + '-' + App.cb_Age2 AS cb_Age,
App.cb_School,
App.cb_evearea, App.cb_evephone1, App.cb_evephone2,
App.cb_MaritalStatusID, App.cb_d_No, App.cb_d_Ages, App.b_Rent,
App.b_Rent_Length,
App.b_Address1, App.b_City1, stateTable_3.state AS
b_state1, App.b_Zip1, App.b_Rent_Length1, App.b_Rent1, App.cb_Address,
App.cb_City,
stateTable_4.state AS Expr1, App.cb_Zip,
App.cb_Rent_Length, App.cb_rent, App.cb_Address1, App.cb_City1,
stateTable_5.state AS BState1,
App.cb_Zip1, App.cb_Rent_Length1, App.cb_Rent1,
App.BCompanyName1, App.BStreet1, App.BCity1, stateTable_6.state AS BState2,
App.BZip1,
App.BTitle1, App.BLOE1, App.BtxtMonths, App.BSelfEmp1,
App.BCompanyName2, App.BStreet2, App.BCity2, stateTable_7.state AS BState2,
App.BZip2,
App.BA2 + '-' + App.BP2_1 + '-' + App.BP2_2 AS BP2,
App.BTitle2, App.BSelfEmp2, App.BFromM2 + '/' + App.BFromY2 AS BFrom2,
App.BToM2 + ' / ' + App.BToY2 AS BTo2, App.BMIncome2,
App.CBCompanyName1, App.CBStreet1, App.CBCity1, stateTable_8.state AS
CBState1,
App.CBZip1, App.CBA1 + '-' + App.CBP1_1 + '-' +
App.CBP1_2 + '-' + App.CBX1 AS CBP1, App.CBTitle1, App.CBLOE1,
App.CBtxtMonths,
App.CBSelfEmp1, App.CBCompanyName2, App.CBStreet2,
App.CBCity2, stateTable_9.state AS CBState2, App.CBZip2,
App.CBA2 + '-' + App.CBP2_1 + '-' + App.CBP2_2 AS
CBP2, App.CBTitle2, App.CBFromM2 + '/' + App.CBFromY2 AS CBFrom2,
App.CBToM2 + '/' + App.CBToY2 AS CBTo2,
App.CBMIncome2, App.CBSelfEmp2, App.BorrowerBaseIncome,
App.BorrowerOvertime,
App.BorrowerBonuses, App.BorrowerCommission,
App.BorrowerDividends_Int, App.BorrowerNetRentalIncome, App.BorrowerOther,
App.CoBorrowerBaseIncome, App.CoBorrowerOvertime,
App.CoBorrowerBonuses, App.CoBorrowerCommission,
App.CoBorrowerDividends_Int,
App.CoBorrowerNetRentalIncome, App.CoBorrowerOther,
App.OtherBC1, App.OtherBC2, App.OtherBC3, OtherDes.OtherDesDesc,
OtherDes_1.OtherDesDesc AS OtherDesDesc2,
OtherDes_2.OtherDesDesc AS OtherDesDesc3, App.OtherAmt1, App.OtherAmt2,
App.OtherAmt3,
App.StockCompany1, App.StockAmt1, App.StockCompany2,
App.StockAmt2, App.StockCompany3, App.StockAmt3, App.VestedInt,
App.BusinessVal,
App.LifeInsAmt, App.AutoMake1, App.AutoVal1,
App.AutoMake2, App.AutoVal2, App.AutoMake3, App.AutoVal3, App.OAssetsText1,
App.OAssetsVal1,
App.OAssetsText2, App.OAssetsVal2, App.OAssetsText3,
App.OAssetsVal3, App.OAssetsText4, App.OAssetsVal4, App.AlimonyText,
App.AlimonyVal,
App.JExpText1, App.JExpVal1, App.JExpText2,
App.JExpVal2, App.RentAmt, App.CMortFirst, App.CMortFin, App.CMortHIns,
App.CMortTax, App.CMortIns,
App.CMortDue, App.CMortOther, App.AddressText1,
App.City1 + ', ' + stateTable_10.state + ' ' + App.Zip1 AS prop1,
propType_1.propType,
propStatus_1.propStatusShort, App.MarketVal1,
App.MortgagePayment1, App.MiscCost1, App.AddressText2,
App.City2 + ', ' + stateTable_11.state + ' ' +
App.Zip2 AS prop2, propType_1.propType AS propType2,
propStatus_1.propStatusShort AS propStatusShort2,
App.MarketVal2, App.MortgagePayment2, App.MiscCost2
FROM App INNER JOIN
stateTable ON App.b_StateID = stateTable.taxID LEFT
OUTER JOIN
stateTable stateTable_11 ON App.State2 =
stateTable_11.taxID LEFT OUTER JOIN
propType propType_1 ON App.Type2 =
propType_1.propTypeID LEFT OUTER JOIN
propStatus propStatus_1 ON App.Status2 =
propStatus_1.propStatusID LEFT OUTER JOIN
propType propType_2 ON App.Type1 =
propType_2.propTypeID LEFT OUTER JOIN
propStatus propStatus_2 ON App.Status1 =
propStatus_2.propStatusID LEFT OUTER JOIN
stateTable stateTable_10 ON App.State1 =
stateTable_10.taxID LEFT OUTER JOIN
OtherDes ON App.OtherDes1 = OtherDes.OtherDesID LEFT
OUTER JOIN
OtherDes OtherDes_1 ON App.OtherDes2 =
OtherDes_1.OtherDesID LEFT OUTER JOIN
OtherDes OtherDes_2 ON App.OtherDes3 =
OtherDes_2.OtherDesID LEFT OUTER JOIN
stateTable stateTable_9 ON App.CBState2 =
stateTable_9.taxID LEFT OUTER JOIN
stateTable stateTable_8 ON App.CBState1 =
stateTable_8.taxID LEFT OUTER JOIN
stateTable stateTable_7 ON App.BState2 =
stateTable_7.taxID LEFT OUTER JOIN
stateTable stateTable_1 ON App.PropertyStateID =
stateTable_1.taxID LEFT OUTER JOIN
stateTable stateTable_2 ON App.PropertyStateID =
stateTable_2.taxID LEFT OUTER JOIN
PropertyType ON App.PropertyTypeID =
PropertyType.PropertyTypeID LEFT OUTER JOIN
stateTable stateTable_6 ON App.BState1 =
stateTable_6.taxID LEFT OUTER JOIN
stateTable stateTable_5 ON App.cb_State1 =
stateTable_5.taxID LEFT OUTER JOIN
stateTable stateTable_4 ON App.cb_State =
stateTable_4.taxID LEFT OUTER JOIN
stateTable stateTable_3 ON App.b_State1 =
stateTable_3.taxID
WHERE (App.appId IN ('@appVals'))
GO
 
K

Ken Schaefer

You can't to it this way.

SQL Server is expecting an numeric value, and you are passing a string.

There are various ways around it - have SQL Server split the string up into
individual numeric values, or Exec() a dynamically constructed string, none
of which are very appealing...

Cheers
Ken



: I am not sure how to pass the variable. It seems to need an int, but if I
: try to pass an in, it drops the commas
:
: Dim dr As SqlDataReader = appGen.AppInfoBuild("1,2,3,4")
:
: Public Function AppInfoBuild(ByVal appVals As String)
: ' Create Instance of Connection and Command Object
: Dim myConnection As New
: SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
: Dim myCommand As New SqlCommand("AppInfoBuild", myConnection)
: ' Mark the Command as a SPROC
: myCommand.CommandType = CommandType.StoredProcedure
:
:
:
: Dim parameterappVals As New SqlParameter("@appVals", SqlDbType.Int)
: parameterappVals.Value = appVals
: myCommand.Parameters.Add(parameterappVals)
:
: ' Open the database connection and execute the command
: myConnection.Open()
: myCommand.ExecuteNonQuery()
: myConnection.Close()
: End Function
:
:
: CREATE Procedure AppInfoBuild
: (
: @appVals int
: )
: AS
: SELECT App.f_name, App.m_initial, App.l_name, App.b_address,
App.b_City,
: stateTable.state, App.b_Zip,
: App.b_dayarea + '-' + App.b_dayphone1 + '-' +
: App.b_dayphone2 + '-' + App.b_dayext AS B_BusPhone,
: App.b_evearea + '-' + App.b_evephone1 + '-' +
: App.b_evephone2 AS B_HomePhone,
: App.b_cellarea + '-' + App.b_cellphone1 + '-' +
: App.b_cellphone2 AS B_CellPhone, App.b_faxphone2,
: App.b_SSN1 + '-' + App.b_SSN2 + '-' + App.b_SSN3 AS
: b_SSN, App.b_Age + '-' + App.b_Age1 + '-' + App.b_Age2 AS b_Age,
: App.b_School,
: App.b_MaritalStatusID, App.b_d_No, App.b_d_Ages,
: App.cb_FirstName, App.cb_MiddleInitial, App.cb_LastName,
: App.cb_SSN1 + '-' + App.cb_SSN2 + '-' + App.cb_SSN3
AS
: cb_SSN, App.cb_Age + '-' + App.cb_Age1 + '-' + App.cb_Age2 AS cb_Age,
: App.cb_School,
: App.cb_evearea, App.cb_evephone1, App.cb_evephone2,
: App.cb_MaritalStatusID, App.cb_d_No, App.cb_d_Ages, App.b_Rent,
: App.b_Rent_Length,
: App.b_Address1, App.b_City1, stateTable_3.state AS
: b_state1, App.b_Zip1, App.b_Rent_Length1, App.b_Rent1, App.cb_Address,
: App.cb_City,
: stateTable_4.state AS Expr1, App.cb_Zip,
: App.cb_Rent_Length, App.cb_rent, App.cb_Address1, App.cb_City1,
: stateTable_5.state AS BState1,
: App.cb_Zip1, App.cb_Rent_Length1, App.cb_Rent1,
: App.BCompanyName1, App.BStreet1, App.BCity1, stateTable_6.state AS
BState2,
: App.BZip1,
: App.BTitle1, App.BLOE1, App.BtxtMonths,
App.BSelfEmp1,
: App.BCompanyName2, App.BStreet2, App.BCity2, stateTable_7.state AS
BState2,
: App.BZip2,
: App.BA2 + '-' + App.BP2_1 + '-' + App.BP2_2 AS BP2,
: App.BTitle2, App.BSelfEmp2, App.BFromM2 + '/' + App.BFromY2 AS BFrom2,
: App.BToM2 + ' / ' + App.BToY2 AS BTo2,
App.BMIncome2,
: App.CBCompanyName1, App.CBStreet1, App.CBCity1, stateTable_8.state AS
: CBState1,
: App.CBZip1, App.CBA1 + '-' + App.CBP1_1 + '-' +
: App.CBP1_2 + '-' + App.CBX1 AS CBP1, App.CBTitle1, App.CBLOE1,
: App.CBtxtMonths,
: App.CBSelfEmp1, App.CBCompanyName2, App.CBStreet2,
: App.CBCity2, stateTable_9.state AS CBState2, App.CBZip2,
: App.CBA2 + '-' + App.CBP2_1 + '-' + App.CBP2_2 AS
: CBP2, App.CBTitle2, App.CBFromM2 + '/' + App.CBFromY2 AS CBFrom2,
: App.CBToM2 + '/' + App.CBToY2 AS CBTo2,
: App.CBMIncome2, App.CBSelfEmp2, App.BorrowerBaseIncome,
: App.BorrowerOvertime,
: App.BorrowerBonuses, App.BorrowerCommission,
: App.BorrowerDividends_Int, App.BorrowerNetRentalIncome, App.BorrowerOther,
: App.CoBorrowerBaseIncome, App.CoBorrowerOvertime,
: App.CoBorrowerBonuses, App.CoBorrowerCommission,
: App.CoBorrowerDividends_Int,
: App.CoBorrowerNetRentalIncome, App.CoBorrowerOther,
: App.OtherBC1, App.OtherBC2, App.OtherBC3, OtherDes.OtherDesDesc,
: OtherDes_1.OtherDesDesc AS OtherDesDesc2,
: OtherDes_2.OtherDesDesc AS OtherDesDesc3, App.OtherAmt1, App.OtherAmt2,
: App.OtherAmt3,
: App.StockCompany1, App.StockAmt1, App.StockCompany2,
: App.StockAmt2, App.StockCompany3, App.StockAmt3, App.VestedInt,
: App.BusinessVal,
: App.LifeInsAmt, App.AutoMake1, App.AutoVal1,
: App.AutoMake2, App.AutoVal2, App.AutoMake3, App.AutoVal3,
App.OAssetsText1,
: App.OAssetsVal1,
: App.OAssetsText2, App.OAssetsVal2, App.OAssetsText3,
: App.OAssetsVal3, App.OAssetsText4, App.OAssetsVal4, App.AlimonyText,
: App.AlimonyVal,
: App.JExpText1, App.JExpVal1, App.JExpText2,
: App.JExpVal2, App.RentAmt, App.CMortFirst, App.CMortFin, App.CMortHIns,
: App.CMortTax, App.CMortIns,
: App.CMortDue, App.CMortOther, App.AddressText1,
: App.City1 + ', ' + stateTable_10.state + ' ' + App.Zip1 AS prop1,
: propType_1.propType,
: propStatus_1.propStatusShort, App.MarketVal1,
: App.MortgagePayment1, App.MiscCost1, App.AddressText2,
: App.City2 + ', ' + stateTable_11.state + ' ' +
: App.Zip2 AS prop2, propType_1.propType AS propType2,
: propStatus_1.propStatusShort AS propStatusShort2,
: App.MarketVal2, App.MortgagePayment2, App.MiscCost2
: FROM App INNER JOIN
: stateTable ON App.b_StateID = stateTable.taxID LEFT
: OUTER JOIN
: stateTable stateTable_11 ON App.State2 =
: stateTable_11.taxID LEFT OUTER JOIN
: propType propType_1 ON App.Type2 =
: propType_1.propTypeID LEFT OUTER JOIN
: propStatus propStatus_1 ON App.Status2 =
: propStatus_1.propStatusID LEFT OUTER JOIN
: propType propType_2 ON App.Type1 =
: propType_2.propTypeID LEFT OUTER JOIN
: propStatus propStatus_2 ON App.Status1 =
: propStatus_2.propStatusID LEFT OUTER JOIN
: stateTable stateTable_10 ON App.State1 =
: stateTable_10.taxID LEFT OUTER JOIN
: OtherDes ON App.OtherDes1 = OtherDes.OtherDesID LEFT
: OUTER JOIN
: OtherDes OtherDes_1 ON App.OtherDes2 =
: OtherDes_1.OtherDesID LEFT OUTER JOIN
: OtherDes OtherDes_2 ON App.OtherDes3 =
: OtherDes_2.OtherDesID LEFT OUTER JOIN
: stateTable stateTable_9 ON App.CBState2 =
: stateTable_9.taxID LEFT OUTER JOIN
: stateTable stateTable_8 ON App.CBState1 =
: stateTable_8.taxID LEFT OUTER JOIN
: stateTable stateTable_7 ON App.BState2 =
: stateTable_7.taxID LEFT OUTER JOIN
: stateTable stateTable_1 ON App.PropertyStateID =
: stateTable_1.taxID LEFT OUTER JOIN
: stateTable stateTable_2 ON App.PropertyStateID =
: stateTable_2.taxID LEFT OUTER JOIN
: PropertyType ON App.PropertyTypeID =
: PropertyType.PropertyTypeID LEFT OUTER JOIN
: stateTable stateTable_6 ON App.BState1 =
: stateTable_6.taxID LEFT OUTER JOIN
: stateTable stateTable_5 ON App.cb_State1 =
: stateTable_5.taxID LEFT OUTER JOIN
: stateTable stateTable_4 ON App.cb_State =
: stateTable_4.taxID LEFT OUTER JOIN
: stateTable stateTable_3 ON App.b_State1 =
: stateTable_3.taxID
: WHERE (App.appId IN ('@appVals'))
: GO
:
:
 
Top