send colors to sql server

send colors to sql server

Page 1 of 1

7 Replies - 971 Views - Last Post: 02 September 2009 - 12:43 AM Rate Topic: -----

#1 virgindayana  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 9
  • Joined: 08-August 09

send colors to sql server

Posted 20 August 2009 - 03:30 AM

I have 15 checkboxes in my vb.net form
they have been set by following code
  Private Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
		SetControlArray()
	End Sub
	Sub SetControlArray()
		checkarray(1) = CheckBox1
		checkarray(2) = CheckBox2
		checkarray(3) = CheckBox3
		checkarray(4) = CheckBox4
		checkarray(5) = CheckBox5
		checkarray(6) = CheckBox6
		checkarray(7) = CheckBox7
		checkarray(8) = CheckBox8
		checkarray(9) = CheckBox9
		checkarray(10) = CheckBox10
		checkarray(11) = CheckBox11
		checkarray(12) = CheckBox12
		checkarray(13) = CheckBox13
		checkarray(14) = CheckBox14
		checkarray(15) = CheckBox15
	End Sub




during runtime some of the checkboxes in control array are set backcolor by the colordialog using the following code


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

		For a = 1 To 15
			If ColorDialog1.ShowDialog() Then
				checkarray(a).BackColor = ColorDialog1.Color
				If ColorDialog1.ShowDialog = DialogResult.Cancel Then
					Exit For
				End If
			End If
		Next
	End Sub


My question is i want to send the colors filled in the check boxes into the sql server table throug insertsql statement. at the same time the name in the textbox24 is also added to the sql table. The table has 2 fields models and colors
if one model is inserted into the table cell and 5 colors are selected in the check boxes the table is fill with the name of the model is same and colors are different
my trial code is as follows but it is wrong
and only one row is inserted into the table
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
		Dim insertsq As String
		Dim a As Integer

		For a = 0 To 14

			insertsq = "insert into colordetails(models,colors)values(' " & TextBox24.Text & " ',' " & checkarray(a).BackColor.ToArgb & " ' )"

		Next

		Dim con As New SqlConnection(connectionstring)
		Dim cmd As New SqlCommand(insertsq, con)
		Dim added As Integer
		Try
			con.Open()
			added = cmd.ExecuteNonQuery
			MsgBox("successfully updated")

		Catch err As Exception
			MsgBox("Error in inserting")
			MsgBox(err.Message)
		Finally
			If (Not con Is Nothing) Then
				con.Close()
			End If
		End Try
	End Sub



Is This A Good Question/Topic? 2
  • +

Replies To: send colors to sql server

#2 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: send colors to sql server

Posted 20 August 2009 - 08:40 AM

Hi virgindayana

You almost got it, try this

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
		Try
			Dim insertsq As String
			Dim a As Integer

			Dim con As New SqlConnection(connectionstring)
			Dim cmd As New SqlCommand
			con.Open()
			cmd.Connection = con

			Dim added As Integer

			For a = 0 To 14

				insertsq = "insert into colordetails(models,colors)values(' " & TextBox24.Text & " ',' " & checkarray(a).BackColor.ToArgb & " ' )"
				cmd.CommandText = insertsq
				added = cmd.ExecuteNonQuery
			Next
			MsgBox("successfully updated")
			cmd.Dispose()
		Catch err As SqlException
			MsgBox("Error in inserting")
			MsgBox(err.Message)
		Finally
			If (Not con Is Nothing) Then
				con.Close()
				con.Dispose()
			End If
		End Try
	End Sub



Note what happened, in your for loop, once you set your insertsq = ... you need to execute it with the sqlCommand.
Was This Post Helpful? 1
  • +
  • -

#3 DataPriest  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 57
  • Joined: 29-April 09

Re: send colors to sql server

Posted 20 August 2009 - 09:48 AM

Hi virgindayana;

I have reconstructed part of your program with the code you supplied and made a few changes of my own which I hope you'll appreciate. I have also commented the code for your convenience so that you can pluck out the parts you need.

I have attached the archived code with this post. It's a small download (16k), it shouldn't take a minute.

NOTE: You have to change the Data source and the database names before you run.

Cheers.

Attached File(s)


This post has been edited by DataPriest: 21 August 2009 - 01:10 AM

Was This Post Helpful? 2
  • +
  • -

#5 DataPriest  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 57
  • Joined: 29-April 09

Re: send colors to sql server

Posted 21 August 2009 - 01:36 AM

Hi virgindayana;

Received your PM but I failed to PM you back. You should be able to open the file with Windows XP if not then try to download WinZip.

As per your request, I have put the code in Microsoft Word format.

NOTE: This code is in two parts, for the Form1.vb and Form1.Designer.vb modules.

Start a new project and replace the default code in the new project's modules respectively and you're good to go.

Hope this works for you :)

Attached File(s)


This post has been edited by DataPriest: 21 August 2009 - 01:43 AM

Was This Post Helpful? 2

