Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Site conversion: Access to SQL Server, parameter problem 1

Status
Not open for further replies.

chpicker

Programmer
Apr 10, 2001
1,316
I'm trying to convert a web site from using an Access database (.wdf file) to using a SQL Server database. In general, all I've done is change instances of <asp:AccessDataSource> to <asp:SqlDataSource> and then alter the properties. This seems to be working so far except for one instance.

On our login page, we have the following structure:
Code:
    <asp:AccessDataSource ID="LoginDataSource" runat="server" Datafile="webdata.wdf"
        SelectCommand="SELECT * FROM users WHERE UserID = ? AND Password = ?">
        <SelectParameters>
            <asp:ControlParameter ControlID="txtUserID" Name="UserID" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="txtPassword" Name="Password" PropertyName="Text" Type="String" />
        </SelectParameters>
    </asp:AccessDataSource>
I tried to just change the Access source to SQL like so:
Code:
    <asp:SqlDataSource ID="LoginDataSource" runat="server" ConnectionString="Data Source=192.168.1.26;Initial Catalog=MyDB;Integrated Security=True" ProviderName="System.Data.SqlClient"
        SelectCommand="SELECT * FROM users WHERE UserID = ? AND Password = ?">
        <SelectParameters>
            <asp:ControlParameter ControlID="txtUserID" Name="UserID" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="txtPassword" Name="Password" PropertyName="Text" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>
When I click the Submit button on the login page, I get the following error: System.Data.SqlClient.SqlException: Incorrect syntax near '?'. The error occurs on this line:
Code:
        Dim loginDV As DataView = CType(LoginDataSource.Select(DataSourceSelectArguments.Empty), DataView)

I'm pretty sure this means that the parameters aren't being inserted properly from the form fields. Do the parameters work differently in SQL than they did in Access? Do I need to pass it in the VB code or will the Parameter tags work?

Ian
 
I tried just replacing ? with @, but I get the error:

Must declare the scalar variable "@".

Can you give me an example of a parameterized SELECT query for SQL server?
 
Thanks, Mark. You've been a great help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top