asked on

what value for parameter is missing?

there are no line continuation lines, because in my code page everything is on one line.
when I use a break on the line cmd.Connection = the holdstring has the entire connection string from above. When I f5 I jump to the error code and it tells me that "no value given for one or more required parameters"
'LOOK AT THIS LINE OR FUNCTION are just additions that I added for you guys!
what values for which parameters are missing from the cnnection string?

gary

Public Class DataBaseConnector
''' <summary>
''' Returns an OLEDB connection string to an Access 2010 database
''' </summary>
''' <returns>The OLEDB connection string to the Access 2010 database</returns>
Private Function GetConnectionString() As String

' Create the Connection string
Dim strConnection As String
'LOOK AT THIS LINE
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\Databases\oursystem.accdb;" & "User ID=Admin;Password=;"

' Return the Conntection string
GetConnectionString = strConnection

End Function ' End of: Private Function GetConnectionString() As String

''' <summary>
''' Allow the user to execute non-record returning queries
''' </summary>
''' <param name="SqlCode">The SQL Statement to execute against the database</param>
''' <returns>True if successful, otherwise False</returns>
Public Function RunSqlNonQuery(ByVal SqlCode As String) As Boolean
On Error GoTo HandleErrors

Dim bResult As Boolean
Dim cmd As OleDb.OleDbCommand

' Create the OLEDB Command object
cmd = New OleDb.OleDbCommand(SqlCode)

' Open the Connection
cmd.Connection = New OleDb.OleDbConnection(GetConnectionString())
cmd.Connection.Open()

' Execute the SQL Statement
cmd.ExecuteNonQuery()

' It looks like we've succeeded - return True
bResult = True

ExitFunction:

' Close the connection
If (Not IsNothing(cmd.Connection)) Then
If (cmd.Connection.State <> ConnectionState.Closed) Then
cmd.Connection.Close()
End If
End If

' Return the result and exit
RunSqlNonQuery = bResult
Exit Function

HandleErrors:

' Handle any errors here...
MsgBox("An error was raised!" & vbNewLine & "Message: " & Err.Description, MsgBoxStyle.Critical, "Error")
Err.Clear()
bResult = False ' Return failure
Resume ExitFunction

End Function ' End of: Public Function RunSqlNonQuery(SqlCode As String) As Boolean

'LOOK AT THIS FUNCTION
Public Function RunSqlSelectQuery(ByVal SqlCode As String) As String
Dim holdstring As String
On Error GoTo HandleErrors

Dim bResult As Boolean
Dim cmd As OleDb.OleDbCommand

' Create the OLEDB Command object
cmd = New OleDb.OleDbCommand(SqlCode)

' Open the Connection
holdstring = GetConnectionString() 'LOOK AT THIS LINE
cmd.Connection = New OleDb.OleDbConnection(holdstring)

cmd.Connection.Open()
' Execute the SQL Statement
Dim sqlResult As Object = cmd.ExecuteScalar()

ExitFunction:

' Close the connection
If (Not IsNothing(cmd.Connection)) Then
If (cmd.Connection.State <> ConnectionState.Closed) Then
cmd.Connection.Close()
End If
End If

' Return the result and exit
If sqlResult Is Nothing Then
Return "notfound" 'result not found
Else
Return sqlResult.ToString() 'result found
End If
Exit Function

HandleErrors:

' Handle any errors here...
MsgBox("An error was raised!" & vbNewLine & "Message: " & Err.Description, MsgBoxStyle.Critical, "Error")
Err.Clear()
Return "notfound" 'result not foundo
Resume ExitFunction

End Function ' End of: Public Function RunSqlSelectQuery(SqlCode As String) As Boolean
End Class

kaufmed

It would be helpful to see the SQL you are passing.

Jacques Bourgeois (James Burger)

LOOK AT THIS LINE always points to the Connection.

The error you receive is an error on the Command object, not the Connection.

You have a couple of Command object in there, and they all have a SqlCode statement defined somewhere else. You problem is in the definition of SqlCode.

javagair

ASKER

the line above where I typed LOOK AT THIS LINE is th only one in the function I am using that uses the sql statement. No error message is posted till I hit the line I indicated.
The following is the code that calls the function
THE SOC. SEC. # was replaced with my name since it is a text value. I have always used text values even for numbers and then did a conversion if I wanted to do math.
I can see only one call to the function and only one use of the byval parameter in the function.

gary

Public Class Form1
Dim useConnector As New DataBaseConnector
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strSql As String
Dim holdResult As String
strSql = ""
strSql = strSql & "Select employee_name from users where soc_sec = " & "gary"

' LOOK AT THIS LINE
holdResult = useConnector.RunSqlSelectQuery(strSql)
If holdResult = "notfound" Then
TextBox1.Text = "No Name Found"
Else
TextBox1.Text = holdResult
End If

End Sub
End Class

ASKER CERTIFIED SOLUTION

kaufmed

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

javagair

ASKER

kaufmed
I haven't done this kind of programming for ten years. I kept thinking there is something about single quotes that I used to do. Good catch!!!!!!!

Related question do I need to loop through the table or will it search the entire soc_sec column for gary? Even though the program now runs with no error it never finds a employee name with the soc_sec as gary.

thanks a million
gary

kaufmed

Because you are using ExecuteScalar you will only get the first row. If you want to get all of the matching rows, you will need to use the ExecuteReader method and then loop over the DataReader.

e.g.

Dim sqlResult As OleDb.OleDbDataReader = cmd.ExecuteReader()

While sqlResult.Read()
    Dim empName As String = sqlResult("employee_name").ToString()
End While

Open in new window

javagair

ASKER

worked only once, gave no answer.

now it get to line
Dim sqlResult As Object = cmd.ExecuteScalar()
and returns Nothing which throughs a new error.
How can the object be nothing when obviously gary is in the soc_sec position.

ran the query in access itself and it finds the right name.

gary

kaufmed

Are you certain that the connection string you are using is pointed to the database that you are manually querying?

Jacques Bourgeois (James Burger)

For the record, ExecuteScalar does not return the first row, it returns only one value, the first column of the first row. Since you are looking for only one value in one row, this is the proper thing to use.

What do you mean by "worked only once, gave no answer"? If it worked, there is an answer. Have you tried looking at cmd.ExecuteScalar in the debugger to see what the real result is, before VB tries its automatic conversion when assigning to the variable?

kaufmed

For the record, ExecuteScalar does not return the first row, it returns only one value, the first column of the first row.

Well, I s'pose if you want to be technical, yes. But since he's only selecting one column..."first row" and "first column of first row" are synonymous in this case, don't you think? ; )

Jacques Bourgeois (James Burger)

In that case yes.

But stating that ExecuteScalar returns the first row can be misleading for beggining programmers. As a trainer, I know that these little details can cause headaches to those who are learning.

javagair

ASKER

what I meant by working is that it went to completion without giving an error, but still gave a a nothing for a answer.

Given the last little discussion between the two of you, Does scalar actually give an answer or the column number where the answer is located?

gary

SOLUTION

kaufmed

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

javagair

ASKER

strSql = strSql & "Select employee_name from users where soc_sec = ' " & "544609764" & "'"

does not return any results

looking at the string as it is passed into the function:
"Select employee_name from users where soc_sec = '544609764' "
employee_name is a text and soc_sec is a text.
this soc_sec is in the very first record.

returns nothing.

so all problems stem from a sql statement that is wrong. I do not see what that is.

gary

SOLUTION

Jacques Bourgeois (James Burger)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer