10 Replies - 2314 Views - Last Post: 11 October 2011 - 01:27 AM Rate Topic: -----

#1 menukadevinda  Icon User is offline

  • D.I.C Regular

Reputation: -7
  • View blog
  • Posts: 470
  • Joined: 14-April 11

saving data + vb.net + sql + insert not working

Posted 10 October 2011 - 10:12 PM

hi all,

I am newbie to vb.net. I am trying to save data to database
but I got a exception
here is the code

  Try 'connection is set
            sqlcon = New SqlConnection("Data Source=MENUKA-PC\SQLEXPRESS;Initial Catalog=studentInformation; Integrated Security=True")
            sqlcon.Open()



            sqlcommand = New SqlCommand() 'Once we have an instance of this object we need to set some of its properties
            sqlcommand.Connection = sqlcon
            ' You'll have to set the SqlCommand's "CommandText" property 
            'to be the SQL commands that need to be carried out by 
            'the function you're creating.
            sqlcommand.CommandType = CommandType.Text
            sqlcommand.CommandText = "INSERT INTO stuDetails(stuReg, stuFullName,stuInitials,stuAddress,stuContact1,stuContact2,stuBday,stuMale,stuFemale) VALUES(@stuReg, @stuFullName,@stuInitials,@stuAddress,@stuContact1,@stuContact2,@stuBday,@stuMale,@stuFemale)"
            ' sqlcommand.Parameters.Add("@cID", SqlDbType.Int).Value = 1234567
            sqlcommand.Parameters.Add("@stuReg", SqlDbType.VarChar).Value = txtRegNo.Text.ToString
            sqlcommand.Parameters.Add("@stuFullName", SqlDbType.VarChar).Value = txtNameFull.Text.ToString
            sqlcommand.Parameters.Add("@stuInitials", SqlDbType.VarChar).Value = txtNameInitals.Text.ToString
            sqlcommand.Parameters.Add("@stuAddress", SqlDbType.VarChar).Value = txtAddress.Text.ToString
            sqlcommand.Parameters.Add("@stuContact1", SqlDbType.VarChar).Value = txtContact1.Text.ToString
            sqlcommand.Parameters.Add("@stuContact2", SqlDbType.VarChar).Value = txtContact2.Text.ToString
            sqlcommand.Parameters.Add("@stuBday", SqlDbType.VarChar).Value = DateTimePicker1.Text.ToString
            sqlcommand.Parameters.Add("@stuMale", SqlDbType.VarChar).Value = rdbMale.Text.ToString
            sqlcommand.Parameters.Add("@stuFemale", SqlDbType.VarChar).Value = rdbFemale.Text.ToString
            sqlcommand.ExecuteNonQuery()
            MsgBox("Data submit succesfully")

            sqlcon.Close()

        Catch ex As Exception
            MsgBox("exceprion " + ex.Message)

        End Try



I think problem is in the radio buttons. radiobutton.value is not working in vb.net though worked in vb6
so I set rdbButton.text . I am not sure about that. How ever I got the following exception

"String or binary data would be truncated the statement has been terminated"

here is the database query
--create table stuDetails(
--id int identity  not null,
--stuReg varchar(7),
--stuFullName varchar(100),
--stuInitials varchar (50),
--stuAddress varchar (200),
--stuContact1 varchar (10),
--stuContact2 varchar (10),
--stuBday varchar (10),
--stuMale varchar (5),
--stuFemale varchar (5))



plz help me
thx in advance,
Menuka.

This post has been edited by menukadevinda: 10 October 2011 - 10:15 PM


Is This A Good Question/Topic? 0
  • +

Replies To: saving data + vb.net + sql + insert not working

#2 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Re: saving data + vb.net + sql + insert not working

Posted 10 October 2011 - 10:47 PM

Hi...

