I'm trying to run the example in Chapter 17 for DataGrid Sorting. I keep getting an error at the statement
objDataAdapter.Fill(objDataSet,"authors") the error is reported by the browser. I have used the same settings for the SQL Connection that were successful in the previous chapter, chapter 16(DataSet Example). Any help will be greatly appreciated.

 Thank you,

Hi SPRIBob

Off the cuff I would think that there may be a syntax error in your SQL. Wrap the code in a TRY CATCH and look at the Exception.ErrorMessage. Remember to look through the InnerExceptions as well to get further to the source of the error.

Here is the BindGrid() procedure as specified in the book. I have run this in both 2002 and 2003 and it works fine.

Grab the code below and replace your BindGrid with this one. If you have kept the name of the grid and so on as in the book, it will work first time, else you may have to modify the grid name etc.



Let me know if it helps. :)

If not send me a mail and I will in turn reply with the entire project source as an attachment for you to look at.



Jonathan Crossland
http://www.jonathancrossland.com

Jonathan;

 I've just tried your code and I still get the same error. Server Error ...Login failed for user "computername\ASPNET".
I'm using the same connection string as I did in the SQL/ADO.NET example. I also have successfully run the Client-Server Processing example in this same chapter. Any help is greatly appreciated.

Thank you

Ok

This is a typical one :)

you probably have either the "integrated security=SSPI;" or
"Trusted_Connection = yes;" in your connectionstring
remove this and add "User ID=yourid;password=yourpassword"

read more
http://support.microsoft.com/default...b;en-us;316989



Jonathan Crossland
http://www.jonathancrossland.com

Jonathan;

  I've tried your latest suggestion and even implemented the example from the Microsoft Website. The following is the source code from the Page_Load procedure:
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        Dim con As New System.Data.SqlClient.SqlConnection("data source =localhost;" & _
         "trusted_connection=yes;uid=RUNIR315745300;pwd=;da tabase=pubs")
        con.Open()
        con.Close()
    End Sub

I get the same error ... Login failed for user 'RUNIR315745300\ASPNET'

If I remove the trusted_connection=yes the error becomes:
Login failed for user 'RUNIR315745300'. Reason: Not associated with a trusted SQL Server connection.
 This is getting very frustrating ... anymore insight to this problem?

Thanks again

Hi SPRIBob

I understand your frustration.

Please read through the following similar thread and try the suggestions put forward.

http://p2p.wrox.com/topic.asp?TOPIC_ID=524


Pay special attention the sql password and sql instance comments
example the comments made about the connectionstring - "data source=MACHINENAME\VSDOTNET"

Jonathan


Jonathan Crossland
http://www.jonathancrossland.com

Jonathan;

  I think I'm getting closer(I hope) ... here are the changes I've made to this test program:

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        Dim con As New System.Data.SqlClient.SqlConnection("data source=RUNIR315745300\VSDOTNET;" & _
         "Initial Catalog=pubs;Integrated Security=sspi;Workstation ID=RUNIR315745300")
        con.Open()
        con.Close()
    End Sub

When double clicking the tray icon for SQL Server I get the following:
Server: RUNIR315745300
Services: SQL Server

on the very bottom of this little form is:
"Running - \\RUNIR315745300 - MSSQLServer" ... when attempting to run this I get a new error:

"SQL Server does not exist or access denied."

I very much appreciate your help.

Thanks again

Jonathan;

  I'm progressing along, still in Chapter 17 and still with the DataGrid Updates example. When I attempt to edit a row and select update, I get the following server error ...
Server Error in '/DataGrid Updates' Application.
--------------------------------------------------------------------------------

Prepared statement '(@ID varchar(6),@Title varchar(80),@Price money)UPDATE titles SE' expects parameter @Price, which was not supplied.
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: Prepared statement '(@ID varchar(6),@Title varchar(80),@Price money)UPDATE titles SE' expects parameter @Price, which was not supplied.

I'm pasting in the UpdateGridData procedure from my project...

        Sub UpdateGridData(Sender As Object, E As DataGridCommandEventArgs)
         Dim objCommand As SqlCommand
         Dim objTextBox As TextBox

         Dim strSQL As String = "UPDATE titles " & _
             "SET title = @Title, price = @Price " & _
             "WHERE title_id = @ID"

         objCommand = New SqlCommand(strSQL, objConnection)
         objCommand.Parameters.Add(New SqlParameter("@ID", SqlDbType.VarChar, 6))
         objCommand.Parameters.Add(New SqlParameter("@Title", SqlDbType.VarChar, 80))
         objCommand.Parameters.Add(New SqlParameter("@Price", SqlDbType.Money, 8))

         objCommand.Parameters("@ID").Value = _
            grdAuthors.DataKeys(CInt(E.Item.ItemIndex))

         objTextBox = E.Item.FindControl("edit_title")
         objCommand.Parameters("@Title").Value = objTextBox.Text
         objTextBox = E.Item.FindControl("edit_price")

         If(Left(objTextBox.Text, 1) = "$")Then
          objTextBox.Text = Right(objTextBox.Text, Len(objTextBox.Text) -1)
         End If

         objCommand.Connection.Open()
         objCommand.ExecuteNonQuery()
         grdAuthors.EditItemIndex = -1
         objCommand.Connection.Close()
         BindGrid()
        End Sub

Any help will be greatly appreciated.

Thank you,

Hi

When you posted this, I was too busy to reply.
Are you still having this problem?

Jonathan Crossland
http://www.jonathancrossland.com

Jonathan;

   I actually went on from here and completed the book. I'm about to start the book Professional VB.NET. I would like to know what was wrong and what caused this error. It is the only unanswered problem I encounterred in the book.

Thank you,
SPRIBob