A few problems. SQL statements and more...

[B&W]

Hi, I have a few problems with my program. It's almost complete however a few things need to be changed before I burn it to CD.

The program (VB.NET 2005) basically involves connecting to a local access db and using queries and sql statements to load/save/edit data.

On the last part I have to save to the DB then edit that same record.

Only 2 rows need to be edited, quantity and total cost.

This is what I used to save, it works fine.

conn = New OleDbConnection(strConn)
        Dim com As New OleDbCommand("Insert into tblOrders (CustomerID, ProductID, ProductPrice, OrderQuantity, TotalCost) values(" & cboname.Text & ", " & _productID & ", " & _ProductPrice & ", " & quantityupdown.Value & ", " & lblcost.Text & ")", conn)
        conn.Open()
        Try
            com.ExecuteNonQuery()
            MessageBox.Show("Saved")
        Catch e1 As OleDb.OleDbException
            MessageBox.Show(e1.Message)
        Finally
            conn.Close()
        End Try

Now I need to edit the record after saving it.

I cannot use the same save query as it will create a new order id. I want to edit the 2 rows and leave everything else alone.

I tried using "update tblOrders SET TotalCost= lblcost.txt" however it didnt work.

Another thing I need to do is make sure that when the program is loaded from the CD it finds the database correctly.

At the moment I have the location as:

Private strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\UNI\Dataprogramming\Assignment\ASS1\WindowsApplication1\WindowsApplication1\Gadgets.mdb"
    Public Property productID() As Integer

How would I go about changing it so that it automatically finds the location of the db on any computer. Ie on some computers the cd drive will be different then D or F I need to make sure that it will load the db on all computers.

 

[Eriedor]

You need to specify the WHERE clause in yer update statement

"update tblOrders SET TotalCost= lblcost.txt WHERE CustomerID = cboname.Text"

For the seceond question yoyu can use the path of the executable to determine where the DB is, Can't remember the exact syntax but you need to use something like

Application.Path & @"\mydatabase.mdb"

 

[pinkaardvark]

create a stored proc or equivalent in Access and then return the inserted row by issuing a new select or using the Output clause. Then change executenonquery to executedatareader to read the new values. Of course if you already have the data in memory because it's required for the insert you can just assume it's upto date if the insert succeeds, otherwise using your methods you run the risk of getting data that has been modified by someone else after your insert but prior to your select.

Ideally your connectionstring should be persisted to the application configuration connectionstring section file so that changes can be made after deployment. If required you can modify the connectionstring as a custom action with the deployment tool you are using.

If you really want bonus points on the assignment use the db object factories rather than the provider specific ones, then you can switch to say SQL server by just changing the configuration.

 

[B&W]

You need to specify the WHERE clause in yer update statement

"update tblOrders SET TotalCost= lblcost.txt WHERE CustomerID = cboname.Text"

For the seceond question yoyu can use the path of the executable to determine where the DB is, Can't remember the exact syntax but you need to use something like

Application.Path & @"\mydatabase.mdb"

got 2nd part done.

for first part it says: "ExecuteNonQuery: Connection property has not been initialized." after I try to update it.

 

[B&W]

create a stored proc or equivalent in Access and then return the inserted row by issuing a new select or using the Output clause. Then change executenonquery to executedatareader to read the new values. Of course if you already have the data in memory because it's required for the insert you can just assume it's upto date if the insert succeeds, otherwise using your methods you run the risk of getting data that has been modified by someone else after your insert but prior to your select.

Ideally your connectionstring should be persisted to the application configuration connectionstring section file so that changes can be made after deployment. If required you can modify the connectionstring as a custom action with the deployment tool you are using.

If you really want bonus points on the assignment use the db object factories rather than the provider specific ones, then you can switch to say SQL server by just changing the configuration.

Thank you for taking your time to write all that.

thing is I didnt understand most of it, this shows my level of vb.net "knowledge"

 

[pinkaardvark]

Thank you for taking your time to write all that.

thing is I didnt understand most of it, this shows my level of vb.net "knowledge"

Well would you like me to try and explain any particular part, or you happy with what you have so far.

 

[B&W]

Well would you like me to try and explain any particular part, or you happy with what you have so far.

I really need to get the update working tbh.