Save date format to database

Question

I want to create a program that upload CSV file in DataGridView then after displaying the csv file I want all the data in datagridview to save in my database... The problem is the date format can't display as yyyy/MM/dd to database.

Error of this code

No default member found for type 'Date'.

Code

Dim sched_date As String

    Dim agent_name, campaign_name, team_name, schedule As Object
    For i As Integer = 0 To DataGridView1.Rows.Count - 1

        agent_name = DataGridView1.Rows(i).Cells(0).Value()
        campaign_name = DataGridView1.Rows(i).Cells(1).Value()
        team_name = DataGridView1.Rows(i).Cells(2).Value()
        sched_date = DataGridView1.Rows(i).Cells(3).Value("yyyy/MM/dd")
        schedule = DataGridView1.Rows(i).Cells(4).Value()



        query = " Select * from tbl_sched WHERE   agent_name = '" + agent_name + "'"
        mysql_connect(True)
        cmd = New MySqlCommand(query, SQLConnection)
        reader = cmd.ExecuteReader()

        If reader.Read() Then
            mysql_connect(False)
            query = "update tbl_sched set campaign_name='" + campaign_name + "',team_name='" + team_name + "',sched_date='" + sched_date + "',schedule='" + schedule + "' WHERE agent_name ='" + agent_name + "'"


            mysql_connect(True)
            cmd = New MySqlCommand(query, SQLConnection)
            cmd.Connection = SQLConnection
            cmd.CommandText = query
            cmd.ExecuteNonQuery()


        Else
            mysql_connect(False)
            query = "insert into tbl_sched (agent_name,campaign_name,team_name,sched_date,schedule) values ('" + agent_name + "','" + campaign_name + "','" + team_name + "','" + sched_date + "','" + schedule + "')"
            mysql_connect(True)
            cmd = New MySqlCommand(query, SQLConnection)
            cmd.Connection = SQLConnection
            cmd.CommandText = query
            cmd.ExecuteNonQuery()

        End If

    Next
    MsgBox("Successfully upload CSV file to Database")

Answer 1

A reworking of your code above could be the following

Dim sched_date As DateTime
Dim agent_name, campaign_name, team_name, schedule As String

mysql_connect(True/False) ' I don't know what happens here '

For i As Integer = 0 To DataGridView1.Rows.Count - 1

    agent_name = DataGridView1.Rows(i).Cells(0).Value.ToString()
    campaign_name = DataGridView1.Rows(i).Cells(1).Value.ToString()
    team_name = DataGridView1.Rows(i).Cells(2).Value.ToString()
    sched_date = Convert.ToDateTime(DataGridView1.Rows(i).Cells(3).Value)
    schedule = DataGridView1.Rows(i).Cells(4).Value().ToString()

    query = "update tbl_sched set campaign_name=@cname,team_name=@tname,sched_date=@sdate," + 
             "schedule=@sched WHERE agent_name =@aname"

    cmd = New MySqlCommand(query, SQLConnection)
    cmd.Connection = SQLConnection
    cmd.CommandText = query
    cmd.Parameters.AddWithValue("@cname",campaign_name)
    cmd.Parameters.AddWithValue("@tname",team_name)
    cmd.Parameters.AddWithValue("@sdate",sched_date)
    cmd.Parameters.AddWithValue("@sched", schedule)
    cmd.Parameters.AddWithValue("@aname", agent_name)
    Dim rowsAffected = cmd.ExecuteNonQuery()

    ' If the Update doesn't find a record then rowsAffected is zero and we could execute the insertion here
    if rowsAffected = 0 Then
        query = "insert into tbl_sched " + 
                "(agent_name,campaign_name,team_name,sched_date,schedule) " + 
                "values (@aname,@cname,@tname,@sdate,@sched)"
        cmd.CommandText = query
        cmd.ExecuteNonQuery()
    End If

Next
MsgBox("Successfully upload CSV file to Database")

First I have used specific data types for your variables. For the DateTime one I have forced the Value grid field to be converted to a DateTime variable and not to a string.

The second step is to remove the lookup for the agent_name. Simply executing the Update will return zero if no record has been found to update and so you could execute the insert (Depending on the number of agent already present this could save a lot of round-trip to the database). You could also look at the syntax INSERT INTO ... ON DUPLICATE used in this question

The third part is removing the string concatenation and replace it with a parameterized query. In this way no sql injection possible and the work to correctly format the parameter value for the underlying database table is passed to the framework code that handle the dates, strings and decimals with better knowledge.

A not to forget advantage of this approach is also the more understandable written sql strings.

(I have no idea what are the other database fields datatype, if needed the conversion should be applied also to other not strings columns)