Adding data using a textbox

[archaeofreak]

Here is my code:

Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing.Printing
Public Class frmAddProject
Private Sub frmAddProject_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
End Sub

Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
Dim cn As New SqlConnection
Dim cmd As New SqlCommand
Dim ProjectName As String
Dim NumAffected As Integer
ProjectName = txtProjectName.Text
Try
cn.ConnectionString = "Data Source=LENOVO-T60\SQLEXPRESS;Initial Catalog=ArchDatabase;Integrated Security=True"
cn.Open()
cmd.Connection = cn
cmd.CommandText = "INSERT INTO ProjectTable([Project Name]) VALUES(@ProjectName,)"
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show("Error while inserting record on table.")
Finally
MsgBox(NumAffected.ToString + " New Project Added")
cn.Close()
End Try
End Sub

Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
Close()
End Sub
End Class


I am just trying to simply add a new project to my project table. I have a textbox that the user types in the name of the project and a button to save the name to the table. The debugger runs fine, and the exception handler catches, displaying the error message and 0 new projects added. No data is written to the SQL table. The Do Not Copy setting is set already.

Help appreciated!

 

[jmcilhinney]

The first thing to do would be to change your Catch block to actually give you some information about the exception. You've got a reference to the exception, i.e. 'ex', so use it to see what happened, where and why. The simplest option to start with is to call ToString on the exception and display the result, which will include the error message and the stack trace, i.e. the list of methods that were executing when the exception occurred. You should always call Debug.WriteLine and pass that info in a Catch block so that you will always see the information about exceptions while debugging. What you show to the user and what you want to see as the developer when an exception occurs are often very different, so don't just view it as a user.

As for the issue, the first thing to do is to get rid of that spurious comma in your SQL code. Just as in grammer, commas are used to separate things. What are you separating in that code? Next, how exactly are you passing the value from the TextBox to the database? You aren't. You're telling it to insert the value of the @ProjectName parameter but you never set that value. Follow the Blog link in my signature and check out my post on Parameters In ADO.NET to learn how to do that.

 

[archaeofreak]

Thanks for replying. I modified the code but I am still getting an error "Must define the scalar variable @ProjectName" but this is spelled correctly and I am using sql server 2008...

Imports System
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing.Printing
Public Class formAddProject
Private Sub AddProject2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

End Sub

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

Dim con As New SqlConnection
Dim cmd As SqlCommand = con.CreateCommand
Dim NumAffected As Integer
Try
con.ConnectionString = "Data Source=LENOVO-T60\SQLEXPRESS;Initial Catalog=ArchDatabase;Integrated Security=True"
Dim sql As String = "INSERT INTO ProjectTable(ProjectID, ProjectName) " & "VALUES(@ProjectID, @ProjectName)"
con.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = sql

If String.IsNullOrEmpty(txtProjectID.Text) Then
MessageBox.Show("Error! Please enter a valid Project ID Number!")
ElseIf txtProjectID.Text > 0 Then
Dim dpProjectID As SqlParameter = cmd.CreateParameter
dpProjectID.ParameterName = "@ProjectID"
dpProjectID.Value = txtProjectID.Text
cmd.Parameters.Add(dpProjectID)
cmd.ExecuteNonQuery()

ElseIf String.IsNullOrEmpty(txtProjectName.Text) Then
MessageBox.Show("Error! Please enter a Project Name!")
ElseIf txtProjectName.TextLength > 0 Then
Dim dpProjectName As SqlParameter = cmd.CreateParameter
dpProjectName.ParameterName = "@ProjectName"
dpProjectName.Value = txtProjectName.Text
cmd.Parameters.Add(dpProjectName)
cmd.ExecuteNonQuery()
End If

Catch ex As Exception
Debug.WriteLine("Error: {0}", ex.ToString())
MessageBox.Show(ex.ToString)
MessageBox.Show("Error while inserting record on table.")
Finally
MsgBox(NumAffected.ToString + " New Project(s) Added")
con.Close()
End Try