Unable to insert record into sql server from vb.net wpf form

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

VB.NET

Dim cn As New SqlConnection
       Dim cmd As New SqlCommand

       Dim str As String


       cn.ConnectionString = "Data Source=PARTH-PC;Initial Catalog=db_hba;Integrated Security=True"
       Dim ca As Integer
 Try

            cn.Open()
 str = "insert into EmMaster(EmMaster_EmpCode,EmMaster_EmpName,EmMaster_EmDesig,"
            str += "EmMaster_EmpPort,EmMaster_EmpDivi,EmMaster_SubDivi,EmMaster_EmplDob,EmMaster_EmplDoj,EmMaster_EmplDor,"
            str += "EmMaster_Service,EmMaster_EmplMob,EmMaster_EmEmail,EmMaster_PaScale) values ('" + txtEmpCode.Text + "','" + txtName.Text + "','" + txtDesignation.Text + "','" + txtPort.Text + "','" + txtDivision.Text + "','" + txtSubDivision.Text + "','" + txtDOB.SelectedDate.ToString + "','" + txtDOJ.SelectedDate.ToString + "','" + txtDOR.SelectedDate.ToString + "','" + txtTotalService.Text + "','" + txtMobile.Text + "','" + txtEmail.Text + "','" + txtPayScale.Text + "')"

  cmd = New SqlCommand(str, cn)
            ca = cmd.ExecuteNonQuery

            MessageBox.Show(ca)

        Catch ex As Exception
            MessageBox.Show("could not insert record")
            cn.Close()
        End Try

Posted 27-Jan-16 21:04pm

Parth Soni

Add a Solution

Comments

Michael_Davies 28-Jan-16 3:16am

What is the exact error message, display the error using ex.Message.

Do not use a constructed string for your SQL statement, use a parameterised string and add the parameters and values, besides being difficult to spot errors in the string you are prone to SQL injection. For instance if _EmpName was O'Connor the single quote in the text in the texbox would close the SQL string and make the rest garbage and cause failure, you would not know as you do not look at the error message.

Use the debugger and examine the string.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-01-27T22:21:00

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
The chances are that that will fix your problem.

VB

str = "insert into EmMaster(EmMaster_EmpCode, EmMaster_EmpName, EmMaster_EmDesig, EmMaster_EmpPort, EmMaster_EmpDivi, EmMaster_SubDivi, EmMaster_EmplDob, EmMaster_EmplDoj, EmMaster_EmplDor, EmMaster_Service, EmMaster_EmplMob, EmMaster_EmEmail, EmMaster_PaScale) values (@COD, @NAM, @DES, @PRT, @DIV, @SDV, @DOB, @DOJ, @DOR, @SER, @MOB, @EML, @PAS)"
cmd = New SqlCommand(str, cn)
cmd.Parameters.AddWithValue("@COD", txtEmpCode.Text)
cmd.Parameters.AddWithValue("@NAM", txtName.Text)
cmd.Parameters.AddWithValue("@DES", txtDesignation.Text)
cmd.Parameters.AddWithValue("@PRT", txtPort.Text)
cmd.Parameters.AddWithValue("@DIV", txtDivision.Text)
cmd.Parameters.AddWithValue("@SDV", txtSubDivision.Text)
cmd.Parameters.AddWithValue("@DOB", txtDOB.SelectedDate)
cmd.Parameters.AddWithValue("@DOJ", txtDOJ.SelectedDate)
cmd.Parameters.AddWithValue("@DOR", txtDOR.SelectedDate)
cmd.Parameters.AddWithValue("@SER", txtTotalService.Text)
cmd.Parameters.AddWithValue("@MOB", txtMobile.Text)
cmd.Parameters.AddWithValue("@EML", txtEmail.Text)
cmd.Parameters.AddWithValue("@PAS", txtPayScale.Text)

But...do yourself a favour and dump the "EmMaster_Emp" prefix on all your columns - you don't need them and it just makes everything harder to read. And don't call everything "txt..." - particularly when it isn't a TextBox! That's just confusing.