asked on

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

thiyaguk

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

Éric Moreau

and what exception exactly do you get?

cflores89

ASKER

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 . . .

ASKER CERTIFIED SOLUTION

thiyaguk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial