Sub routine not executing

[dougancil]

I have the following code:

 Private Sub Exceptionquery()
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim _sql As String = "SELECT [Exceptions].Employeenumber,[Exceptions].exceptiondate, [Exceptions].starttime, [exceptions].endtime, [Exceptions].code, datediff(minute, starttime, endtime)  as duration INTO scratchpad3 " + _
        "FROM [Exceptions]" + _
        "where [Exceptions].exceptiondate between @payperiodstartdate and payperiodenddate" + _
"GROUP BY [Exceptions].Employeenumber, [Exceptions].Exceptiondate, [Exceptions].starttime, [exceptions].endtime," + _
"[Exceptions].code, [Exceptions].exceptiondate"
        connection = New SqlConnection(connectionString)
        connection.Open()
        Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        _CMD.Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
        _CMD.Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
        connection.Close()
    End Sub
    Public Sub exceptionsButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        Exceptionquery()
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim ds As New DataSet
        Dim _sql As String = "SELECT * from scratchpad3"
        connection = New SqlConnection(connectionString)
        connection.Open()
        Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        _CMD.Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
        _CMD.Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
        adapter.SelectCommand = _CMD
        Try
            adapter.Fill(ds)
            If ds Is Nothing OrElse ds.Tables.Count = 0 OrElse ds.Tables(0).Rows.Count = 0 Then
                'it's empty
                MessageBox.Show("There was no data for this time period. Press Ok to continue", "No Data")
                connection.Close()
                Exceptions.saveButton.Enabled = False
                Exceptions.Show()
            Else
                connection.Close()
                Exceptions.Show()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try
    End Sub

and when I click the button:

    Public Sub exceptionsButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click

I get the error Invalid Object name 'Scratchpad3' on this line:

    adapter.Fill(ds)

I have tried to debug this and can see that the query is correct, that the values that are being passed to the sql server are correct but it doesnt look like my code is firing off the Exceptionquery Sub. Can anyone help me figure out why this sub isn't working? I thought to call a sub all you had to enter was the name of the sub such as this line:

   Exceptionquery()

Any help would be appreciated.

Thank you

Doug

[jmcilhinney]

I think that it's safe to say that your method is being executed. Put a breakpoint (F9) on that line and then step into the method (F11) to see that for yourself.

You should not be creating two different connections. I'd wager that your temp table exists for the life of the first session only. You should be creating one connection only, opening it once and executing both queries in the one session.

[dougancil]

JM,

It was suggested to me that I add the line

_CMD.ExecuteNonQuery()

which I did, but when I do that, I get the error "Incorrect Syntax near the keyword BY" and I have no idea why I'm getting that error.

[Shaggy Hiker]

It was suggested that you add that randomly into the code? In the code you posted, you have a pair of SELECT statements, which would normally be expected to return something. ExecuteNonQuery is used when you have a SQL statement that is not going to return anything. While you can use it for SELECT queries, there is no rational reason to do so. However, if the suggestion was that you would put that into the ExceptionQuery sub, then I can (almost) see what the suggestion was about. After all, you create a command in there, you give it a SQL string, then you do nothing with it. Without calling some form of Execute on the Command, it won't do anything, and that whole sub will do nothing at all (which is probably why you thought it wasn't doing anything. It was doing all it was written to do, which is Nothing). Still, something is wrong with that sub, and ExecuteNonQuery is not enough to fix it.

What is that sub supposed to do? The SQL query is a SELECT statement, which would return one or more rows. You could use that to fill a datatable, or you could use that with a DataReader. Right now, you do nothing at all with it. However, it almost looks like you were expecting to Update a record with that (except for the GroupBy clause, which makes little sense for an Update), which is one of the situations where you WOULD use ExecuteNonQuery, since an Update query doesn't return any records. So what was it that you were trying to do with that SQL?

[dougancil]

Shaggy,

The first select statement is expected to post data into Scratchpad 3, then on the second statement, I'm trying to view that data in a datagridview. I hope that makes sense.

[Shaggy Hiker]

Is the first SQL statement the one in ExceptionQuery?

