ASP.net INSERTING To Access

[Bakedgoods]

I'm having a problem INSERTING into an Access database. I'm following the DAL tutorial from http://www.asp.net/learn/data-access/tutorial-01-vb.aspx and using the Northwind database. The error message i'm getting is:

Error 1 'Public Overridable Function InsertProduct() As Integer' has no parameters and its return type cannot be indexed. H:\Projects\ManHours\Beverages.aspx.vb 17 56 H:\Projects\ManHours\

And here's the code:

INSERT INTO [Products]
                      ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued])
VALUES     (?, ?, ?, ?, ?, ?, ?, ?, ?);

SELECT     SCOPE_IDENTITY()

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim productsAdapter As New NorthwindTableAdapters.ProductsTableAdapter()

        Dim new_productID As Integer = Convert.ToInt32(productsAdapter.InsertProduct( _
            "New Product", 1, 1, "12 tins per carton", 14.95, 10, 0, 10, False))

        productsAdapter.Delete(new_productID)

I am new to this and any help will be appreciated

I've tried inserting data using another method and still had no luck. Here's that post.

Learning has slowed down greatly, I just can't seem to get a new field created using a DetailsView control.

I'm thinking the problem is because of the AutoNumber (ID) field in the table i'm trying to enter data into.

"You tried to assign the Null value to a variable that is not a Variant data type. "

This is the insert query:

INSERT INTO [tbl_Data] ([project_code], [date], [monday], [tuesday], [wednesday], [friday], [thursday], [saturday], [sunday], [ID]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

When I click the New hyperlink on the DetailsView control it removed the ID field from view (which is ideal).

Also.... how can I autopopulate fields? For example, the ID field needs to increment in sync with the database, the date field should really autopopulate with todays current date when a new entry is being made. What if I want to make an entry for the next date today though?

Argghhhh trying to teach yourself from a book that is using SQL Express and you have to use Access is an absolute pain.

I'm probably not even being clear at all

Alternatively, if someone uses ASP.NET 2.0 and Visual Web Developer and doesn't mind helping me on MSN (don't worry I do try fix things myself first, let me know if I can add you)

 

[TrUz]

Not tested but should do the trick.

    Private Function test() As Integer
        Dim sql As String = ""
        Dim newID As Integer = 0

        Try
            Using cnn As New OleDbConnection("ConnectionString")
                cnn.Open()

                sql = "INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued])"
                sql += "VALUES     (?, ?, ?, ?, ?, ?, ?, ?, ?)"

                Using cmd As New OleDbCommand(sql, cnn)
                    cmd.Parameters.Add("@p1", OleDbType.Integer).Value = 0
                    cmd.Parameters.Add("@p2", OleDbType.Integer).Value = 0
                    cmd.Parameters.Add("@p3", OleDbType.Integer).Value = 0
                    cmd.Parameters.Add("@p4", OleDbType.Integer).Value = 0
                    cmd.Parameters.Add("@p5", OleDbType.Integer).Value = 0
                    cmd.Parameters.Add("@p6", OleDbType.Integer).Value = 0
                    cmd.Parameters.Add("@p7", OleDbType.Integer).Value = 0
                    cmd.Parameters.Add("@p8", OleDbType.Integer).Value = 0
                    cmd.Parameters.Add("@p9", OleDbType.Integer).Value = 0
                    cmd.ExecuteNonQuery()
                End Using

                sql = "SELECT SCOPE_IDENTITY()"

                Using cmd As New OleDbCommand(sql, cnn)
                    newID = CInt(cmd.ExecuteScalar())
                End Using

                cnn.Close()
            End Using

            Return newID
        Catch ex As Exception
            Return 0
        End Try
    End Function

Change OleDbType to match the type of you columns and obviously but values in replace of 0.

TrUz

 

[Bakedgoods]

I'd really like to try stick to using the DAL method and keep all SQL code and so on seperate from presentation etc.

Plus i've got no idea what's going on there (I'm a noob).

 

[KevinCB]

I'd really like to try stick to using the DAL method and keep all SQL code and so on seperate from presentation etc.

Sorry, if you read my reply on your previous post.
I just looked up queries in Access and apparently you can't use SCOPE_IDENTITY() in the same query.

If you just want to insert a product remove the SCOPE_IDENTITY() bit, aswell as the ending ';' after the insert, it should keep the running order of any autonumbers.

 

[Bakedgoods]

Sorry, if you read my reply on your previous post.
I just looked up queries in Access and apparently you can't use SCOPE_IDENTITY() in the same query.

If you just want to insert a product remove the SCOPE_IDENTITY() bit, aswell as the ending ';' after the insert, it should keep the running order of any autonumbers.

I gave it a go, but like you said it will not work.

If you check my post again I still can't seem to INSERT any way I try :\

Cheers for trying to help people, much love <3

 

[KevinCB]

What will work however is a BLL file, I just tried it and it works for the Northwind database.
There is a tutorial on the ASP.NET site for this (if you haven't seen it, it's here http://www.asp.net/learn/data-access/tutorial-02-vb.aspx).

I'll run through with you on MSN tonight if you are on, still at work atm.

It's fairly straightforward to pickup, and once you've done one any others are a piece of cake

 

[Bakedgoods]

I hadn't checked the BLL tutorial because it was the next tutorial following from the first DAL one. I wasn't going to move on until I had got everything from the first one sussed.

I'll have a look at it now and let you know, cheers for the help

 

[Bakedgoods]

Ok well that looks complicated :\

Not totally understanding the point for this 'Business Layer'.

I should really try get SQL Server sorted (can't be bothered explaining why i'm not using it right now, I just can't - work related).

 

[KevinCB]

OK, I have create a zip file with the test I did with the Northwind database.

Click

This method is slightly different from the example in the tutorial on the ASP.NET page.
In the method I used I hard coded the values for the insert query in the BLL file.

Open the BLL file and change the values in the query to something else.
Run the Default.aspx page, and click the button.
Refresh the page, scroll down and you should see that it has added the values from the insert query as a new record in the GridView.

Have a look at the code, anything you don't understand let me know, and I'll try and explain it.

 

[Bakedgoods]

Can't download that ZIP from work but i'll be sure to give it a go when I get hope.

Thanks for being so helpful, it's appreciated