• (3 Pages)
  • +
  • 1
  • 2
  • 3

SQL Basics In VB.Net Rate Topic: ***** 1 Votes

#16 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 08 September 2007 - 11:10 AM

I found that if you are not using stored procedures, you must change the line of code in the procedures to this...

CommandType = CommandType.Text



Also, if there is an error, such as I was getting before I changed the type from StoredProcedure to Text, you will get this error...

Conversion from string "Error Retrieving Data" to type 'Integer' is not valid.

On this line of code...

MsgBox(ex.Message, "Error Retrieving Data")


Was This Post Helpful? 0
  • +
  • -

#17 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1633
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Posted 08 September 2007 - 12:15 PM

You're right, I forgot to add the button types between the message and the title, should be

MsgBox("ex.Message,MsgBoxStyle.OkOnly,"Error receiving data")



How weird the IDE didnt catch it (or the compiler). Also, why arent you using stored procedures? They're much safer than inline SQL (no chance of a SQL Inject Attack) and they're compiled code, thus running faster.

Thanks for finding that for me anyways :)

This post has been edited by PsychoCoder: 08 September 2007 - 12:23 PM

Was This Post Helpful? 0
  • +
  • -

#18 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 08 September 2007 - 03:10 PM

I'm on that next, using stored procedures, just wanted to figure out the inline code way first.
Was This Post Helpful? 0
  • +
  • -

#19 karankochhar25  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 17-September 07

Posted 18 September 2007 - 01:17 AM

i want
Was This Post Helpful? 0
  • +
  • -

#20 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 24 September 2007 - 12:07 PM

Hey ya, I've been working on trying to use the stored procedures... they work good with Visual Studio's Datasets,... but when I try with your code example I get this error...

Incorrect Syntax Near 'InsertInvoiceItem'. in the Error Receiving Data message box window.

So I was wondering, is it just not finding my Stored Procedure, do I have to tell it where it is? Here's my code, LOL, or your code rather... :).

	Public Shared Function InsertNewInvoiceItemRecord(ByVal item1 As String, ByVal item2 As String, ByVal item3 As String, ByVal item4 As String, ByVal item5 As String, ByVal item6 As Double, ByVal item7 As Double, ByVal item8 As Double) As Boolean
		'If not using the Express Edition uncomment the next line
		'Dim cnInsert As New SqlConnection(GetConnectionString("YourConnName"))
		'If using Express Edition uncomment the next line
		Dim cnInsert As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\CustomerInventoryInvoice\ProductSales.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
		Dim cmdInsert As New SqlCommand
		Dim sSQL As New String("")
		Dim iSqlStatus As Integer

		'Set the stored procedure we're going to execute
		sSQL = "InsertInvoiceItem"

		'Inline sql needs to be structured like so
		'sSQL = "INSERT INTO YourTable(column1,column2,column3) VALUES('" & item1 & "','" & item2 & "','" & item3 & "')"

		'Clear any parameters
		cmdInsert.Parameters.Clear()
		Try
			'Set the SqlCommand Object Properties
			With cmdInsert
				'Tell it what to execute
				.CommandText = sSQL 'Your sql statement
				'Tell it its a stored procedure (if using inline sql uncomment this line
				'.CommandType = CommandType.StoredProcedure 'CommandType.Text for inline sql
				'If you arent using a stored procedure uncomment the next line
				'.CommandType = CommandType.StoredProcedure 'For inline sql
				'If you are indeed using a stored procedure
				'the next 3 lines pertain to you
				'Now add the parameters to our procedure
				'NOTE: Replace @value1.... with your parameter names in your stored procedure
				'and add all your parameters in this fashion
				.Parameters.AddWithValue("@invoice_item_invoice_id", item1)
				.Parameters.AddWithValue("@invoice_item_inventory_id", item2)
				.Parameters.AddWithValue("@invoice_item_inventory_product_id", item3)
				.Parameters.AddWithValue("@invoice_item_inventory_name", item4)
				.Parameters.AddWithValue("@invoice_item_inventory_description", item5)
				.Parameters.AddWithValue("@invoice_item_qty", item6)
				.Parameters.AddWithValue("@invoice_item_cost", item7)
				.Parameters.AddWithValue("@invoice_item_total", item8)
				'Set the connection of the object
				.Connection = cnInsert
			End With

			'Now take care of the connection
			HandleConnection(cnInsert)

			'Set the iSqlStatus to the ExecuteNonQuery 
			'status of the insert (0 = success, 1 = failed)
			iSqlStatus = cmdInsert.ExecuteNonQuery

			'Now check the status
			If Not iSqlStatus = 0 Then
				'DO your failed messaging here
				Return False
			Else
				'Do your success work here
				Return True
			End If
		Catch ex As Exception
			MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error receiving data")
		Finally
			'Now close the connection
			HandleConnection(cnInsert)
		End Try
	End Function


Was This Post Helpful? 0
  • +
  • -

#21 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 24 September 2007 - 12:42 PM

Never mind, I'm retarded... I forgot to uncomment out the line that tells it to use a stored procedure.
Was This Post Helpful? 0
  • +
  • -

#22 rakyomin  Icon User is offline

  • D.I.C Head

Reputation: 12
  • View blog
  • Posts: 77
  • Joined: 12-September 08

Posted 28 October 2008 - 07:29 AM

For VB.net 2008 to add the app.config.

Click on Project > [Project Name] properties > choose Settings
Name: [name any name you want] example DBList
Type: [Connection String]
Scope: Application
Value: Click on "..." button, a dialog will popup, in the dialog there is a box known as Data Source you may see
1. Microsoft Access Database File
2. Microsoft SQL server Compact 3.5
3. Microsoft SQL server database file

