error converting data type nvarchar to bigint in sql server

Question

0.00/5 (No votes)

See more:

Hello everyone can anybody here help me to figure this out the correct command for this error:
"error converting data type nvarchar to bigint in sql server"

this is the line calls the error

Line 329: conn.Open()
Line 330: cmdtest.Connection = conn
Line 331: cmdtest.ExecuteNonQuery()
Line 332: conn.Close()
Line 333: ''Dim myconnect As New SqlClient.SqlConnection

yet ive change my command in this manner :

VB

Dim param = New System.Data.SqlClient.SqlParameter()
       param.ParameterName = "@srn"
       param.Direction = ParameterDirection.Output
       param.Size = 10
       cmdtest.Parameters.Add(param)

and works same error so please help me to figure out this one..

thanks in advance

Posted 3-Jun-13 17:34pm

Member 10033107

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 3-Jun-13 23:59pm

Which column is bigint in the table? Can you check?

Member 10033107 4-Jun-13 0:54am

those column are id,srn, srncode, srnumber and promocode which i address to bigint

3 solutions

Solution 2

ABhinav i already check my parameters and i didnt put a varchar value can you please see this program as well to check..

VB

Dim conn As New SqlConnection("Data Source=192.168.0.4;Initial Catalog=CYBERYA;Persist Security Info=True;User ID=cyberya;Password=Piso4minutes;MultipleActiveResultSets=True")

        ' set up call for stored procedure

        Dim cmdtest As New SqlCommand("Populatecsv", conn)
        cmdtest.CommandType = CommandType.StoredProcedure
        conn.Open()
        cmdtest.Connection = conn
        ' parameter settings
        cmdtest.Parameters.Clear()
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@srn", "srn"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Id", "Id"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@status", "status"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@CustomerName", "customername"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@HomeContact", "homecontact"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@BusinessContact", "businesscontact"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@MobileContact", "mobilecontact"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@LotHouseNo", "lothouseno"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@RoomUnitStall", "roomunitstall"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@BldgFloor", "bldgfloor"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@BldgName", "bldgname"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Street", "street"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Subdivision", "subdivision"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Barangay", "barangay"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@CityMunicipality", "citymunicipality"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Province", "province"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Region", "region"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Package", "package"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@PromoCode", "promocode"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@ApplicationDate", "applicationdate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@EndorsedDate", "endorseddate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@paymenttype", "paymenttype"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@scheduledate", "scheduledate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@loanstatus", "loanstatus"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@ActivationDate", "activationdate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@pcdelivery", "pcdelivery"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@nopcavailed", "nopcavailed"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@datepcpickup", "datepcpickup"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@pcissuance", "pcissuance"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@salesagent", "salesagent"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Coordinator", "Coordinator"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@SalesChannel", "saleschannel"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@SalesGroup", "salesgroup"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@CreatedBy", "CreatedBy"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@timestamp", "timestamp"))
        ' end call params

-------------------------------stored procedure-----------------------------

SQL

