Insert Statement

Hi Everyone

I am having difficulties with my first hand coded insert statement. The record inserts BUT the first item VALUE is selected for all the drop down lists ( 3 of them are optional) I have Prerenders on the page to insert a null value at the top of the list. For the dropdown that is mandatory it only enter first item. Thats without specifying type. As soon as I specify type I get - Input string not in correct format - doesn't actually tell me which one!!!!!! All char types match the database.

Here is my insert:

Sub Add_Click(sender as object, e as EventArgs)
Dim conBooks As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand
conBooks = New SqlConnection( System.Configuration.ConfigurationSettings.AppSettings("conFantasy") )
strInsert = "INSERT INTO dbo.Books (ISBN, BookTitle, AuthorID, AuthorType, author2, author3, Series, VolNo, Synopsis, PubYear, PubDate) VALUES (@.ISBN, @.BookTitle, @.AuthorID, @.AuthorType, @.author2, @.author3, @.Series, @.VolNo, @.Synopsis, @.PubYear, @.PubDate )"
cmdInsert = New SqlCommand( strInsert, conBooks )
' Add Parameters
cmdInsert.Parameters.Add( "@.ISBN", SqlDbType.nvarchar).Value = txtISBN.Text
cmdInsert.Parameters.Add( "@.BookTitle", SqlDbType.nvarchar).Value = title.Text
cmdInsert.Parameters.Add( "@.AuthorID", SqlDbType.int).Value = AuthorID.SelectedItem.Value
cmdInsert.Parameters.Add( "@.AuthorType", SqlDbType.char).Value = AuthorType.SelectedItem.Value
cmdInsert.Parameters.Add( "@.author2", SqlDbType.int).Value = author2.SelectedItem.Value
cmdInsert.Parameters.Add( "@.author3", SqlDbType.int).Value = Author3.SelectedItem.Value
cmdInsert.Parameters.Add( "@.Series", SqlDbType.nvarchar).Value = Series.SelectedItem.Value
 cmdInsert.Parameters.Add( "@.VolNo", SqlDbType.int).Value = volumeno.Text
cmdInsert.Parameters.Add( "@.Synopsis", SqlDbType.ntext).Value = txtSynopsis.Text
cmdInsert.Parameters.Add( "@.PubYear", SqlDbType.bigint).Value = pubyear.Text
cmdInsert.Parameters.Add( "@.PubDate", SqlDbType.nvarchar).Value = PubDate.Text
conBooks.Open()
cmdInsert.ExecuteNonQuery()
conBooks.Close()
Response.Redirect("/admin/default.aspx")
End Sub

You are passing strings to all as the value. Try, for instance:

cmdInsert.Parameters.Add( "@.AuthorID", SqlDbType.int).Value = int.parse(AuthorID.SelectedItem.Value)

|||hmmm

that particular statement gives me this error:

Overload resolution failed because no accessible 'Int' accepts this number of arguments.

??

Could it be because the optional fields have NULL instead of an int?|||I have updated the code as follows. There are no errors when I do an insert, however, for the optional fields there are zero's bring inserted instead of NULL?

Sub Add_Click(sender as object, e as EventArgs)

Dim conBooks As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand

conBooks = New SqlConnection( System.Configuration.ConfigurationSettings.AppSettings("conFantasy") )
strInsert = "INSERT INTO dbo.Books (ISBN, BookTitle, AuthorID, AuthorType, author2, author3, Series, VolNo, Synopsis, PubYear, PubDate) VALUES (@.ISBN, @.BookTitle, @.AuthorID, @.AuthorType, @.author2, @.author3, @.Series, @.VolNo, @.Synopsis, @.PubYear, @.PubDate )"

cmdInsert = New SqlCommand( strInsert, conBooks )

' Add Parameters
dim myParam1 as sqlParameter
myParam1 = cmdInsert.Parameters.Add("@.ISBN", SqlDbType.nvarchar)
myParam1.Value = txtISBN.Text

dim myParam2 as sqlParameter
myParam2 = cmdInsert.Parameters.Add("@.BookTitle", SqlDbType.nvarchar)
myParam2.Value = title.Text

dim myParam3 as sqlParameter
myParam3 = cmdInsert.Parameters.Add("@.AuthorID", SqlDbType.Int)
myParam3.Value = AuthorID.SelectedItem.Value

dim myParam4 as sqlParameter
myParam4 = cmdInsert.Parameters.Add("@.AuthorType", SqlDbType.char)
myParam4.Value = AuthorType.SelectedItem.Value

dim myParam5 as sqlParameter
myParam5 = cmdInsert.Parameters.Add("@.author2", author2.SelectedItem.Value)

dim myParam6 as sqlParameter
myParam6 = cmdInsert.Parameters.Add("@.author3", Author3.SelectedItem.Value)

dim myParam7 as sqlParameter
myParam7 = cmdInsert.Parameters.Add("@.Series", SqlDbType.nvarchar)
myParam7.Value = Series.SelectedItem.Value

dim myParam8 as sqlParameter
myParam8 = cmdInsert.Parameters.Add("@.VolNo", volumeno.Text)

dim myParam9 as sqlParameter
myParam9 = cmdInsert.Parameters.Add("@.Synopsis", SqlDbType.ntext)
myParam9.Value = txtSynopsis.Text

dim myParam10 as sqlParameter
myParam10 = cmdInsert.Parameters.Add("@.PubYear", SqlDbType.Int)
myParam10.Value = pubyear.Text

dim myParam11 as sqlParameter
myParam11 = cmdInsert.Parameters.Add("@.PubDate", SqlDbType.nvarchar)
myParam11.Value = PubDate.Text