#6 DataPriest  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 57
  • Joined: 29-April 09

Re: send colors to sql server

Posted 22 August 2009 - 01:07 AM

Hi virgindayana;

To read the colors back to the buttons from the SQL database use this code:

Private Sub ShowColor_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ShowColor.Click
		If Not GetColors(ComboBox1.SelectedItem) Then
			'What to do if the function fails.
		End If
	End Sub


	Private Function GetColors(ByVal sModel) As Boolean
		'Declare the Connection string.
		Dim ConnectionString As String = "Server=DATA\SQLEXPRESS;Database=DIC;Integrated Security = True"

		'Please note:
		'		   Change the Server name with your own server name.
		'		   Change the Database name with your own Database name.
		Try
			Dim SelectSQL As String = "SELECT colors FROM colordetails WHERE UPPER(models) = '" & sModel.ToUpper & "'"
			Dim conn As New SqlConnection
			Dim cmd As New SqlCommand

			conn = New SqlConnection(ConnectionString)
			conn.Open()

			cmd.Connection = conn
			cmd.CommandType = CommandType.Text
			cmd.CommandText = SelectSQL

			Dim reader As SqlDataReader = cmd.ExecuteReader(Commandbehavior.CloseConnection)

			If reader.HasRows Then
				Dim ButtonCount As Integer = 1
				While reader.Read()
					CheckArray(ButtonCount).BackColor = Color.FromArgb(reader("colors"))
					ButtonCount += 1
				End While
			Else
				MessageBox.Show("Model name not found.  Please try another model.", Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
			End If
		Catch err As Exception
			'If Update is a success, display an error message box.
			MessageBox.Show(err.Message, Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
		End Try
		Return True
	End Function 

Was This Post Helpful? 2
  • +
  • -

#7 DataPriest  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 57
  • Joined: 29-April 09

Re: send colors to sql server

Posted 27 August 2009 - 06:03 AM

Hi virgindayana;

If this is not the same form we have been working on all along and assuming that your buttons are named Button1 to Button10 and that your picture box is name PictureBox1 then all you need to do is this:

	Private Sub Buttonclick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles _
		Button1.Click, Button2.Click, Button3.Click, Button4.Click, Button5.Click, _
		Button6.Click, Button7.Click, Button8.Click, Button9.Click, Button10.Click
		Dim NewButton As Button = DirectCast(sender, Button)

		PictureBox1.BackColor = NewButton.BackColor
	End Sub


Good luck.
Was This Post Helpful? 2
  • +
  • -

#9 DataPriest  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 57
  • Joined: 29-April 09

Re: send colors to sql server

Posted 31 August 2009 - 02:57 AM

Hi virgindayana;

I'm very fine. This code will run through all the records found by the SqlDataReader down to the last one. Mind you, the records will be unsorted.

	Private Sub invoice_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		Dim selectsql As String = "SELECT * FROM customerdetails"
		Dim con As New SqlConnection(connectionstring)

		Try
			con.Open()
			Dim cmd As New SqlCommand(selectsql, con)
			Dim rd As SqlDataReader

			rd = cmd.ExecuteReader(Commandbehavior.CloseConnection)
			'The Commandbehavior.CloseConnection parameter will close the connection
			'soon after the query has been executed.

			While rd.HasRows
				rd.Read()
				TextBox3.Text = rd("ordno").ToString
				TextBox4.Text = rd("name").ToString
				TextBox5.Text = rd("street").ToString
				TextBox6.Text = rd("place").ToString
				TextBox7.Text = rd("city").ToString
				TextBox8.Text = rd("pin").ToString
				TextBox11.Text = rd("phone").ToString
				TextBox9.Text = rd("model").ToString
				TextBox10.Text = Format(rd("amt"), "#,##0.00").ToString
				Button1.BackColor = Color.FromArgb(rd("color")).ToString
			End While
			rd.Close()
		Catch ex As Exception
			MsgBox(ex.Message)
		End Try

		Call invno()

		TextBox2.Text = DateTimePicker1.Value.Date()

		DateTimePicker1.Hide()
	End Sub

This post has been edited by DataPriest: 31 August 2009 - 02:57 AM

Was This Post Helpful? 2
  • +
  • -

#11 DataPriest  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 57
  • Joined: 29-April 09

Re: send colors to sql server

Posted 02 September 2009 - 12:43 AM

Hi Virgindayana;

I was just wondering why you want a second form to appear while the first one is still in its load event.

Before you do that - not that I recommend it, have to know that the second form will appear first before the first loads. The first form only appears after the load event has been executed.

What I don't recommend:

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		Form2.ShowDialog()
	End Sub


I have worked on programs that required me to call other forms. For example, if you are doing something like an invoice. You may need to call a form that will show you a list of all your customers.

In such a case, you may want to use a button with code that calls the customers list form.

If you can drop me an email, then I think we can discuss your project in depth and see if there are better options to perform the tasks you need.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1