Make it like this


 Sql = "INSERT INTO stuDetails(stuReg, stuFullName,stuInitials,stuAddress,stuContact1,stuContact2,stuBday,stuMale,stuFemale) VALUES(@stuReg, @stuFullName,@stuInitials,@stuAddress,@stuContact1,@stuContact2,@stuBday,@stuMale,@stuFemale)"
        constring=....
        Con = New SqlConnection(constring)
        cmd = New SqlCommand(Sql, con)


        cmd.Parameters.AddWithValue("@stuReg", txtRegNo.Text)

        ......
        .....
        .....

        con.Open()
        cmd.ExecuteNonQuery()

        con.Close()
        cmd.Dispose()

        MsgBox("Successfully saved")




Was This Post Helpful? 0
  • +
  • -

#3 menukadevinda  Icon User is offline

  • D.I.C Regular

Reputation: -7
  • View blog
  • Posts: 470
  • Joined: 14-April 11

Re: saving data + vb.net + sql + insert not working

Posted 10 October 2011 - 11:18 PM

problem is not in the syntax. I remove radiobutton values and calender values from the sql query. then it works properly.
problem is in data types according to my conclusion

do you know the suitable datatype for radiobuttons and calender

thx in advacne,
menuka
Was This Post Helpful? 0
  • +
  • -

#4 menukadevinda  Icon User is offline

  • D.I.C Regular

Reputation: -7
  • View blog
  • Posts: 470
  • Joined: 14-April 11

Re: saving data + vb.net + sql + insert not working

Posted 11 October 2011 - 12:16 AM

for calender
dateTime is ok, date is not supporting. therefore date will save in this format 2011-10-11 00:00:00.000

I have to truncate time.
Was This Post Helpful? 0
  • +
  • -

#5 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: saving data + vb.net + sql + insert not working

Posted 11 October 2011 - 12:34 AM

View Postmenukadevinda, on 11 October 2011 - 12:03 PM, said:

do you know the suitable datatype for radiobuttons and calender

What do you want to get from radio buttons? very interesting:

Quote

--stuBday varchar (10),
--stuMale varchar (5),
--stuFemale varchar (5))
Can a student be male and female also?
This is bad designed database, you need only one field for gender, then you check before add into query like this:
 Dim gender As String
If rdbFemale.Checked Then
  gender = "Female"
Else If rdbMale.Checked Then
  gender = "male"
End If

Then since you have only one column, you add in your parameter the string value gender for either male or female
Was This Post Helpful? 1
  • +
  • -

#6 menukadevinda  Icon User is offline

  • D.I.C Regular

Reputation: -7
  • View blog
  • Posts: 470
  • Joined: 14-April 11

Re: saving data + vb.net + sql + insert not working

Posted 11 October 2011 - 12:34 AM

hi, I stil couldnt solve this radio button issue....
what is the data type for radio buttons,
can any body help???
Was This Post Helpful? 0
  • +
  • -

#7 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: saving data + vb.net + sql + insert not working

Posted 11 October 2011 - 12:54 AM

As I said above, there is no radio button data type in database, but what you are doing is saving the string according to what has been selected. Read my previous post...
Was This Post Helpful? 1
  • +
  • -

#8 menukadevinda  Icon User is offline

  • D.I.C Regular

Reputation: -7
  • View blog
  • Posts: 470
  • Joined: 14-April 11

Re: saving data + vb.net + sql + insert not working

Posted 11 October 2011 - 01:12 AM

thanks smohd... ya it is my fault..
Was This Post Helpful? 0
  • +
  • -

#9 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: saving data + vb.net + sql + insert not working

Posted 11 October 2011 - 01:16 AM

Dont worry, so it is working now?

This post has been edited by smohd: 11 October 2011 - 01:16 AM

Was This Post Helpful? 0
  • +
  • -

#10 menukadevinda  Icon User is offline

  • D.I.C Regular

Reputation: -7
  • View blog
  • Posts: 470
  • Joined: 14-April 11

Re: saving data + vb.net + sql + insert not working

Posted 11 October 2011 - 01:20 AM

yes of course. I was trying to find data type for radio buttons. :)
thanks again.
Was This Post Helpful? 0
  • +
  • -

#11 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: saving data + vb.net + sql + insert not working

Posted 11 October 2011 - 01:27 AM

Glad we could help :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1