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)
Advertisement
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.