Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Can memory variables in the vb program have the same name as field names in the tables?

I have Dimed all my variable names with the same names as my field names.  Now, when I try to write the record is gives me this error message.

The name admDate is not permitted in this context.  Only contraints, variables, or expressions are allowed here.  Column names are not permitted.

????????


Here is the code
For m4 = 1 To 60
                    objCommand2.CommandText = "Insert Into itemAnalysisDetail " _
                                & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentResp) " & _
                                "VALUES(admDate, studentId, teacherId, mathobj(m4), mathItemCorrectRespArray(m4), mathStudentRespArray(m4))"
                    objConnection2.Open()
                    objCommand2.ExecuteNonQuery()
                    objConnection2.Close()
                Next


Thanks
Kenny
0
kwh3856
Asked:
kwh3856
  • 4
  • 4
1 Solution
 
natlozCommented:
I don't think you can do this actually...you need to split out your string...the values from your code cannot be in the "" of the main string...you have to concatenate the values together....note single quotes needed for strings...I am assuming your Arrays and IDs are numeric...if not you will have to add in the single quotes.

objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentResp) " & _
"VALUES( '" & admDate & "'," & studentId & "," & teacherId "," &  readingElaobj(r4) & "," & readingElaItemCorrectRespArray(r4) & "," & readingElaStudentRespArray(r4) & ")"
0
 
kwh3856Author Commented:
Is there a better way to accomplish this task.  I just have a bunch of variables I want to save back down to the database.  Any other ideas?????

Thanks
Kenny
0
 
kwh3856Author Commented:
Thanks for the help.  If you know an easier way I sure would like to know.

Thanks
Kenny
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
natlozCommented:
This is pretty standard SQL string building in VB
If this is a SQL database...I find it much easier to create a stored procedure...then pass the values to the stored procedure, this way if  you have to make any changes to the the insert...you only need to change the stored procedure.
0
 
kwh3856Author Commented:
Can you point me in a direction where I can learn about stored procedures????

Thanks
Kenny
0
 
natlozCommented:
Here is a sample where a user logs into the system by passing values to a stored procedure in SQL...
The stored procedure is created using SQL Query Analyzer...

'************************************************
    'The user wants to attempt to login to the system
    '************************************************
    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        'Variables and objects
        Dim cDB As clsDBConn = New clsDBConn 'Connection Class
        Dim strConn As String = cDB.getStrConn 'Get connection string for database
        Dim oConn As New SqlConnection 'SQL connection object
        Dim intReturnID As Integer 'Return ID from Stored Procedures
        Dim oComm As SqlCommand 'SQL Command object
        Dim bFail As Boolean = False 'Test for failure

        'Validate form has data
        If Me.txtUsername.Text = "" Then
            MsgBox("Please enter a User Name.")
            Me.txtUsername.Focus()
            bFail = True
        Else
            If Me.txtPassword.Text = "" Then
                MsgBox("Please enter a Password.")
                Me.txtPassword.Focus()
                bFail = True
            End If
        End If

        If bFail = False Then 'All is well
            'Connect to SQL server and validate login
            Try
                'Pass connection string to SqlConnection
                oConn.ConnectionString = strConn

                'SQL Database statements
                oComm = New SqlCommand
                oComm.CommandType = CommandType.StoredProcedure
                oComm.CommandText = "spValidateLogin"
                oComm.Connection = oConn

                'Send in parameters
                oComm.Parameters.Add(New SqlParameter("@varUserName", SqlDbType.VarChar)).Value = Me.txtUsername.Text
                oComm.Parameters.Add(New SqlParameter("@varPassword", SqlDbType.VarChar)).Value = Me.txtPassword.Text

                'Return value from stored procedure
                Dim sParam As SqlParameter
                sParam = New SqlParameter
                sParam.ParameterName = "@ReturnID"
                sParam.SqlDbType = SqlDbType.Int
                sParam.Direction = ParameterDirection.Output
                oComm.Parameters.Add(sParam)

                'Open connection and execute oComm
                oConn.Open() 'Open connection
                oComm.ExecuteNonQuery() 'Execute stored procedure
                intReturnID = oComm.Parameters("@ReturnID").Value 'Get Return value
                oConn.Close() 'Close connection

                'Analyze Return value for errors
                If intReturnID < 0 Then 'All errors are less than 0
                    MsgBox("Invalid Login, please try again.")
                    Me.txtUsername.Focus()
                Else
                    Dim frmMain As frmMain = New frmMain
                    'Set the global variable for user logged in
                    _intUserID = intReturnID

                    'Open Main form and hide Login screen
                    frmMain.Show()
                    Me.Hide()
                End If

            Catch
                MsgBox("Error connecting to SQL Server")
            Finally
                oConn.Close() 'Close connection
            End Try
        End If
    End Sub


***************HERE IS THE STORED PROCEDURE*********
--Used to validate a user login...empty result is invalid
create proc spValidateLogin
     @varUsername varchar(25),
     @varPassword varchar(25),
     @ReturnID int output as
     
     SET NOCOUNT ON

     select * from tblUser where varUsername = @varUsername and varPassword = @varPassword

     if @@rowcount = 0 --if nothing came back from the query
          begin
               set @ReturnID = -3 --Invalid Login
          end
     else
          begin
               set @ReturnID = (select pkUserID from tblUser
                              where varUsername = @varUsername)
          end

     select @ReturnID
     return @ReturnID
GO
0
 
natlozCommented:
The Stored Procedure is not a VB.Net thing...it is a SQL Server thing...once you have created the Stored Procedure...you can call it from VB and Pass parameters to it.
0
 
kwh3856Author Commented:
Thanks...I think I understand...


Thanks
kenny
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now