Visual Studo (VB.net) inserting datetime in database - must declare the scalar variable

Question

I tried to insert datatime in MS SQL. With varchar or int I don't have any problems. Here is the code. I am new to VB.net... Variable is called ''godina_zaposlenja''.

Public Class Zaposleni_Insert_Update Private conn As SqlConnection Private uCom As New SqlCommand Private iCom As New SqlCommand Private id As String

Public Sub New(ByVal p_conn As SqlConnection)
    MyBase.New()
    conn = p_conn
    id = ""
    'This call is required by the Windows Form Designer.
    InitializeComponent()
    InitInsertCommand()
    'InitCombo() Ovo je onaj kombo box za Gradove Acove
    'Add any initialization after the InitializeComponent() call

End Sub

Public Sub New(ByVal p_conn As SqlConnection, ByVal JMBG As String)
    MyBase.New()
    conn = p_conn
    id = JMBG
    'This call is required by the Windows Form Designer.
    InitializeComponent()
    InitUpdateCommand()
    InitCombo()
    InitFields()
    'Add any initialization after the InitializeComponent() call
End Sub

Private Sub InitUpdateCommand()

    Try
        uCom.Connection = conn
        uCom.CommandText = "UPDATE Zaposleni SET prezime = @prezime, ime = @ime, adresa=@adresa, broj_telefona=@broj_telefona, plata=@plata, godina_zaposlenja=@godna_zaposlenja WHERE JMBG = @JMBG"
        uCom.Parameters.Add(New SqlParameter("@JMBG", System.Data.SqlDbType.BigInt, 13, "JMBG"))
        uCom.Parameters.Add(New SqlParameter("@ime", System.Data.SqlDbType.VarChar, 50, "ime"))
        uCom.Parameters.Add(New SqlParameter("@prezime", System.Data.SqlDbType.VarChar, 50, "prezime"))
        uCom.Parameters.Add(New SqlParameter("@adresa", System.Data.SqlDbType.VarChar, 50, "adresa"))
        uCom.Parameters.Add(New SqlParameter("@broj_telefona", System.Data.SqlDbType.VarChar, 50, "broj_telefona"))
        uCom.Parameters.Add(New SqlParameter("@plata", System.Data.SqlDbType.Money, 50, "plata"))
        uCom.Parameters.Add(New SqlParameter("@godina_zaposlenja", System.Data.SqlDbType.DateTime, 7, "godina_zaposlenja"))
    Catch ex As Exception

    End Try
End Sub

Private Sub InitInsertCommand()

    Try
        iCom.Connection = conn
        iCom.CommandText = "INSERT INTO Zaposleni(JMBG, ime, prezime, adresa, broj_telefona, plata, godina_zaposlenja) VALUES (@JMBG, @ime, @prezime, @adresa, @broj_telefona, @plata, @godina_zaposlenja)"
        iCom.Parameters.Add(New SqlParameter("@JMBG", System.Data.SqlDbType.VarChar, 13, "JMBG"))
        iCom.Parameters.Add(New SqlParameter("@ime", System.Data.SqlDbType.VarChar, 50, "ime"))
        iCom.Parameters.Add(New SqlParameter("@prezime", System.Data.SqlDbType.VarChar, 50, "prezime"))
        iCom.Parameters.Add(New SqlParameter("@adresa", System.Data.SqlDbType.VarChar, 50, "adresa"))
        iCom.Parameters.Add(New SqlParameter("@broj_telefona", System.Data.SqlDbType.VarChar, 50, "broj_telefona"))
        iCom.Parameters.Add(New SqlParameter("@plata", System.Data.SqlDbType.Money, 50, "plata"))
        iCom.Parameters.Add(New SqlParameter("@godina_zaposlenja", System.Data.SqlDbType.DateTime, DateTime, "godina_zaposlenja")).ToString()
    Catch ex As Exception

    End Try
End Sub

