Sql Insert error for auto incrimental field

chime

Member
Joined
Jul 12, 2005
Messages
22
Location
wexford, Ireland
Programming Experience
3-5
Hi

I am trying to insert a row into a sql Server Database
The InvoiceID field is an auto incremental field which in increased by 1 each time


When I try to run this code I get the following error

"Input string was not in a correct format."


The code works when the field InvoiceID is not an auto incrimental field

InvoiceID is also an int in the database and is the primary key.

When I remove the field completly from the insert statement it doesn't work


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have tried this also
VB.NET:
INSERT INTO Invoice (InvoiceID, .....)
VALUES (@InvoiceID, ......)

change the text to:
VB.NET:
INSERT INTO Invoice (InvoiceID, .....)
VALUES (@InvoiceID, ......));
SELECT SCOPE_IDENTITY() AS OrderID
and make sure that the InsertCommand's UpdatedRowSource is set to

UpdateRowSource.FirstReturnedRecord.

from

http://dotnet247.com/247reference/msgs/12/61123.aspx

But it doesn't like the scope sql part.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I am using dataadapters and datasets on VB .net. 2003 trying to insert to an SQL Server database

Any help would be gratefully appreciated as I can't see where its going wrong

VB.NET:
'Insert the choosen user from the registered users listbox and Update the database
 
SqlInsertCommandChangeInvoice.CommandText = "INSERT INTO Invoices (InvoiceID, EntityID, FundID, LOBSID, InvoicePeriod, InvoiceDate, PaymentDate, CurrencyISO, Amount, CreatedBy, CreateDate, LastEditedBy, LastEditDate, SubmittedFinanceIndicator, SubmittedFinanceBy, SubmitDate, ExportedIndicator, ExportDate, ExportedBy) " & _
 
"VALUES (@InvoiceID, @EntityID, @FundID, @LOBSID, @InvoicePeriod, @InvoiceDate, @PaymentDate, @CurrencyISO, @Amount, @CreatedBy, @CreateDate, @LastEditedBy, @LastEditDate, @SubmittedFinanceIndicator, @SubmittedFinanceBy, @SubmitDate, @ExportedIndicator, @ExportDate, @ExportedBy) "
 
'"SELECT SCOPE_IDENTITY() AS InvoiceID" 
 
 
 
' Creating
 
 
 
