websss
asked on
executeScalar always returns 0 - trying to get ID of record just inserted
For some reasons the excutescalar result is always 0 (trying to get ID of record inserted)
i've read about input/output parameters but all the stuff i read is to do with SP's and not inline sql like this:
i've read about input/output parameters but all the stuff i read is to do with SP's and not inline sql like this:
Public Function insertGroupLoan(ByVal _groupId As Integer, _
ByVal _loanId As Integer, _
ByVal _LoanActive As Integer, _
ByVal _notes As String) As Integer
Dim dataHelperInsert As New HelperInsert
'construct SQL Statement
Dim sb As New StringBuilder
sb.Append("INSERT INTO GroupLoans ")
sb.Append("(FK_GroupId, FK_LoanId, LoanActive, Notes)")
sb.Append(" VALUES (")
sb.Append("@FK_GroupId")
sb.Append(",")
sb.Append("@FK_LoanId")
sb.Append(",")
sb.Append("@LoanActive")
sb.Append(",")
sb.Append("@Notes")
sb.Append(")")
Dim IdOfRow As Integer
Dim conn = New SqlCeConnection(My.Settings.mfmConn)
Dim cmd As New SqlCeCommand
cmd.Connection = conn
cmd.CommandText = sb.ToString
cmd.Parameters.Add("@FK_GroupId", SqlDbType.Int).Value = _groupId
cmd.Parameters.Add("@FK_LoanId", SqlDbType.Int).Value = _loanId
cmd.Parameters.Add("@LoanActive", SqlDbType.Bit).Value = CBool(_LoanActive)
cmd.Parameters.Add("@Notes", SqlDbType.NVarChar).Value = _notes
conn.Open()
IdOfRow = CInt(cmd.ExecuteScalar())
conn.Close()
Return IdOfRow
End Function
Getting the identity of the most recently added record
http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record
http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record
ASKER
Thanks
I've tried to use this example but it errors on this line
with the following:
-------------------------- -
There was an error inserting the member
-------------------------- -
There was an error parsing the query. [ Token line number = 1,Token line offset = 374,Token in error = Select ]
-------------------------- -
OK
-------------------------- -
Here is the code
the SQL looks like this (sb.ToString)
any ideas?
I've tried to use this example but it errors on this line
ID = CInt(cmd.ExecuteScalar())
with the following:
--------------------------
There was an error inserting the member
--------------------------
There was an error parsing the query. [ Token line number = 1,Token line offset = 374,Token in error = Select ]
--------------------------
OK
--------------------------
Here is the code
Public Function insertMember(ByVal _memberName As String, _
ByVal _gender As String, _
ByVal _phoneNumber As String, _
ByVal _noOfDependants As Integer, _
ByVal _maritalStatus As String, _
ByVal _businessType As String, _
ByVal _rent As String, _
ByVal _prevEmployment As String, _
ByVal _otherNotes As String, _
ByVal _mpesaEnabled As Boolean, _
ByVal _centreId As Integer, _
ByVal _nationalId As String) As Integer
'construct SQL Statement
Dim sb As New StringBuilder
sb.Append("INSERT INTO Members ")
sb.Append("(MemberName, Gender, PhoneNumber, NumberOfDependants, MaritalStatus, BusinessType, Rent, PreviousEmployment, OtherNotes, MpesaEnabled, CentreId, Active, NationalIdNumber)")
sb.Append(" VALUES (")
sb.Append("@MemberName")
sb.Append(",")
sb.Append("@Gender")
sb.Append(",")
sb.Append("@PhoneNumber")
sb.Append(",")
sb.Append("@NumberOfDependants")
sb.Append(",")
sb.Append("@MaritalStatus")
sb.Append(",")
sb.Append("@BusinessType")
sb.Append(",")
sb.Append("@Rent")
sb.Append(",")
sb.Append("@PreviousEmployment")
sb.Append(",")
sb.Append("@OtherNotes")
sb.Append(",")
sb.Append("@MpesaEnabled")
sb.Append(",")
sb.Append("@CentreId")
sb.Append(",")
sb.Append("@Active")
sb.Append(",")
sb.Append("@NationalIdNumber")
sb.Append("); Select Scope_Identity()")
Dim ID As Integer = 0
Using conn As New SqlCeConnection(My.Settings.mfmConn)
Using cmd As New SqlCeCommand(sb.ToString, conn)
cmd.Parameters.Add("@MemberName", SqlDbType.NVarChar).Value = _memberName
cmd.Parameters.Add("@Gender", SqlDbType.NVarChar).Value = _gender
cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = _phoneNumber
cmd.Parameters.Add("@NumberOfDependants", SqlDbType.Int).Value = _noOfDependants
cmd.Parameters.Add("@MaritalStatus", SqlDbType.NVarChar).Value = _maritalStatus
cmd.Parameters.Add("@BusinessType", SqlDbType.NVarChar).Value = _businessType
cmd.Parameters.Add("@Rent", SqlDbType.NVarChar).Value = _rent
cmd.Parameters.Add("@PreviousEmployment", SqlDbType.NVarChar).Value = _prevEmployment
cmd.Parameters.Add("@OtherNotes", SqlDbType.NVarChar).Value = _otherNotes
cmd.Parameters.Add("@MpesaEnabled", SqlDbType.Bit).Value = _mpesaEnabled
cmd.Parameters.Add("@CentreId", SqlDbType.Int).Value = _centreId
cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = 1
cmd.Parameters.Add("@NationalIdNumber", SqlDbType.NVarChar).Value = _nationalId
conn.Open()
ID = CInt(cmd.ExecuteScalar())
conn.Close()
End Using
End Using
Return ID
the SQL looks like this (sb.ToString)
INSERT INTO Members (MemberName, Gender, PhoneNumber, NumberOfDependants, MaritalStatus, BusinessType, Rent, PreviousEmployment, OtherNotes, MpesaEnabled, CentreId, Active, NationalIdNumber) VALUES (@MemberName,@Gender,@PhoneNumber,@NumberOfDependants,@MaritalStatus,@BusinessType,@Rent,@PreviousEmployment,@OtherNotes,@MpesaEnabled,@CentreId,@Active,@NationalIdNumber); Select Scope_Identity()
any ideas?
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
alternatively you can check the below linkhttp://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/91bd10ce-c83b-4766-920b-2137ddc29908
or
http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id