[Aboch]

This code was taken from a tutorial by PsychoCoder, I have left it largely intact because I am trying to understand how it works.

 
Public Shared Function InsertNewRecord(ByVal item1 As String, ByVal item2 As String, ByVal item3 As String) As Boolean' I deleted this declaration because I get my variables somewhere else
		'Create the objects we need to insert a new record
		Dim cnInsert As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\core.mdf;Integrated Security=True;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 = "Insertuniqueuser"

		'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 if using inline sql
				'Tell it its a stored procedure
				.CommandType = CommandType.StoredProcedure 'CommandType.Text 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("@badge", NewBadge)' There was item1 in place of NewBadge variable
				.Parameters.AddWithValue("@Firstname", NewFirName)' There was item2 in place of Newfirname variable
				.Parameters.AddWithValue("@Lastname", NewLasName)' There was item3 in place of NewLasName variable
				.Parameters.AddWithValue("@Password", NewPassword)' I added this line
				.Parameters.AddWithValue("@perm", NewPerm)' I added this line
				'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, "Error")
		Finally
			'Now close the connection
			HandleConnection(cnInsert)
		End Try

	End Function

The Added variables are public and I get their values prior to this function.
I also deleted the declare of item1, 2 and 3 at the top since I was getting the value from another place....but lets face it I may not understand What is going on. The stored procedure works i checked that. If I change what I change when i run the function nothing Happens please take a look and see what i am missing or not understanding. thank you for your time. If I do not delete the Declaration at the top i get this error,

Error	6	Argument not specified for parameter 'item1' of 'Public Shared Function InsertNewRecord(item1 As String, item2 As String, item3 As String) As Boolean'.	C:\Documents and Settings\kschuett\My Documents\Visual Studio 2008\Projects\MAP1\MAP1\Newclass1.vb	183	9	MAP1

Respectfully
Aboch

[PsychoCoder]

You need to show us how you're calling this function. I think I know what's going on (I should have been more descriptive in the tutorial explaining this) but I need to see how you're using the function before saying

[Aboch]

Thank you for responding! Sorry it took me several days to respond I have been out on training. Here is what I was trying to use...

Private Sub NewClassCreateBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewClassCreateBut.Click


		NewBadge = "WC0555"
		NewFirName = "Doppy"
		NewLasName = "dwarf"
		NewPassword = "8888"
		NewPerm = "30"

		Call PC.DataAccess.InsertNewRecord()


	End Sub

The variables are public and I am hard coding them at this point to figure out how to make SQL and VB talk.
Also here is my stored procedure

ALTER PROCEDURE InsertUniqueUser  
@Badge varchar(6),  
@Firstname varchar(50),  
@LastName varchar(50),  
@password varchar(50),  
@perm int output
AS 
IF NOT EXISTS(SELECT Badge FROM Users WHERE Badge=@badge)  
 BEGIN  
 INSERT INTO Users  
 (Badge, Firstname, LastName, Password, perm)  
 VALUES (@Badge, @FirstName, @LastName, @Password, @perm)  
END  
  
 

The stored procedure is also from a tutorial and I may not understand what I think I understand and when i say it worked was that if I hard coded the values in the procedure it would add the person.

Thanks again for your help and the tutorial I hope someday I can understand because I have been kissing the concrete wall with my forehead for some time now.

[Aboch]

Please forgive the fact i am an idiot.....

Private Sub NewClassCreateBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewClassCreateBut.Click
		Dim Functiontest As String

		NewBadge = "WC0555"
		NewFirName = "Doppy"
		NewLasName = "dwarf"
		NewPassword = "8888"
		NewPerm = "30"

		Functiontest = PC.DataAccess.InsertNewRecord

		MsgBox(Functiontest)



	End Sub

I get a response of false so i still have a problem but i am at least running the function.

[Aboch]

Update: It is adding my new user somewhere because I generate a list when I start the debugger and if I don't restart vb the new user is in the list but I can not find it in the actual sql server or dataset.... and if I restart VB I lose it... I have read about this somewhere so ill look for the fix.

[Aboch]

Well i can not find where i read this prior any help would be greatly appreciated

[PsychoCoder]

Sorry for the delay, been kind of chaotic in my life lately. You're using SQLExpress and have attached a MDF file, right?

[Aboch]

PsychoCoder, on 15 Jun, 2009 - 11:18 AM, said:

Sorry for the delay, been kind of chaotic in my life lately. You're using SQLExpress and have attached a MDF file, right?

No problem I really appreciate your time. I am using SQL server 2008. I think it is express since it was free and I got it from the same place as the VB express. Um, I think I have attached the MDF it is in my project window and I manipulate it in the VB database explorer window....so maybe, sorry I am kinda slow on the uptake so I am not sure what you are asking. So I published the application to see if it would work there and sure enough everything works and the new user is in a list even if i restart the computer. However, past doing that I am not sure how to verify the SQL database is updating correctly. So in the short i think everything is right with the world if i can just confirm that the program is writing correctly so I can move on. Is there a way I can look at the MDF the published program creates. I can't find where the program is being installed. But i think it is all right with the world....

Thanks again.