Converting an int value to a string not working

Question

I have successfully created a timetable which displays the weekly classes for a student and staff member using session variables for each; StudentId and StaffId respectively. However I want to display a timetable for a module in the same way and so I have used a cascading dropdown list to allow the user to select a course first and then show the available modules by using onSelectedIndexChange (Fig.1)

The dropdown list is called ddlModule and so when the user selects a module they are choosing a ModuleId. I want this value ddlModuleId to be then passed into the query which is defined in App_Code/DBAccess.cs shown in Fig.2.

I have tried this string conversion but it throws an error. (Fig.3)

string module_ID = Convert.ToString(ddlModule.text);

I have hardcoded

string Module_ID = "6";

to show how it should look when the module is selected

Fig 1. - ddlModule_SelectedIndexChanged

protected void ddlModule_SelectedIndexChanged(object sender, EventArgs e)
        {
            ddlModule.Items.Clear();

            ddlModule.AppendDataBoundItems = true;
            String strConnString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            String strQuery = "select ModuleId, ModuleCode from Module where CourseId=@CourseId";
            SqlConnection con = new SqlConnection(strConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.AddWithValue("@CourseId", ddlCourse.SelectedItem.Value);
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strQuery;
            cmd.Connection = con;
            try
            {
                con.Open();
                ddlModule.DataSource = cmd.ExecuteReader();
                ddlModule.DataTextField = "ModuleCode";
                ddlModule.DataValueField = "ModuleId";
                ddlModule.DataBind();
                if (ddlModule.Items.Count > 1)
                {
                    ddlModule.Enabled = true;
                }
                else
                {
                    ddlModule.Enabled = false;

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

        }

Fig 2. - DBAccess.cs

  public String[] getModulesAtCurrentSlot(int timeslotInt, String moduleID, String Day)
        {
            List<String> modulesList = new List<string>();
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }
            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "SELECT DISTINCT Module.ModuleCode,ClassType.ClassTypeName,Convert(time,Class.StartTime), Convert(time,Class.EndTime),Building.BuildingName,RoomCode.RoomCode,Class.Color" +
                                " FROM Class INNER JOIN Module ON Class.ModuleId = Module.ModuleId INNER JOIN RoomCode ON Class.RoomCodeId = RoomCode.RoomcodeId INNER JOIN Building ON RoomCode.BuildingId = Building.BuildingId INNER JOIN Days ON Class.DayId = Days.DayID INNER JOIN ClassType ON Class.ClassTypeId = ClassType.ClassTypeId WHERE " +
                                " Module.ModuleId = " + moduleID + " AND Convert(Date,StartTime) = '" + Day + "' AND ClassScheduleStartTimeId = " + timeslotInt.ToString();
            SqlDataReader dr = newCmd.ExecuteReader();
            while (dr.Read())
            {
                //Module.ModuleCode,ClassType.ClassTypeName,Convert(time,Class.StartTime), Convert(time,Class.EndTime),Building.BuildingName,RoomCode.RoomCode,Class.Color
                String current = "<div class='slot'>";
                current += "<div class='line1'>" + dr.GetString(0) + "&nbsp;" + dr.GetString(1) + "</div>";// +"<br />";
                current += "<div class='line2'>" + dr.GetTimeSpan(2).ToString().TrimEnd('0').TrimEnd('0').TrimEnd(':') + "&nbsp;-&nbsp;" + dr.GetTimeSpan(3).ToString().TrimEnd('0').TrimEnd('0').TrimEnd(':') + "</div>";// +"<br />";
                current += "<div class='line3'>" + dr.GetString(4) + "&nbsp;" + dr.GetString(5) + "</div>";
                current += "</div>";
                modulesList.Add(current);
            }
            conn.Close();
            return modulesList.ToArray();
        }

Fig.3 - Error thrown

Fig.4 - Using ModuleId = "6"

The error message is:

Incorrect syntax near '='.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '='.

Source Error: 


Line 117:                                " FROM Class INNER JOIN Module ON Class.ModuleId = Module.ModuleId INNER JOIN RoomCode ON Class.RoomCodeId = RoomCode.RoomcodeId INNER JOIN Building ON RoomCode.BuildingId = Building.BuildingId INNER JOIN Days ON Class.DayId = Days.DayID INNER JOIN ClassType ON Class.ClassTypeId = ClassType.ClassTypeId WHERE " +
Line 118:                                " Module.ModuleId = " + moduleID + " AND Convert(Date,StartTime) = '" + Day + "' AND ClassScheduleStartTimeId = " + timeslotInt.ToString();
Line 119:            SqlDataReader dr = newCmd.ExecuteReader();
Line 120:            while (dr.Read())
Line 121:            {

Source File: c:\Users\40104198\Desktop\CIT\Qsis\DBAccess.cs    Line: 119 

Stack Trace: 


[SqlException (0x80131904): Incorrect syntax near '='.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2436598
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5731392
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +628
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +3731
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +58
   System.Data.SqlClient.SqlDataReader.get_MetaData() +89
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +379
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2026
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +375
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +240
   System.Data.SqlClient.SqlCommand.ExecuteReader() +99
   Qsis.DBAccess.getModulesAtCurrentSlot(Int32 timeslotInt, String moduleID, String Day) in c:\Users\40104198\Desktop\CIT\Qsis\DBAccess.cs:119
   Qsis.Students.ModuleTimetable.Page_Load(Object sender, EventArgs e) in c:\Users\40104198\Desktop\CIT\Qsis\Students\ModuleTimetable.aspx.cs:140
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51
   System.Web.UI.Control.OnLoad(EventArgs e) +95
   System.Web.UI.Control.LoadRecursive() +59
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2952

Please consider shrinking down your code to a [minimal, complete, verifiable example](http://stackoverflow.com/help/mcve) — Psi, Mar 12 '17 at 14:20
`string Module_ID = "6"` needs a `;` at the end (Doubt this is causing the actual problem). — stelioslogothetis, Mar 12 '17 at 14:22
From the error in the screenshot, it doesn't seem like it has anything to do with the conversion but rather an invalid syntax in your SQL query. — Abion47, Mar 12 '17 at 14:23
@dcraven: The error is coming from SQL. What's the actual runtime value of the query being executed? (Note that you really should be using query parameters, both to avoid SQL injection as well as to not have syntax problems from dynamically built SQL code like you're having right now.) — David, Mar 12 '17 at 14:25
This doesn't directly answer your question, but I would consider using parameters, rather than trying to bake the parameters into the query text. Amongst other benefits, it might also help with the problem that you're seeing (or at least make spotting the issue easier). — Paul Michaels, Mar 12 '17 at 14:26
Thanks guys I will use parameters after i solve this issue , thanks — dcraven, Mar 12 '17 at 14:29
DropDownLists have a `Text` property (uppercase), not a `text` property. — Crowcoder, Mar 12 '17 at 14:29
Side note: Your `catch` block is not only unnecessary, it's actually detrimental. You might as well remove it entirely. — David, Mar 12 '17 at 14:29
Funny thing is that he uses parameters on `ddlModule_SelectedIndexChanged` but not on the query that's causing errors. — GGG, Mar 12 '17 at 14:29
@dcraven: `"I will use parameters after i solve this issue"` - Or you could potentially solve this issue *by* using parameters. "I'll do it right after I figure out how to do it wrong" is kind of self-defeating. — David, Mar 12 '17 at 14:30
In a another page I use a session variable to capture the Studentid for their timetable but in this case Im not using a session for module String student_id = "anything"; student_id = Session["StudentId"].ToString(); — dcraven, Mar 12 '17 at 14:30
@David In a previous page I use this code and needed the catch to catch the exceptions, i guess in this case I dont require it. Thanks :) — dcraven, Mar 12 '17 at 14:34
@dcraven Seeming that if you use a static value it works properly, instead of investigating the query generation, I would investigate where and how you are retrieving the `moduleID` instead, since it seems to be the cause of your problems. I'd also use Visual Studio's analyzer to check the value of the query to see the exact query being generated. — GGG, Mar 12 '17 at 14:35
@dcraven Did you try to debug the code and see what value is being assigned to `newCmd.CommandText` ? You can copy that value and run directly against your database and see what is the actual SQL error. You can share that query here too. — Chetan, Mar 12 '17 at 14:44
On ddlModule_SelectedIndexChanged I use ddlModule.DataValueField = "ModuleId" , so ModuleId is an int value so when my query runs in App_Data its passing the values as strings so I believe I just need to do a conversion from int to string? Im quite new to programming so go easy :P — dcraven, Mar 12 '17 at 14:45
Can you post your schema (at least for the tables involved here)? — lesscode, Mar 12 '17 at 14:47
You should try `string module_ID = Convert.ToString(ddlModule.SelectedValue);` SelectedValue of ddlModule will return the ModuleID which is I think expected value in the SQL Query. — Chetan, Mar 12 '17 at 14:50
@dcraven: `"so I believe I just need to ..."` - Stop *believing* and start *debugging*. In Visual Studio place debugging breakpoints in the code and examine the actual runtime values of the variables. Specifically the SQL query that's failing, the variables used to build that query (which *really should* be query parameters instead, likely making this problem moot), where those values came from, etc. You keep *assuming* that things *should* work based on what you think *should* be happening. But clearly it's *not* working, so your assumptions are incorrect. — David, Mar 12 '17 at 14:51
Few suggestions Create a breakpoint (F9 in visual studio) on line 119 of DBAccess.cs to see what the value for moduleID is A lot of times, it ends up being another variable that you are not looking at. Have you verified that timeslotInt is not NULL? — ullfindsmit, Mar 12 '17 at 15:41
I did put a breakpoint but it throws the error immediately. I put one at the string module_ID = Convert.ToString(ddlModule.text); and it shows the value is Null. The query executes on page load so as the ModuleId has not been selected from the ddlModule would this be the core issue of the problem? Screenshot attached ! — dcraven, Mar 12 '17 at 16:01
You put the breakpoint on the line that assigns a value to `module_ID`, so that line hasn't been executed yet. Of course it's going to be null. What is the value of `ddlModule.SelectedValue` at that point? — Abion47, Mar 13 '17 at 16:15

score -1 · Answer 1 · answered Mar 12 '17 at 16:18

I was able to pass the ModuleId through a query string on the url so Im going to have a page which the user selects a module then it passing in the ModuleId selected through a query string. I would have liked it all on the one page though but this is a compromise.

 String module_ID = "2";
    if (Request.QueryString["module"] != null)
    {
        module_ID = Request.QueryString["module"];
    }else{
        Response.Write("Missing ?module=XX from url :(");
        Response.End();// EndRequest;
    }

Converting an int value to a string not working

1 Answers1