• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

How do I use AddWithValue in Visual Basic?

This is what I have, but it keeps failing, I've added dummy data instead of variables to see if it is set up correctly but it fails everytime.  Unless I comment out the executenonquery

the table is defined as:

   an_id  int Not Null                
 , cl_id  int Not Null
 , an_name  varchar (35) Null
 , an_type  varchar (15) Not Null
 , an_sex  varchar (1) Not Null


Friend Params As String = "INSERT INTO vt_animals VALUES (an_id, cl_id, an_name,an_type,an_sex) VALUES (@an_id,@cl_id,@an_name,@an_type,@an_sex)"
 
. . . 
 
 
	Sub insertWithParams()
		Dim cmd As SqlCommand = New SqlCommand(Params, cnn)
		cmd.Parameters.AddWithValue("@an_id", 99999)
		cmd.Parameters.AddWithValue("@cl_id", 99999)
		cmd.Parameters.AddWithValue("@an_name", "Mike")
		'cmd.Parameters.AddWithValue("@an_type", "'lion'")
		cmd.Parameters.AddWithValue("@an_sex", "'M'")
 
		Try
			cnn.Open()
			WriteLine("Attempting to insert a new animal with entered values")
			cmd.ExecuteNonQuery()
 
 
		Catch ex As Exception
			WriteLine("Unable to insert with entered values")
		Finally
			cnn.Close()
		End Try
	End Sub

Open in new window

0
cflores89
Asked:
cflores89
  • 2
1 Solution
 
thiyagukCommented:
Try this :
Friend Params As String = "INSERT INTO vt_animals VALUES (an_id, cl_id, an_name,an_type,an_sex) VALUES (@an_id,@cl_id,@an_name,@an_type,@an_sex)"
 
. . . 
 
 
        Sub insertWithParams()
                Dim cmd As SqlCommand = New SqlCommand(Params, cnn)
                cmd.Parameters.AddWithValue("@an_id", 99999)
                cmd.Parameters.AddWithValue("@cl_id", 99999)
                cmd.Parameters.AddWithValue("@an_name", "Mike")
                cmd.Parameters.AddWithValue("@an_type", "lion")
                cmd.Parameters.AddWithValue("@an_sex", "M")
 
                Try
                        cnn.Open()
                        WriteLine("Attempting to insert a new animal with entered values")
                        cmd.ExecuteNonQuery()
 
 
                Catch ex As Exception
                        WriteLine("Unable to insert with entered values")
                Finally
                        cnn.Close()
                End Try
        End Sub

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
and what exception exactly do you get?
0
 
cflores89Author Commented:
Attempting to insert a new animal with entered values

Unhandled Exception: System.Data.SqlClient.SqlException: The name "an_id" is not
 permitted in this context. Valid expressions are constants, constant expression
s, and (in some contexts) variables. Column names are not permitted.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds
ParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Run
Behavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBe
havior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav
ior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult
 result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult res
ult, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at A05_Flores.A05_Flores.insertWithParams() in C:\Users\Carlos\Documents\Home
work\112D\A05_Flores\A05_Flores\A05_Flores.vb:line 102
   at A05_Flores.A05_Flores.Main() in C:\Users\Carlos\Documents\Homework\112D\A0
5_Flores\A05_Flores\A05_Flores.vb:line 13
Press any key to continue . . .
0
 
thiyagukCommented:
Please Rewrite the insert query as follows :

Remove the First 'Values' clause :
Friend Params As String = "INSERT INTO vt_animals (an_id, cl_id, an_name,an_type,an_sex) VALUES (@an_id,@cl_id,@an_name,@an_type,@an_sex)"

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now