ALTER procedure [dbo].[Populatecsv]
@ID BIGINT,
@SRCode BIGINT,
@SRNumber BIGINT,
@Status NVARCHAR (MAX),
@CustomerName NVARCHAR (MAX),
@HomeContact NVARCHAR (MAX),
@BusinessContact NVARCHAR (MAX),
@MobileContact NVARCHAR (MAX),
@LotHouseNo NVARCHAR (MAX),
@RoomUnitStall NVARCHAR (MAX),
@BldgFloor NVARCHAR (MAX),
@BldgName NVARCHAR (MAX),
@Street NVARCHAR (MAX),
@Subdivision NVARCHAR (MAX),
@Barangay NVARCHAR (MAX),
@CityMunicipality NVARCHAR(MAX),
@Province NVARCHAR (MAX),
@Region NVARCHAR (MAX),
@Package NVARCHAR (MAX),
@PromoCode NVARCHAR (MAX),
@PaymentType Nvarchar (MAX),
@ApplicationDate DATETIME,
@EndorsedDate DATETIME,
@PaymentDate DATETIME,
@ScheduleDate DATETIME,
@ActivationDate DATETIME,
@PCDelivery NVARCHAR (MAX),
@NoPCAvailed BIGINT,
@datepcpickedup DATETIME,
@pcissuance nvarchar (MAX),
@SalesAgent NVARCHAR (MAX),
@Coordinator NVARCHAR (MAX),
@SalesChannel NVARCHAR (MAX),
@SalesGroup NVARCHAR (MAX),
@CreatedBy NVARCHAR (MAX),
@LoanStatus NVARCHAR (MAX),
@TimeStamp DATETIME,
@SRN BIGINT OUTPUT
AS
BEGIN
    INSERT INTO SRN (ID,SRCode,SRNumber,Status,CustomerName, HomeContact,BusinessContact,MobileContact,LotHouseNo,RoomUnitStall,BldgFloor,BldgName,Street, Subdivision,Barangay,CityMunicipality,Province,Region ,Package, PromoCode,PaymentType,ApplicationDate,EndorsedDate,PaymentDate,ScheduleDate,ActivationDate,PCDelivery ,NoPCAvailed,datepcpickedup, pcissuance,SalesAgent ,Coordinator,  SalesChannel,SalesGroup, CreatedBy,LoanStatus,TimeStamp )
    VALUES (@ID, @SRCode,@SRNumber,@Status,@CustomerName,@HomeContact,@BusinessContact,@MobileContact,@LotHouseNo,@RoomUnitStall,@BldgFloor,@BldgName,@Street,@Subdivision,@Barangay,@CityMunicipality,@Province,@Package,@PromoCode,@PaymentType,@ApplicationDate,@EndorsedDate ,@PaymentDate,@ScheduleDate,@ActivationDate,@PCDelivery,@NoPCAvailed,@datepcpickedup,@pcissuance,@SalesAgent,@Coordinator,@SalesChannel ,@SalesGroup,@CreatedBy, @LoanStatus, @TimeStamp  );
    SELECT @SRN= SCOPE_IDENTITY();
END

Posted 3-Jun-13 19:09pm

Member 10033107

Updated 3-Jun-13 20:24pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Abhinav S · Accepted Answer · 2013-06-03T18:28:00

Solution 1

Check the types of the variable - you are putting a varchar value into an integer in one of your parameters.

Posted 3-Jun-13 18:28pm

Abhinav S

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2013-06-03T20:23:00

Solution 3

Quote:
those column are id,srn, srncode, srnumber and promocode which i address to bigint

So, you need to pass some integer value to these fields like below for @srn.

C#

cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@srn", Convert.ToInt64(someValue)))

Thus, the logic is, you can't pass string value to a BigInt field as you are doing "srn", which is not an Integer value. Instead you have to pass one Integer value, else convert it to Integer.

Note
You should do like this for all the BigInt field you are sending data to.

Posted 3-Jun-13 20:23pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

Member 10033107 4-Jun-13 3:21am

ok sorry im a newbie here.. by the way..(correct me if im wrong) within my statement i encode, i will state on the code like this: cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@srn", Convert.ToInt64(srn)))?? because the scenario of my program will be they will input some data that i will add it to the database.. thanks for the response by the way

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 4-Jun-13 3:24am

Yes no problem...

Yes, if the user inputs the data, then you will store it in a variable right? Something like below...

string srn = txtSrn.Text; // I guess he input a integer value. Else you will face problems.
cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@srn", Convert.ToInt64(srn)));

Member 10033107 4-Jun-13 3:37am

yes some thing like that sir, yet there are some error because i do not state the string value, anyway with regarding to the string, does might have effect if the data sent has some null values?? can you give an example for that so i might clearly understand on what im going to do.. thank you again for the response

Member 10033107 4-Jun-13 3:54am

bro i already test what youve been said yet the error message is like this "STRING is a classtype and cannot be used as an expression"

also

"." expected

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 4-Jun-13 6:36am

Sorry I thought it was C#. You are using vb.net. So, do like below...

Dim srn As String = txtSrn.Text;

When you get any errors or exception, try to search that exact error message in Google first, you will get answers.