current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I' m trying to insert a row in a database residing on SQL Server. I'm using VB .NET and the ADO .NET framework. The structure of the table I want affect is shown below:

   CREATE TABLE [dbo].[Users](
  [UserName] [nvarchar](80) NOT NULL,
  [Description] [nvarchar](600) NULL,
  [IDCategory] [int] NULL,
  [RegistrationDate] [datetime] NOT NULL,
  [CategoryFlag] [bit] NULL,
  [TypeFlag] [bit] NULL,
   ...

I have implemented this, with the following code:

 <WebMethod()> _
   Sub insertElement()
     Dim conn As SqlConnection
     Dim sqlcmd As SqlCommand
     Dim res As Integer

     conn = New SqlConnection(Utilities.ConnectionString)
     sqlcmd = New SqlClient.SqlCommand()
     sqlcmd.Connection = conn
     sqlcmd.CommandType = CommandType.Text
     sqlcmd.CommandText = "insert into Users (UserName, RegistrationDate) values (@user, @date)"
     Dim param As SqlParameter
     param = New SqlParameter("@user", "myself")
     sqlcmd.Parameters.Add(param)
     param = New SqlParameter("@date", '2012/10/11 20:30:15')
     sqlcmd.Parameters.Add(param)

     Try
        conn.Open()
        res = sqlcmd.ExecuteNonQuery()
    Catch ex As Exception

    Finally
        sqlcmd.Dispose()
        conn.Close()
    End Try

Utilities.ConnectionString is just a class created by me for retuning the ConnectionString associated to my database.

I have tried to do something like this, without affecting any datatime filed and it worked. But with the above code there is probably a mistake in datatime management, because it does not work. Someone can help me?

share|improve this question
1  
so, what is wrong then...? What does or does not happen? –  gbn Aug 29 '12 at 13:54
    
then please edit your question and add that line –  sloth Aug 29 '12 at 13:55
    
The table "Users" on my database is not affected from the insert. But no error returns! –  Joseph82 Aug 29 '12 at 13:58
1  
What is the exception, anyway? Remove the catch block and please post the error message. –  sloth Aug 29 '12 at 14:00
add comment

1 Answer 1

up vote 1 down vote accepted

I don't see how this row 

param = New SqlParameter("@date", '2012/10/11 20:30:15')

does even compile, but I guess it should read

param = New SqlParameter("@date", CDate("2012/10/11 20:30:15", CultureInfo.InvariantCulture))

using a DateTime value instead of a string.

I think you just swallow the exception by using an empty catch block.

Nonetheless, you are better off using the using statement:

using conn = New SqlConnection(Utilities.ConnectionString), sqlcmd = New SqlClient.SqlCommand()
    sqlcmd.Connection = conn
    sqlcmd.CommandType = CommandType.Text
    ...
    res = sqlcmd.ExecuteNonQuery()
End Using

which will automatically close/dispose your connection etc.

share|improve this answer
    
I solved the problem: was related to a FOREIGN KEY CONSTRAINT. So the above code it is correct. @BigYellowCactus I don't now why but it is working using even the statement: param = New SqlParameter("@date", '2012/10/11 20:30:15') –  Joseph82 Aug 29 '12 at 14:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.