Solved

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

Posted on 2004-09-03
8
278 Views
Last Modified: 2010-04-23
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
Comment
Question by:kwh3856
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 7

Accepted Solution

by:
natloz earned 500 total points
ID: 11976997
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
 

Author Comment

by:kwh3856
ID: 11977030
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
 

Author Comment

by:kwh3856
ID: 11977106
Thanks for the help.  If you know an easier way I sure would like to know.

Thanks
Kenny
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 7

Expert Comment

by:natloz
ID: 11977115
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
 

Author Comment

by:kwh3856
ID: 11977142
Can you point me in a direction where I can learn about stored procedures????

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
ID: 11977167
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
 
LVL 7

Expert Comment

by:natloz
ID: 11977180
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
 

Author Comment

by:kwh3856
ID: 11977190
Thanks...I think I understand...


Thanks
kenny
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question