3 Connections are not working

[tiffany]

Hi, i come out with this codes but there are some error at the 3 connections. Bascially this codes are to increment the roomID in db. I had three SQL statments and created 3 connections.

If i command this line "dr = cmd.ExecuteReader()" they will show "The ConnectionString property has not been initialized." in this line "cmd2.Connection.Open()".
If i didn;t command this "dr = cmd.ExecuteReader()" it will said "No value given for one or more required parameters."

What are this two error msg means?


Dim z As Integer
Dim ID As Integer

For a = 1 To i

Dim strSQL1 As String = "Select Max(ID) as z from roomDet"

z += 1

Dim strSQL2 As String = "INSERT INTO roomDet ([roomID],[branch]) VALUES('" & ID & "','" & DropDownList1.SelectedValue & "')"

Dim strSQL3 As String = "UPDATE roomDet SET status = 'available' WHERE branch = '" + DropDownList1.SelectedValue + " ' "

Dim cmd As New OleDbCommand(strSQL1, cnn)

Dim cmd2 As New OleDbCommand(strSQL2, cnn)

Dim cmd3 As New OleDbCommand(strSQL3, cnn)

Dim dr As OleDbDataReader

cmd.Connection.Open()

'cmd.ExecuteNonQuery()

dr = cmd.ExecuteReader()

'cmd.ExecuteReader(CommandBehavior.CloseConnection)

cmd.Connection.Close()

cmd.Connection.Dispose()

cmd2.Connection.Open()

cmd2.ExecuteNonQuery()

cmd2.Connection.Close()

cmd3.Connection.Open()

cmd3.ExecuteNonQuery()

cmd3.Connection.Close()

Next


Regards
tiffany

 

[jmcilhinney]

If it says that the ConnectionString has not been initialised then it means you haven't created a connection string and assigned it to the connections ConnectionString property.

Apart from that, you have other issues.

You are using the same connection for all your commands, which is good but you are opening it and closing it three times, which is bad. You should just Open it once, call ExecuteReader, close the DataReader, call ExecuteNonQuery twice, then Close the connection.

You are calling ExecuteReader but you are not reading any data from it. Also, it would be better to call ExecuteScalar given that your query will only ever return a single value.

Finally, and worst of all, you are calling Dispose on your connection after executing the DataReader. Dispose destroys an object, so how are you then going to use that connection for the other two commands? Calling Dispose on an object is good practice, but only when you have no further use for it.

 

[tiffany]

Hi, thank for ur reply. I had tried u had advice me. But it still gave me "No value given for one or more required parameters". Codes i editted, but don't know whether are in teh right way.

Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("movie.mdb"))



If DropDownList1.SelectedIndex = 1 Then

Dim z As Integer

Dim ID As Integer

For a = 1 To i



Dim strSQL1 As String = "Select Max(ID) as z from roomDet"

z += 1

Dim strSQL2 As String = "INSERT INTO roomDet ([roomID],[branch]) VALUES('" & ID & "','" & DropDownList1.SelectedValue & "')"

Dim strSQL3 As String = "UPDATE roomDet SET status = 'available' WHERE branch = '" + DropDownList1.SelectedValue + " ' "

Dim cmd As New OleDbCommand(strSQL1, cnn)

Dim cmd2 As New OleDbCommand(strSQL2, cnn)

Dim cmd3 As New OleDbCommand(strSQL3, cnn)

Dim dr As OleDbDataReader

cmd.Connection.Open()

dr = cmd.ExecuteReader()

dr.Close()

cmd.ExecuteScalar()

cmd2.ExecuteNonQuery()

cmd3.ExecuteNonQuery()

cmd.Connection.Close()

Next

thnkx

 

[jmcilhinney]

You are still calling ExecuteReader and reading no data. Either execute the reader and read the data using Read OR call ExecuteScalar and use the return value. If your query returns a single value then a call to ExecuteScalar will return that value as an Object, which you must then cast as the correct type.

cnn.Open()

dr = cmd.ExecuteReader()
dr.Read()

Dim maxID as Integer = CInt(dr(0))

dr.Close()

cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
cnn.Close()

OR

cnn.Open()

 Dim maxID as Integer = CInt(cmd.ExecuteScalar())
 
 cmd2.ExecuteNonQuery()
 cmd3.ExecuteNonQuery()
cnn.Close()

As for the error you got, you need to tell us what line of code produced the error. Make these changes and see if that works. If you still get an error please let us know where exactly the error occurred. Also please put

 tags around your code to make it more readable. See my signature for details.

 

[TechGnome]

you may want to also MessageBox.Show the sql statements to make sure they are being built properly.

-tg

 

[jmcilhinney]

I just looked at your code a bit more closely and it dawned on me that you are trying to get the largest ID into the "z" variable, then increment it, then put that value into your Insert statement. You can't do that the way you are trying to because you haven't even retrieved the ID when you build the Insert statement. Also, your Select statement will retrieve the ID into a column named "z", but that is completely unrelated to the variable "z" in your code.

You need to retrieve the max ID value into a variable first, then increment it, then build your Insert statement, then insert the row.

Also, why are you inserting a record and then immediately updating it? Why not insert all the values you want using the Insert statement?

Finally, if the ID column is a number you should not be putting single quotes around its value in your Insert statement. Single quotes are for strings ONLY.