I’m getting the OleDb exception ‘Unspecified error the scale is Invalid’ when trying to use a stored procedure

I made a stored procedure in SQLExpress to insert information in both the Header and Details table. I’ve ran the stored procedure in SQL Server Management Studio to make sure it works fine.

But when I try to use the stored procedure using TableAdapters in visual studio it gives me the error: System.Data.OleDb.OleDbException: ‘Unspecified error The scale is invalid.’

Here is the code fore calling the stored procedure:

HeaderTableAdapter.InsertData(
        selectedID,                 'Current Selected ID
        ProblemsTextBox.Text,       'Encountered Problem
        AccesoriesTextBox.Text,     'Accesories
        ReceivedDateTimePicker.Value.ToString("dd-MM-yyyy"),'Received Date
        EndorsedToTextBox.Text,     'Endorsed To
        1,                          ' Header table ID
        1,                          ' Detail table ID
        StatusComboBox.Text,        'Status
        RemarksTextBox.Text,        ' Remarks
        ReceivedDateTimePicker.Value.ToString("dd-MM-yyyy"), 'Input Date 
        True                        'Boolean for Newest insert in detail table
        )

Here is the code for the stored procedure:

    -- Header variables
@itemID int,
@problem nvarchar(max),
@accesories nvarchar(max),
@receivedDate datetime,
@endorsedTo nvarchar(255),

-- Details variables
@headerID int,
@claimID int,
@status nvarchar(255),
@remarks nvarchar(255),
@transactionDate datetime,
@newest bit = 1

AS BEGIN — SET NOCOUNT ON added to prevent extra result sets from — interfering with SELECT statements. SET NOCOUNT ON;

-- Insert into header table
INSERT INTO Header_Table(Item_ID,Problem,Accesories,Received_Date,EndorsedTo)
VALUES(@itemID,@problem,@accesories,@receivedDate,@endorsedTo)

-- Get Newest ID from header table
SET @headerID = SCOPE_IDENTITY()
    
-- Insert Information into Details Table
INSERT INTO Details_Table(Header_ID,Status,Remarks,Transaction_Date,Newest)
VALUES (@headerID,@status,@remarks,@transactionDate,@newest)

Answer

Changing the Parameter ProviderType in the Data Set from DBTimeStamp to DBDate seems to have removed the problem.

I think that the DBTimeStamp can’t be passed into the datetime variable in SQL.

Advertisement

Answer