CODEWITHSUNDEEP
sqlasp.net
echelon
echelon

Reputation: 3

How can I move the code from Access to MSSQL

I have already project file working on my computer with localhost and access database.

Access Database Code:

<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
    DataFile="~/App_Data/Database2.mdb" 
    SelectCommand="SELECT [ProductID], [Price] FROM [GENERAL] WHERE ([ProductID] = ?)">
        <SelectParameters>
            <asp:Parameter Name="ProductID" Type="String" />
        </SelectParameters>
    </asp:AccessDataSource>

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings: blabla %>" 
    SelectCommand="SELECT [ProductID], [Price] FROM [GENERAL] WHERE ([ProductID] = ?)">
    <SelectParameters>
        <asp:Parameter Name="ProductID" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

I connect my project to MSSQL database. Everything works if I use the code SelectCommand="SELECT [ProductID], [Price] FROM [GENERAL]. I need to do more specific. What did I miss? I expect results on Gridview related to [ProductID]. But, it shows:

"System.Data.SqlClient.SqlException: Incorrect syntax near '?'."

Upvotes: 0

Views: 45

Answers (1)

HBlackorby
HBlackorby

Reputation: 1338

You should not use ? inline parameters with SqlServer. That's fine for Access data sources, but not fine for SQL. You should use named parameters instead. Try this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings: blabla %>" 
 SelectCommand="SELECT [ProductID], [Price] FROM [GENERAL] WHERE ([ProductID] = @ProductID)">
    <SelectParameters>
        <asp:Parameter Name="ProductID" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

Also, how are you setting the SelectParam? Via a control or via the code? It looks like it is unset right now.

Upvotes: 1

Related Questions