[size=2]SqlInsertCommandChangeInvoice.Parameters([/size][size=2][color=#800000]"@InvoiceID"[/color][/size][size=2]).Value = [/size][size=2][color=#800000]""
 
[/color][/size]
' or setting strInvoiceID to "" and [size=2]SqlInsertCommandChangeInvoice.Parameters([/size][size=2][color=#800000]"@InvoiceID"[/color][/size][size=2]).Value = strInvoice[/size]
' or setting cint(strInvoiceID) to intInvoiceID (integer) and [size=2]SqlInsertCommandChangeInvoice.Parameters([/size][size=2][color=#800000]"@InvoiceID"[/color][/size][size=2]).Value = intInvoice[/size]
 
[size=2][color=#800000]
[/color][/size]Parameters("@InvoiceID").Value = ""
 
 
SqlInsertCommandChangeInvoice.Parameters("@EntityID").Value = strEntityID
 
SqlInsertCommandChangeInvoice.Parameters("@FundID").Value = strFundID
 
SqlInsertCommandChangeInvoice.Parameters("@LOBSID").Value = strLobsid
 
SqlInsertCommandChangeInvoice.Parameters("@InvoicePeriod").Value = strInvoicePeriod
 
SqlInsertCommandChangeInvoice.Parameters("@InvoiceDate").Value = DateTime.Parse(strInvoiceDate)
 
' Paying
 
If ((strPaymentDate = "") Or (strPaymentDate = "//")) Then
 
SqlInsertCommandChangeInvoice.Parameters("@PaymentDate").Value = DBNull.Value
 
Else
 
SqlInsertCommandChangeInvoice.Parameters("@PaymentDate").Value = DateTime.Parse(strPaymentDate)
 
End If
 
'creating
 
SqlInsertCommandChangeInvoice.Parameters("@CurrencyISO").Value = strCurrency
 
SqlInsertCommandChangeInvoice.Parameters("@Amount").Value = strAmount
 
SqlInsertCommandChangeInvoice.Parameters("@CreatedBy").Value = strCreatedBy
 
SqlInsertCommandChangeInvoice.Parameters("@CreateDate").Value = DateTime.Parse(strCreateDate)
 
' editing
 
SqlInsertCommandChangeInvoice.Parameters("@LastEditedBy").Value = DBNull.Value
 
If (strLastEditDate = "") Then
 
SqlInsertCommandChangeInvoice.Parameters("@LastEditDate").Value = DBNull.Value
 
Else
 
SqlInsertCommandChangeInvoice.Parameters("@LastEditDate").Value = DateTime.Parse(strLastEditDate)
 
End If
 
 
 
' submitting
 
SqlInsertCommandChangeInvoice.Parameters("@SubmittedFinanceIndicator").Value = strSubmittedFinanceIndicator
 
SqlInsertCommandChangeInvoice.Parameters("@SubmittedFinanceBy").Value = DBNull.Value
 
If (strSubmitDate = "") Then
 
SqlInsertCommandChangeInvoice.Parameters("@SubmitDate").Value = DBNull.Value
 
Else
 
SqlInsertCommandChangeInvoice.Parameters("@SubmitDate").Value = DateTime.Parse(strSubmitDate)
 
End If
 
 
 
' exporting
 
SqlInsertCommandChangeInvoice.Parameters("@ExportedIndicator").Value = strExportedIndicator
 
If (strExportDate = "") Then
 
SqlInsertCommandChangeInvoice.Parameters("@ExportDate").Value = DBNull.Value
 
Else
 
SqlInsertCommandChangeInvoice.Parameters("@ExportDate").Value = DateTime.Parse(strExportDate)
 
End If
 
SqlInsertCommandChangeInvoice.Parameters("@ExportedBy").Value = DBNull.Value
 
 
 
Try
 
' Set the Insert command
 
SqlDataAdapterChangeInvoice.InsertCommand = SqlInsertCommandChangeInvoice
 
 
 
' Update any changes made to the database and run the commmand
 
SqlConnectionJintsu.Open()
 
SqlInsertCommandChangeInvoice.ExecuteNonQuery()
 
SqlConnectionJintsu.Close()
 
' Finished with the command
 
SqlInsertCommandChangeInvoice.Dispose()
 
Catch ex As Exception
 
SqlConnectionJintsu.Close()
 
' Finished with the command
 
SqlInsertCommandChangeInvoice.Dispose()
 
End Try
 
 
 
' Change to the Details Form
 
Response.Redirect(strFACreateInvoiceDetailsFormLink)
 
Catch ex As Exception
 
End Try
 

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
Remove the InvoiceID from your INSERT SQL statement.... since it is an IDENTITY field, you don't need to insert it yourself.

-tg
 

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
You only need to remove it from the insert.

-tg
 

chime

Member
Joined
Jul 12, 2005
Messages
22
Location
wexford, Ireland
Programming Experience
3-5
Hi TechGnome

When I remove the invoiceID parameter

VB.NET:
SqlInsertCommandChangeInvoice.CommandText = "INSERT INTO Invoices (EntityID, FundID, LOBSID, InvoicePeriod, InvoiceDate, PaymentDate, CurrencyISO, Amount, CreatedBy, CreateDate, LastEditedBy, LastEditDate, SubmittedFinanceIndicator, SubmittedFinanceBy, SubmitDate, ExportedIndicator, ExportDate, ExportedBy) " & _
 
"VALUES (@EntityID, @FundID, @LOBSID, @InvoicePeriod, @InvoiceDate, @PaymentDate, @CurrencyISO, @Amount, @CreatedBy, @CreateDate, @LastEditedBy, @LastEditDate, @SubmittedFinanceIndicator, @SubmittedFinanceBy, @SubmitDate, @ExportedIndicator, @ExportDate, @ExportedBy) "
 
'"SELECT SCOPE_IDENTITY() AS InvoiceID" 
 
'SqlInsertCommandChangeInvoice.Parameters("@InvoiceID").Value = ""
I get the following error

"Prepared statement '(@InvoiceID int,@EntityID varchar(4),@FundID varchar(10),@LOBSID' expects parameter @InvoiceID, which was not supplied."

When I remove the parameter from the dataadapter and the SQL statement I get this error

Message "Input string was not in a correct format." String


So I am not sure why I am getting a format error when there is no parameters.

Thanks
 

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
Since you are dealing with SQL Server, have you thought about Stored PRocedures..... I'm now remembering why I dislike the command builder and having SQL in the code....

-tg
 

chime

Member
Joined
Jul 12, 2005
Messages
22
Location
wexford, Ireland
Programming Experience
3-5
Hi TechGnome,

I have only being using sql server for 5 days and vb for a few months so I just wanted to do the insert statement
Is there a quick fix for this problem or why can't I do this insert.

I have a working insert with mysql which hasn't caused me these problems.

Thanks
 

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
I'm only now fully realizing what you are trying to do.
1) Since you are no longer using @InvoiceID in the insert, you can't create a parameter for it. You don't need it anyways.
Because
2) SELECT SCOPE_IDENTITY() AS InvoiceID will result in a One Record, One Row datatable with the ID in it. So any attempt to create and add a parameter @InvoiceID will give you problems.

Reccomendation: Spend a little time this weekend reading up on Stored Procedures in SQL Server. They aren't anything complicated, and can make a HUGE difference in development and maintenance.

-tg
 
Top Bottom