We help IT Professionals succeed at work.

I need help with a SQL Insert staement with paramaters.

Dovberman
Dovberman asked
on
195 Views
Last Modified: 2010-05-18
I need help with a SQL Insert staement with paramaters. The insert is called from a button on an ASP.NET page.

This is what I have:
Protected Sub btnNewQuoteHist_Click(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles btnNewQuoteHist.Click
         Dim conStockSelector As SqlConnection

        Dim strConnection As String = "Data Source=BURCEL05;Initial Catalog=StockSelectSQL;" _
        & "Integrated Security=SSPI"
        lblFileDate.Text = Me.calQuoteDate.SelectedDate
        conStockSelector = New SqlConnection(strConnection)
        conStockSelector.Open()
        Dim strInsert As String
        strInsert = "INSERT INTO DownLoadHistory " _
        & " VALUES (@DL_FileName,@DL_Date,MarketID,@DL_Appended);"
        Dim cmdSQL As SqlCommand = New SqlCommand(strInsert, conStockSelector)
        cmdSQL.CommandText = strInsert

        cmdSQL.Parameters.Add("@DL_FileName", Data.SqlDbType.NVarChar).Value = "ABC.txt"
        cmdSQL.Parameters.Add("@DL_Date", Data.SqlDbType.DateTime).Value = calQuoteDate.SelectedDate
        cmdSQL.Parameters.Add("@MarketID", Data.SqlDbType.SmallInt).Value = 2
        cmdSQL.Parameters.Add("@DL_Appended", Data.SqlDbType.Bit).Value = 0
        cmdSQL.ExecuteNonQuery()
        'Error The name "MarketID" is not permitted in this context
        'Column names are not permitted
        conStockSelector.Close()

I just need a hint on one of the Parameters.Add variables.

Thanks,
Comment
Watch Question

Applications and Integrations Consultan
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Try this .. this should work...

replaces the "@" with ":"

strInsert = "INSERT INTO DownLoadHistory " _ & "VALUES :DL_FileName, :DL_Date, :MarketID, :DL_Appended)"

cmdSQL.Parameters.Add("DL_FileName", Data.SqlDbType.NVarChar).Value = "ABC.txt"
cmdSQL.Parameters.Add("DL_Date", Data.SqlDbType.DateTime).Value = calQuoteDate.SelectedDate
cmdSQL.Parameters.Add("MarketID", Data.SqlDbType.SmallInt).Value = 2
cmdSQL.Parameters.Add("DL_Appended", Data.SqlDbType.Bit).Value = 0

Author

Commented:
TimCottee
How simple. I wondered why only MarketID was the error source. I guess I should not have gotten up at 2 AM to work on this.

Thanks,
TimCotteeApplications and Integrations Consultan

Commented:
Always a bad idea to work at 2am. There are far better things to be doing at that time of day, not least sleeping!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.