1 Replies - 1397 Views - Last Post: 07 December 2012 - 07:41 PM Rate Topic: -----

#1 Hayter06  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 06-December 12

Insert into multiple tables through VB.Net forms

Posted 06 December 2012 - 03:37 AM

I'm making a database with various tables, with a UI in VB.net forms. The database will hold stock, and details about the stock (sizes/quantities), and information about suppliers.

My code for viewing the data is this:
  Private Sub frmView_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dbprovider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbsource = "Data Source ="
        con.ConnectionString = dbprovider + dbsource
        con.Open()
        sql1 = "SELECT * FROM tblStock WHERE Active = true"
        sql2 = "SELECT * FROM tblStockDetail"
        da = New OleDb.OleDbDataAdapter(sql1, con)
        da.Fill(ds1, "Stock")
        da = New OleDb.OleDbDataAdapter(sql2, con)
        da.Fill(ds2, "StockDetail")
        con.Close()
        MaxRows = ds1.Tables("Stock").Rows.Count
        qBoxArray.Add(txtVQ0)
        qBoxArray.Add(txtVQ1)
        qBoxArray.Add(txtVQ2)
        qBoxArray.Add(txtVQ3)
        qBoxArray.Add(txtVQ4)
        qBoxArray.Add(txtVQ5)
        sBoxArray.Add(txtVS0)
        sBoxArray.Add(txtVS1)
        sBoxArray.Add(txtVS2)
        sBoxArray.Add(txtVS3)
        sBoxArray.Add(txtVS4)
        sBoxArray.Add(txtVS5)

        navigate()
    End Sub

    Private Sub navigate()
        StockNumber = ds1.Tables("Stock").Rows(stockCount).Item(0)
        txtVSName.Text = ds1.Tables("Stock").Rows(stockCount).Item(1)
        txtVMemo.Text = ds1.Tables("Stock").Rows(stockCount).Item(2)
        txtViewDate.Text = ds1.Tables("Stock").Rows(stockCount).Item(3)
        pbViewImage.ImageLocation = ds1.Tables("Stock").Rows(stockCount).Item(4)

        detailPlace = 0
        For Me.detailCount = 0 To ds2.Tables("StockDetail").Rows.Count - 1
            If StockNumber = ds2.Tables("StockDetail").Rows(detailCount).Item(0) Then
                sBoxArray(detailPlace).Text = ds2.Tables("StockDetail").Rows(detailCount).Item(1)
                qBoxArray(detailPlace).Text = ds2.Tables("StockDetail").Rows(detailCount).Item(2)
                detailPlace = detailPlace + 1
            End If
        Next
    End Sub



The problem I have, is when I write TO the database. In the form where I add data, there is six boxes for quantities and six boxes for sizes, as shown with
qBoxArray.Add(txtVQ4)
        qBoxArray.Add(txtVQ5)
        sBoxArray.Add(txtVS0)
        sBoxArray.Add(txtVS1)


As this is in a different table, I have to link the data from tblStock, to tblStockDetail with the primary key being the StockID for tblStock, and StockID/StockSize for tblStockDetail (composite key).
So far my code for adding to the database is this:
  Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsRowStock As DataRow
        Dim dsRowDetail As DataRow
        Dim command As OleDb.OleDbCommand

        dsRowStock = ds1.Tables("Stock").NewRow
        dsRowStock.Item("StockName") = txtSName.Text
        dsRowStock.Item("Description") = txtDesc.Text
        dsRowStock.Item("DateAdded") = calAdded.SelectionRange.End.ToString
        dsRowStock.Item("StockImage") = strFileName.ToString
        ds1.Tables("Stock").Rows.Add(dsRowStock)
        da.Update(ds1, "Stock")

        For i As Integer = 0 To 5
            If quanArray(i).Text.Count > 0 Then
                dsRowDetail = ds2.Tables("StockDetail").NewRow
                dsRowDetail.Item("StockID") = dsRowStock.Item(0)
                dsRowDetail.Item("StockSize") = sizeArray(i).Text
                dsRowDetail.Item("Quantity") = quanArray(i).Text
                ds2.Tables("StockDetail").Rows.Add(dsRowDetail)
                command = New OleDb.OleDbCommand("INSERT INTO tblStockDetail (StockID, StockSize, Quantity) VALUES (?, ?, ?)", con)
                command.Parameters.Add(dsRowDetail.Item(0), OleDb.OleDbType.Integer, "StockID")
                command.Parameters.Add(dsRowDetail.Item("StockSize"), OleDb.OleDbType.VarChar, "StockSize")
                command.Parameters.Add(dsRowDetail.Item("Quantity"), OleDb.OleDbType.Integer, "Quantity")
                da.InsertCommand = command
                da2.Update(ds2, "StockDetail")
            End If
        Next
        MsgBox("Record added")
    End Sub



This doesn't work - it has given me multiple errors since I started coding it and currently the error is:
"Conversion from type 'DBNull' to type 'String' is not valid."
on line:
                command.Parameters.Add(dsRowDetail.Item(0), OleDb.OleDbType.Integer, "StockID")



Does anyone know how to get past these errors, or can anyone help me with a whole new approach to this?
Thanks in advance.

(P.S, I'm very new to forms programming, so please give me the benefit of the doubt with the bad code :dontgetit:/> )

Is This A Good Question/Topic? 0
  • +

Replies To: Insert into multiple tables through VB.Net forms

#2 trevster344  Icon User is offline

  • The Peasant
  • member icon

Reputation: 221
  • View blog
  • Posts: 1,478
  • Joined: 16-March 11

Re: Insert into multiple tables through VB.Net forms

Posted 07 December 2012 - 07:41 PM

Well did you place a breakpoint and see if that dsrowdetail.item(0) is null? What I read from that error is that you're trying to put a null value into a string data type where null is not allowed.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1