Member Avatar for aabbccbryanmark_1

im having this as my problem
my connection to database is purely coded with the connection to the datagrid

Public Sub btnProdSave_Click(sender As Object, e As EventArgs) Handles btnProdSave.Click
        Dim res As New DialogResult

        res = MsgBox("Do you want to save data?", MsgBoxStyle.YesNo, "Inventory")
        If res = DialogResult.Yes Then
            Save_Record()
        End If

        datGridProd.Enabled = True
        btnProdEdit.Enabled = True
        btnProdDelete.Enabled = True
        btnProdAdd.Enabled = True
        btnProdSave.Enabled = False
        Me.Size = New System.Drawing.Size(769, 435)
        disableTextbox()
        btnExit.Location = New Point(732, 4)
        btnMinimize.Location = New Point(696, 4)
    End Sub

    Private Sub Save_Record()
        Try
            conn = New OleDbConnection(Get_Constring)
            conn.Open()
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text

            If Me.txtProdName.Tag = 0 Then
                sSQL = "INSERT INTO Inventory ( ProductID, ProductName, ProductDescription, ProductColor, ProductWidth, ProductWeight, ProductPrice, ProductQuantity)"
                sSQL = sSQL & "  VALUES(@ProductID, @ProductName, @ProductDescripion, @ProductColor, @ProductWidth, @ProductWeight, @ProductPrice, @ProductQuantity)"
                cmd.CommandText = sSQL
            Else
                sSQL = "UPDATE Inventory SET ProductID = @ProductID, ProductName =  @ProductName, ProductDescription = @ProductDescription, ProductColor =  @ProductColor"
                sSQL = sSQL & ", ProductWidth = @ProductWidth, ProductWeight = @ProductWeight, ProductPrice = @ProductPrice, ProductQuantity = @ProductQuantity WHERE ProductID = @ProductID"
                cmd.CommandText = sSQL
            End If

            cmd.Parameters.Add("@ProductID", OleDbType.Numeric).Value = datGridProd.Rows.Count.ToString + 1
            cmd.Parameters.Add("@ProductName", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtProdName.Text)) > 0, Me.txtProdName.Text, DBNull.Value)
            cmd.Parameters.Add("@ProductDescription", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtProdDesc.Text)) > 0, Me.txtProdDesc.Text, DBNull.Value)
            cmd.Parameters.Add("@ProductColor", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtProdColor.Text)) > 0, Me.txtProdColor.Text, DBNull.Value)
            cmd.Parameters.Add("@ProductWidth", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtProdWidth.Text)) > 0, Me.txtProdWidth.Text, DBNull.Value)
            cmd.Parameters.Add("@ProductWeight", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtProdWeight.Text)) > 0, Me.txtProdWeight.Text, DBNull.Value)
            cmd.Parameters.Add("@ProuctPrice", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtProdPrice.Text)) > 0, Me.txtProdPrice.Text, DBNull.Value)
            cmd.Parameters.Add("@ProductQuantity", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtProdQuantity.Text)) > 0, Me.txtProdQuantity.Text, DBNull.Value)
            cmd.ExecuteNonQuery()

            If Me.txtProdName.Tag = 0 Then
                cmd.CommandText = "Select @@Identity"
                Me.txtProdName.Tag = cmd.ExecuteScalar()
            End If
            MsgBox("Data has been save.")

        Catch ex As Exception
            MsgBox(ErrorToString)
        Finally
            conn.Close()
        End Try
    End Sub

these are my code that generates the report, can anybody here help me?

If you comment out your Catch statement within your Try block of Save_Record() Sub and try executing the code again; is there a particular element that is highlighted as an 'object reference not set to an instance of object' and what is it?

Member Avatar for aabbccbryanmark_1

nothing happen bro, it seems that its executing but not doing the work :(

Try opening the connection after you've set the command type, command text and parameters but before ExecuteNonQuery(), see if that does the trick. I seem to recall I've had problems with this in the past.

Member Avatar for aabbccbryanmark_1

still get the error

You probably need to add this after line 23:

cmd = New SqlCommand()

so your code will be:

conn = New OleDbConnection(Get_Constring)
conn.Open()

cmd = New SqlCommand()
cmd.Connection = conn

but it is hard to know for sure because you didn't post the rest or your variable declarations.

Also, add this to your Try-Catch:

Catch ex As System.Data.OleDb.OleDbException
    MsgBox(ErrorToString)
Member Avatar for aabbccbryanmark_1

i get the error now when i try to follow ur steps
the error is on this

cmd.Connection = conn

idk why

BTW my variable declarion are these

Dim cmd As OleDbCommand
    Dim conn As New OleDbConnection
    Dim dr As OleDbDataReader
    Dim da As New OleDbDataAdapter
    Dim ds As New DataSet
    Dim dt As New DataTable
    Dim bs As New BindingSource
    Dim da1 As New OleDbDataAdapter
    Dim ds1 As New DataSet
    Dim dt1 As New DataTable
    Dim bs1 As New BindingSource
    Dim da2 As New OleDbDataAdapter
    Dim ds2 As New DataSet
    Dim dt2 As New DataTable
    Dim bs2 As New BindingSource
    Dim clicked As Boolean
    Dim sqltext As String
    Dim sSQL As String = String.Empty
    Public sEditType As String = String.Empty
    Dim value As Object
    Dim rowValue As String
Member Avatar for aabbccbryanmark_1

i now get
ExecuteNonQuery: Connection property ha not been initialized.

through

cmd.ExecuteNonQuery()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.