Problems Inserting Char() Type into MySql Table Structure

please help to examining the following code snippet :

.......................................................................
.......................................................................
.......................................................................

the compiler highlight the exception caused syntax If (mycommand.ExecuteNonQuery() > 0) Then bSaved = True
with the message "MySqlException was Unhandled; Column "id_cust" cannot be null"

please help to be patiently delve deeper the following code:
__________________________________________________________________________________
VB CLASS : MyProspek.vb*

Imports MySql.Data
Imports MySql.Data.MySqlClient

Public Class MyProspek

Inherits System.Collections.Hashtable

Private fKey As String() = {"id_cust", "nama", "kota", "tgl_lahir", "telpon", "hobbies", "titel", _ "pekerjaan","jkelamin", "kawin", "opini", "maksud"}

Public Sub New()

End Sub

Public Sub New(ByVal prospList As Hashtable)
For j As Integer = 0 To prospList.Count - 1
Add(fKey(j), prospList.Item(fKey(j)))
Next
End Sub

Public Function Append(Optional ByVal ListProsp As Hashtable = Nothing) As Integer
If (ListProsp Is Nothing) Then
Return 0
Else
For j As Integer = 0 To ListProsp.Count - 1
Add(fKey(j), ListProsp.Item(fKey(j)))
Next
End If
Return Count
End Function

Public Function Save() As Boolean
Dim bSaved As Boolean = False
Dim myconn As MySqlConnection = Nothing
Dim mycommand As MySqlCommand = Nothing
Dim strSQL As String = Nothing
Dim n As Integer = 0
Dim connsql As String = "server=localhost; user id=root; password=; Database=Test1; pooling=false"

myconn = New MySqlConnection(connsql)
myconn.Open()

For n = 0 To (Count / Count) - 1
strSQL = "insert into tprospek(id_cust,nama,kota,tgl_lahir,telpon,hobbies,titel,pekerjaan,jkelamin,kawin,opini,maksud)" + _
" values(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11)"

mycommand = New MySqlCommand(strSQL, myconn)

mycommand.Parameters.AddWithValue("@p0", Item(n))
mycommand.Parameters.AddWithValue("@p1", Item(n + 1))
mycommand.Parameters.AddWithValue("@p2", Item(n + 2))
mycommand.Parameters.AddWithValue("@p3", Item(n + 3))
mycommand.Parameters.AddWithValue("@p4", Item(n + 4))
mycommand.Parameters.AddWithValue("@p5", Item(n + 5))
mycommand.Parameters.AddWithValue("@p6", Item(n + 6))
mycommand.Parameters.AddWithValue("@p7", Item(n + 7))
mycommand.Parameters.AddWithValue("@p8", Item(n + 8))
mycommand.Parameters.AddWithValue("@p9", Item(n + 9))
mycommand.Parameters.AddWithValue("@p10", Item(n + 10))
mycommand.Parameters.AddWithValue("@p11", Item(n + 11))

MySqlException was Unhandled: 'Column "id_cust" cannot be null
If (mycommand.ExecuteNonQuery() > 0) Then bSaved = True
Next
Return bSaved
End Function

End Class

The Consumer Class in Form1.vb*

Public Class Form1

Private Sub BttnSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BttnSimpan.Click

Dim mylist As New Hashtable()

Dim mypelanggan As MyProspek = Nothing

Dim str As String = IDText1.Text.Trim

Dim lchr As Char() = str.ToCharArray()

mylist.Add("id_cust", lchr)

mylist.Add("nama", txtNama.Text.Trim)

mylist.Add("kota", txtTempat.Text.Trim)

mylist.Add("tgl_lahir", DTPicker1.Value)

mylist.Add("telpon", txtTelpon.Text.Trim)

mylist.Add("hobbies", txtHobbies.Text.Trim)

mylist.Add("title", cboGelar.Text.Trim)

mylist.Add("pekerjaan", txtPekerjaan.Text.Trim)

mylist.Add("jkelamin", If((rdoPria.Checked), "1", "0"))

mylist.Add("kawin", If((rdoYa.Checked), "1", "0"))

mylist.Add("opini", txtOpini.Text.Trim)

mylist.Add("maksud", txtMaksud.Text.Trim)

mypelanggan = New MyProspek()

mypelanggan.Append(mylist)

If Not (mypelanggan.Save()) Then MessageBox.Show("Gagal Backup SystemData ..!", "Ada Kesalahan", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Sub

Oooohhh ... finally I realized that it's all my fault, I slipped to include the argument fKey(n) in the mycommand.Parameters.AddWithValue("@p0" ,Item(n)) so it should be the following :

mycommand.Parameters.AddWithValue("@p0", Item(fKey(n)))
mycommand.Parameters.AddWithValue("@p1", Item(fKey(n + 1)))
mycommand.Parameters.AddWithValue("@p2", Item(fKey(n + 2)))
mycommand.Parameters.AddWithValue("@p3", Item(fKey(n + 3)))
...
etc.

Also in the Form1.ButtonClick procedure the char() type lchr must be cloned to perform a shallow copy of it contents to pass it to the item collection's storage of the Hashtable.
.......................

Private Sub BttnSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BttnSimpan.Click
Dim mylist As New Hashtable()
Dim mypelanggan As MyProspek = Nothing
Dim str As String = New String(IDText1.Text.Trim)
Dim lchr As Char() = New Char() {}

lchr = str.ToCharArray

mylist.Add("id_cust", lchr.Clone)
....etc.

I Say Very Sorry If had bothered or wasted your valuable time.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
No problem, Eddy.

I'm pleased you've managed to figure it out :)
Eddy Wu14y ago
Oooohhh ... finally I realized that it's all my fault, I slipped to include the argument fKey(n) in the mycommand.Parameters.AddWithValue("@p0" ,Item(n)) so it should be the following :

mycommand.Parameters.AddWithValue("@p0", Item(fKey(n)))
mycommand.Parameters.AddWithValue("@p1", Item(fKey(n + 1)))
mycommand.Parameters.AddWithValue("@p2", Item(fKey(n + 2)))
mycommand.Parameters.AddWithValue("@p3", Item(fKey(n + 3)))
...
etc.

Also in the Form1.ButtonClick procedure the char() type lchr must be cloned to perform a shallow copy of it contents to pass it to the item collection's storage of the Hashtable.
.......................

Private Sub BttnSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BttnSimpan.Click
Dim mylist As New Hashtable()
Dim mypelanggan As MyProspek = Nothing
Dim str As String = New String(IDText1.Text.Trim)
Dim lchr As Char() = New Char() {}

lchr = str.ToCharArray

mylist.Add("id_cust", lchr.Clone)
....etc.

I Say Very Sorry If had bothered or wasted your valuable time.
Eddy Wu14y ago
of course Item(0) of HashTable is populated from user entered textbox when the program run, I had entered value 'CST0000001' or any else in the textbox named IDText1 but still it couldn't pass any usable value instead null over and over, wasn't it looks strange ??, did you've ever met these symptons before??, do you have any better suggestion ??
Vulpes14y ago
Well, it looks like Item(0) is either Nothing or an empty string.

However, I can't tell from the code how the Item array or list is being populated in the first place.

If that particular column is being populated from the contents of a textbox, then I'd check that the textbox isn't blank i.e. it's Text property isn't an empty string.