Member Avatar for kikunosuke

first, i am sorry if i can't speak english well

i have problem where i can't insert data to data base mySQl using vb2005.
it is my simple code on vb.

----------------------------------

Imports MySql.Data
Imports MySql.Data.MySqlClient

Public Class Form1
    Dim xobjConnection As New MySqlConnection("Server=localhost;uid=root;pwd=;database=test1;")

    Dim xobjAdapter As New MySqlDataAdapter()
    Dim xobjDataSet As DataSet


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim nim As String
        Dim nama As String

        nim = Me.TextBox1.Text
        nama = Me.TextBox2.Text

        Dim xobjCommand As MySqlCommand = New MySqlCommand()

        xobjCommand.Connection = Me.xobjConnection
        xobjCommand.CommandText = "INSERT INTO apple " & _
        "( nama, nim ) " & _
        "VALUES ( @nama, @nim)"

        xobjCommand.Parameters.AddWithValue("@nim", nim)
        xobjCommand.Parameters.AddWithValue("@nama", nama)

        Me.xobjConnection.Open()
        Try
            xobjCommand.ExecuteNonQuery()
        Catch mySqlExceptionErr As MySqlException
            MessageBox.Show(mySqlExceptionErr.Message)
        End Try
        Me.xobjConnection.Close()

    End Sub
End Class
--------------------------------

the message of this error is "column nim cannot be null"

//  i use 2 input box, and 1 button.
//  use phptriad. 
//  database = test1
// database = apple -> table = apple
// database = apple -> table = apple -> field = nim (varchar) primary key
                                                      -> field = nama (varchar) not primary key

// i used the newst mySQL_ connector  
// used visualstudio2005

Please help me.
sorry, and thanks (n_n)

  • 3 Contributors
  • 4 Replies
  • 104 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by kikunosuke

Dani AI

Generated

A few focused troubleshooting steps and likely causes based on the thread and the suggestions from and @bhi.

The most common root cause in situations like this is that the command parameters are not actually being bound to the SQL text, so the server receives an SQL that still contains @nim/@nama. MySQL treats @name in SQL as a user variable; if that variable was never set the effect is inserting NULL. Confirm whether the .NET provider actually matched the parameter names you added to the CommandText exactly (prefix and spelling matter).

Quick checks (do these before ExecuteNonQuery):

  • Inspect the command's Parameters collection to confirm both parameters exist and hold the expected values. For example, loop the collection and show each ParameterName and Value to make sure names match the placeholders in CommandText.
  • Ensure you are adding parameters to the same MySqlCommand instance that you execute (not to another command or adapter).
  • If parameters exist but are Nothing, explicitly set DBNull.Value for true NULLs.

If parameters are missing or names differ:

  • Use identical names when you add parameters and in the SQL (including the prefix you used: @param vs ?param).
  • If a simple AddWithValue approach fails, create MySqlParameter explicitly with MySqlDbType and a length and add it to the command; this avoids type/size inference issues.

If parameters look fine and values are correct, enable logging on the server (or turn on the general query log) so you can see exactly what the server receives; that will show whether it got user variables instead of bound parameter values.

For reference on parameter rules and MySQL user variables see the Connector/NET parameters guide and MySQL user variables documentation:

Suggested immediate next action: enumerate the Parameters collection (ParameterName and Value) and paste that output — it will show whether a naming/prefix mismatch is the culprit.

Recommended Answers

All 4 Replies

Member Avatar for asnider

Try using the following:

If nim = vbnullstring Then
                xobjCommand.Parameters.AddWithValue("@nim", dBNull.Value)
            Else
                xobjCommand.Parameters.AddWithValue("@nim", nim)
            End If
Member Avatar for kikunosuke

hemm. it dosen't work..
the message error still same "column nim cannot be null"

...
i have try to check the 'nim' and 'nama'
//using
msgbox (nim) // and
msgbox (nama)

and put that syntax above
Me.xobjConnection.Open()

and the output of msgbox is same with what i have write on 'textbox'
so nim and nama wasn't null

Member Avatar for @bhi

The problem seems to be that the values are not being set. Try this alternate method its basically doing the same thing:

xobjCommand.Parameters.Add("@nim",varchar)
xobjCommand.Parameters.Add("@nama",varchar)

xobjCommand.Parameters("@nim").value=nim
xobjCommand.Parameters("@nama").value=nama
Member Avatar for kikunosuke

when i use that
there are message error
'Parameter '@nim' not found in the collection'

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.