SQL Insert Command

[tehon3299]

Hey -

I was just wondering how to use the SQL Insert Command. I have some code but I do not know how good it is. It doesnt seem to work so I was wondering what the problem was. Heres some of the code:

myConnection = New SqlConnection("server=(local);database=Pitch;Trusted_Connection=yes")

Dim InsertCmd As String = "insert into PitchRecord (Date, Name, Score, Win) values (TodayDate, team1, team1totpoints, 1)"

MyCommand = New SqlCommand(InsertCmd, myConnection)

MyCommand.Parameters.Add(New SqlParameter("TodayDate", SqlDbType.DateTime, 8))
MyCommand.Parameters("TodayDate").Value = Now()

MyCommand.Parameters.Add(New SqlParameter("Name", SqlDbType.NVarChar, 100))
MyCommand.Parameters("Name").Value = team1

Can someone please give me some insight?

Thanks,
Tehon

 

[Robby]

try this instead...
The follwing assume that (in the table)
- Date is a Date
- Name is a string
- Score is numeric
- Win is numeric

myConnection = New SqlConnection("server=(local);database=Pitch;Trusted_Connection=yes")
Dim recordsAffected As Integer
Dim InsertCmd As String = "insert into PitchRecord (Date, Name, Score, Win) values (#" & TodayDate & #", " & team1 & "," &  team1totpoints & " , 1)"

MyCommand = New SqlCommand(InsertCmd, myConnection)

recordsAffected = MyCommand.ExecuteNonQuery()

 

[tehon3299]

Hey -

I did that I my code is at the bottom but I had an error with the # so I took it out and it went away. Dont know if I did the right thing. Let me know...

            myConnection = New SqlConnection("server=(local);database=Pitch;Trusted_Connection=yes")

            Dim InsertCmd As String = "insert into PitchRecord (Date, Name, Score, Win) values (#" & TodayDate & ", " & team1 & "," & team1totPoints & " , 1)"

            MyCommand = New SqlCommand(InsertCmd, myConnection)

            MyCommand.Parameters.Add(New SqlParameter("TodayDate", SqlDbType.DateTime, 8))
            MyCommand.Parameters("TodayDate").Value = Now()

            MyCommand.Parameters.Add(New SqlParameter("Name", SqlDbType.NVarChar, 100))
            MyCommand.Parameters("Name").Value = team1

            MyCommand.Parameters.Add(New SqlParameter("Score", SqlDbType.NVarChar, 100))
            MyCommand.Parameters("Score").Value = txtTeam1total.Text

            MyCommand.Parameters.Add(New SqlParameter("Win", SqlDbType.Text, 3000))
            MyCommand.Parameters("Win").Value = 1

            MyCommand.Connection.Open()

            Try
                recordsAffected = MyCommand.ExecuteNonQuery()

            Catch Exp As SqlException
                If Exp.Number = 2627 Then

                Else

                End If


            End Try
            MyCommand.Connection.Close()

Thanks,
Tehon