Public Sub InitCombo()
    Try
        Dim command As New SqlCommand("select JMBG, ime from Zaposleni", conn)
        Dim adapter As New SqlDataAdapter(command)

        Dim table As New DataTable
        adapter.Fill(table)

        Me.JMBG.DataSource = table
        Me.JMBG.ValueMember = "JMBG"
        Me.JMBG.DisplayMember = "JMBG"

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Public Sub InitFields()
    Try
        Dim command As New SqlCommand("select * from zaposleni where JMBG =" & id, conn)
        Dim adapter As New SqlDataAdapter(command)

        Dim dataReader As SqlDataReader = command.ExecuteReader()

        If dataReader.Read Then
            Me.imeTxt.Text = dataReader("ime")
            Me.prezimeTxt.Text = dataReader("prezime")
            Me.JMBG.Text = dataReader("JMBG")
            Me.adresa.Text = dataReader("adresa")
            Me.plata.Text = dataReader("plata")
            Me.br_tel.Text = dataReader("broj_telefona")
            Me.datum.Text = dataReader("godina_zaposlenja").ToString
        End If

        dataReader.Close()

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Public Sub Save()

    If id = "" Then
        Try
            iCom.Parameters("@ime").Value = Me.imeTxt.Text
            iCom.Parameters("@prezime").Value = Me.prezimeTxt.Text
            iCom.Parameters("@JMBG").Value = Me.JMBG.Text
            iCom.Parameters("@adresa").Value = Me.adresa.Text
            iCom.Parameters("@plata").Value = Me.plata.Text
            iCom.Parameters("@broj_telefona").Value = Me.br_tel.Text
            iCom.Parameters("@godina_zaposlenja").Value = Me.datum.Text
            iCom.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    Else
        Try
            uCom.Parameters("@ime").Value = Me.imeTxt.Text
            uCom.Parameters("@prezime").Value = Me.prezimeTxt.Text
            uCom.Parameters("@JMBG").Value = Me.JMBG.Text
            'uCom.Parameters("@id").Value = id

            uCom.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End If

    Me.Close()

End Sub

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

    Save()

End Sub

End Class

End here is my ''first form''. I work with DataGrids... Public Class Form1

Dim conStr As String = _
    "packet size=4096;integrated security=SSPI;" & _
    "data source=DESKTOP-T2LP2GR\SQLEXPRESS;" & _
    "persist security info=false;" & _
    "initial catalog=Aerodrom"

Dim conn As New SqlConnection(conStr)

Private adapter As New SqlDataAdapter
Private table As New DataTable
Private dataSet As New DataSet
Private command As New SqlCommand
Private cmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(adapter)
Private iCom As New SqlCommand
Private uCom As New SqlCommand
Private dCom As New SqlCommand
Private JMBG As String
Friend WithEvents ViewRecords As DataView

Public Sub New()

    ' This call is required by the Windows Form Designer.
    InitializeComponent()

    ' Add any initialization after the InitializeComponent() call.

    Me.Open()

End Sub

Public Sub Open()
    Try
        Me.conn.Open()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub
''Ucitavnaje iz baze:
Private Sub InitGrid()

    Dim str As String = "SELECT * FROM zaposleni"

    dataSet.Clear()


    command.Connection = conn
    command.CommandText = str

    adapter.SelectCommand = command

    adapter.Fill(dataSet)

    ViewRecords = New DataView(dataSet.Tables(0))
    ViewRecords.AllowEdit = False

    zaposleniGrid.DataSource = ViewRecords


End Sub

Private Sub UpdateByDataSet()
    Try

        ''dataSet.AcceptChanges()
        adapter.UpdateCommand = cmdBuilder.GetUpdateCommand()
        adapter.InsertCommand = cmdBuilder.GetInsertCommand()
        adapter.DeleteCommand = cmdBuilder.GetDeleteCommand()

        adapter.Update(dataSet)
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Private Sub UpdateManualy()

    Dim table As DataTable = dataSet.Tables(0)
    Dim i As Integer
    Dim row As DataRow
    Dim len As Integer = table.Rows.Count
    Dim strSql As String
    Dim command As New SqlCommand

    command.Connection = conn
    command.CommandType = CommandType.Text

    For i = 0 To len - 1

        row = table.Rows(i)

        Select Case row.RowState
            Case DataRowState.Deleted

                Try
                    strSql = "delete from zaposleni where JMBG=" & row("JMBG", DataRowVersion.Original)
                    command.CommandText = strSql
                    command.ExecuteNonQuery()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try

        End Select
    Next
End Sub



Private Sub UcitajToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UcitajToolStripMenuItem.Click

    InitGrid()

End Sub

Private Sub SacuvajToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SacuvajToolStripMenuItem.Click

    UpdateManualy()

End Sub

Private Sub NoviToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NoviToolStripMenuItem.Click
    Dim frm As Zaposleni_Insert_Update
    frm = New Zaposleni_Insert_Update(Me.conn)
    frm.ShowDialog()
    Me.InitGrid()

End Sub
Private Sub PromijeniToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PromijeniToolStripMenuItem.Click

    Dim i As Integer
    i = Me.zaposleniGrid.CurrentCell.RowIndex
    Dim id As String = Me.dataSet.Tables(0).Rows(i)("JMBG")
    Dim frm As Zaposleni_Insert_Update
    frm = New Zaposleni_Insert_Update(Me.conn, id)
    frm.ShowDialog()
    Me.InitGrid()

End Sub

Private Sub filterIme_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles filterIme.TextChanged
    ViewRecords.RowFilter = "[IME] " & "LIKE '%" & filterIme.Text & "%'"
End Sub

Private Sub ConvertToCombo()
    Try

       Dim cCol As New DataGridViewComboBoxColumn

        Dim adapter As New SqlDataAdapter("select oznaka, ime from grad", Me.conn)
        Dim comboTable As New DataTable
        adapter.Fill(comboTable)

        cCol.Name = "oznaka"
        cCol.DataSource = comboTable
        cCol.DisplayMember = "ime"
        cCol.ValueMember = "oznaka"
        cCol.DataPropertyName = "oznaka"
        ''Me.zaposleniGrid.Columns.Remove("oznaka")
        Me.zaposleniGrid.Columns.Add(cCol)
    Catch ex As Exception

    End Try
End Sub

Private Sub PromijeniUKomboToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    ConvertToCombo()

End Sub


Private Sub zaposleniGrid_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles zaposleniGrid.CellContentClick

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSacuvaj.Click

    Dim index As Integer = Me.zaposleniGrid.CurrentCell.RowIndex

    If index < 0 Then
        Return
    End If

    Dim row As DataRow = dataSet.Tables(0).Rows(index)


    'Dim uCom As New SqlCommand("update zaposleni set slika = @slika where JMBG=@JMBG", Me.conn)

    'uCom.Parameters.Add(New SqlParameter("@JMBG", System.Data.SqlDbType.BigInt, 50))
    'uCom.Parameters.Add(New SqlParameter("@slika", System.Data.SqlDbType.Image))

    'Dim ms As New MemoryStream()
    'PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
    'Dim arrImage() As Byte = ms.GetBuffer

    'uCom.Parameters("@JMBG").Value = row("JMBG")  'veli nece da brise ???

    'uCom.Parameters("@slika").Value = arrImage

    'uCom.ExecuteNonQuery()
    UpdateManualy()
    InitGrid()
    'UpdateByComannds()
End Sub

Private Sub zaposleniGrid_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles zaposleniGrid.CurrentCellChanged

    If zaposleniGrid Is Nothing Or Me.zaposleniGrid.CurrentCell Is Nothing Then
        Return
    End If

    Dim index As Integer = Me.zaposleniGrid.CurrentCell.RowIndex

    If index < 0 Then
        Return
    End If

    'Dim row As DataRow = dataSet.Tables(0).Rows(index)
    'zasto puca ovamo iznad???


    'If IsDBNull(row("slika")) Then
    ' Return
    ' End If

    'Dim arrPicture() As Byte = CType(row("slika"), Byte())
    'Dim ms As New MemoryStream(arrPicture)

    'Me.PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
    'Me.PictureBox1.Image = Image.FromStream(ms)
    'Me.PictureBox1.Refresh()
End Sub

End Class

Answer 1

Well, It was all my funny mistake. I have tried to pass String in uCom.Parameters.Add(New SqlParameter("@godina_zaposlenja", System.Data.SqlDbType.DateTime, 7, "godina_zaposlenja"))

and

iCom.Parameters.Add(New SqlParameter("@godina_zaposlenja", System.Data.SqlDbType.DateTime, 7, "godina_zaposlenja"))

... :)

Answer 2

Your variable name doesn't match the variable name in the query. In your UPDATE query your variable is @godna_zaposlenja wheras when adding the parameter you use @godina_zaposlenja

uCom.CommandText = "UPDATE Zaposleni SET prezime = @prezime, ime = @ime, adresa=@adresa, broj_telefona=@broj_telefona, plata=@plata, godina_zaposlenja=@godna_zaposlenja WHERE JMBG = @JMBG"

uCom.Parameters.Add(New SqlParameter("@godina_zaposlenja", System.Data.SqlDbType.DateTime, 7, "godina_zaposlenja")) Catch ex As Exception