conBooks.Open()
cmdInsert.ExecuteNonQuery()
conBooks.Close()

Response.Redirect("/admin/default.aspx")
End Sub|||Last message. I got it all working, however, I would love to have some comments on the cosing. If anything could be simplified or written better etc.

My insert statement:

Sub Add_Click(sender as object, e as EventArgs)

Dim conBooks As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand

conBooks = New SqlConnection( System.Configuration.ConfigurationSettings.AppSettings("conFantasy") )
strInsert = "INSERT INTO dbo.Books (ISBN, BookTitle, AuthorID, AuthorType, author2, author3, Series, VolNo, Synopsis, PubYear, PubDate) VALUES (@.ISBN, @.BookTitle, @.AuthorID, @.AuthorType, @.author2, @.author3, @.Series, @.VolNo, @.Synopsis, @.PubYear, @.PubDate )"

cmdInsert = New SqlCommand( strInsert, conBooks )

' Add Parameters
dim myParam1 as sqlParameter
myParam1 = cmdInsert.Parameters.Add("@.ISBN", SqlDbType.nvarchar)
myParam1.Value = txtISBN.Text

dim myParam2 as sqlParameter
myParam2 = cmdInsert.Parameters.Add("@.BookTitle", SqlDbType.nvarchar)
myParam2.Value = title.Text

dim myParam3 as sqlParameter
myParam3 = cmdInsert.Parameters.Add("@.AuthorID", SqlDbType.Int)
myParam3.Value = AuthorID.SelectedItem.Value

dim myParam4 as sqlParameter
myParam4 = cmdInsert.Parameters.Add("@.AuthorType", SqlDbType.char)
myParam4.Value = AuthorType.SelectedItem.Value

dim myParam5 as sqlParameter
If author2.SelectedItem.Value <> ""
myParam5 = cmdInsert.Parameters.Add("@.author2", Author2.SelectedItem.Value)
Else
myParam5 = cmdInsert.Parameters.Add("@.author2", DBNull.Value)
End If

dim myParam6 as sqlParameter
If Author3.SelectedItem.Value <> ""
myParam6 = cmdInsert.Parameters.Add("@.author3", Author3.SelectedItem.Value)
Else
myParam6 = cmdInsert.Parameters.Add("@.author3", DBNull.Value)
End If

dim myParam7 as sqlParameter
myParam7 = cmdInsert.Parameters.Add("@.Series", SqlDbType.nvarchar)
myParam7.Value = Series.SelectedItem.Value

dim myParam8 as sqlParameter
If volumeno.Text <> ""
myParam8 = cmdInsert.Parameters.Add("@.VolNo", volumeno.Text)
Else
myParam8 = cmdInsert.Parameters.Add("@.VolNo", DBNull.Value)
End If

dim myParam9 as sqlParameter
myParam9 = cmdInsert.Parameters.Add("@.Synopsis", SqlDbType.ntext)
myParam9.Value = txtSynopsis.Text

dim myParam10 as sqlParameter
myParam10 = cmdInsert.Parameters.Add("@.PubYear", SqlDbType.Int)
myParam10.Value = pubyear.Text

dim myParam11 as sqlParameter
myParam11 = cmdInsert.Parameters.Add("@.PubDate", SqlDbType.nvarchar)
myParam11.Value = PubDate.Text

conBooks.Open()
cmdInsert.ExecuteNonQuery()
conBooks.Close()

Response.Redirect("/admin/default.aspx")
End Sub

My Page_Load

Sub Page_Load(Src As Object, E As EventArgs)
If Not IsPostBack Then
Dim dsAuthors As New System.Data.DataSet
Dim dvwAuthors As Dataview
Dim conAuthors As New SqlConnection( System.Configuration.ConfigurationSettings.AppSettings("conFantasy") )
Dim dadAuthor As New SqlDataAdapter( "SELECT * FROM dbo.qryAuthors", conAuthors )

dadAuthor.Fill( dsAuthors, "Authors" )
dvwAuthors = dsAuthors.Tables( "Authors" ).DefaultView()

AuthorID.DataSource = dvwAuthors
AuthorID.DataTextfield = "Fullname"
AuthorID.DataValueField = "AuthorID"
AuthorID.DataBind()

Author2.DataSource = dvwAuthors
Author2.DataTextfield = "Fullname"
Author2.DataValueField = "AuthorID"
Author2.DataBind()

Author3.DataSource = dvwAuthors
Author3.DataTextfield = "Fullname"
Author3.DataValueField = "AuthorID"
Author3.DataBind()

Dim dsSeries As New System.Data.DataSet
Dim dvwSeries As Dataview
Dim conSeries As New SqlConnection( System.Configuration.ConfigurationSettings.AppSettings("conFantasy") )
Dim dadSeries As New SqlDataAdapter( "SELECT SeriesID, Series FROM dbo.tblSeries ORDER BY Series ASC", conSeries )

dadSeries.Fill( dsSeries, "Series" )
dvwSeries = dsSeries.Tables( "Series" ).DefaultView()

Series.DataSource = dvwSeries
Series.DataTextfield = "Series"
Series.DataValueField = "Series"
Series.DataBind()

Series.Items.Insert(0, New System.Web.UI.WebControls.ListItem("None","None"))
Author2.Items.Insert(0, New System.Web.UI.WebControls.ListItem("None", ""))
Author3.Items.Insert(0, New System.Web.UI.WebControls.ListItem("None", ""))
End If
End Sub

Regards

Nimmie