Error Deleting

RPG SEARCH · June 4th, 2005, 06:10 PM

Hey there,

I am having trouble deleting from a datagrid. it throwing a datatypr mismatch error if SelectedItemID is in '', and no value passed if its not... I cant see any difference from code i used awhile back...

Code:

 Sub dgNewsDelete_Click(ByVal Sender As Object, ByVal e As DataGridCommandEventArgs)
        Dim SelectedNewsID As String = (e.Item.ItemIndex)

        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\wwwroot\MYWEBSITE\davesdatabase.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

        Dim queryString As String = "DELETE FROM [tbl_News] WHERE ([tbl_News].[NewsID] = 'SelectedNewsID')"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        dbConnection.Open()
        dbCommand.ExecuteNonQuery()
        dbConnection.Close()

        dgNews.DataSource() = getNews()
        dgNews.DataBind()

    End Sub

thanks for the assistance.

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

Imar · June 5th, 2005, 03:32 AM

Hi David,

What type is NewsID in the database? Sounds like a number....

To pass a number, you need something like this:

WHERE ([tbl_News].[NewsID] = " & SelectedNewsID & ")"

However, there are some other problems in your code. Take a look at this:

Dim SelectedNewsID As String = (e.Item.ItemIndex)

First of all, ItemIndex is a number so you should either declare SelectedNewsID as an Integer, or convert ItemIndex to a String.

This line of code returns the index of the item you're about to delete. So let's say you clicked on the second item which has an index of 1. Are you then going to delete the item with an Id of 1 in the database? Don't think so; you need to get the associated DataKey for the item you're deleting.

Did you set up DataKeys for the DataGrid?

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

RPG SEARCH · June 5th, 2005, 06:30 AM

Hi Imar,

Yeah its the AutoNumber in the database and the Primary Key.

I changed over the code to an integer and altered the delete string.... and set DataKeysField to NewsID. How can i tell it to delete equal to the datakey?

Thanks.

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

Imar · June 5th, 2005, 06:41 AM

Hi David,

You can get the data key from the DataKeys collection of the DataGrid using the ItemIndex like this:

newsId = Convert.ToInt32(myDataGrid.DataKeys[e.Item.ItemIndex])

HtH,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

RPG SEARCH · June 5th, 2005, 08:02 AM

I incorporated that, possibly not in the right way way though.

Code:

Sub dgNewsDelete_Click(ByVal Sender As Object, ByVal e As DataGridCommandEventArgs)

        Dim NewsId as Integer = Convert.ToInt32(myDataGrid.DataKeys[e.Item.ItemIndex])


        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\wwwroot\MYWEBSITE\davesdatabase.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

        Dim queryString As String = "DELETE FROM [tbl_News] WHERE ([tbl_News].[NewsID] = " & NewsId & ")"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        dbConnection.Open()
        dbCommand.ExecuteNonQuery()
        dbConnection.Close()

        dgNews.DataSource() = getNews()
        dgNews.DataBind()

    End Sub

visual studio says that identifier expect in errors for e.Item.ItemIndex - ran it anyway and it just postback but nothing happens...

have i put it into the code wrong?

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

Imar · June 5th, 2005, 08:11 AM

You should use *your* DataGrid, not mine ;)

Dim NewsId as Integer = Convert.ToInt32(dgNewsDelete.DataKeys(e.Item.ItemIndex))

Also, I used C# array indexing instead of VB.NET. Sorry about that. Replace [] with ()

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

RPG SEARCH · June 5th, 2005, 10:23 AM

*Blush* oh yeah!

Thanks that all works now - i can freely delete till my heart is content!

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521

Imar · June 6th, 2005, 01:06 PM

You're welcome. Glad it's working.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Time Is Running Out by Muse (Track 3 from the album: Absolution) What's This?