[Kairi]

I'm just gonna make one thread for my problems if that is ok to ya guys.

Well alright so far as maybe some of ya guys might know i am stuck doing project during summer.

Any way i tried going into the college each time and it turns out the college was doing major construction on labs etc... So getting a lab with the appropriate software is near to impossible as i have just found that out last Friday!

Any way since i have to present the project in the college i have to use two programs in conjunction with each other to make my database to work.

Alright to the main route. Hope someone can help.

If i remember one main problem is when i run add member form for example. it wont update to the database or connect. I run debugging and the problem occurs in my INSERT statement.

Here is my entire code and hope someone can help me in sorting out this problem. I am trying to figure out a solution like perhaps try a "Try and Catch" statement but not sure if it will work entirely since i don't have the appropriate software at home.

Private Sub cmdAddMem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddMem.Click
        'open database
        OpenDB(conn)

        'retrieve the max MemId assigned

        strSQL = "INSERT INTO Members VALUES ('" + txtMemNo.Text + "','" + _
                                txtname1.Text + "','" + txtsurname.Text + _
                                "','" + txtAddress.Text + "','" + txtTown.Text + _
                                "','" + txtCounty.Text + "'," + txtHomeNo.Text + _
                                "," + txtMobileNo.Text + ",'" + gender.ToString() + "', 0)"
        'determine if the user entered a blank in any of the fields



        cmd.Connection = conn
        cmd.CommandText = strSQL
        cmd.CommandType = CommandType.Text
        cmd.ExecuteReader()
        MessageBox.Show("Member Created")

        'call clear form Method
        clearForm()

        ' Message Box yes no add another member
        Dim NewMember As Integer

        'Output a messagebox that alerts the user that the details are being saved to the databse
        NewMember = MessageBox.Show("Saving Member", "Member", MessageBoxButtons.YesNo, _
                                        MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

        'If yes call clear form
        If NewMember = DialogResult.Yes Then
            'clearForm()
            clearForm()
        ElseIf NewMember = DialogResult.No Then
            ' If No close form
            End
        End If

        'Update Member id field

    End Sub

Entire code

Imports Oracle.DataAccess.Client

Public Class frmNewMem
    Dim Main As New frmMain
    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
        End
    End Sub

    Private Sub cmdAddMem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddMem.Click
        'open database
        OpenDB(conn)

        'retrieve the max MemId assigned

        strSQL = "INSERT INTO Members VALUES ('" + txtMemNo.Text + "','" + _
                                txtname1.Text + "','" + txtsurname.Text + _
                                "','" + txtAddress.Text + "','" + txtTown.Text + _
                                "','" + txtCounty.Text + "'," + txtHomeNo.Text + _
                                "," + txtMobileNo.Text + ",'" + gender.ToString() + "', 0)"
        'determine if the user entered a blank in any of the fields



        cmd.Connection = conn
        cmd.CommandText = strSQL
        cmd.CommandType = CommandType.Text
        cmd.ExecuteReader()
        MessageBox.Show("Member Created")

        'call clear form Method
        clearForm()

        ' Message Box yes no add another member
        Dim NewMember As Integer

        'Output a messagebox that alerts the user that the details are being saved to the databse
        NewMember = MessageBox.Show("Saving Member", "Member", MessageBoxButtons.YesNo, _
                                        MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

        'If yes call clear form
        If NewMember = DialogResult.Yes Then
            'clearForm()
            clearForm()
        ElseIf NewMember = DialogResult.No Then
            ' If No close form
            End
        End If

        'Update Member id field

    End Sub

    Private Sub btnMale_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMale.CheckedChanged
        'determine if the button clicked is male or female
        If btnMale.Checked = True Then
            gender = "m"
        Else
            gender = "f"
        End If
    End Sub
    Private Sub MainToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MainToolStripMenuItem.Click
        Me.Hide()
        Main.Show()
    End Sub

    Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
        frmMain.Show()
        Me.Close()
    End Sub

    Private Sub frmNewMem_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        txtMemNo.Text = LastMemNo() + 1
    End Sub

    Private Function LastMemNo() As Integer
        'connect to database
        OpenDB(conn)

        'retrieve the max MemId assigned
        Dim strSQL As String = "SELECT MAX(ID) as MaxID FROM Members"

        'execute SQL query
        Dim cmd As New OracleCommand
        cmd.Connection = conn
        cmd.CommandText = strSQL
        cmd.CommandType = CommandType.Text

        Dim dr As OracleDataReader = cmd.ExecuteReader()
        dr.Read()
        If IsDBNull(dr.Item(0)) Then
            conn.Close()
            Return 0
        Else
            Return dr.Item("MaxId")
        End If
    End Function

    Private Function clearForm() As Integer
        'clear all fields
        txtHomeNo.Text = ""
        txtMobileNo.Text = ""
        txtname1.Text = ""
        txtsurname.Text = ""
        txtTown.Text = ""
        txtCounty.Text = ""
        txtAddress.Text = ""
        Return 0
    End Function
End Class

Any way don't advise me on alternatives such as asking lecturer coz she is on holiday at this stage or taking other transport into the college or finding other downloads etc... coz it might spark up an argument.

This is the code i got on my hard-drive but i could have been working off a copy on my college hard-drive.

[modi123_1]

Your insert needs to specify the columns you are inserting into and the values must match that same order.

insert into <table name> (<column_name1>, <column_name2>, <column_name3>)
values ('a', 1, 'abc123')

http://msdn.microsof...y/ms174335.aspx


PS: Don't advise me on what alternatives I can or cannot suggest to you!

This post has been edited by modi123_1: 30 July 2011 - 07:33 AM

[Kairi]

modi123_1, on 30 July 2011 - 08:32 AM, said:

Your insert needs to specify the columns you are inserting into and the values must match that same order.

insert into <table name> (<column_name1>, <column_name2>, <column_name3>)
values ('a', 1, 'abc123')

http://msdn.microsof...y/ms174335.aspx


PS: Don't advise me on what alternatives I can or cannot suggest to you!

Ah so will it be something like this?

	        strSQL = "INSERT INTO Members (ID,Forename,Surname,Street,TownCity,County,
                                               HomeNo,MobileNo,Gender,Fees) VALUES ('" + txtMemNo.Text + "','" + _
	                                txtname1.Text + "','" + txtsurname.Text + _
	                                "','" + txtAddress.Text + "','" + txtTown.Text + _
	                                "','" + txtCounty.Text + "'," + txtHomeNo.Text + _
	                                "," + txtMobileNo.Text + ",'" + gender.ToString() + "', 0)"

Right?

Coz i did that last time and for some reason it was causing me trouble and in the lecturer notes she had left out the column names before values.
Hmm... well i guess no harm in trying that since it could work. Not sure if it is right.
Naa don't need alternatives just mentioning it coz last time people had advised me on alternatives and it messed up my entire project.

BTW thanks for the link. it is confusing but might help alot to see where i went wrong

This post has been edited by Kairi: 30 July 2011 - 03:02 PM

[CharlieMay]

If ID is an autonumber field you won't need to specify it in the statement. If it's not and is numeric, you don't surround it with ' as that denotes text.

strSQL = "INSERT INTO Members (Forename,Surname,Street,TownCity,County,HomeNo, MobileNo,Gender,Fees)
                        VALUES ('" + _
                        txtname1.Text + "','" + txtsurname.Text + _
                        "','" + txtAddress.Text + "','" + txtTown.Text + _
                        "','" + txtCounty.Text + "'," + txtHomeNo.Text + _
                        "," + txtMobileNo.Text + ",'" + gender.ToString() + "', 0)"

[Kairi]

Ok I keep getting NullReferenceException unhandled in terms of this part of code... See below

   strSQL = "INSERT INTO Members VALUES ('" + txtMemNo.Text + "','" + _
                                txtname1.Text + "','" + txtsurname.Text + _
                                "','" + txtAddress.Text + "','" + txtTown.Text + _
                                "','" + txtCounty.Text + "'," + txtHomeNo.Text + _
                                "," + txtMobileNo.Text + ",'" + gender.ToString() + "', 0)"

It makes no difference whether or not i got this bit of code -->

(Forename,Surname,Street,TownCity,County,HomeNo, MobileNo,Gender,Fees)

in or not. I'm pretty sure perhaps a try and catch statement might be needed but will i need to do that to correct the error and can someone help me on how i can go about doing this.

[JackOfAllTrades]

Is there any reason you can't put a breakpoint on the offending line and run the project in the debugger, so you can see exactly what variable is null?

[CharlieMay]

You have 8 fields listed for 9 10 values.

This post has been edited by CharlieMay: 02 August 2011 - 05:37 AM

[Kairi]

JackOfAllTrades, on 02 August 2011 - 06:31 AM, said:

Is there any reason you can't put a breakpoint on the offending line and run the project in the debugger, so you can see exactly what variable is null?

Tried that and it doesn't seem to show anything except the home and mobile numbers but i have that as numeric in my SQL script

CharlieMay, on 02 August 2011 - 06:32 AM, said:

You have 8 fields listed for 9 values.

Really?

Can you perhaps point out which value i got in that is extra. Boy oh boy i'm so confused and stressed out

This post has been edited by Kairi: 02 August 2011 - 05:39 AM

[Ionut]

The names of the columns has to be specified before the keyword VALUES when the number of columns in which you want to insert doesn't equal table's number of columns. Identity columns are ignored.
Example (MSSQL code)

CREATE TABLE myTable(
myTableId INT PRIMARY KEY IDENTITY,
MyFirstColumn VARCHAR(20),
MySecondColumn VARCHAR(20),
myThirdColumn DATETIME,
SomeOtherColumn BIT,
myFinalColumn double
)
GO

Now, to make an insert

Insert INTO myTable (myFirstColumn, MySecondColumn, myThirdColumn,SomeOtherColumn, myFinalColumn)
VALUES ('insert1', 'insert2', 'insert3', 1, 2.5)

it is the same thing with

Insert INTO myTable VALUES ('insert1', 'insert2', 'insert3', 1, 2.5) 

But, if you want to make an insert only in 2 columns, you have to put the names of the columns. Also, You have to specify the name of the columns if change the order of the columns

Insert INTO myTable (myFinalColumn, MySecondColumn, myThirdColumn, myFirstColumn, SomeOtherColumn)
VALUES (2.5, 'insert2', 'insert3', 'insert1', 1)

otherwise, database engine will raise an error if you try to insert an incorrect value(a varchar into a double column, for example).

To your problem. NullReferenceException appears when you try to use an object that hasn't been initialized.
Looking at your code, it is something wrong. strSQL and cmd are global variables? Where have you insitialized cmd? I see something in the LastMemoNo, but the scope of the variable is till the end of the method. I would suggest to rewrite the method. Just put all the variables locally

Dim strSql as String = "insert statement here"
Dim cmd as New ORacleCommand 

'the rest of the code

This post has been edited by Ionut: 02 August 2011 - 05:45 AM

[CharlieMay]

What is txtMemNo. I don't see a field in the list you showed that accounts for it. Is it an AUTO ID field in your database?

[Kairi]

Ionut, on 02 August 2011 - 06:42 AM, said:

The names of the columns has to be specified before the keyword VALUES when the number of columns in which you want to insert doesn't equal table's number of columns. Identity columns are ignored.
Example (MSSQL code)

CREATE TABLE myTable(
myTableId INT PRIMARY KEY IDENTITY,
MyFirstColumn VARCHAR(20),
MySecondColumn VARCHAR(20),
myThirdColumn DATETIME,
SomeOtherColumn BIT,
myFinalColumn double
)
GO

Now, to make an insert

Insert INTO myTable (myFirstColumn, MySecondColumn, myThirdColumn,SomeOtherColumn, myFinalColumn)
VALUES ('insert1', 'insert2', 'insert3', 1, 2.5)

it is the same thing with

Insert INTO myTable VALUES ('insert1', 'insert2', 'insert3', 1, 2.5) 

But, if you want to make an insert only in 2 columns, you have to put the names of the columns. Also, You have to specify the name of the columns if change the order of the columns

Insert INTO myTable (myFinalColumn, MySecondColumn, myThirdColumn, myFirstColumn, SomeOtherColumn)
VALUES (2.5, 'insert2', 'insert3', 'insert1', 1)

otherwise, database engine will raise an error if you try to insert an incorrect value(a varchar into a double column, for example).

To your problem. NullReferenceException appears when you try to use an object that hasn't been initialized.
Looking at your code, it is something wrong. strSQL and cmd are global variables? Where have you insitialized cmd? I see something in the LastMemoNo, but the scope of the variable is till the end of the method. I would suggest to rewrite the method. Just put all the variables locally

Dim strSql as String = "insert statement here"
Dim cmd as New ORacleCommand 

'the rest of the code

Errm I have already stated in a global

    Public strSQL As String
    Public rValue As Integer
    Public oraDb As String = "Data Source=Oracle;User Id=txxxxxx;Password=xxxxxx;"
    Public conn As New OracleConnection(oraDb)
    Public dr As OracleDataReader
    Public cmd As New OracleCommand
    Public gender As String
    Dim da As OracleDataAdapter = New OracleDataAdapter(strSQL, conn)
    Dim ds As DataSet = New DataSet

I'm just following an example my lecturer had given us.

CharlieMay, on 02 August 2011 - 06:42 AM, said:

What is txtMemNo. I don't see a field in the list you showed that accounts for it. Is it an AUTO ID field in your database?

The MemNo is a fixed number for each member when a new member is registered.

This post has been edited by Kairi: 02 August 2011 - 06:02 AM

[CharlieMay]

OK Kairi, here is the reason you're getting the nullreference exception.

You are using the checkchanged event of the checkbox to determine male or female.

What happens if you don't click the checkbox at least once? Checkchanged isn't fired so nothing is stored in gender.

A way to test this is to check the checkbox and then uncheck it and click your button. Viola!! right?

If you move the code in your checkchanged event to just before your SQL statement, it should work.

This post has been edited by CharlieMay: 02 August 2011 - 07:13 AM

[Kairi]

CharlieMay, on 02 August 2011 - 08:10 AM, said:

OK Kairi, here is the reason you're getting the nullreference exception.

You are using the checkchanged event of the checkbox to determine male or female.

What happens if you don't click the checkbox at least once? Checkchanged isn't fired so nothing is stored in gender.

A way to test this is to check the checkbox and then uncheck it and click your button. Viola!! right?

If you move the code in your checkchanged event to just before your SQL statement, it should work.

Let's see so it might be easier if i perhaps use checkboxes instead of radio options?

Well i am at home so i can't test the method but hopefully Friday i will try this and see if it works. If it does ya all getting a +1 on rep

[Kairi]

Sorry for double post but i'm in the college at the moment and it doesn't allow the edit post option to show.

Well I run the program and for some reason it shows this error

Quote

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

I've done a help for further information but it shows i might need a if statement but i am unsure where it's needed.

The error occurs in the Global

    Public Sub OpenDB(ByVal X As OracleConnection)
        X.Open()
    End Sub

[Kairi]

Hey, back again and sorry for triple post. No-one has posted in a while and well i got yet another problem. What is worse is now my lecturer's own samples don't work and it's coz of similiar reasons.

Any way the problem occurs in

rValue = cmd.ExecuteNonQuery()

line but i am unsure what is wrong even when i had debugged the program.

Can someone help me out on that pretty please.