chime
Member
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
change the text to:
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
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
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