Unable to save data to sql using vb.net

Question

I am trying to save data to an mdf file which has been detached from SQL server and added to my APP_DATA folder in Solution Explorer, but I am unable do that. Your help will be appreciated. Thank you! Here is my code:

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand

    Dim EmployeeNo As Integer
    Dim EmployeeName As String
    Dim SupervisorName As String
    Dim DateCreated As Date
    Dim WeekRange As String
    Dim MonthRange As String
    Dim ScheduleIn As String
    Dim ScheduleOut As String
    Dim WorkStatus As String

    EmployeeNo = EmpNoText.Text
    EmployeeName = EmpNameText.Text
    SupervisorName = TeamLeadDropDown.Text
    DateCreated = DateCreatedTextBox.Text
    WeekRange = WeekRangeTextBox.Text
    MonthRange = MonthDropDown.Text
    ScheduleIn = ScheduleInBox.Text
    ScheduleOut = ScheduleOutBox.Text
    WorkStatus = StatusDropDown.Text


    Try
        con.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\JIBKPI.mdf;Initial Catalog=JIBKPI;Integrated Security=True"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "INSERT INTO AgentAttendance ([EmployeeNo], [EmployeeName], [SupervisorName], [DateCreated], [WeekRange], [MonthRange], [ScheduleIn], [ScheduleOut], [WorkStatus]) VALUES (@EmployeeNo, @EmployeeName, @SupervisorName, @DateCreated, @WeekRange, @MonthRange, @ScheduleIn, @ScheduleOut, @WorkStatus,)"
        cmd.ExecuteNonQuery()
        MsgBox("Successfuly saved!", MsgBoxStyle.Information + MsgBoxStyle.OkOnly)
    Catch ex As Exception
        MsgBox("Error: Unable to save data.")
    Finally
        con.Close()
    End Try

In my web.config the connection string is:

<connectionStrings>
<add name="JIBKPIConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=&quot;C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\JIBKPI.mdf&quot;;Initial Catalog=JIBKPI;Integrated Security=True" providerName="System.Data.SqlClient"/>

Hi Garath! It has no error, it just doesn't save the data I entered to the textboxes. — Gilbert Jacob
– Gilbert Jacob, Commented Aug 5, 2013 at 12:17
@SecretSquirrel I just want to clarify what you mean about not using the web.config setting, do I need to copy the exact connection string from web.config and use it on my code to save data? — Gilbert Jacob
– Gilbert Jacob, Commented Aug 5, 2013 at 12:19
I was just looking back through your example again and alot of it doesn't seem to make sense... how are you setting the @ param values?? if you are doing direct SQL shouldn't you just break up the query string with the values extracted from the textbox? VALUES (" + EmployeeNo + "," + EmployeeName + — Squirrel5853
– Squirrel5853, Commented Aug 5, 2013 at 12:29

Riv · Accepted Answer · 2013-08-05 12:33:34Z

Looking at your code, first thing I notice is that you're using parameters in your query:

cmd.CommandText = "INSERT INTO AgentAttendance ([EmployeeNo], [EmployeeName], [SupervisorName], [DateCreated], [WeekRange], [MonthRange], [ScheduleIn], [ScheduleOut], [WorkStatus]) VALUES (@EmployeeNo, @EmployeeName, @SupervisorName, @DateCreated, @WeekRange, @MonthRange, @ScheduleIn, @ScheduleOut, @WorkStatus,)"

But nowhere have you actually set those parameters. So before you execute your query you need to create those parameters. Something like:

cmd.Parameters.Add("@EmployeeNo", SqlDbType.Int)
command.Parameters("@EmployeeNo").Value = EmployeeNo
etc...

Then execute your query:

cmd.ExecuteNonQuery()

Also, if you're going to use the connection string in your web.config, remove both the &quot from the AttachDbFilename

Change this:

AttachDbFilename=&quot;C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\JIBKPI.mdf&quot;

to this:

AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\JIBKPI.mdf

EDIT: remove the last comma from your query after @WorkStatus

 @WorkStatus,)"

becomes

 @WorkStatus)"

Collectives™ on Stack Overflow

Unable to save data to sql using vb.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related