In any case, I don't know what Scratchpad is, so I don't know what format you would need the data in to post there, but the SQL in ExceptionQuery does nothing, so it doesn't really matter. You would need to call _CMD.<something>, but I'm not sure what that something is. ExecuteNonQuery is certainly NOT right. Since you want to get the data, then the SELECT query is right (though it may be GROUPBY rather than GROUP BY, I'd have to look it up). Since it is a SELECT query, then you either want to use a dataadapter to get the data into a datatable, or perhaps you can use ExecuteReader and get a datareader. The latter is slightly quicker, but not knowing anything about Scratchpad, I can't say whether it will do.

[dougancil]

Shaggy,

The scratchpad is just a temp table for now but it will be a real table once I get this to working. Heres a row of sample data:

8244 5/2/2010 9:00:00 AM 5:00:00 PM Vacation 480

and the second, third and fourth column is datetime, all others are varchar.

For the first query, even though I'm not actually viewing any data, I still need a datareader or dataadapter? I thought that was only if I was looking to read data back from it.

[Shaggy Hiker]

Yes, you still need something to receive the data. When you execute the query via the command object, whether as part of a dataadapter, tableadapter, or more directly, the query returns some datarows. If you don't put that data somewhere, it is the same as doing nothing...just slower. The datatable or datareader would just be a structure in memory that is created to receive the information from the database. You don't have to look at it, you don't have to display it, and you don't even have to work with it right away, but you have to hold it somewhere if you EVER want to work with it.

Considering your description of Scratchpad, I would say that you want a datatable. There is another possibility, though. Since I don't know anything about Scratchpad, it is possible that Scratchpad will interact with the DB directly, and only wants a SQL statement. That seems kind of unlikely, though.

[dougancil]

Ok I can now say that my subroutine is working (it was a spacing issue with my query) but I'm not able to see data in my datagridview. When I press the button:

Public Sub exceptionsButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click

that should execute this query and fill my adapter:

"SELECT * from scratchpad3"

and when I look at my adapter

adapter.Fill (ds) I can see that the table value is 0. Looking into my sql database, I know that my previous query has now executed and there is a scratchpad3 in my table. When I run the "SELECT * from scratchpad3" query from the SQL query analyzer (on the sql server) I see this data:

8244 2010-05-02 00:00:00.000 1899-12-30 09:00:00.000 1899-12-30 17:00:00.000 Vacation 480
8245 2010-05-08 00:00:00.000 1899-12-30 13:00:00.000 1899-12-30 13:30:00.000 Coaching Session 30

I'm missing something here and I'm not sure what I'm missing. Do I need to bind my adapter to my datagridview?

[Shaggy Hiker]

I'm not quite sure how that would work. When I fill a table, I would do something like:

adapter.Fill(ds, "my table name here")

If you supply no table name, you probably get a default name, which I would avoid as a general rule. However, if you are getting no tables in the ds at all, then it certainly is not working. You would bind the datatable to the datagridview, but that's not the issue if the dataset has no tables. However, I'm not clear that you are looking at the right thing. After the .Fill command executes, on the next line, if you looked at ds.Tables.Count, what would it be? Is that what you were doing when you said that the "table value is 0"?

[dougancil]

Shaggy,

I put my line break here:

adapter.Fill(ds)

and then looked at the debug output for my table. It shows a table count of 0.

[jmcilhinney]

It would there because the table isn't created until you actually call Fill. What is the table count AFTER that line? Also, Shaggy has already suggested that you specify a table name when filling. I second the motion.

[dougancil]

I have now modified my code so it's as follows:

  Public Sub exceptionsButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        Exceptionquery()
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim ds As New DataSet
        Dim _sql As String = "SELECT * from scratchpad3"
        connection = New SqlConnection(connectionString)
        connection.Open()
        Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        _CMD.Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
        _CMD.Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
        adapter.SelectCommand = _CMD
        Try
            adapter.Fill(ds, "DataGridViewexceptions")
            If ds Is Nothing OrElse ds.Tables.Count = 0 OrElse ds.Tables(0).Rows.Count = 0 Then
                'it's empty
                MessageBox.Show("There was no data for this time period. Press Ok to continue", "No Data")
                connection.Close()
                Exceptions.saveButton.Enabled = False
                Exceptions.Show()
            Else
                connection.Close()
                Exceptions.Show()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try
    End Sub

and I can see the table count as 1. I bound the table to datagridviewexceptions which is what I chose to call the datagridview. I'm now getting an invalid object named Scratchpad4 error. My next form basically runs 4 SP's and is coded as follows:

Imports System.Data.SqlClient
Public Class Exceptions
    Private Sub Exceptions_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SPRun()
        SPRun1()
        SPRun2()
        SPRun3()
    End Sub
    Private Sub SPRun()
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_opsum"
                oDr = .ExecuteReader()
                oCmd.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
    End Sub
    Private Sub SPRun1()
        Dim oCmd2 As System.Data.SqlClient.SqlCommand
        Dim oDr2 As System.Data.SqlClient.SqlDataReader
        oCmd2 = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd2
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_opintcheck"
                oDr2 = .ExecuteReader()
                oCmd2.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
    End Sub
    Private Sub SPRun2()
        Dim oCmd2 As System.Data.SqlClient.SqlCommand
        Dim oDr2 As System.Data.SqlClient.SqlDataReader
        oCmd2 = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd2
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_empsum"
                oDr2 = .ExecuteReader()
                oCmd2.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
    End Sub
    Private Sub SPRun3()
        Dim oCmd2 As System.Data.SqlClient.SqlCommand
        Dim oDr2 As System.Data.SqlClient.SqlDataReader
        oCmd2 = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd2
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_empexceptsum"
                oDr2 = .ExecuteReader()
                oCmd2.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Main.payrollButton.Enabled = True
        Main.exceptionsButton.Enabled = False
        Payrollfinal.Show()
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewexceptions.CellContentClick

    End Sub
End Class

and I can verify that all of these SP's run and are correct, if I run them from the SQL server. I'm not sure why I'm getting the invalid Object name error here.

[Shaggy Hiker]

Not sure what those subs are supposed to do, since they populate datareaders that are local objects and are subsequently destroyed when they go out of scope, but that is unrelated to the error message.

Where do you get the error? Is it in one of the subs? If so, on which line do you get it? I would guess that it occurs at the ExecuteReader line, since nothing much happens until that line. I don't see anything called Scratchpad4 in the code, so if it is in one of the stored procedures....well, that would be interesting.

[dougancil]

Shaggy,

This error is coming from one of my stored procedures but I'm not absolutely sure which one is throwing the error. I have validated my SP's standalone, and they all work fine. I guess I can comment out my sp's until I figure out which one throws the error. Also one other question, since I am now able to get my data into my datagridview, I notice that you can enable the ability to save/edit in your data adapter. I want to be able to edit and save to a new database, but I'm not seeing the option of where I want the data written to in my code. Where would I find that in my code?

[dougancil]

Is this not the way to call a stored procedure?

 Private Sub SPRun()
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_opsum"
                oDr = .ExecuteReader()
                oCmd.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try

I've commented out all of my sp's and can't find what is failing.

[techgnome]

open your code... press Ctrl+Alt+E ... that will open an exceptions window... the second items should be "Common Language Runtime Exceptions" ... there should then be a column with "Thrown" as the header... make sure the check box that is in that column for that entry is checked. If it isn't, check it.

Run your app... it should BREAK and STOP and HIGHLIGHT the offending code line... once you have that ... TELL us WHAT line it is and WHAT the error message is.

-tg

[dougancil]

Gnome,

Thank you for that instruction. I think I just discovered my problem. I'm missing an important SP thats supposed to be in there. I'll build that and update the thread when I have more information.

[techgnome]

that's the dangers of building your error handling up front... the Try Catch makes it a little harder to determine what the error is and where. Just for reference for those who will understand it... the steps I listed above is the VS.NET equivalent of "Break on All Errors" from the Classic VB days...

-tg

[dougancil]

gnome,

One last thing I'm wondering, on one of my forms, I present the ability to edit data to end users, but I want them to be able to edit and save to a different data set than the one that fills the datagrid view, what's the best way to handle that?

[Shaggy Hiker]

The dataadapter is tied to the connection you have to the single database. The tie is not exactly a direct one, so you might not notice, but you need the connection for the command object, and you need the command object for the dataadapter, so the connection is tied to the dataadapter. In your case, you are talking about saving the data to a whole different database, which means a different connection, which means a different dataadapter than the one that is bound to the datagridview. You can edit and save back to the database you got the data from relatively easily. However, it isn't quite like a textfile where you can designate a save location and have it write there. It's not all that much more difficult, though.

You have a datatable tied to the datagridview. That datatable is in a dataset, in your case, though that is somewhat irrelevant. Every record that you edit in that datatable has its state changed to Modified (or Deleted if you delete it). To save to a different database, you would create a connection to that database...and then you have a couple options. If the datatables are built without JOINs, and the new database has the exact same table layouts as the original database, then the easiest thing to do would be to use a CommandBuilder to populate the database:

cmd.CommandText = "Your SELECT statement here"
cb = New SqlClient.SqlCommandBuilder(da)
da.Update("Your table name here")
cb.Dispose()

In this example, which I took from some code I am working on, I set the command text to a SELECT statement that would return the fields that are in the datatable. You won't really be using it, since you aren't selecting anything, but the CommandBuilder object takes the SQL statement and builds UPDATE, INSERT, and DELETE statements based on that SELECT statement. You would then also need a dataadapter that has the command as its SelectCommand property. You would then call the Update method either with the datatable, or with your dataset and the datatable name.

[dougancil]

Shaggy,

One other question related to this. I currently have a button called Save Changes, but it's set to true when the page is opened, what would be the best way to have that button stay disabled unless a person made a change to the data that's being displayed in the datagridview? In other words, showing them that they've made some sort of change.

[Shaggy Hiker]

That's a difficult problem to solve in great detail. The easiest solution is to check whether the datatable has been changed. You can call the .GetChanges method and see whether it returns anything. That may be good enough, but it isn't entirely correct. If the user changed 1 to 2, then changed it back to 1, there has been no net change, but the datatable will see it as having changed. In most cases, that is acceptable, and the alternative is pretty bad, but it is something to keep in mind.

[dougancil]

Shaggy,

In going back to this code:

cmd.CommandText = "Your SELECT statement here"
cb = New SqlClient.SqlCommandBuilder(da)
da.Update("Your table name here")
cb.Dispose()

and here's the code I'll be modifying to attach your code too:

    Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click
        Dim oCmd2 As System.Data.SqlClient.SqlCommand
        Dim oDr2 As System.Data.SqlClient.SqlDataReader
        oCmd2 = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd2
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandText = "SELECT * from Exceptions"
                oDr2 = .ExecuteReader()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
    End Sub

and the database that I'll be "appending" the data to will be exactly the same. Since you said that I'm really not selecting anything, what do I have to put in my select statement? Can I just put "SELECT * from Exceptions" or does it really matter?

[Shaggy Hiker]

That would work fine. You want to make sure that you have all of the fields that you want the user to be able to update, at least, and the primary key is probably good (though I don't know that it matters for the CommandBuilder). There can be a minor issue with SELECT *, but in this case it really doesn't matter. Also, in this case, you really don't want any records back. The purpose for that SELECT statement is to give the CommandBuilder object the information it needs to create the UPDATE, INSERT, and DELETE statements. Therefore, you might write the statement as:

"SELECT * FROM Exceptions WHERE 1=0"

Since one never equals zero, this means that no records will be returned. I'm not precisely sure of the actual steps performed in that sequence, but it seems possible that the SELECT query will actually get executed, at least to get the table schema from the DB. You want as little information as possible, so the WHERE clause certainly gets you there....though it may also be completely unnecessary.

[dougancil]

here is my code:

Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click
        Dim oCmd2 As System.Data.SqlClient.SqlCommand
        Dim cb As System.Data.SqlClient.SqlCommandBuilder
        Dim da As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        oCmd2 = New System.Data.SqlClient.SqlCommand
        cb = New SqlClient.SqlCommandBuilder(da)
        Try
            With oCmd2
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandText = "SELECT * FROM Exceptions WHERE 1=0"""
            End With
            cb.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
    End Sub

and the only thing I am lost on is where do I put your da.Update code?

[Shaggy Hiker]

Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click
        Dim oCmd2 As System.Data.SqlClient.SqlCommand
        Dim cb As System.Data.SqlClient.SqlCommandBuilder
        Dim da As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        oCmd2 = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd2
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandText = "SELECT * FROM Exceptions WHERE 1=0"""
            End With
            da.SelectCommand = oCmd2
            cb = New SqlClient.SqlCommandBuilder(da)
            da.Update(<whatever your dataset or table is>)
            cb.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
    End Sub

That should do. You just have to supply the right arguments to the Update method. I'm not actually sure that you need to create the commandbuilder after you have given the da the select command. It probably doesn't matter, but I moved the line anyways.

[dougancil]

Shaggy,

Thank you. I added your lines of code and on this line:

   da.Update("Exceptions_edit")

I get the warning:
Overload resolution failed because no accessible "Update" can be called with these arguments. Also, since this is a table that I'm updating, I assume that you enter it this way, if I remove the quotations, it's asking me to declare that.

[Shaggy Hiker]

Your table is in a dataset isn't it? In that case, it would be written as:

da.update(<your dataset object here>,<your table name here>)

If you don't have the table in a dataset then you would just pass in the datatable object:

da.Update(<your datatable object here>)

and not deal with the table name at all. After all, the dataadapter already knows which DB table to work with because that is part of the SQL statement you gave it. If you are just updating a datatable, you need to give it just the table. The name of the table is irrelevant. If you have the table in a dataset, you pass it the dataset, but in that case you also have to tell it which table in the dataset to work with.

[dougancil]

Shaggy,

When I try to save my edited data with my saveButton_Click I receive the following error:

Update unable to find TableMapping['Exceptions_edit'] or DataTable 'Exceptions_edit'.

here is my code for that section:

 Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click
        Dim oCmd2 As System.Data.SqlClient.SqlCommand
        Dim cb As System.Data.SqlClient.SqlCommandBuilder
        Dim da As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        oCmd2 = New System.Data.SqlClient.SqlCommand
        cb = New SqlClient.SqlCommandBuilder(da)
        Try
            With oCmd2
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandText = "SELECT * FROM Exceptions WHERE 1=0"""
            End With
            da.SelectCommand = oCmd2
            cb = New SqlClient.SqlCommandBuilder(da)
            da.Update(MDRDataSet, "Exceptions_edit")
            cb.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
    End Sub

[dougancil]

Shaggy,

Could this be because on my dataset that I have the MDRDataSet mapped to a specific table?

Here is the line of code from my load event:

 Me.Scratchpad3TableAdapter.Fill(Me.MDRDataSet.scratchpad3)

If that's the case do I need to have another data set that is just connected to the database and not specifies a table?

[Shaggy Hiker]

Is the table really named "Exceptions_edit"?

In that last code snippet, you used a tableadapter. Those are a somewhat different animal from the datadapter, and produce a different kind of datatable (a strongly typed table). I would think that the code would still work as long as you used the right table name, which is not at all clear. Alternatively, you could update just the table, since da.Update will take a datatable as an argument or a dataset plus a table name. However, once you get into mixing dataadapters with tableadapters, you have left the realm that I have experience with. I would not expect that a strongly typed table would act in any way differently than a regular table. After all, the CommandBuilder creates some SQL that gets executed against the database. That SQL could be written by hand, if you wanted to, and the CommandBuilder does nothing more than automate that writing. Since you could write an execute those SQL statements by hand, just like the dataadapter does, there is no reason why this technique would not work...unless the dataadapter doesn't know what the source table looks like, and that is the question. I don't know whether a storngly typed datatable acts the same, though I would expect it to.

In short, this is not a situation I am familiar with, but I would expect that the name "Exception_edits" is not the right name for the datatable you want to update.

[dougancil]

Shaggy,

I did double check the table name and did find out that it was incorrect, so I corrected my error and when I debug and looked at this line of code:

  da.Update(MDRDataSet, "ExceptionsEdit")

It appears that my Update statement is trying to update the Dataset that I have but my dataset has a specific table that it's trying to update, which is scratchpad3. My assumption is then that the da.Update is trying to update that specific table. Would it be possible for me to have another dataset, with no specific table selected, and do my da.update to the new dataset which would have no table selected?

[Shaggy Hiker]

When you call da.Update, it looks at the table specified in the arguments (which is either a datatable, or a dataset plus a specific table in that dataset), gets all the rows that have been changed in any way (rowstate Added, Modified, or Deleted), then goes through that set of rows calling a specific SQL statement for each row. If the rowstate is Added, then it calls the INSERT query. If the rowstate is Modified, then it calls the Update query. If the rowstate is Deleted, then it calls the DELETE query. These queries act against the database in the connection that the dataadapter is given (which it can get from the command object), to insert, update, or delete rows in the database table or tables. Technically, there could be multiple tables if the dataset table was based on a SELECT query that had JOINs in it, though you would not be able to use a CommandBuilder object to generate the queries in that case.

There's nothing magic about this. You can do the exact same steps if you wanted to. For instance, you could call GetChanges to get the set of datarows that have been changed in any way, then loop through that table and run a query of the right sort depending on the rowstate. The dataadapter.Update is just a more convenient way of doing the same thing.

Considering that, I'm not quite sure what you mean by updating to a new dataset. Update deals with pushing changes in a datatable back to a database. Pushing those changes to a different datatable in a different dataset would be a much different animal. I think you would have to write that yourself to take the changes in one datatable and make those changes to the other datatable. I may well be misundertanding your purpose, though.

[techgnome]

Hang on.... what do you think the da.Update does? It sends any pending updates back to the DATABASE. So I'm not sure what you mean by "no specific table selected..." What is your end goal here? I'd go back and re-read the thread, but there's a few things that seem to be all over the place, so I'm trying to get a current and accurate read on the situation as it is now.

-tg

[dougancil]

Tech and Shaggy,

I decided to go in another direction and basically now if a user has to make a change, they're taken to another form where they enter their changes/edits and then it's just submitted back to the database. My question is, since these changes will have to be re-calculated in my other forms, how can I write that logic into my code? In other words, how can I tell each time that the "Save Changes" button click event is fired and that that event will fire off the logic of recalculating from the updated table and not the "original" table that the data was initially viewed from.