___
CREATE PROCEDURE ins_MemberPayment
(@.MemberId VarChar(10),
@.CCNum Char(4),
@.Amount smallmoney)
AS
INSERT INTO MemberPayments
(MemberId, PaymentDate, CCNum, Amount)
VALUES
(@.MemberId, GetDate(), @.CCNum, @.Amount)
GO
--
The ASP.NET page looks like this:
___
Dim UserName As String = txtUserName.Text 'hotrodjimmy73
Dim CreditCard As String = Trim(txtCreditCard.Text) '5464655458776221
Dim Amount As String = "1.00"
Dim cnn As New SqlConnection(Application("SQLConnectionString"))
Dim trans As SqlTransaction
Dim cmd2 As New SqlCommand(dbo() & "ins_MemberPayment", cnn, trans)
cmd.CommandType = CommandType.StoredProcedure
With cmd2.Parameters
.Add("@.MemberID", UserName)
.Add("@.CCNum", Right(CreditCard, 4))
.Add("@.Amount", Convert.ToDecimal(Amount))
End With
cnn.Open()
cmd2.ExecuteNonQuery()
cnn.Close()
--
When I run the page, I get the error:
___
Incorrect syntax near 'ins_MemberPayment'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'ins_MemberPayment'.
Source Error:
Line 261: cmd2.ExecuteNonQuery()
--
Does anybody see the error? I'm blind to it after looking for a couple hours now.In this line
Dim cmd2 As New SqlCommand(dbo() & "ins_MemberPayment", cnn, trans)what is the purpose of the dbo() function call?
Terri|||What does dbo() evaluate to? Does it translate to "dbo."?
Try this alternative syntax and see if it works.
|||Oh, sorry. I should have simplified that for the purpose of posting to the forum. The dbo function call simply returns a conditional string: "dbo." if the page is running in it's production environment, and "" if it is running on my local development machine.
Dim cm as SqlCommand = New SqlCommand()
cm.Connection= cnn
cm.CommandType= CommandType.StoredProcedure
cm.CommandText= "ins_MemberPayment"
cm.Transaction = trans
It's not relevant for the question at hand.|||I didn't want to "lead the witness" by originally saying that I have a hunch that the problem has something to do with smallmoney and string conversion. But now that I've tested this in Query Analyzer, I'm pretty sure this is the case.
I can get the procedure to work when passing it a value of 12 or 12.00, but as soon as I put single quotes around it, I get the error:
Implicit conversion from data type varchar to smallmoney is not allowed. Use the CONVERT function to run this query.
If I hope to accomplish the conversion in ASP.NET before sending the value to SQL Server, what do I need to convert it too? Int16, Int32, Double?
What's the "RIGHT" data type to convert to?|||The right .NET Framework data type is SQLMoney. (Here's across reference map of SQL Server and .NET Framework data types).
I suggest adding your parameters as such:
With cmd2.Parameters
.Add("@.MemberID", SqlDbType.Varchar, 10, UserName)
.Add("@.CCNum", SqlDbType.Char,4,Right(CreditCard, 4))
.Add("@.Amount", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(Amount))
End With
Terri|||Using your code I get:
Value of type 'System.Data.SqlTypes.SqlMoney' cannot be converted to 'String'.|||Going back to your originally supplied code, change this:
cmd.CommandType = CommandType.StoredProcedureto this:
cmd2.CommandType = CommandType.StoredProcedure
Terri|||Oops. Thanks for the fresh pair of eyes. That did one good thing for me; It allowed me to get more accurate errors returned.
Now it works as:
.Add("@.Amount", SqlTypes.SqlMoney.Parse(Amount))
But not as:
.Add("@.Amount", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(Amount))
Hmm. Strange. Oh, well. It works for my purposes. THANKS!!|||I normally don't add my parameters that way, so I am sure I provided you with syntactical errors :-(
But I am glad you got it working now!
Terri
