Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can't Insert records into a MS SQL SERVER IN VB.NET

Posted on 2014-08-27
3
Medium Priority
?
384 Views
Last Modified: 2014-08-27
i HAVE A WINFORM I'm trying to get my code to insert textboxes into a MS SQL SERVER.

i GET THIS AS AN ERROR
ExecuteNonQuery: CommandText property has not been initialized   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SDDSPlayers.frmPlayerupdate.Insertdata() in C:\Users\tpowers\Desktop\sdds .net\SDDSPlayers\SDDSPlayers\frmPlayerupdate.vb:line 52System.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized
   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SDDSPlayers.frmPlayerupdate.Insertdata() in C:\Users\tpowers\Desktop\sdds .net\SDDSPlayers\SDDSPlayers\frmPlayerupdate.vb:line 52

Open in new window



Using this in my winform
 Public Sub Insertdata()

        Dim strFirstName As String = CStr(txtFname.Text)
        Dim strLastname As String = CStr(txtLname.Text)
        Dim strTeamID As String = CStr(txtTeamID.Text)
        Dim strJersey As String = CStr(txtJersey.Text)
        Dim strPosition As String = CStr(cmbPosition.Text)
        Dim strStatus As String = CStr(cmbStatus.Text)
        Dim strHeight As String = CStr(txtHeight.Text)
        Dim strWeight As String = CStr(txtWeight.Text)
        Dim strBorn As String = CStr(dtp1.Text)
        Dim strCollege As String = CStr(txtCollege.Text)
        Dim strBioHTML As String = CStr(txtPlayerNote.Text)
        Dim query As String = String.Empty
        Using conn As New SqlConnection("Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=xxxxxxxxx")
            
                Using comm As New SqlCommand()
                    With comm
                        .Connection = conn
                        .CommandType = CommandType.Text
                        .CommandText = query

                    query &= "INSERT INTO NFLPlayers (FirstName, Lastname,"
                    query &= "TeamID,Jersey,Position,Status,Height,Weight,Born,College,BioHTML"
                    query &= "VALUES (@FirstName,@Lastname,@TeamID,@Jersey,@Position,@Status,@Height,@Born,@College,@BioHTML)"
                    .Parameters.AddWithValue("@FirstName", strFirstName)
                    .Parameters.AddWithValue("@Lastname", strLastname)
                    .Parameters.AddWithValue("@TeamID", strTeamID)
                    .Parameters.AddWithValue("@Jersey", strJersey)
                    .Parameters.AddWithValue("@Position", strPosition)
                    .Parameters.AddWithValue("@Status", strStatus)
                    .Parameters.AddWithValue("@Height", strHeight)
                    .Parameters.AddWithValue("@Weight", strWeight)
                    .Parameters.AddWithValue("@Born", strBorn)
                    .Parameters.AddWithValue("@College", strCollege)
                    .Parameters.AddWithValue("@BioHTML", strBioHTML)

                End With
                Try
                    conn.Open()
                    comm.ExecuteNonQuery()
                Catch ex As Exception
                    txtPlayerNote.Text = ex.Message & ex.StackTrace & ex.ToString
                End Try
                conn.Close()
            End Using
        End Using
    End Sub

Open in new window

0
Comment
Question by:powerztom
  • 2
3 Comments
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 40289036
Hi powerztom;

Change the order of the CommandText property till after the query is built as shown below.

query &= "INSERT INTO NFLPlayers (FirstName, Lastname,"
query &= "TeamID,Jersey,Position,Status,Height,Weight,Born,College,BioHTML"
query &= "VALUES (@FirstName,@Lastname,@TeamID,@Jersey,@Position,@Status,@Height,@Born,@College,@BioHTML)"
.CommandText = query

Open in new window

0
 

Author Closing Comment

by:powerztom
ID: 40289088
Good Eye man Good Eye! Very Happy you spotted that
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 40289509
Glad I was able to help out powerztom.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question