For my project I chose the 1st one.
after you have chosen your selection, click "continue"
Under the Database file name box there is a browse button, browse and choose your database file
Was This Post Helpful? 0
  • +
  • -

#23 morke  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 21-January 09

Posted 21 January 2009 - 05:48 AM

Hi,
Zip file still seems to be corrupt. Winzip reports that the 'start of central directory not found"

Regards
Morke
Was This Post Helpful? 0
  • +
  • -

#24 Vortex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 07-February 09

Posted 07 February 2009 - 07:39 AM

Your tutorial is a great help. I would like to use the stored procedure method, however, never written a stored procedure before. I see under the SQL 2005 management where I can add a proceedure, looks easy enought, yet I don't have clue of the syntax I should use. Is it possible to see a sample or is there one of the many already there that would work for simply adding/updating data to an existing table?
Was This Post Helpful? 0
  • +
  • -

#25 The_Boy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 24-February 09

Posted 24 February 2009 - 05:33 AM

Hi there PsychoCoder!

This was an excellent post hat's off was very helpful

demonstrates inline and stored procedure's use

excelent use of open connection

The_Boy
Was This Post Helpful? 0
  • +
  • -

#26 The_Boy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 24-February 09

Posted 25 February 2009 - 05:34 AM

Hi there PsycoCoder,

When handeling the iSqlStatus it is always 1, Now in someother post they metion this is
true when calling/using a stored procedure pl/Sql. Now I should mention the stored procedure im
using is adding the data the the table.

any thoughts?
or should I move on and except this?

The_Boy
Was This Post Helpful? 0
  • +
  • -

#27 dwains  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 46
  • Joined: 27-December 08

Posted 04 April 2009 - 04:34 PM

The zip file is corrupt for dataaccess I tried twice anybody have the zipfile that I can download? Thanks Dwain
Was This Post Helpful? 0
  • +
  • -

#28 Aboch  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 2
  • View blog
  • Posts: 48
  • Joined: 28-April 09

Posted 04 May 2009 - 12:31 PM

sSQL = "INSERT INTO YourTable(column1,column2,column3) VALUES('" & item1 & "','" & item2 & "','" & item3 & "')"




			.Parameters.AddWithValue("@value1", item1)
			.Parameters.AddWithValue("@value2", item2)
			.Parameters.AddWithValue("@YourID", id)
			'Set the connection of the object




Is the "item1" variable the information you are placing in the Database?

or is that something else.

Respectfully

Aboch
Was This Post Helpful? 0
  • +
  • -

#29 LEMarshall  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 04-May 09

Posted 24 May 2009 - 04:10 PM

Using your example, I tried this and

	 Public Shared Function GetRecords() As BindingSource
		'The value that will be passed to the Command Object (this is a stored procedure)
		Dim sSQL As String
		'If using inline sql format is as such
		sSQL = "SELECT ecoLease.Name, ecoCostCenter.CCName, ecoCostCenter.CCCode, " & _
				"ecoWell.WellNumber FROM ecoCostCenter INNER JOIN " & _
				"(ecoLease INNER JOIN ecoWell ON " & _
				"ecoLease.ecoLeaseID = ecoWell.ecoLeaseID) " & _
				"ON ecoCostCenter.ecoCostCenterID = ecoWell.ecoCostCenterID;"
		'If not using the Express Edition uncomment the next line
		Dim cnGetRecords As New SqlConnection(GetConnectionString("Ecologics"))
		'SqlConnection Object to use
		Dim cmdGetRecords As New SqlCommand()
		'SqlCommand Object to use
		Dim daGetRecords As New SqlDataAdapter()
		Dim dsGetRecords As New DataSet()
		'Clear any parameters
		cmdGetRecords.Parameters.Clear()
		Try
			With cmdGetRecords
				'set the SqlCommand Object Parameters
				.CommandText = sSQL
				'Tell it its inline sql 
				.CommandType = CommandType.Text 'for inline sql
				'Set the Connection for the Command Object
				'.Connection = cnGetRecords
				.Connection = cnGetRecords
			End With
			'set the state of the SqlConnection Object
			HandleConnection(cnGetRecords)
			'create BindingSource to return for our DataGrid Control
			Dim oBindingSource As BindingSource = GetBindingSource(cmdGetRecords)
			'now check to make sure a BindingSource was returned
			If Not oBindingSource Is Nothing Then
				'return the binding source to the calling method
				Return oBindingSource
			Else
				'no binding source was returned
				'let the user know the error
				Throw New Exception("There was no BindingSource returned")
				Return Nothing
			End If
		Catch ex As Exception
			MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error Retrieving Data")
			Return Nothing
		Finally
			HandleConnection(cnGetRecords)
		End Try
	End Function
  
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
		GetConnectionString("EcoLogics")
		GetBindingSource(GetRecords)
		GetRecords()
		LoadJanusGridEx()
	End Sub 


I get an error on the line "GetBindingSource(GetRecords)" which says:
Value of type 'System.Windows.Forms.BindingSource' can not be converted to 'System.Data.SqlClient.SqlCommand.'
I've spent about 2 hours now going through MSDN and just getting more confused than ever. Doesn't seem to make any sense to me. I'm certain it may just be a reference problem but can't figure outr what.

Any assistance is greatly appreciated,
Larry
Was This Post Helpful? 0
  • +
  • -

#30 masikak  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 31-July 09

Posted 31 July 2009 - 07:22 AM

Hi,

thanks for your content, now..I am developing an appilication in vb 2005 and I tried using your example above and this is what I got (as attached or as below)...any advice?

Attached Image
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3