Connection Property has not been initialized

[Lawrence 007]

Hi,

I am new to VB.Net and I am trying to create a program that inserts data into a SQL table. Below you will find my code that gives me the following error: Connection Property has not been initialized

I cannot see (from inexperience) where I am having the issue.

Many thanks!!!




Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Me.BillTableAdapter.Fill(Me.SenTestDataSet.Bill)
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand

Dim Conn = New SqlClient.SqlConnection
Conn.ConnectionString = "Data Source=[ServerName]; Initial Catalog=SenTest; password=""[Password]""; persist security info =True; user id=sa; workstation ID=Man1; Packet Size =4096"
Dim InsertQuery As String


daBill.SelectCommand = cmdSelectBill
daBill.UpdateCommand = cmdUpdateBill
daBill.InsertCommand = cmdInsertBill
daBill.DeleteCommand = cmdDeleteBill

daBill.SelectCommand.CommandText = "Select * from Bill"
daBill.SelectCommand.Connection = Conn


daBill.InsertCommand.CommandText = "Insert Into Bill(BillNumber, Description) Values (@BillNumber, @Description)"
daBill.InsertCommand.Connection = Conn

cmd.Parameters.Add("@BillNumber", SqlDbType.Char, 10, "@BillNumber")
cmd.Parameters.Item("@BillNumber").Value = TextBox1.Text

cmd.Parameters.Add("@Description", SqlDbType.Char, 1000, "@Description")
cmd.Parameters.Item("@Description").Value = TextBox2.Text

Conn.open()
cmd.Connection = Conn
InsertQuery = cmd.ExecuteNonQuery
Conn.Close()


End Sub

[kenobewan]

I believe the problem is the use of the conn & cmd connections, trying to combine the SQL queries into one executed script, opening one connection and trying to make the it equivalent to the other and execute that.

Try executing the scripts separately, opening and closing the connections separately (if you need separate connections). I suggest having another go and posting the result.

[Lawrence 007]

kenobewan,

Thanks for the help. I changed my code drastically on your recommendations and came up with the following that actually worked.

Private Sub AddProduct()

Dim cnSQL = New SqlClient.SqlConnection
cnSQL.ConnectionString = "Data Source=[ServerName]; Initial Catalog=SenTest; password=""[Password]""; persist security info =True; user id=sa; workstation ID=[computerName]; Packet Size =4096"
Dim cmSQL As SqlClient.SqlCommand
Dim strSQL As String

cnSQL.Open()

Try
' Build Insert statement to insert new product into the Bill table
strSQL = "Insert Bill Values (" & TextBox1.Text & "," & TextBox2.Text & " ," & TextBox3.Text & ")"


cmSQL = New SqlClient.SqlCommand(strSQL, cnSQL)
cmSQL.ExecuteNonQuery()

' Close and Clean up objects
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()

Catch Exp As SqlClient.SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try

Me.BillTableAdapter.Fill(Me.SenTestDataSet.Bill)

End Sub

And then in the end I just called the AddProduct sub on my button_click.

I am really new to this, and was struggling with it for a while now and you pointed me in the right direction.

Thank you very much!!!

[Lawrence 007]

I wonder if you could help me again. I am trying to get in a bit deeper and I am trying to swop TextBox3 with a combobox.

I am getting a new error that is saying: "Converting Varchar value 'ID' to a cloumn of data type int".

I am sure it is because I am not converting the value to int, but I have no idea how to do that.

Thanks!!

[kenobewan]

Glad that it worked :). For the latest I would try using the ComboBox.SelectedText property, rather than attempting to convert an integer value.

Hope that this helps.

[icetalks]

Dear ,

Can u please explain this statment to me .... am new .. excuse me .

Me.BillTableAdapter.Fill(Me.SenTestDataSet.Bill)


Thanx very much

[Lawrence 007]

IceTalks,

I ran into this a couple of times and being new to this myself my answer could be very limited. Here goes:

I am using Visual Studio 2005 and there is a lot of differences in this version and Visual Studio. The line that you are talking about is to fill a table adapter in Visual studio 2005. If that is the same as Visual studio am cannot say and I appologize, but I am also new.

Example: you have a datagrid on your application that is connected to a SQL database and you need to "get" your information from your Dataset into your datagrid, you will be using that line in either your button_click command or on your page load command to load the information into the datagrid.

The data Adapter also needs to be configured before it will work though. This I found very easy in Visual Studio 2005.

Hope this helps

[ProfSchiavo]

Dear ,

Can u please explain this statment to me .... am new .. excuse me .

Me.BillTableAdapter.Fill(Me.SenTestDataSet.Bill)


Thanx very much

If you mean explain what it is/means? Well, the TableAdapter is an object that processes SQL queries -- among other things -- and in this case, you're asking it to fill the "Bill" table in the"SenTest" dataset with the results of the SQL query deinfed for the Bill tableadapter -- probably something you set up when you first defined the data source, such as "Select * from Bills" or some such.

The table adapter most often is used to execute selections (from the host database, into your local working dataset), or updates (to the host database, to reflect changes you've made to your local dataset.)

I sure hope that's what you were asking.